ADP Reports cancell with no error because sql server takes to long

[Ben]

(repost from setupconfig)
I have an ADP that runs stored procedures from a SQL server 2000 database
and now that the table size has reached over 2million records, when we run
the reports, they just cancell them selves with no error being raised. I
believe this is caused by some sort of timeout occuring because SQL server is
taking too long to return records.

Is there any way to fix this? My users understand that they will need to
wait for their results due to the record size.

Thanks for any and all help
ben

 

[Sylvain Lafontaine]

There are three timeouts in ADP; the first two are located in the advanced
tab on the connection window: Connection menu: File --> Connection -->
Advanced tab for the « Connection TimeOut » and the All tab for the «
General Timeout ».


The third one is the « OLE/DDE TimeOut » in the Options dialog window, you
can try to set it to 0 and close/reopen Access when changing its value;
however I don't expect it to be usefull in your situation.


(There is also a fourth one: the « Command time-out » but this one is only
used when you are creating your own recordsets by using the OLEDB collection
of ADO objects. You should not be concerned with this one unless you are
creating your own recordsets as the sources of your reports. It may be
possible to set this value in the connection string by using the Extended
Properties in the All tab of the connection menu but I never tried this
myself.)


In your case, you should try setting the General Timeout to 0. However,
redesigning your schema and making sure that SQL statements and the stored
procedures used as the sources of your forms and reports are well optimized
and that you have all the required indexes might be a good idea.


You should try these record sources in SQL Query Analyser to see how much
time it takes and, most important, take a look at the generated query plan.
If your queries are not optimized then it's possible that you will be able
to greatly reduce their execution times. See m.p.sqlserver.server and
m.p.sqlserver.programming for more infos.

 

[Ben]

Thank you very much for your help.

I did as you suggested and it looks like its working. Queries that would
take 2 min to complete are being reported. It turns out the connection
timeout value was blank and i set that to zero.

Also, I have tried optimizing my queries, however, i still find that without
a structure change (which i cannot do because the table structure is
proprietary), I am stuck with the current performance levels.

Thanks again.