Problem using cursorlocation for recordset

[shubha.sunkada]

Hi,

I have a recordset connection in asp that I am using to search
records.If I use the client side cursorlocation (rs.cursorlocation=3)
then it takes really long to return back the records due to which a
timeout occurs.If I change the cursorlocation to adUseNone(1) or
adUseServer(2) then the search is faster and without any problems.But
the sort on records cannot be done if I use adUseClient(3).I need to
have sort on these records.

Can somebody help me out.



Thanks

 

[Bob Barrows [MVP]]

Hi,

I have a recordset connection in asp that I am using to search
records.If I use the client side cursorlocation (rs.cursorlocation=3)
then it takes really long to return back the records due to which a
timeout occurs.If I change the cursorlocation to adUseNone(1) or
adUseServer(2) then the search is faster and without any problems.But
the sort on records cannot be done if I use adUseClient(3).I need to
have sort on these records.

Can somebody help me out.

Since you did not tell us what type of server-side cursor you are using
(forward-only, static, dynamic or keyset), I am going to assume it's the
default forward-only cursor. The reason the server-side cursor appears
to be faster is that ADO is only retrieving records from the server one
record at a time (the default CacheSize value is 1). the illusion of
speed you are seeing is simply that: an illusion. If you loop through
the recordset, you will see this for yourself. Looping through a
forward-only cursor will still be quicker than populating a client-side
static cursor.

The client-side cursor is a static cursor (you have no say in this: if a
client-side cursor is requested, you get a static cursor, regardless of
the cursor type you request). What happens with a client-side cursor is
that ADO uses a server-side firehose cursor to retrieve all the records
returned by your query and puts them into a static cursor supplied by
the ADO Cursor Library. This will take some time, especially if you are
retrieving a large number of records.

So, the conclusion is that your query is retrieving too many records,
leading to the timeout, and that you need to limit this in some way if
you need to use use the ADO Sort method. If you absolutely have to
retrieve such a large number of records, then you should consider using
a server-side cursor and allowing the database to sort the records
instead of ADO. Alternatively, you could increase the releant Timeout
properties, but this is not recommended in a web application.

PS. Using adUseNone causes ADO to default to adUseServer. You can view
the documentation here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireference.asp

 

[shubha.sunkada]

Since you did not tell us what type of server-side cursor you are using
(forward-only, static, dynamic or keyset), I am going to assume it's the
default forward-only cursor. The reason the server-side cursor appears
to be faster is that ADO is only retrieving records from the server one
record at a time (the default CacheSize value is 1). the illusion of
speed you are seeing is simply that: an illusion. If you loop through
the recordset, you will see this for yourself. Looping through a
forward-only cursor will still be quicker than populating a client-side
static cursor.

The client-side cursor is a static cursor (you have no say in this: if a
client-side cursor is requested, you get a static cursor, regardless of
the cursor type you request). What happens with a client-side cursor is
that ADO uses a server-side firehose cursor to retrieve all the records
returned by your query and puts them into a static cursor supplied by
the ADO Cursor Library. This will take some time, especially if you are
retrieving a large number of records.

So, the conclusion is that your query is retrieving too many records,
leading to the timeout, and that you need to limit this in some way if
you need to use use the ADO Sort method. If you absolutely have to
retrieve such a large number of records, then you should consider using
a server-side cursor and allowing the database to sort the records
instead of ADO. Alternatively, you could increase the releant Timeout
properties, but this is not recommended in a web application.

PS. Using adUseNone causes ADO to default to adUseServer. You can view
the documentation here:http://msdn.microsoft.com/library/en-us/ado270/htm/mdmscadoapireferen...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Hi,
I am using a dynamic server side cursor.Also my result set is not
huge ..just 80 records.One more thing that is wierd is that the result
set comes back instantly for the first search,but takes forever when
searched again for the same criteria.Eveytime I logout and login and
do the search for the 1st time the search is quick and takes forever
for consequnt searches.

Thanks

 

[Bob Barrows [MVP]]

On Jun 5, 10:05 am, "Bob Barrows [MVP]" <[email protected]>
wrote:
Hi,
I am using a dynamic server side cursor.

Why? Are you planning to be connected long enough for it to matter what
other users do? If so, you should probably rethink this. With ASP, the
idea should be to get in, get your data, and get out as quickly as
possible. Dynamic cursors aren't really suited for that goal.

Also my result set is not
huge ..just 80 records.

??? Well, there goes my theory. I would not expect there to be a
difference between a server-side and client-side cursor with only 80
records.

One more thing that is wierd is that the result
set comes back instantly for the first search,but takes forever when
searched again for the same criteria.Eveytime I logout and login and
do the search for the 1st time the search is quick and takes forever
for consequnt searches.

Without being able to reproduce your problem I am at a loss.
Try using SQL Profiler to see what is happening behind the scenes. Oh
wait, you never identified your database type and version. I don't know
why i assumed SQL Server.

 

[shubha.sunkada]

Why? Are you planning to be connected long enough for it to matter what
other users do? If so, you should probably rethink this. With ASP, the
idea should be to get in, get your data, and get out as quickly as
possible. Dynamic cursors aren't really suited for that goal.


??? Well, there goes my theory. I would not expect there to be a
difference between a server-side and client-side cursor with only 80
records.


Without being able to reproduce your problem I am at a loss.
Try using SQL Profiler to see what is happening behind the scenes. Oh
wait, you never identified your database type and version. I don't know
why i assumed SQL Server.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Thanks Bob.
I am using SQL Server and will try using the profiler.
About the time for searching using client side and server side as I
said I am not particular about using either.But the problem is I
cannot sort if using client side.

 

