[AccessD] fIRST TIME ACCESS TO SQL SERVER

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

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com