Sql Query Execution Time difference between SSMS & SqlCommand

Question

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.

Hi Marc, thank for quick comment. I added the query in the question. — nRk
– nRk, Commented Apr 22, 2013 at 17:05
OK, do you need all the columns?? If not - specify which ones you really do need! Also: is there an index on Group_ID ? — marc_s
– marc_s, Commented Apr 22, 2013 at 17:09
yes I need all the columns and the group_id included the index keys (composite primary key). — nRk
– nRk, Commented Apr 22, 2013 at 17:19
Hi , the below url can help; sommarskog.se/query-plan-mysteries.html — nRk
– nRk, Commented Apr 23, 2013 at 9:13

Community · Accepted Answer · 2017-05-23 11:43:18Z

2

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.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Apr 23, 2013 at 0:24

Ann L.

14k5 gold badges39 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nRk Over a year ago

Thanks Ann, for the reply. I can at the url explaining in detail: sommarskog.se/query-plan-mysteries.html

Collectives™ on Stack Overflow

Sql Query Execution Time difference between SSMS & SqlCommand

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related