Problem using cursorlocation for recordset

Discussion in 'ASP General' started by shubha.sunkada@fds.com, Jun 5, 2007.

  1. Guest

    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
     
    , Jun 5, 2007
    #1
    1. Advertising

  2. wrote:
    > 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
    --
    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.
     
    Bob Barrows [MVP], Jun 5, 2007
    #2
    1. Advertising

  3. Guest

    On Jun 5, 10:05 am, "Bob Barrows [MVP]" <>
    wrote:
    > wrote:
    > > 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/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
     
    , Jun 5, 2007
    #3
  4. wrote:
    > On Jun 5, 10:05 am, "Bob Barrows [MVP]" <>
    > 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.



    --
    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.
     
    Bob Barrows [MVP], Jun 5, 2007
    #4
  5. Guest

    On Jun 5, 2:13 pm, "Bob Barrows [MVP]" <>
    wrote:
    > wrote:
    > > On Jun 5, 10:05 am, "Bob Barrows [MVP]" <>
    > > 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.
    >
    > --
    > 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.
     
    , Jun 5, 2007
    #5
  6. wrote:
    >
    > 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

    --
    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.
     
    Bob Barrows [MVP], Jun 5, 2007
    #6
  7. wrote:
    > 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 ...
    --
    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.
     
    Bob Barrows [MVP], Jun 5, 2007
    #7
  8. Guest

    On Jun 5, 4:13 pm, "Bob Barrows [MVP]" <>
    wrote:
    > wrote:
    > > 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 ...
    > --
    > 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.
     
    , Jun 5, 2007
    #8
  9. wrote:
    > On Jun 5, 4:13 pm, "Bob Barrows [MVP]" <>
    > wrote:
    >> wrote:
    >>> 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


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

    --
    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.
     
    Bob Barrows [MVP], Jun 5, 2007
    #9
  10. Guest

    On Jun 5, 6:10 pm, "Bob Barrows [MVP]" <>
    wrote:
    > wrote:
    > > On Jun 5, 4:13 pm, "Bob Barrows [MVP]" <>
    > > wrote:
    > >> wrote:
    > >>> 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

    >
    > > 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.
    >
    > --
    > 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
     
    , Jun 6, 2007
    #10
  11. wrote:
    > 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

    --
    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.
     
    Bob Barrows [MVP], Jun 6, 2007
    #11
    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. Hung Huynh
    Replies:
    8
    Views:
    330
    Bob Barrows
    Sep 24, 2003
  2. Roger Withnell
    Replies:
    9
    Views:
    352
    Aaron Bertrand - MVP
    Dec 2, 2003
  3. Bruno Alexandre
    Replies:
    5
    Views:
    180
    Bob Barrows
    Mar 3, 2004
  4. Dan Sikorsky
    Replies:
    0
    Views:
    181
    Dan Sikorsky
    Apr 10, 2004
  5. Cursorlocation issue

    , Jun 5, 2007, in forum: ASP General
    Replies:
    0
    Views:
    90
Loading...

Share This Page