Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL server in loop Evaluation


SQL server in loop Evaluation

Author
Message
yuvipoy
yuvipoy
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 1308
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.


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.

Also, it's not a good idea to call Console.WriteLine inside the measured time.

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!
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18081
yuvipoy (9/25/2012)

INSERT INTO test(col1, col2) VALUES
( @param1, @param2),
( @param1, @param2),
( @param1, @param2),
( @param1, @param2),
( @param1, @param2)


How you are saying that the above and

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)


are same when i am issuing a loop for each and every record


While i<=2
Begin
INSERT INTO test(Col0, col1, col2) VALUES( i,@param1, @param2)
Set @i=@+1
End
which means that the loop value is incremented and same need to be stored and not as you stated right?

Thanks!

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:


DECLARE @Test   TABLE(
   col0   int,
   col1   varchar(15),
   col2   datetime);

DECLARE @int   int,
      @param1   varchar(15),
      @param2   datetime2,
      @i      int

SET @int = 1
SET @param1 = 'MyTest'
SET @param2 = SYSDATETIME()

While @int <= 100000
Begin
   INSERT INTO @Test(Col0, col1, col2) VALUES( @int,@param1, GETDATE())--@param2)
   Set @int=@int+1
End ;

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 test

SET @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 <= 100000

SELECT DATEDIFF( MCS, @param2, SYSDATETIME())
--Once again, you could show the results but it's time consuming
--SELECT * FROM @Test




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
yuvipoy
yuvipoy
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 1308
Thanks for your replay,
As i have stated early
Inserting the data externally not from the database itself.
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!
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7624 Visits: 18043
yuvipoy (9/25/2012)
Thanks for your replay,
As i have stated early
Inserting the data externally not from the database itself.
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!



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.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18081
yuvipoy (9/25/2012)
Thanks for your replay,
As i have stated early
Inserting the data externally not from the database itself.
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!



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 http://www.sqlservercentral.com/articles/T-SQL/63003/


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10640 Visits: 11980
yuvipoy (9/25/2012)
I have missed the last 2 lines command.ExecuteNonQuery(); and command.Parameters.Clear(); statement in the code.

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.

Tom

Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10332 Visits: 11350
Using 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 http://msdn.microsoft.com/en-us/library/ms162802.aspx
- The BULK INSERT command http://msdn.microsoft.com/en-us/library/ms188365.aspx
- The OPENROWSET (BULK... command http://msdn.microsoft.com/en-us/library/ms190312.aspx

You can find an overview at http://msdn.microsoft.com/en-us/library/ms175937.aspx

From a .NET language connecting to SQL Server 2008, options for bulk/batch loading include:

- SqlBulkCopy http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
- Table-valued parameters http://msdn.microsoft.com/en-us/library/bb675163.aspx

You can find examples of using SqlBulkCopy and table-valued parameters all over the Internet. A couple of examples below:

http://orionseven.com/blog/2009/09/30/using-table-valued-parameters-in-sql-server-2008-and-c/
http://weblogs.sqlteam.com/mladenp/archive/2006/08/26/11368.aspx
http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server
http://florianreischl.blogspot.co.nz/2009/11/table-valued-parametes-performance.html

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.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search