Timout when opening recordset


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Timout when opening recordset

  1. #1
    T. Bradley Dean Guest

    Timout when opening recordset

    I'm trying to open an ADO recordset, but I'm getting a timeout error. DB is
    SQL7.

    Using SQL Server Query Analyzer I can successfully run the SQL statement,
    but it takes 31 seconds. I'm assuming that my recordset object times out at
    30.

    Question 1) How do I adjust the timeout property for a recordset? I see it
    for the connection object, but not the recordset.

    Question 2) What is wrong with my statement?

    This takes 31 seconds and returns one row:
    Select Top 1 tblPurchaseActivations.OrderNumber,
    tblPurchaseActivations.Serial From tblPurchaseActivations Inner Join
    tblPurchases On tblPurchaseActivations.OrderNumber =
    tblPurchases.OrderNumber Where Item = 'DPC-USB' And tblPurchases.OrderDate <
    '11-01-00' And tblPurchases.PayDate < '10-31-00' And
    Len(tblPurchaseActivations.Serial) > 10 Order By tblPurchases.OrderDate Desc

    If I take out "Top 1" then it returns 1698 rows in 1 second! Am I not using
    "Top" correctly?

    TIA,

    --
    Bradley Dean





  2. #2
    jy Guest

    Re: Timout when opening recordset


    "T. Bradley Dean" <Bradley.Dean@InfoDish.com> wrote:
    >I'm trying to open an ADO recordset, but I'm getting a timeout error. DB

    is
    >SQL7.
    >
    >Using SQL Server Query Analyzer I can successfully run the SQL statement,
    >but it takes 31 seconds. I'm assuming that my recordset object times out

    at
    >30.
    >
    >Question 1) How do I adjust the timeout property for a recordset? I see

    it
    >for the connection object, but not the recordset.
    >
    >Question 2) What is wrong with my statement?
    >
    >This takes 31 seconds and returns one row:
    >Select Top 1 tblPurchaseActivations.OrderNumber,
    >tblPurchaseActivations.Serial From tblPurchaseActivations Inner Join
    >tblPurchases On tblPurchaseActivations.OrderNumber =
    >tblPurchases.OrderNumber Where Item = 'DPC-USB' And tblPurchases.OrderDate

    <
    >'11-01-00' And tblPurchases.PayDate < '10-31-00' And
    >Len(tblPurchaseActivations.Serial) > 10 Order By tblPurchases.OrderDate

    Desc
    >
    >If I take out "Top 1" then it returns 1698 rows in 1 second! Am I not using
    >"Top" correctly?
    >
    >TIA,
    >
    >--
    >Bradley Dean
    >
    >
    >
    >

    Firstly the timeout: try using a command object as the recordset's source,
    and set its commandtimeout property to a higher figure (eg. 60). If you only
    need a read-only, forward-only recordset then the command.execute method
    will suffice, else pass the command object to the recordset.open method.

    Secondly, the 'Top 1' issue: don't forget that sql has to first extract all
    records that satisfy your criteria, then sort them, and then pull out the
    first row - hence the large increase in time taken. You might try playing
    about with the table indices - an index on the tblPurchases.OrderDate field
    should prove helpful, since that's the field you are ordering on. J-Y.

  3. #3
    T. Bradley Dean Guest

    Re: Timout when opening recordset

    Thanks so much!

    > Firstly the timeout: try using a command object as the recordset's source


    Using a command to open the recordset should work great.

    > Secondly, the 'Top 1' issue: don't forget that sql has to first extract

    all

    Okay, that makes sense. I was assuming that SQL would just pull the first
    record, not pull all the records and then pull the first from those. Now I
    see why it takes so long.

    Thanks again,

    --
    Bradley Dean


    "jy" <jy@directdialog.com> wrote in message news:3ab9c709$1@news.devx.com...
    >
    > "T. Bradley Dean" <Bradley.Dean@InfoDish.com> wrote:
    > >I'm trying to open an ADO recordset, but I'm getting a timeout error. DB

    > is
    > >SQL7.
    > >
    > >Using SQL Server Query Analyzer I can successfully run the SQL statement,
    > >but it takes 31 seconds. I'm assuming that my recordset object times out

    > at
    > >30.
    > >
    > >Question 1) How do I adjust the timeout property for a recordset? I see

    > it
    > >for the connection object, but not the recordset.
    > >
    > >Question 2) What is wrong with my statement?
    > >
    > >This takes 31 seconds and returns one row:
    > >Select Top 1 tblPurchaseActivations.OrderNumber,
    > >tblPurchaseActivations.Serial From tblPurchaseActivations Inner Join
    > >tblPurchases On tblPurchaseActivations.OrderNumber =
    > >tblPurchases.OrderNumber Where Item = 'DPC-USB' And

    tblPurchases.OrderDate
    > <
    > >'11-01-00' And tblPurchases.PayDate < '10-31-00' And
    > >Len(tblPurchaseActivations.Serial) > 10 Order By tblPurchases.OrderDate

    > Desc
    > >
    > >If I take out "Top 1" then it returns 1698 rows in 1 second! Am I not

    using
    > >"Top" correctly?
    > >
    > >TIA,
    > >
    > >--
    > >Bradley Dean
    > >
    > >
    > >
    > >

    > Firstly the timeout: try using a command object as the recordset's source,
    > and set its commandtimeout property to a higher figure (eg. 60). If you

    only
    > need a read-only, forward-only recordset then the command.execute method
    > will suffice, else pass the command object to the recordset.open method.
    >
    > Secondly, the 'Top 1' issue: don't forget that sql has to first extract

    all
    > records that satisfy your criteria, then sort them, and then pull out the
    > first row - hence the large increase in time taken. You might try playing
    > about with the table indices - an index on the tblPurchases.OrderDate

    field
    > should prove helpful, since that's the field you are ordering on. J-Y.




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center