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. Advertisements

  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.
     
    George Ter-Saakov, Dec 2, 2003
    #2
    1. Advertisements

  3. Dai Hao

    sallemann

    Joined:
    Nov 14, 2011
    Messages:
    2
    Likes Received:
    0
    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
    Likes Received:
    0
    sallemann, Nov 16, 2011
    #4
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.