Conditional SQL commands: Good? Bad?
-
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the
if...elsein the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.Just don't count on that to work on any database. And for that purpose it's better to use MERGE[^]
Wrong is evil and must be defeated. - Jeff Ello[^]
-
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the
if...elsein the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.IMHO anything that data handling (update, insert, delete, move, aggregate and on) is good to do in SQL - it's very good at it. However IF isn't exactly in that category - so do update then insert where the where clause contains an addition of @@ROWCOUNT = 0 at the end...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the
if...elsein the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.Thanks for the feedback; I will use the
ROWCOUNTtest. -
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the
if...elsein the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.I don't like the rowcount method as is uses 2 writes to the database. Have not used the Merge function so I have no opinion. I use convention instead, EVERY tale has an identity field as the primary key, so when I pass in the object (via variables) the ID field has either 0 or the record value. I then use IF to test the ID and either insert or update the record. I put it into a tip [^]. While it is not guaranteed to be perfect it has been working for me for decades! 99% of database our work goes through stored procedure. String queries are heavily discouraged.
Never underestimate the power of human stupidity RAH
-
I don't like the rowcount method as is uses 2 writes to the database. Have not used the Merge function so I have no opinion. I use convention instead, EVERY tale has an identity field as the primary key, so when I pass in the object (via variables) the ID field has either 0 or the record value. I then use IF to test the ID and either insert or update the record. I put it into a tip [^]. While it is not guaranteed to be perfect it has been working for me for decades! 99% of database our work goes through stored procedure. String queries are heavily discouraged.
Never underestimate the power of human stupidity RAH
SQL is very smart - an update of non existing record will end in a single index page read. And that read may be from cached index page if it's a highly used table. So no writing at the first part. And if ROWCOUNT isn't 0 no write in the second part...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
SQL is very smart - an update of non existing record will end in a single index page read. And that read may be from cached index page if it's a highly used table. So no writing at the first part. And if ROWCOUNT isn't 0 no write in the second part...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
To be honest, I don't think it makes any difference which way it is done. Unless you are working with really big data the time taken is irrelevant these days. I was just offering an alternative, my code generator (written in the 90s) does all this work for me :laugh:
Never underestimate the power of human stupidity RAH
-
To be honest, I don't think it makes any difference which way it is done. Unless you are working with really big data the time taken is irrelevant these days. I was just offering an alternative, my code generator (written in the 90s) does all this work for me :laugh:
Never underestimate the power of human stupidity RAH
You may be right - and your approach is very good one. I read your tip and bookmarked it, as I plan to run some benchmarks on large data with you against rowcount...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
-
You may be right - and your approach is very good one. I read your tip and bookmarked it, as I plan to run some benchmarks on large data with you against rowcount...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
Kornfeld Eliyahu Peter wrote:
as I plan to run some benchmarks on large data with you against rowcount
I would be interested in the results from an intellectual POV but I suspect the differences will be so minute as to be irrelevant. Look forward to the results.
Never underestimate the power of human stupidity RAH
-
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the
if...elsein the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.It is always better to put all the database code on the database side. An equivalent application code for the above query will make atleast three database calls as opposed to one call when the IF is on the database side. There could be a race condition with you query and the possibility of a row being inserted by another user between the IF and the INSERT, so you should actually handle the exception and take appropriate action (either let the user know that someone else has already inserted it or do an update). The possibility is even higher in case of application code because of network latency between the server and the client. And your UPDATE statement is missing the WHERE clause and it would end up updating all the rows in the table :-)
-
I don't like the rowcount method as is uses 2 writes to the database. Have not used the Merge function so I have no opinion. I use convention instead, EVERY tale has an identity field as the primary key, so when I pass in the object (via variables) the ID field has either 0 or the record value. I then use IF to test the ID and either insert or update the record. I put it into a tip [^]. While it is not guaranteed to be perfect it has been working for me for decades! 99% of database our work goes through stored procedure. String queries are heavily discouraged.
Never underestimate the power of human stupidity RAH
The most important advantage of the
MERGEstatement is that the table is read and processed in one pass only. So if you insert/update only one record there's no real win. It's when you insert/update from a query you'll get the real performance gain.Wrong is evil and must be defeated. - Jeff Ello[^]
-
The most important advantage of the
MERGEstatement is that the table is read and processed in one pass only. So if you insert/update only one record there's no real win. It's when you insert/update from a query you'll get the real performance gain.Wrong is evil and must be defeated. - Jeff Ello[^]
Jörgen Andersson wrote:
The most important advantage of the
MERGEstatement is that the table is read and processed in one pass only.Apparently not in Microsoft land:
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/[^]
MERGE looks like it will take care of concurrency for you, because implicitly it seems to a single, atomic statement. However, under the covers, SQL Server really does perform the different operations independently.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Jörgen Andersson wrote:
The most important advantage of the
MERGEstatement is that the table is read and processed in one pass only.Apparently not in Microsoft land:
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/[^]
MERGE looks like it will take care of concurrency for you, because implicitly it seems to a single, atomic statement. However, under the covers, SQL Server really does perform the different operations independently.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
No it doesn't handle concurrency for you. X| It was about performance only. <edit>Some interesting info[^] on the subject</edit>
Wrong is evil and must be defeated. - Jeff Ello[^]
-
I'd agree that it's better to put this in SQL than the application. In SQL, there's less chance of the row being deleted between the
If Existstest and theUpdatestatement. However, I've seen suggestions that something like this might be slightly more efficient:UPDATE YourTable SET ... WHERE ID = @ID
If @@ROWCOUNT = 0
INSERT YourTable (...) VALUES (...);
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the
if...elsein the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.Gregory.Gadow wrote:
I would think this is more efficient than putting the
if...elsein the application,And this matters because your application currently has an efficiency problem?
Gregory.Gadow wrote:
move a lot of code to SQL Server as stored procedures
I use stored procedures for the same reason I have a database layer in an application - to make the layers more distinct and hopefully insuring that access happens in a controlled and reasonable way. Some business logic which might have performance concerns, can be done must more efficiently in the database server than a client application. There are of course still ways to badly mess this up. Other than that one problem with using conditional structures in stored procedures is that programmers who do not understand how to really use SQL can end up creating structured programs (ifs, whiles, etc). Of course if one uses stored procedures then if all else fails one can hire a real DBA to redo the stored procs.
-
Kornfeld Eliyahu Peter wrote:
as I plan to run some benchmarks on large data with you against rowcount
I would be interested in the results from an intellectual POV but I suspect the differences will be so minute as to be irrelevant. Look forward to the results.
Never underestimate the power of human stupidity RAH
Here it comes... I got a personal info table - containing id, name, user, password, address, email and phone - with 19,245,718 records. The table has PK and indexes... Run an insert/update of 108,382 records of which 32,190 where duplicated (same id). With your method it took an average of 26.77 ms/record. With the @@ROWCOUNT approach it took an average of 13.07 ms/record. Both test run on the same machine with local SQL after restart. The computer is a bit old one (one I can spare) with 4x2.93 CPU and 8GB memory, but I believe that the differences are real...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
