A very slow sql query execution using asp.net

[Dai Hao]

Hi all,

I have sql query to search for fields in a rather big view. If I execute the
query in sql server enterprise manager, the results will be displayed in
less than 6 seconds. However, if I execute it using asp.net, it will take
very long (more than 2 minutes).

The query is a simple one like "SELECT * FROM myview WHERE name LIKE
'%Microsoft%'". And the code I use to execute it in asp.net is

Dim dsRtn As DataSet
Dim objConnection As OleDbConnection
Try
objConnection = GetOleDbConnection()
objConnection.Open()
Dim objDataAdapter As New OleDbDataAdapter(strSearch, objConnection)
Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet, strTableName)
dsRtn = objDataSet
Catch ex As Exception
dsRtn = Nothing
Finally
If objConnection.State = ConnectionState.Open Then
objConnection.Close()
End If
End Try

Where strSearch is the sql search string.

I don't have any problem using such code for other queries.

Could somebody suggest how to solve the problem? Thanks!

Best regards,
David

 

[George Ter-Saakov]

Enterprise Manager shows data asynchronically.
Meaning as soon as first records available it shows.
Thus you having an impression that it worked instantly.

In your application you are showing records only after they all become
available.

You can always tell how long did it take to run the query by using SQL Query
Analyzer.

George.

 

[sallemann]

Query running slow in ASP.NET, fast in SSMS... solution...

I have a SQL stored procedure that I use as the SELECT for a SQLDATASOURCE for a GridView. When I run that query in SSMS, it runs in a second. When I run the ASP.NET app and go to that GridView, it would take 10 seconds to load. I had been making major mods to that stored procedure by commenting out the old lines and adding new lines of code. I had about 20 lines of code commented out. By deleting those lines altogether, the query ran in under a second from both SSMS and from ASP.NET. Guess it's not good to have a bunch of commented lines in a SPROC you're using for ASP.NET.

Who would have thought? Not me!

Hope this helps someone else.

 

[sallemann]

UPDATE

Well, it wasn't the commented out lines that were causing the problem. It was that I needed the WITH RECOMPILE option in the SPROC so the execution plan would be recreated each time it runs.

See this article for more info: http://www.techrepublic.com/article/understanding-sql-servers-with-recompile-option/5662581