none
Limit of SQL

    Question

  • Is there a limit to the number of semi-colon seperated sql commands you can send to a database at one time?

    I am making a function to go through and advance all the date fields in a database by a certain amount, and some of the tables have upwards of 100k records all of which will have at least 1 date field that needs to be updated, more commonly there will be 2+ fields per record. My current plan was to create a string to hold all the Update commands and seperate them with semi-colons then send the string into a command and do .ExecuteNonQuery.

    Will this approach still work with a large number of records being updated? Or is there a better/more efficient way to advance all the dates in a database?

    Thursday, August 25, 2011 2:52 PM

Answers

  • Is there any sort of speed gained from doing them all at once instead of making a trip to the database for each individual record? normally I would go to the database with one command per record but I've run into it being extremely slow once you get to a large enough number of records so I was hoping to avoid that by executing all the statements for a single table at once.
     


    It sounds like you are creating a string to update each row - one at a time and building that into a single batch.  Is this similar to what you are building:
    UPDATE table SET datecolumn = dateadd(day, 1, datecolumn) WHERE keyvalue = somevalue;
    UPDATE table SET datecolumn2 - dateadd(day, 1, datecolumn2) WHERE keyvalue = somevalue;
    UPDATE table SET datecolumn = dateadd(day, 1, datecolumn) WHERE keyvalue = othervalue;
    UPDATE table SET datecolumn2 - dateadd(day, 1, datecolumn2) WHERE keyvalue = othervalue;
    

    Or, are you actually building and sending something like this?
    UPDATE table SET datecolumn = somedatevalue, datecolumn2 = somedatevalue;
    UPDATE table2 SET datecolumn = somedatevalue;
    UPDATE table3 SET datecolumn = somedatevalue, datecolumn2 = somdatevalue, datecolumn3 = somedatevalue;
    

    With the former, yes - you are going to have a lot of update statements and it will take a long time.  With the latter - you just build the first table, execute the statement, build the next table, execute the statement, etc...  Each 'batch' will update all datecolumns in that table.

    Jeff Williams
    • Marked as answer by Rien1782 Friday, August 26, 2011 5:02 PM
    Friday, August 26, 2011 2:45 AM

All replies

  • I am not sure about the max limit of the semi-colon separated sql commands. However, I would suggest you to go ahead and execute each statement as and when you prepare it rather concatenating it to create a whole LOT of statements.

    This approach will help your get rid of the max limit issue if at all it exists.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Thursday, August 25, 2011 2:56 PM
  • What is your SQL Server version? In SQL 2008 I suggest to pass a table parameter

    http://blog.sqlauthority.com/2011/08/25/sql-server-tips-from-the-sql-joes-2-pros-development-series-table-valued-store-procedure-parameters-day-25-of-35/#comment-161579

    In SQL 2005 you may want to send XML with all changes needed to apply to database. This is the approach I'm using in our SP since we maintain SQL 2005 compatibility.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, August 25, 2011 3:04 PM
  • Is there any sort of speed gained from doing them all at once instead of making a trip to the database for each individual record? normally I would go to the database with one command per record but I've run into it being extremely slow once you get to a large enough number of records so I was hoping to avoid that by executing all the statements for a single table at once.

     


    Thursday, August 25, 2011 4:14 PM
  • I'm actually trying to do this from a VB.Net app getting the information from one database, changin the dates, and then shoving it into a SQL database using Update/Insert commands depending on a few things. Do the methods you mentioned only work in stored procedures or can you use datatables in VB and pass them through a database conenction and have it update the records accordingly?
    Thursday, August 25, 2011 4:14 PM
  • If you want to pass data table, then I believe you must use a stored procedure.

    For XML approach you don't really need an SP, you can do everything with inline script.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, August 25, 2011 4:18 PM
  • Is there any sort of speed gained from doing them all at once instead of making a trip to the database for each individual record? normally I would go to the database with one command per record but I've run into it being extremely slow once you get to a large enough number of records so I was hoping to avoid that by executing all the statements for a single table at once.
     


    It sounds like you are creating a string to update each row - one at a time and building that into a single batch.  Is this similar to what you are building:
    UPDATE table SET datecolumn = dateadd(day, 1, datecolumn) WHERE keyvalue = somevalue;
    UPDATE table SET datecolumn2 - dateadd(day, 1, datecolumn2) WHERE keyvalue = somevalue;
    UPDATE table SET datecolumn = dateadd(day, 1, datecolumn) WHERE keyvalue = othervalue;
    UPDATE table SET datecolumn2 - dateadd(day, 1, datecolumn2) WHERE keyvalue = othervalue;
    

    Or, are you actually building and sending something like this?
    UPDATE table SET datecolumn = somedatevalue, datecolumn2 = somedatevalue;
    UPDATE table2 SET datecolumn = somedatevalue;
    UPDATE table3 SET datecolumn = somedatevalue, datecolumn2 = somdatevalue, datecolumn3 = somedatevalue;
    

    With the former, yes - you are going to have a lot of update statements and it will take a long time.  With the latter - you just build the first table, execute the statement, build the next table, execute the statement, etc...  Each 'batch' will update all datecolumns in that table.

    Jeff Williams
    • Marked as answer by Rien1782 Friday, August 26, 2011 5:02 PM
    Friday, August 26, 2011 2:45 AM
  • The best way I can think of is, as already said above me, to use table parameters into a store or batch and pass all the data all at once (and only a simple update statement), but that works only on SQL 2008. If you're on SQL 2005 or 2000, XML are a nice replacements for table parameters with a few modifications.

    I've tried that sort of gigantic batch once on a similar work, but on MySQL instead, to try to speed up numerous transfers from a local service to a web database. It worked but crashed against such a limitation on batch size. To prevent that, I sent the multiple UPDATEs in chunks. Counting the lenght of the generated SQL string and stopping after reaching some upper mark, then send that pack across the network and begin building a new bunch of UPDATEs with the remaining data, until no more records are to be updated. You may try that chunking in your client side code if you really want to do this way.

    Checking the specifications of SQL Server, there is a limit on such queries (http://msdn.microsoft.com/en-us/library/ms143432.aspx)

    The maximum batch size can be up to 65535*packet lenght. With a default packet size of 4KB, it amounts to 256MB of plain text statements, which allows for a very large amount of rows. The document doesn't says anything about the number of statements though, so I assume they are unlimited as long as they don't exceed the batch size.

     

    Friday, August 26, 2011 2:49 AM
  • If you want to pass data table, then I believe you must use a stored procedure.


    Any parameterized statement can use a TVP, although a stored procedure is most common in my experience.  I did some performance testing recently and found that an IEnumerable<SqlDataRecord> outperformed a DataTable significantly, although I doubt it will matter for Rein1782's needs.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Friday, August 26, 2011 3:23 AM
  • I too agree with Jeff
    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Friday, August 26, 2011 4:52 AM
  • What is the SQL Version are you working with ?

    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    Friday, August 26, 2011 4:56 AM
  • Hey guys,

    Jeff- This was actually much easier than I originally thought. Once I stumbled across the "dateadd" function early yesterday afternoon I was able to update each column in the database all at once by not passing a unique identifier. So I am using statments much like your first example but without a where clause. Which means it cut the number of statements down to 40(since I have that many darte columns in the database) from however many it would have been had I had to update each record individually. This only worked beacuse I was just pushing all the dates forward by a set amount.

    the sql I actually used was:

    UPDATE Table SET Date_Column = DATEADD(dayofyear, Num_Days, Date_Column) WHERE LocationID = lID;

    Alejandro- Once I realized how many actual records I would be dealing with I tried to break it down by table but one particular tabe has 4 date fields and roughly 2000 records that would be updated so having one statement per column per record would have pushed that character limit I believe. But this was a moot point once I started working on the above method. Even so the limitation is good to know if not just for curiosities sake.

    Dan- I'm not quite sure I follow what you were saying, is the IEnumerable<SQLDataRecord> just basically an array of records? If so, how does that differ from the datatable? The only time I've seen the IEnumerable data type used specifically is while working with LINQ and I find that very useful so if I could learn to do more with them I'd like to.

    Vinay- I am currently Using SQL 2008.

     

    Thanks for all the information guys,

    - Mike

     

    Friday, August 26, 2011 1:22 PM