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.

"Dai Hao" <wh**********@hotmail.com> wrote in message
news:Ok**************@tk2msftngp13.phx.gbl...

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