ODBC Inital connection taking a long time

[priestlm]

Hi Guys,

Wonder if anyone can help, my application uses the datadirect 4.10 32-bit Progress SQL92 v9.1E ODBC driver to connected to an MFG db to retreive order details, everything works fine until the MFG db server is restarted. After a server restart the ODBC driver takes approximatly 6 minutes to return order details, after the inital connection is made the ODBC driver responds & returns orders in a few seconds.

This happens eveytime the server is restarted (the server is restarted daily), is there any way i can get this first connection to behave in the normal mannor & return data within seconds instead of 5+ minutes?

Thanks
Mark

 

[TomBascom]

After a restart the buffer cache is empty. Until it is refilled your queries will have to got to disk. Disk access is thousands of times slower than memory access...

You could pre-fill the cache as part of the restart process. Just arrange to run the query right after restarting.

It is also possible that part of your problem is a poor query plan. You might want to run UPDATE STATISTICS on the SQL side.