Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL server in loop Evaluation Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 8:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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!
Post #1364075
Posted Tuesday, September 25, 2012 8:36 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 4,046, Visits: 9,205
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1364085
Posted Tuesday, September 25, 2012 8:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:11 AM
Points: 336, Visits: 1,155
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!

Post #1364093
Posted Tuesday, September 25, 2012 8:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 7,179, Visits: 15,776
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?
Post #1364103
Posted Tuesday, September 25, 2012 9:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 4,046, Visits: 9,205
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1364114
Posted Tuesday, September 25, 2012 9:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:21 PM
Points: 7,928, Visits: 9,653
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
Post #1364121
Posted Tuesday, September 25, 2012 9:42 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1364141
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse