[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

Updating a table/File thru Logical/View

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.
3 Solutions
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.

CamilliaAuthor Commented:
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.
Dave FordSoftware Developer / Database AdministratorCommented:

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.

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

"...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.

CamilliaAuthor Commented:
>> 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.
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now