Pass through query speed

[Vaughan]

I have written a pretty convoluted pass through query to a SQL Server
database. A parameter is collected using a simple form that uses code to
modify the query definition before running it.

The first time I run the query with a revised parameter it takes about a
minute to return the required data. If I then run it again without changing
the parameter it takes around 3 seconds.

Obviously I prefer the 3 second response time. Can anyone explain why it
takes so long the first time? Is the data being cached by SQL Server or
Access? Is there some kind of compilation process going on invisibly? Is
there any way I can shortcut whatever is causing the delay? Any tips for
speeding up my query?

 

[Rick Brandt]

I have written a pretty convoluted pass through query to a SQL Server
database. A parameter is collected using a simple form that uses code
to modify the query definition before running it.

The first time I run the query with a revised parameter it takes
about a minute to return the required data. If I then run it again
without changing the parameter it takes around 3 seconds.

Obviously I prefer the 3 second response time. Can anyone explain why
it takes so long the first time? Is the data being cached by SQL
Server or Access? Is there some kind of compilation process going on
invisibly? Is there any way I can shortcut whatever is causing the
delay? Any tips for speeding up my query?

If you make a stored procedure that is run by your passthrough query then you
will get a pre-compiled execution plan. With a pure passthrough then the plan
is built at execution time. Caching of some sort probably does account for the
speedier performance after the first time.