Professional Web Applications Themes

A Very slow response when I use 2 or more recordsets in ASP - ASP Database

I use ASP + SQL server 2000, one day I noticed that my scripts work rather slow... I found that it happens when I use 2 or more queries, for example, the following script <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "server3", "", "" Response.Write Now & "<br>" sql="SELECT 2 as sample" Set RS=Conn.Execute(sql) Response.Write Now & "<br>" Set RS=Conn.Execute(sql) Response.Write Now & "<br>" RS.close Conn.close set RS = nothing set Conn = nothing %> produces the following: 21.05.2004 18:03:03 21.05.2004 18:03:03 21.05.2004 18:03:12 9 seconds for a simple select?.. I noticed that if I use Conn.CursorLocation = adUseClient everything works ...

  1. #1

    Default A Very slow response when I use 2 or more recordsets in ASP

    I use ASP + SQL server 2000, one day I noticed that my scripts work
    rather slow... I found that it happens when I use 2 or more queries,
    for example, the following script

    <%
    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "server3", "", ""
    Response.Write Now & "<br>"
    sql="SELECT 2 as sample"
    Set RS=Conn.Execute(sql)
    Response.Write Now & "<br>"
    Set RS=Conn.Execute(sql)
    Response.Write Now & "<br>"
    RS.close
    Conn.close
    set RS = nothing
    set Conn = nothing
    %>

    produces the following:

    21.05.2004 18:03:03
    21.05.2004 18:03:03
    21.05.2004 18:03:12

    9 seconds for a simple select?..

    I noticed that if I use

    Conn.CursorLocation = adUseClient

    everything works as before, but I wonder, what could happen to server?
    no hardware or software or quantity of simultaneous users has been
    changed lately. The same script on other IIS works quickly without
    client cursors...
    Dmitry Guest

  2. #2

    Default Re: A Very slow response when I use 2 or more recordsets in ASP

    Turn on profiler and perfmon and watch what else is going on between select
    1 and select 2. Obviously there is no I/O or contention on the database
    itself, because you're not actually touching any tables or procedures. But
    something is going on that you're not going to be able to explain by code
    alone, I don't think...

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]




    "Dmitry" <bohr936hotmail.com> wrote in message
    news:29cb33c.0405210618.66d9405fposting.google.co m...
    > I use ASP + SQL server 2000, one day I noticed that my scripts work
    > rather slow... I found that it happens when I use 2 or more queries,
    > for example, the following script
    >
    > <%
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open "server3", "", ""
    > Response.Write Now & "<br>"
    > sql="SELECT 2 as sample"
    > Set RS=Conn.Execute(sql)
    > Response.Write Now & "<br>"
    > Set RS=Conn.Execute(sql)
    > Response.Write Now & "<br>"
    > RS.close
    > Conn.close
    > set RS = nothing
    > set Conn = nothing
    > %>
    >
    > produces the following:
    >
    > 21.05.2004 18:03:03
    > 21.05.2004 18:03:03
    > 21.05.2004 18:03:12
    >
    > 9 seconds for a simple select?..
    >
    > I noticed that if I use
    >
    > Conn.CursorLocation = adUseClient
    >
    > everything works as before, but I wonder, what could happen to server?
    > no hardware or software or quantity of simultaneous users has been
    > changed lately. The same script on other IIS works quickly without
    > client cursors...

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: A Very slow response when I use 2 or more recordsets in ASP

    You probabally need to set up dome form of Index on the Table.

    You can see whats going on with your queries by entering them into SQL
    Query yzer and opening up the Execution plan window, Query->Show
    Execution Plan.

    Now run your query in the yzer and look at the excution plan.
    Hover your mouse right click on the table and go to manage indexes.

    To know more about indexes try:
    [url]http://www.sql-server-performance.com/query_execution_plan_ysis.asp[/url]
    Sonic Guest

  4. #4

    Default Re: A Very slow response when I use 2 or more recordsets in ASP

    I think the queries are not the matter, because when I run them in
    Query yzer, they work quite quickly, I've set up the necessary
    indices.
    The problem appears when I use ASP recordsets, or, for example, open
    the tables in Access using ODBC. the delay doesn't depend on the
    queries, even the simple "SELECT 1" causes the delay.
    I tried to reinstall MDAC 2.8 but it didn't improve the situation.

    [email]liamliamhughes.com[/email] (Sonic) wrote in message news:<e00ae24f.0405260240.65566c2eposting.google. com>...
    > You probabally need to set up dome form of Index on the Table.
    >
    > You can see whats going on with your queries by entering them into SQL
    > Query yzer and opening up the Execution plan window, Query->Show
    > Execution Plan.
    >
    > Now run your query in the yzer and look at the excution plan.
    > Hover your mouse right click on the table and go to manage indexes.
    >
    > To know more about indexes try:
    > [url]http://www.sql-server-performance.com/query_execution_plan_ysis.asp[/url]
    Dmitry Guest

  5. #5

    Default Re: A Very slow response when I use 2 or more recordsets in ASP

    Could it be related to:
    [url]http://support.microsoft.com/default.aspx?scid=kb;en-[/url]
    us;235282
    INFO: SQL Server Spawns Additional Connections When You
    Open Multiple ForwardOnly ADO Recordsets

    that would explain why Conn.CursorLocation = adUseClient
    makes it faster.

    9 seconds is still a long time for opening a connection,
    so the problem may be something else.

    (put a resopnse.write now() at the begining so you can see
    how long it takes to open the first connection)

    Good luck

    Mendel Nemanov
    Spotlight Design
    >-----Original Message-----
    >I think the queries are not the matter, because when I
    run them in
    >Query yzer, they work quite quickly, I've set up the
    necessary
    >indices.
    >The problem appears when I use ASP recordsets, or, for
    example, open
    >the tables in Access using ODBC. the delay doesn't depend
    on the
    >queries, even the simple "SELECT 1" causes the delay.
    >I tried to reinstall MDAC 2.8 but it didn't improve the
    situation.
    >
    >liamliamhughes.com (Sonic) wrote in message
    news:<e00ae24f.0405260240.65566c2eposting.google. com>...
    >> You probabally need to set up dome form of Index on the
    Table.
    >>
    >> You can see whats going on with your queries by
    entering them into SQL
    >> Query yzer and opening up the Execution plan
    window, Query->Show
    >> Execution Plan.
    >>
    >> Now run your query in the yzer and look at the
    excution plan.
    >> Hover your mouse right click on the table and go to
    manage indexes.
    >>
    >> To know more about indexes try:
    >> [url]http://www.sql-server-[/url]
    performance.com/query_execution_plan_ysis.asp
    >.
    >
    Mendel Nemanov Guest

Similar Threads

  1. Slow to no response
    By Purple Haze in forum Coldfusion Server Administration
    Replies: 3
    Last Post: December 15th, 02:57 PM
  2. Slow Response
    By mwnfbcp in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: March 28th, 10:33 PM
  3. select * incredibly slow response in CF 7?
    By GaryG in forum Coldfusion Database Access
    Replies: 1
    Last Post: August 19th, 12:41 PM
  4. Slow login response response on TS 03 in AD mixed mode
    By Greg Glenn in forum Windows Server
    Replies: 0
    Last Post: June 25th, 09:53 PM
  5. FMP5.5 Application Response... slow!!!
    By Ben Bradley in forum FileMaker
    Replies: 7
    Last Post: October 8th, 05:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139