A very slow sql query execution using asp.net

Discussion in 'ASP .Net' started by Dai Hao, Dec 2, 2003.

  1. Dai Hao

    Dai Hao Guest

    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
    Dai Hao, Dec 2, 2003
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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, Dec 2, 2003
    #2
    1. Advertising

  3. Dai Hao

    sallemann

    Joined:
    Nov 14, 2011
    Messages:
    2
    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, Nov 14, 2011
    #3
  4. Dai Hao

    sallemann

    Joined:
    Nov 14, 2011
    Messages:
    2
    sallemann, Nov 16, 2011
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Earl Teigrob
    Replies:
    7
    Views:
    420
    Scott M.
    Feb 16, 2004
  2. Kevin
    Replies:
    1
    Views:
    438
    ~kurt
    May 25, 2007
  3. Alf P. Steinbach /Usenet

    Slow -- VERY slow brain

    Alf P. Steinbach /Usenet, Jun 16, 2011, in forum: C++
    Replies:
    17
    Views:
    501
    Noah Roberts
    Jun 29, 2011
  4. Nick Green
    Replies:
    4
    Views:
    182
    Nick Green
    Nov 18, 2009
  5. George
    Replies:
    3
    Views:
    294
    Robert Klemme
    May 17, 2012
Loading...

Share This Page