SQL Clone
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 1387
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!
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98581 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 1387
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!
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98581 Visits: 33014
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 1387
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!
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42025 Visits: 19829
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.
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 1387
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!
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42025 Visits: 19829
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.
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26015 Visits: 12499
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

yuvipoy
yuvipoy
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2400 Visits: 1387

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