InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 08:54:15 GMT20http://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxUsing prepare and a loop will be fine for a very small number of rows, but it will not scale well. Using prepare is effectively the same as creating a stored procedure on the SQL Server that inserts one row at a time. The scalability issue is that each row requires a network round-trip between your application and the SQL Server; also auto-committing each row insert means SQL Server has to flush a log record to disk when each single row insert auto-commits.For anything except a very small number of rows, you need to look at batching the inserts. This means submitting multiple rows (or all rows) in one or more steps to SQL Server. This results in many fewer network communications, and SQL Server can optimize the logging requirements, using a very fast bulk-load mode of operation.There are multiple methods available, both within SQL Server (directly reading a text file, for example) and via .NET. SQL Server provides:- The bcp utility [url]http://msdn.microsoft.com/en-us/library/ms162802.aspx[/url]- The BULK INSERT command [url]http://msdn.microsoft.com/en-us/library/ms188365.aspx[/url]- The OPENROWSET (BULK... command [url]http://msdn.microsoft.com/en-us/library/ms190312.aspx[/url]You can find an overview at [url]http://msdn.microsoft.com/en-us/library/ms175937.aspx[/url]From a .NET language connecting to SQL Server 2008, options for bulk/batch loading include:- SqlBulkCopy [url]http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx[/url]- Table-valued parameters [url]http://msdn.microsoft.com/en-us/library/bb675163.aspx[/url]You can find examples of using SqlBulkCopy and table-valued parameters all over the Internet. A couple of examples below:[url]http://orionseven.com/blog/2009/09/30/using-table-valued-parameters-in-sql-server-2008-and-c/[/url][url]http://weblogs.sqlteam.com/mladenp/archive/2006/08/26/11368.aspx[/url][url]http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server[/url][url]http://florianreischl.blogspot.co.nz/2009/11/table-valued-parametes-performance.html[/url]As far as repeatable tests are concerned, you may find it sufficient to drop and recreate the destination table in SQL Server between each test. It is hard to argue that restarting SQL Server between tests is not a more thorough approach, however.Tue, 25 Sep 2012 09:42:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspx[quote][b]yuvipoy (9/25/2012)[/b][hr]I have missed the last 2 lines command.ExecuteNonQuery(); and command.Parameters.Clear(); statement in the code.[/quote]Yes, adding those two lines fixes that issue. However, you will have very poor performance - you could get orders of magnitude improvement in time to insert by using a set-based method.Tue, 25 Sep 2012 09:18:50 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspx[quote][b]yuvipoy (9/25/2012)[/b][hr]Thanks for your replay,As i have stated early [Quote]Inserting the data externally not from the database itself. [/Quote] my aim is not inserting from the database i need to insert data from non database which may be java or .net c# C++ or anything i need to check how for different connection how the times are taken.Now i'm working with C# so i have posted my example with C# where as i am coding on different platforms also.The code which you have shared here is a internal one which done at the database level not at the C# level.I'm not check the loop on database i am checking with C# code with data insertion as my preliminary requirement for now. Thanks! [/quote]I get your point, you will insert data from the outside. However, I wanted to show you how different would the two approaches be. There're plenty of ways to do it and, as Matt said, a while loop will be the worst. If you're trying to test how will a server respond under a high load (many users inserting data at the same time) it can be a good excercise. If you're trying how to improve the performance for a single user inserting lots of data, then you should look for alternatives.Here's another way you might want to check [url]http://www.sqlservercentral.com/articles/T-SQL/63003/[/url]Tue, 25 Sep 2012 09:10:45 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspx[quote][b]yuvipoy (9/25/2012)[/b][hr]Thanks for your replay,As i have stated early [Quote]Inserting the data externally not from the database itself. [/Quote] my aim is not inserting from the database i need to insert data from non database which may be java or .net c# C++ or anything i need to check how for different connection how the times are taken.Now i'm working with C# so i have posted my example with C# where as i am coding on different platforms also.The code which you have shared here is a internal one which done at the database level not at the C# level.I'm not check the loop on database i am checking with C# code with data insertion as my preliminary requirement for now. Thanks! [/quote]If you're inserting from procedural code, and looking for performance, you're going to want to look at several options:1. You will likely want to look into using a table-valued parameter. You could then create an array in your procedural code (or various other ienumerable types) which can then be streamed in and inserted as a set. I've used this approach successfully on 1000-5000 record sets and it's orders of magnitudes faster than single insert statements.2. if you're talking in large scale inserts - you may do even better by writing it out to an external file anf having SQL pick up and insert the file in bulk. BCP in with a bulk insert can really get some screaming perf.3. on the .NET side specifically, you could even look at recordset objects. they all for in-memory inserts which then can be "flushed" into the DB as a set. I haven't played with any such object in java, but would be surprised if they didn't have a similar concept.The loop used in this way is going to consistently give you the worse perf. You also will have nothing but issues with consistency: with the code as is, you have no good way to get the data out if, say, row 57000 contains garbage and can't be inserted into the DB.Tue, 25 Sep 2012 08:58:40 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxThanks for your replay,As i have stated early [Quote]Inserting the data externally not from the database itself. [/Quote] my aim is not inserting from the database i need to insert data from non database which may be java or .net c# C++ or anything i need to check how for different connection how the times are taken.Now i'm working with C# so i have posted my example with C# where as i am coding on different platforms also.The code which you have shared here is a internal one which done at the database level not at the C# level.I'm not check the loop on database i am checking with C# code with data insertion as my preliminary requirement for now. Thanks! Tue, 25 Sep 2012 08:47:39 GMTyuvipoyhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspx[quote][b]yuvipoy (9/25/2012)[/b][hr][Quote]INSERT INTO test(col1, col2) VALUES ( @param1, @param2), ( @param1, @param2), ( @param1, @param2), ( @param1, @param2), ( @param1, @param2) [/Quote]How you are saying that the above and [Quote]INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)[/Quote]are same when i am issuing a loop for each and every record While i<=2BeginINSERT INTO test(Col0, col1, col2) VALUES( i,@param1, @param2)Set @i=@+1End which means that the loop value is incremented and same need to be stored and not as you stated right?Thanks![/quote]What I was saying is that both instructions would do the same thing but not the same way. If you execute your last while loop in sql, it would be the same as the second statement you quoted (it might be slower). In other words, you could change your approach as Tom suggested. Here's something for you to test:[code="sql"]DECLARE @Test TABLE( col0 int, col1 varchar(15), col2 datetime);DECLARE @int int, @param1 varchar(15), @param2 datetime2, @i intSET @int = 1SET @param1 = 'MyTest'SET @param2 = SYSDATETIME()While @int <= 100000Begin INSERT INTO @Test(Col0, col1, col2) VALUES( @int,@param1, GETDATE())--@param2) Set @int=@int+1End ;SELECT DATEDIFF( MCS, @param2, SYSDATETIME())--You could show the results but it's time consuming--SELECT * FROM @Test DELETE @Test --This would be out of our testSET @param2 = SYSDATETIME();WITH Tally(n) AS( SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) FROM master.sys.all_columns a, master.sys.all_columns b)INSERT INTO @Test(Col0, col1, col2) SELECT n, @param1, GETDATE()FROM Tally WHERE n <= 100000SELECT DATEDIFF( MCS, @param2, SYSDATETIME())--Once again, you could show the results but it's time consuming--SELECT * FROM @Test [/code]Tue, 25 Sep 2012 08:36:45 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspx[Quote]Your loop doesn't insert any data - it just repeatedly overwrites the command's parameters. The only calls to the database are befor the loop begins and after it ends, to get the starttime and the endtime. [/Quote]how you are saying that loop will not insert the data.I have used command.CommandText = "Insert into test (Col1, Col2);command.Prepare();for (int i = 0; i < count; i++) /*Loop Starts heres*/{command.Parameters.AddWithValue("@param1", 0);command.Parameters.AddWithValue("@param2", 0);command.ExecuteNonQuery();command.Parameters.Clear();}I have missed the last 2 lines command.ExecuteNonQuery(); and command.Parameters.Clear(); statement in the code.[Quote]Also, it's not a good idea to call Console.WriteLine inside the measured time.[/Quote]I am writing the output to a variable and writing it finally to a text file at the end of the work completion. i have specified Console.WriteLine(); in order to show you how it is taken.Thanks!Tue, 25 Sep 2012 08:25:35 GMTyuvipoyhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspx[Quote]INSERT INTO test(col1, col2) VALUES ( @param1, @param2), ( @param1, @param2), ( @param1, @param2), ( @param1, @param2), ( @param1, @param2) [/Quote]How you are saying that the above and [Quote]INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)[/Quote]are same when i am issuing a loop for each and every record While i<=2BeginINSERT INTO test(Col0, col1, col2) VALUES( i,@param1, @param2)Set @i=@+1End which means that the loop value is incremented and same need to be stored and not as you stated right?Thanks!Tue, 25 Sep 2012 08:17:56 GMTyuvipoyhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxYour loop doesn't insert any data - it just repeatedly overwrites the command's parameters. The only calls to the database are befor the loop begins and after it ends, to get the starttime and the endtime.So you seem to be measuring the client-side ADO.net cost of repeatedly writing values into parameters, not anything in SQL.Also, it's not a good idea to call Console.WriteLine inside the measured time.I agree with previous posts which have suggested looking for a set-oriented approach instead of this one row at a time stuff. Maybe look at ways of batching inserts/updates in ADO.net - I think dataadaptors provide a means of doing this. Or maybe write SQL to accept an XML object as a string, parse that into a collection of rows and insert them in a single statement. Of course to have to write the c# or whatever code to generate the XML, but the performance gains will make it well worth while.Tue, 25 Sep 2012 08:13:46 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxWhat I was really hoping to see is how the final statement looks when you execute it.It's different if it looks like this[code="sql"]INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)INSERT INTO test(col1, col2) VALUES( @param1, @param2)[/code]Than if it looks like this[code="sql"]INSERT INTO test(col1, col2) VALUES ( @param1, @param2), ( @param1, @param2), ( @param1, @param2), ( @param1, @param2), ( @param1, @param2)[/code]I'm not sure if I'm clear enough.If you're just testing, then just test, and test several times to obtain consistent results because times can vary depending on the workload of the server.Tue, 25 Sep 2012 07:50:34 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspx[Quote] Most of the people here won't take the approach you're using. I'm not sure what is completely your goal. [/Quote]So what is your suggestion for inserting the data externally not from the database itself. I'm inserting the data and checking the performance for each loop how much time it is taken to insert that's is why i have used prepare statement for each and every statement. [Quote]Can you show us what is the code sent to SQL Server for your 5 row insertion?[/Quote]I have already given my approach in the above connectionString = ("server=MYServerName;Database=MYDB;User id=XXXXX;Password=XXXXXXX;");then comes the insertion command.CommandText = "SELECT Getdate() ";StartTime = (DateTime)command.ExecuteScalar();/*Getting the StartTime*/Console.WriteLine(StartTime.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));command.CommandText = "Insert into test (Col1, Col2);command.Prepare();for (int i = 0; i < count; i++) /*Loop Starts heres*/{command.Parameters.AddWithValue("@param1", 0);command.Parameters.AddWithValue("@param2", 0);}command.CommandText = "SELECT Getdate() ";EndTime = (DateTime)command.ExecuteScalar();/*Getting the EndTime*/Console.WriteLine(StartTime.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));i= 1,5,50,100.... 10000k Thanks!Tue, 25 Sep 2012 07:41:01 GMTyuvipoyhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxAre you just trying to measure performance in SQL Server?Most of the people here won't take the approach you're using. I'm not sure what is completely your goal. If you're using .Net to create the sample data you will insert, then you should take a look at this. [url=http://www.sqlservercentral.com/articles/Data+Generation/87901/] Generating Test Data: Part 1 - Generating Random Integers and Floats[/url] and this [url=http://www.sqlservercentral.com/articles/Test+Data/88964/]Generating Test Data: Part 2 - Generating Sequential and Random Dates[/url] The process of inserting 1000 rows is different if you insert them as a set or if you insert them row by (agonizing) row. The first will perform much better.By the way, the article that Grant linked doesn't show a set based solution but just another loop. For a better solution, you should take a look at this [url=http://www.sqlservercentral.com/articles/T-SQL/62867/]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]Can you show us what is the code sent to SQL Server for your 5 row insertion?Tue, 25 Sep 2012 07:23:29 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxThe SQL server' default is Auto commit which is Read committed transaction,here each command is a single transaction so once the loop run each and every record is auto committed to the database.[quote]I still don't completely understand what you're trying to do or why it has to be done in a loop.[/quote]Since i am measuring the total insertion time for bunch of loop values 1,5,50,100.... 10000k.The article which you have tagged does not contain any insert statement options rather it is having Select statement.I am just using a prepare statement (which may be java ,.net,C#,Vc++ and so on) and measuring the time.I am just checking what is the time take for different loop values.Thanks!Tue, 25 Sep 2012 06:23:52 GMTyuvipoyhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxI'm still very uncomfortable with the loop approach. It's just going to be slow. But, you need to make sure that each command is a single transaction, or, the entire set will wait until you're done through the whole loop to commit. So explicitly start and commit the transaction around the command to do the insert.Also, instead of capturing start & stop time in the code (unless you're trying to measure the code itself) instead use Extended Events to capture the execution times of the commands.I still don't completely understand what you're trying to do or why it has to be done in a loop.[url=http://www.codeproject.com/Articles/34142/Understanding-Set-based-and-Procedural-approaches]Here's an article differentiating the set-based operation.[/url]Tue, 25 Sep 2012 06:07:37 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxWhat is Set based approach.How can i check the data insertion time with .net or java as interface.i need to measure the insertion time.Start time then insertion(population of data) then end time. here in population of data i have used .net as my interface This is my approach command.CommandText = "SELECT Getdate() "; StartTime = (DateTime)command.ExecuteScalar();/*Getting the StartTime*/ Console.WriteLine(StartTime.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));command.CommandText = "Insert into test (Col1, Col2);command.Prepare(); for (int i = 0; i < count; i++) /*Loop Starts heres*/ { command.Parameters.AddWithValue("@param1", 0); command.Parameters.AddWithValue("@param2", 0); } command.CommandText = "SELECT Getdate() "; EndTime = (DateTime)command.ExecuteScalar();/*Getting the EndTime*/ Console.WriteLine(StartTime.ToString("MM/dd/yyyy hh:mm:ss.fff tt"));here i= 1,5,50,100.... 10000k I need to collect the data based on the above loop and i am asking suggestion which three conditions is best.Thanks!Tue, 25 Sep 2012 06:01:40 GMTyuvipoyhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxLooping within the SQL Server environment is generally a poor approach. If you need to validate data sets or behavior, I would strongly recommend finding a set-based approach to get it done. T-SQL is a set-based language and doesn't perform loops well at all.Tue, 25 Sep 2012 04:23:03 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1363791-391-1.aspxHi,I have an assignment to check the consistency of SQL server database for different conditions.I am having some 200+ columns i a table i need to populate data using prepare statement in .net and check the conditions how it works for each loop values.I am having loop values starting from 1,5,50,100.... 10000k i will increment the loop value as 1,5,50,100.... 10000k and need to get the start time and end time of each loop, i need to check the consistency of the database. Value for the loop(1,5,50,100.... 10000k) will be from a xml file or text fileFor the 200+ Columns i am having 2PK's.[b]CONDIDTIONS[/b]1)Whether i need to restart for every loop value say if i run loop value as 1 then i need to restart the system (not the services) and run for loop value 5 and restart and so on.2)Whether i need to restart for every loop value say if i run loop value as 1 then i need to restart the services and run for loop value 5 and restart services and so on.3)Whether i can continue my loop value starting from 1,5...10000k without restart or any other process continuously running starting from 1...10000k in single go why i am asking this is whether SQL server will act as differently when i do the above process and will there be any performance improvement or performance variations from the above steps if i followed. Whether there be same value for all the three steps or will there be variation in from time and end time for each conditions I need to check for insertion time(time difference b/w from time and then loop condition and then end time) for each loopThanks!Mon, 24 Sep 2012 23:24:23 GMTyuvipoy