Updating a table/File thru Logical/View

Posted on 2007-10-18
Last Modified: 2012-08-13
I asked this question on the sql server forum since we're coding against as400 and sql server. I like to know the answer with AS400 as well:

we have a table with 422276 rows. We could have less rows or even more but in one database, it's that many. There is one  field to update in that table:
update table set field='ppp' where field1='lll' and field2='nnn'

That can be using the table. My coworker created a Logical (view in sql server):
create view myView as select <all fields> from table where field1='lll' and field2='nnn'

*** and to update that table he uses the View: update MyView set field='ppp' where dateField > '2007-04-04'

*** Is there any benefit to using a view/logical to do this update?? He has the update in a stored proc and the stored proc uses the view/logical to update the table. I see it as unnecessary.
Question by:Camillia
    LVL 3

    Accepted Solution

    There is no performance advantage (or disadvantage) to the view, but it does mean his update statement doesn't have to have the [field1='lll' and field2='nnn'] in it. Personal preference... I see that as a downside, but others would like that.

    An index might be helpful here if that update was run often, but 250K rows is relatively small. If this happens rarely I'd just let the 400 hammer the table. There's a tradeoff, reading the rows would be quicker, but the system would have to maintain that index. There's never a _need_ for an index in SQL. Indicies are all about performance, not functionality.

    As for the stored proc, again it's preference. The way he's done it the caller of the stored proc could remain unchanged but the stored proc could be updated (to change the date for example) and again, the stored proc could remain unchanged but the view could be changed (to select fields where field2='xxx' for example)

    If this is a one-off run all this is silly and should just be run from STRSQL. If this is something that happens regularly then the structure is sound but over-engineered, personal preference would be to tear it down and use something simpler.

    LVL 7

    Author Comment

    I dont think he needs a logical to update a table. At least not in SQL Srver (i'm more familiar with sql server). There are 2 logicals on the table.
    LVL 18

    Assisted Solution


    There is neither a need nor a benefit to updating via the viewif you could just as easily update the table.

    But, that being said, it's certainly not detrimental, either.

    LVL 3

    Expert Comment

    "...I dont think he needs a logical to update a table..."

    I can promise you he does NOT _need_ a logical to do the update. It's solely a performance thing.

    LVL 7

    Author Comment

    >> it's certainly not detrimental, either.

    I agree but why make it complicated. I just dont get the programmers here at my work. Everything has to be so difficult because " this is my style" !

    They dont believe in K.I.S.S. method i guess.
    LVL 26

    Assisted Solution

    Minor note... the "concept" of updating through a view is not exactly the same as the specifics of this particular situation. It certainly looks as if _this_ situation has no need to use a view. However, in concept, the use of a view can provide an abstraction layer. I see no evidence of that here, but perhaps the WHERE clause of the view is changed under changing circumstances by some other process that we don't see here.


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now