June 30, 2010 at 12:02 pm
I just came across this today while investigating some performance issues on our main accounting sofwtare system and befoe I go back to the vendor to ask for an epxlination I want to verify there's not something about thsi I'm not seeing.
When an UPDATE statement is run for any table, what possible reason would one have for updating the column that stores the Primary Key especially when the update is simply setting the PK value equal to what that key is already set to?
EXAMPLE:
This is the DDL for the Table being update. NOTE: I have not listed the full tables def because its over 200 columns and of the few columns in the update all are in the sample below.
CREATE TABLE dbo.ACCT_TRANS (hID NUMERIC(18,0) NOT NULL,
CashAccount NUMERIC(18,0) NULL,
Type NUMERIC(18,0) NULL,
Person NUMERIC(18,0) NULL,
REFERECE VARCHAR(50) NULL,
Total NUMERIC(21,2) NULL,
Paid NUMERIC(21,2) NULL,
ClosedTran NUMERIC(18,0) NULL,
Period DATETIME NULL,
DateOcc DATETIME NULL,
TypeOther_10 NUMERIC(18,0) NULL,
TypeOther_20 NUMERIC(18,0) NULL,
Created DATETIME NULL,
CreatedBy NUMERIC(18,0) NULL,
Modified DATETIME NULL,
ModifiedBy NUMERIC(18,0) NULL,
Notes VARCHAR(512) NULL,
CONSTRAINT PK_ACCT_TRANS PRIMARY KEY CLUSTERED( hID ASC ) ON [PRIMARY]
) ON [PRIMARY]
GO
With the above table as the example, why the heck would someone construct an update statement like this? It just doesn;t make any sense to me to include the PK column in the update.
UPDATE ACCOUNT_TRANS
SET hID = 1900112129
Type = 19,
CashAccount = 252,
Person = 0,
REFERECE = 'REC - 06/30/2010',
Total = 100.00,
Paid = 100.00,
ClosedTran = -1,
Period = Convert(DATETIME, '06/01/2010', 101),
DateOcc = Convert(DATETIME, '06/30/2010', 101),
TypeOther_10 = 1,
TypeOther_20 = 3,
Created = Convert(DATETIME, '06/30/2010', 101),
CreatedBy = 1925,
Modified = NULL,
ModifiedBy = NULL,
Notes = '',
WHERE hID = 1900112129
When I run this Update on a Test database and I comapre it to an update that is identical except that it leaves out the hID = 1900112129 part of the udpate, the ACTUAL EXECUTION PLAN shows a diff of %77 to %23 with the orginal update bineg at %73 and the modified one at 1/3rd that at %23.
Can anyone shed light on why someone would write this kind of update?
Thanks
Kindest Regards,
Just say No to Facebook!June 30, 2010 at 12:31 pm
I saw something like this once.
What I discovered was what I considered to be lazy programming but basically there was a trigger that fired on upate. The belief was that in order for the trigger to send the PK it had to be updated. this allowed the trigger to use from updated statement. I have no idea why this logic formed or became popular but this was at any rate the explanation I got. this may not be helpful to your case but it is the only time I have ever seen this.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 30, 2010 at 12:41 pm
Dan.Humphries (6/30/2010)
I saw something like this once.What I discovered was what I considered to be lazy programming but basically there was a trigger that fired on upate. The belief was that in order for the trigger to send the PK it had to be updated. this allowed the trigger to use from updated statement. I have no idea why this logic formed or became popular but this was at any rate the explanation I got. this may not be helpful to your case but it is the only time I have ever seen this.
Dan,
In otherwords, because they didn't have anyone who was properly trained in using SQL, the developers ended up constructing something that was a lot less effecient because they did not know any better.
Sound about right?
This kind of thing burns my butt every time I have to deal with it. The vendor who makes this accounting system has the same "we don't need no stinkin DBA to tell uzz howz to do hour jobz" and so we and other clients pay the price.
I know it's not a big deal in the grand scheme of the world of databases but I recently got to tell some folks who were visiting from this Vendors office that we had just over a quarter billion rows of data in the DB now and their eyes and mouths about hit the floor. They fo course are suprised at how we can use the system with such a big DB. They have no clue what a VLDB (Very Large DB) is and so something in the hundreads of GB range is unbelievably large to them.
Thanks for replying.
Kindest Regards,
Just say No to Facebook!June 30, 2010 at 12:49 pm
that about sums it up. I even have a few DB's with what I refer to as a self destruct table.
The best example is a progrma I will not name that was designed with the belief that all data integrity should be handled by the app and the DB should be allowed to do nothing but store data. So table were created to hold sequence numbers. These are unique ids that are read from the sequence table used as a primary key and then written back out to the sequence table for the next update. So you can imagine why I refer to this as the self destruct table.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 30, 2010 at 1:10 pm
Dan.Humphries (6/30/2010)
that about sums it up. I even have a few DB's with what I refer to as a self destruct table.The best example is a progrma I will not name that was designed with the belief that all data integrity should be handled by the app and the DB should be allowed to do nothing but store data. So table were created to hold sequence numbers. These are unique ids that are read from the sequence table used as a primary key and then written back out to the sequence table for the next update. So you can imagine why I refer to this as the self destruct table.
OMG, that sounds deathly familiar. This system also has something similiar but instead of one table they have 1 for each variety of transaction type you can have in an accounting software system (i.e. Receipts, Charges, Invoices, Payments, Journal Entries, ect). Even though they have different tables to stores these they were at least smart enough to dump everything into that one ACCOUNT_TRANS table ( :crazy: ) and save some sapce on the computer drives. I almost feel out of my chair trying to taype that last sentence.
Kindest Regards,
Just say No to Facebook!June 30, 2010 at 1:42 pm
This happens when an application is designed to be portable across a variety of database platforms. The theory is that all logic is kept at the application layer so that minimal changes are required when it is ported to whatever platform it gets deployed upon.
The end result is that they usually don't tune well on any platform.
June 30, 2010 at 3:14 pm
It could be due to the code is auto-generated...
And obviuosly it was not done very well 😎
July 1, 2010 at 12:53 am
About this subject ...:-)
I know a clear example regarding your question. The programmers don't have always good knowledge regarding the best possible DB structure behind the application, or the SQL issues, data inconsistency posibility.
For example, a programmer (who is working for many years in the branch!!!) made an application with 2 big mistakes: he let a button on the form (on clicking it, all the salaries in the db became 0!!! and he didn't let the button there by mistake!!!) and he didn't put foreign keys between two tables (which was absolutely needed)... so just in one day... you can imagine what disaster happened in all salary data...
Wish you good ideas! 🙂
Andreea
July 2, 2010 at 8:41 am
Eugene Elutin (6/30/2010)
It could be due to the code is auto-generated...And obviuosly it was not done very well 😎
You know its funny you should mention this because this is exactly what appears to be happening. I managed to get confirmation from one of the programmers who works for this vendor that they do have some programmimg code that provides them with SQL DML's and all they have to do is specify the tables and the columns they are using and the magic programming genie (as seen on procedural prgrammer TV) does the rest. Guaranteed to work (even if it takes a long while) or no more sales calls will be made to sale you more addons.
I guarantee that instead of the software vendor looking at this and performance problems their bigger clients are having and realizing its time to get seirous about SQL and forlk over the money to hire someone who knows what they are doing, they are probably finding ways like LINQ and so on to get around using T-SQL correctly.
This kind of thing just kills me. I do some programming work (like a secondary role at my job) and so I follow some procedural programming websites/forums and even as of today you have progarmmers both new and experienced looing for ways to get around learning how to properly use T-SQL (or PL/SQL for Oracle users) and so they've come up with every idea/solution from Object oriented DB's to God only know what.
Kindest Regards,
Just say No to Facebook!July 6, 2010 at 11:02 pm
YSLGuru (6/30/2010)
With the above table as the example, why the heck would someone construct an update statement like this? It just doesn;t make any sense to me to include the PK column in the update.
Is that the real statement actually being issued or did you simplify it by substituting literal values where there would have been variables? Assuming there are variables driving these updates somewhere in the code then there could be a perfectly valid reason for changing the value of the primary key column. Updating it to the existing value is not so logical of course.
July 7, 2010 at 1:32 am
David Portas (7/6/2010)
...Is that the real statement actually being issued or did you simplify it by substituting literal values where there would have been variables? Assuming there are variables driving these updates somewhere in the code then there could be a perfectly valid reason for changing the value of the primary key column...
If the person who did it found perfectly valid reason for changing the value of the primary key column as possible regular task...
Then, I would say that he is also a bad database designer, as this column is not very sutable to be a PK. It can be a natural key, so make it unique index, but don't make it to be PK...
Of course, there are exclusions from all rules, but looking into the sample I am sure that it is not the case...
July 7, 2010 at 11:56 am
David Portas (7/6/2010)
YSLGuru (6/30/2010)
With the above table as the example, why the heck would someone construct an update statement like this? It just doesn;t make any sense to me to include the PK column in the update.Is that the real statement actually being issued or did you simplify it by substituting literal values where there would have been variables? Assuming there are variables driving these updates somewhere in the code then there could be a perfectly valid reason for changing the value of the primary key column. Updating it to the existing value is not so logical of course.
David,
That was the actual update I captured.
Kindest Regards,
Just say No to Facebook!July 7, 2010 at 11:58 am
Eugene Elutin (7/7/2010)
David Portas (7/6/2010)
...Is that the real statement actually being issued or did you simplify it by substituting literal values where there would have been variables? Assuming there are variables driving these updates somewhere in the code then there could be a perfectly valid reason for changing the value of the primary key column...
If the person who did it found perfectly valid reason for changing the value of the primary key column as possible regular task...
Then, I would say that he is also a bad database designer, as this column is not very sutable to be a PK. It can be a natural key, so make it unique index, but don't make it to be PK...
Of course, there are exclusions from all rules, but looking into the sample I am sure that it is not the case...
Eugene,
I woudl like to be able to say they had a valid reason but the truth is they didn't. I have read where some developers (procedural programmers) will do this because it makes the code portable but our system/product is SQL Server only and so I can't see any reason for 'portablity'.
Kindest Regards,
Just say No to Facebook!July 7, 2010 at 12:02 pm
UPDATE:
After some digging around and some aksing around I have discovered that much of the SQL code used in our application is being auto-generated. When a developer adds some new functionality or feature to the product and they have to create a new data entry window (be it a traditional widow in a client-server app or an IE window in the web based products add-ons) they don't create the T-SQl the app uses but insetad use a set of program objects to handle the DML statements. They tell it what tables and what columsn in those tables and this program code creates the T-SQL at run-time.
Apparently this lets the developers avoid learning how to use SQL and in a twisted way it promotes consistency. Granted its perfromance poor consistency, but it is consistent.
Kindest Regards,
Just say No to Facebook!July 8, 2010 at 6:35 am
I had a similar experience with a 3rd party app db and the vendor support team did nothing to help the situation. Lucky enough the code was in a stored proc and I could easily change it with calling the same proc of course I backed up the original. Some cases all the code is within the Application Layer and the DBA's simply stuck!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply