[AccessD] fIRST TIME ACCESS TO SQL SERVER

I find it amusing that 50 years after the birth of the database any query presented to the database 
engine will be given all the available resources of the entire world.  Something reasonable like 
"20% of the remaining available resources unless specified otherwise" certainly makes sense.

John W. Colby
www.ColbyConsulting.com


Jim Lawrence wrote:
> Immediately after a sequel is run on your server it is cached and will
> remain cached for a certain amount of time, depending on how you have it set
> up. After a while the run sequel command will be closed, the query execution
> plan will terminated, any 'hints' will be lost and the cache will emptied.
> 
> The next time you go in with the same sequel request, after a delay
> everything will have to be restarted again from scratch. You will probably
> notice that SPs starts up and run far faster than passing a sequel query
> string. 
> 
> A few years ago I was working doing maintenance and producing reports on an
> big Oracle installation. When doing testing before translating the sequel
> into procedures (Oracles SPs) I would run a series of tests via Access
> pass-throughs. A particularly complex string; one of around 500 lines with
> lots of joins would take about 15 minutes to run, at which time I would head
> for another coffee. As I pasted desks people would be remarking things like,
> "My station seems to have locked up...". Well it appears that this
> pass-through query was actually locking up three honking giant HP Prolinent
> servers... Finally the senior IT guy came down and quietly asked me to do my
> tests at lunch and get these tests done as quickly as possible and converted
> into Procedures.
> 
> After that I have tried to move all sequel query strings, for clients into
> SPs as quickly as possible. Running complex pass-through queries is just too
> expensive for most sites.
> 
> Jim
> 
>       
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, May 25, 2010 12:13 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] fIRST TIME ACCESS TO SQL SERVER
> 
> I am using ODBC to link to sql server views from Access 2003.  The very
> first view I open causes a 
> delay of several seconds.  After that none of the views have a delay.  I
> think (and will report back 
> later) that after a time of inactivity I encounter that delay again.
> 
> Is there something I can do to prevent that opening delay? (I suspect not).
> 
> Is there something I can do to prevent the "timeout" or whatever is
> occurring that causes the delay 
> to be encountered again later?  (I suspect so).
>