CODEWITHSUNDEEP
c#databasedataadapter
seeker
seeker

Reputation: 3333

Correct work with MS SQL

I have a program that gets data from SQL server table. The code is the following:

SqlConnection conn=new SqlConnection(...);//correct
conn.Open();
DataTable dt=new DataTable();
SqlCommand selectCMD = new SqlCommand("SELECT * FROM TABLE WHERE Condition", conn);
SqlDataAdapter custDA = new SqlDataAdapter();
custDA.SelectCommand = selectCMD;
custDA.Fill(dt);
Datagridview1.DataSource=dt;
Datagridview1.DataBind();

But the problem is that when executing the same query in SQL server management studio, it takes less then second to execute. Meanwhile when using program, it takes half a minute to get the result. Using debugger I see, that the main row, where program "thinks" a lot time is when data adapter is filling DataTable. Any suggestions how to reduce the time? What's wrong in my code?

Upvotes: 0

Views: 385

Answers (4)

seeker
seeker

Reputation: 3333

Thank Everyone for help. I used parameters in SqlCommand object. Unfortunately I haven't mentioned that so you couldn't help me. But as James posted a link, I have found that when SqlCommand is uded with parameters then the execution is made using stored procedure sp_execute. Because server has to compile it, that's why it takes so long. After parameters were removed, everything is working good. The other way is that you can turn off automatic recomplilation each time stored procedure executes. Thanks everyone again. +1 for everyone.

Upvotes: 0

AA.
AA.

Reputation: 4606

You can to create index and to set options to accelerate query execution. And you can to use this approach to load data SqlDataAdapter.Fill - Asynchronous approach

create index condition_idx on table (condition)

new SqlCommand("set nocount on;SELECT * FROM TABLE WHERE Condition", conn);

Upvotes: 1

Pit Digger
Pit Digger

Reputation: 9780

Check that you might need proper indexing on required columns . When you are running query from SQL it might be using an execution plan which is more optimized compared to when .Fill method is doing .

Can you try cleaning out procedure cache and memory buffers using SSMS:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Doing so before you test your query prevents usage of cached execution plans and previous results cache.

Upvotes: 1

Peter Ritchie
Peter Ritchie

Reputation: 35881

Managaement studio just displays text results. SqlDataAdapter must map each result column value to a DataGridView column value. One will take much more time than the other. With Management Studio, it also virtualizes the results--it doesn't show all the results at once as you scroll down for large result sets more data is retrieved.

Upvotes: 3

Related Questions