[Bob Barrows [MVP]]

Thanks Bob.
I am using SQL Server

What version?

and will try using the profiler.
About the time for searching using client side and server side as I
said I am not particular about using either.But the problem is I
cannot sort if using client side.

Why not? If anything, I would have thought you might have a problem with
Sort when using a server-side cursor ...

Let's get this out of the way. What problem are you having using Sort?
And why can't you use an Order By clause to allow the database to sort
your records? My preference would be to use a default server-side
forward-only cursor unless extra functionality is needed

You are going to have to start posting some relevant code (no html
please - just the vbscript code to allow us to see what you are doing).
It is impossible to help while in the dark like this.

http://www.aspfaq.com/5006

 

[Bob Barrows [MVP]]

Thanks Bob.
I am using SQL Server and will try using the profiler.
About the time for searching using client side and server side as I
said I am not particular about using either.But the problem is I
cannot sort if using client side.

I've just looked at the documentation and I am baffled. From the
documentation for the Sort property:

This property requires the CursorLocation property to be set to
adUseClient.

So you should not have any problem sorting with a clientside cursor ...

 

[shubha.sunkada]

I've just looked at the documentation and I am baffled. From the
documentation for the Sort property:

This property requires the CursorLocation property to be set to
adUseClient.

So you should not have any problem sorting with a clientside cursor ...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

When I say sort I do not just need the records in sort order but
should be able to let user sort dynamically.The user should be able to
point to any of the fields in result set and sort in ascending or
descending order.When I use client side cursor it is possible but not
server side.
Everything works fine when I use client side cursor except that after
the first search it keeps searching and never returns.

 

[Bob Barrows [MVP]]

When I say sort I do not just need the records in sort order but
should be able to let user sort dynamically.The user should be able to
point to any of the fields in result set .

Um, the user is not clicking on your recordset. he is clicking on an
html element that you generated using data in a recordset which is no
longer in existence. I assume the user's click is causing a post back to
your asp page in which you open a new recordset based on what the user
clicked.

Is that a fair description?

and sort in ascending or
descending order.

So modify the Order By clause in the sql used to retrieve the records
based on what the user clicked. I still see no need to use the Sort
property of the recordset.

When I use client side cursor it is possible but not
server side.

Well now you are saying the reverse of what you said earlier.

In your first post you said "But the sort on records cannot be done if I
use adUseClient(3)." (I should have questioned this statement at this
point)
In your second post you said " ... cannot sort if using client side."
And now you say : "When I use client side cursor it is possible but not
server side."

Everything works fine when I use client side cursor except that after
the first search it keeps searching and never returns.

And I keep saying, without seeing some code to see what you are doing,
we cannot help.

 

[shubha.sunkada]

Um, the user is not clicking on your recordset. he is clicking on an
html element that you generated using data in a recordset which is no
longer in existence. I assume the user's click is causing a post back to
your asp page in which you open a new recordset based on what the user
clicked.

Is that a fair description?


So modify the Order By clause in the sql used to retrieve the records
based on what the user clicked. I still see no need to use the Sort
property of the recordset.


Well now you are saying the reverse of what you said earlier.

In your first post you said "But the sort on records cannot be done if I
use adUseClient(3)." (I should have questioned this statement at this
point)
In your second post you said " ... cannot sort if using client side."
And now you say : "When I use client side cursor it is possible but not
server side."


And I keep saying, without seeing some code to see what you are doing,
we cannot help.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.- Hide quoted text -

- Show quoted text -

The description you gave about the way the records are sorted is
right.
I am sorry about the confusion on the client/server side.I am using
client side cursor.
I tried using the sql profiler yday and what I found is that the first
time the search is done the select query is called straight but the
second time a cursor.open method is used and then after the
cursor.fetch I see the cursor.close when I am getting the timeout.
Here is the code I am trying to use

Set Connection = Session("Connectionname")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
SQLText = "select * from CaseData"
rs.Open SQLText, Connection,1,3

Thanks

 

[Bob Barrows [MVP]]

The description you gave about the way the records are sorted is
right.
I am sorry about the confusion on the client/server side.I am using
client side cursor.
I tried using the sql profiler yday and what I found is that the first
time the search is done the select query is called straight but the
second time a cursor.open method is used and then after the
cursor.fetch I see the cursor.close when I am getting the timeout.
Here is the code I am trying to use

Set Connection = Session("Connectionname")

This is bad! You are storing an ADO Connection in Session. See:
http://www.aspfaq.com/show.asp?id=2053
Store the connection string in Application (not Session - only use
Session for variables that are user-dependant). In you ASP page,
instantiate a new Connection object and open it using the string stored
in Application. Always close the connection as soon as you are finished
with it. Allow ADO Session Pooling to work for you.

This is probably the root of your problem. Since you don't close the
connection, it is likely to still be busy with the previous resultset,
and therefore uses the cursor, which is very slow as you now have
verified.

The secondary lesson you should be learning from this is that cursors
should be avoided if possible.

Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
SQLText = "select * from CaseData"
rs.Open SQLText, Connection,1,3

<gasp>
You're retrieving ALL the records??? Why aren't you using a WHERE clause
to limit the records retrieved?

I suspect you are handling the retrieved records in a less than optimal
manner as well. See
http://databases.aspfaq.com/database/should-i-use-recordset-iteration-or-getrows-or-getstring.html

Also, you should avoid using selstar (select *) in production code:
http://www.aspfaq.com/show.asp?id=2096