Limits on SQL Statements
-
Hi, I wonder if there is any limitation concerning the length of a sql statement. I have the following situation: I need to update a single column of a lot of records in a table. Updating those 20K (20000) records one at the time consumes a lot of time. In order to speed things up I collect those queries to create one big SQL statement. Now the problem is the following whenever I place more that 455 update queries only the first 455 are executed. The others are neglected, because no error has happened (neighter in my try/catch block or in the SQL Server checked with the profiler). The size of the sql statement (455 queries) is about 20Kb in size. Any thoughts on this would be very appriciated BTW: I'm developing in VC++ 2005 on a SQL Server V2000 codito ergo sum
-
Hi, I wonder if there is any limitation concerning the length of a sql statement. I have the following situation: I need to update a single column of a lot of records in a table. Updating those 20K (20000) records one at the time consumes a lot of time. In order to speed things up I collect those queries to create one big SQL statement. Now the problem is the following whenever I place more that 455 update queries only the first 455 are executed. The others are neglected, because no error has happened (neighter in my try/catch block or in the SQL Server checked with the profiler). The size of the sql statement (455 queries) is about 20Kb in size. Any thoughts on this would be very appriciated BTW: I'm developing in VC++ 2005 on a SQL Server V2000 codito ergo sum
BadKarma wrote:
Updating those 20K (20000) records one at the time consumes a lot of time. In order to speed things up I collect those queries to create one big SQL statement. Now the problem is the following whenever I place more that 455 update queries only the first 455 are executed. The others are neglected, because no error has happened (neighter
You did what??? :omg: You have a big problem in your design. Like you've found out, if one statement fails, you have to consider the entire batch a failure. You can't catch an error because nothing throws one. Best practice says that you call a stored procedure on the SQL Server to do the update for you and just pass the parameters to it, 20,000 times. The stored procedure can check to see if the update works and RAISEERROR if it failed for some reason. You can catch that error in your code. You also get the benefit of knowing exactly which one of these calls failed. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Hi, I wonder if there is any limitation concerning the length of a sql statement. I have the following situation: I need to update a single column of a lot of records in a table. Updating those 20K (20000) records one at the time consumes a lot of time. In order to speed things up I collect those queries to create one big SQL statement. Now the problem is the following whenever I place more that 455 update queries only the first 455 are executed. The others are neglected, because no error has happened (neighter in my try/catch block or in the SQL Server checked with the profiler). The size of the sql statement (455 queries) is about 20Kb in size. Any thoughts on this would be very appriciated BTW: I'm developing in VC++ 2005 on a SQL Server V2000 codito ergo sum
SQL Server 2000 has a limit on the batch size, which is 65,536 times the network packet size, which is, by default, 4096 bytes, so what we have is somewhere around 256 Mb. So batch size is not an issue in your case. You may try issuing the same batch from Query Analyzer to see the results.
-
BadKarma wrote:
Updating those 20K (20000) records one at the time consumes a lot of time. In order to speed things up I collect those queries to create one big SQL statement. Now the problem is the following whenever I place more that 455 update queries only the first 455 are executed. The others are neglected, because no error has happened (neighter
You did what??? :omg: You have a big problem in your design. Like you've found out, if one statement fails, you have to consider the entire batch a failure. You can't catch an error because nothing throws one. Best practice says that you call a stored procedure on the SQL Server to do the update for you and just pass the parameters to it, 20,000 times. The stored procedure can check to see if the update works and RAISEERROR if it failed for some reason. You can catch that error in your code. You also get the benefit of knowing exactly which one of these calls failed. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
Dave Kreskowiak wrote:
if one statement fails, you have to consider the entire batch a failure
I know for certain that all queries are valid. If I execute 400 of them in batch there is no problem, even 455 of them. Only when I exceed the 455 queries 'limit' in the batch, then the last are neglected. Due to what reason can a valid query be neglected ??? codito ergo sum
-
Dave Kreskowiak wrote:
if one statement fails, you have to consider the entire batch a failure
I know for certain that all queries are valid. If I execute 400 of them in batch there is no problem, even 455 of them. Only when I exceed the 455 queries 'limit' in the batch, then the last are neglected. Due to what reason can a valid query be neglected ??? codito ergo sum
Because the query IS invalid, the last lines are being cut off. There is a limit to the size of a query string you can pass. I think it's 8192 bytes, but I can't find solid documentation on it. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Because the query IS invalid, the last lines are being cut off. There is a limit to the size of a query string you can pass. I think it's 8192 bytes, but I can't find solid documentation on it. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
First of all thanks for your time too reply. The queries i execute look like this:
UPDATE MyTable SET MyField = 1 WHERE Data = 1; UPDATE MyTable SET MyField = 1 WHERE Data = 2; UPDATE MyTable SET MyField = 1 WHERE Data = 3; UPDATE MyTable SET MyField = 1 WHERE Data = 4;this goes on up until Data reaches 1000. So like you can see the size of the query is relative small not even 50 characters. The batch statement though, can reach the size of more the 20k of characters but shouldn't be a problem because the max bacth size is 65536 * network packet size (see response from Sl0n[^]) When I execute this batch to an limit of 455 queries, there is no problem. The size of this batch is over 8192 byte limit (its arround 18K). When I add more queries to the bacth every query past this 455 limit just doesn't get executed and the batch statement is returned as a success. So even though I stay well under the limits, those queries aren't executed ??? Or do I miss something? codito ergo sum
-
First of all thanks for your time too reply. The queries i execute look like this:
UPDATE MyTable SET MyField = 1 WHERE Data = 1; UPDATE MyTable SET MyField = 1 WHERE Data = 2; UPDATE MyTable SET MyField = 1 WHERE Data = 3; UPDATE MyTable SET MyField = 1 WHERE Data = 4;this goes on up until Data reaches 1000. So like you can see the size of the query is relative small not even 50 characters. The batch statement though, can reach the size of more the 20k of characters but shouldn't be a problem because the max bacth size is 65536 * network packet size (see response from Sl0n[^]) When I execute this batch to an limit of 455 queries, there is no problem. The size of this batch is over 8192 byte limit (its arround 18K). When I add more queries to the bacth every query past this 455 limit just doesn't get executed and the batch statement is returned as a success. So even though I stay well under the limits, those queries aren't executed ??? Or do I miss something? codito ergo sum
BadKarma wrote:
So like you can see the size of the query is relative small not even 50 characters. The batch statement though, can reach the size of more the 20k of characters but shouldn't be
With emphasis on "shouldn't". It's not so much the size of the query in characters, but I'm thinking the number of statements in the batch might be your problem. It's not listed in the specs anywhere. There are other minor possibilities, but I've got no time to test them. Maximum statements cachable in a connection, limits on uncompiled vs. compiled code vs. batch code, ... What you're doing is just never done outside a stored procedure in the database (at least I've never seen it done), so data on your technique is very hard to come by. You can try the theory out by batching together statements 400 at a time. ORrrrrrr, you could just write the batch query using an SQL loop, since your data doesn't appear to change from iteration to iteration, just the WHERE clause changes. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome -- modified at 11:46 Monday 30th January, 2006