Timout when opening recordset

[T. Bradley Dean]

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

[jy]

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

[T. Bradley Dean]

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.