SQL server in loop Evaluation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL server in loop Evaluation

17 posts, Page 1 of 2

1 2 »»

SQL server in loop Evaluation

Rate Topic

Display Mode

Topic Options

Author

Message

yuvipoy

Posted Monday, September 24, 2012 11:24 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:41 AM
Points: 110, Visits: 513

Hi,
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 file
For the 200+ Columns i am having 2PK's.

CONDIDTIONS

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 loop

Thanks!

Post #1363791

Grant Fritchey

Posted Tuesday, September 25, 2012 4:23 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 13,375, Visits: 25,159

Looping 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.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1363915

yuvipoy

Posted Tuesday, September 25, 2012 6:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:41 AM
Points: 110, Visits: 513

What 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!

Post #1363962

Grant Fritchey

Posted Tuesday, September 25, 2012 6:07 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:04 PM
Points: 13,375, Visits: 25,159

I'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.

Here's an article differentiating the set-based operation.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1363965

yuvipoy

Posted Tuesday, September 25, 2012 6:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:41 AM
Points: 110, Visits: 513

The 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.

I still don't completely understand what you're trying to do or why it has to be done in a loop.

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!

Post #1363973

Luis Cazares

Posted Tuesday, September 25, 2012 7:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:52 PM
Points: 960, Visits: 1,921

Are 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. Generating Test Data: Part 1 - Generating Random Integers and Floats and this Generating Test Data: Part 2 - Generating Sequential and Random Dates
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 The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

Can you show us what is the code sent to SQL Server for your 5 row insertion?

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1364028

yuvipoy

Posted Tuesday, September 25, 2012 7:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:41 AM
Points: 110, Visits: 513

Most of the people here won't take the approach you're using. I'm not sure what is completely your goal.

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.

Can you show us what is the code sent to SQL Server for your 5 row insertion?

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!

Post #1364036

Luis Cazares

Posted Tuesday, September 25, 2012 7:50 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:52 PM
Points: 960, Visits: 1,921

What I was really hoping to see is how the final statement looks when you execute it.
It's different if it looks like this

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)

Than if it looks like this

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

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.

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1364048

L' Eomot Inversé

Posted Tuesday, September 25, 2012 8:13 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 7,088, Visits: 7,143

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.
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.

Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1364066

yuvipoy

Posted Tuesday, September 25, 2012 8:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 12:41 AM
Points: 110, Visits: 513

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!

Post #1364071

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions