April 6, 2005 at 12:33 am
I am currently involved in planning an overhaul of our database.
We are trying to move from applications performing direct DML statements to having the apps call procedures only. I understand the benefits of this approach, but I am specifically curious about update procedures.
If a table has 80 columns, should my update procedure have 80 parameters? It seems inappropriate to update every column for a record if you only need to update 5. But then there are occasions where 8, 10, or more columns need to be updated. How can one procedure handle all the variations?
Should I create one procedure for each column update? This also seems counter intuitive as it goes against the optimizing strategy of updating a record only once in a given transaction.
Should I create 10 or 20 procedures to update specific sets of columns -- one procedure for every type of update that the application expects to perform on a given table?
FYI - Many of my tables' columns allow NULL. And the app will not always know the value of every column in the table, except the columns it updates.
Thanks
April 6, 2005 at 2:12 am
Firstly, is your 80 column table normalised? If you can have a greatly varied set of update routines then it sort of suggests that the table design could be improved.
Assuming that the table design is as good as it gets then I would write a stored procedure per business function for the update.
If you have a user interface screen that allows you to update 20 columns then I would write a stored procedure to deal with those columns.
I have written procedures for a large number of columns where the parameters have a default value. If a parameter remains at that default value then the column value for the record is read into the parameter. The update statement then runs for all columns.
This does mean that you are going to have to run at lease one SELECT before you do an update but as you will be retrieving a single record this shouldn't have much of a hit.
The alternative is to keep your dynamic SQL and use sp_executeSQL.
April 6, 2005 at 10:31 am
Normalized? No. Normalization will be part of the db overhaul. Good insight... I thought you were looking over my shoulder. (What color shirt am I wearing?)
But even for our normalized tables, like [People], if you wanted to update FirstName, but not LastName some of the time, and update both columns the other times, would these be separate procedures (spUpdFirstName and spUpdFirstAndLastName)?
My concerns are about unnecessarily updating indexes if the value in a column doesn't change (eg updating 'Poole' to 'Poole') and having too parameters to validate vrs having a large collection of procedures performing specific updates. Ultimately, the goal would be on performance.
I think I see where you're going with your suggestions.
Great input, thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply