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

[Dmitry]

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]

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" <bohr936@hotmail.com> wrote in message
news:29cb33c.0405210618.66d9405f@posting.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...

[Sonic]

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 Analyzer and opening up the Execution plan window, Query->Show
Execution Plan.

Now run your query in the analyzer 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_analysis.asp[/url]

[Dmitry]

I think the queries are not the matter, because when I run them in
Query Analyzer, 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]liam@liamhughes.com[/email] (Sonic) wrote in message news:<e00ae24f.0405260240.65566c2e@posting.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 Analyzer and opening up the Execution plan window, Query->Show
> Execution Plan.
>
> Now run your query in the analyzer 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_analysis.asp[/url]

[Mendel Nemanov]

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 Analyzer, 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.

>
>liam@liamhughes.com (Sonic) wrote in message

news:<e00ae24f.0405260240.65566c2e@posting.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 Analyzer and opening up the Execution plan

window, Query->Show

>> Execution Plan.
>>
>> Now run your query in the analyzer 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_analysis.asp

>.
>