CODEWITHSUNDEEP
sql-server-2008.net-3.5sqlcommand
nRk
nRk

Reputation: 1239

Sql Query Execution Time difference between SSMS & SqlCommand

I am executing one SQL query in SQL Server Management Studio (2008) and the same query using SqlCommand (.net 3.5) in C#.

In Management Studio the query executes in 1.6 minutes to complete. The same query is taking 9 minutes using SqlCommand in C#.

Why is the huge difference between SQL Server Management Studio & SqlCommand?

Please somebody help me in finding the solution in fixing the time difference.

Thanks in advance.

The query:

SELECT * INTO [target_table]
FROM [source_table] WHERE Group_ID IN (1,2,3,4,5,6,7,8,9,10)

This copies around 1 million rows into target_table & table contains 220 columns.

Upvotes: 1

Views: 1130

Answers (1)

Ann L.
Ann L.

Reputation: 13965

Since you're asking what causes the difference between the two environments, I'm going to try to answer that question, and not the much bigger question of how to make copying 1,000,000 records faster.

This question and answer provides a good guide to troubleshooting your situation. If you're using the exact same command or stored procedure call and consistently get this difference in results, the most likely culprit is different SET options.

Try using the profiler to see what SET options are set when you execute the insert in SSMS. Do the same thing with your C# routine. Compare them and see what SSMS sets differently than ADO.NET does.

Upvotes: 2

Related Questions