pass-through query report recordsource extremely slow

[tiesto]

I have a strange problem which I have not been able to solve after hours and hours of google searching. I have a report in Access 2003 which is based on a pass-through query linked to an SQL Server 2005 database. On a button click I alter the SQL of a saved pass-through query which the report has as it's recordsource.

In Management Studio the query runs in about 15 seconds. If I run the query directly from the Queries pane in Access, it also takes about 15 seconds. However, when I open the report based on the pass-through query, it takes around 3-4 minutes. I have tried using a make-table query to have the pass-through query results append to a temp table and then based the report on that, the CurrentDb.Execute line in VBA takes 3-4 minutes to run. Same as if I have the temp table already created and use an append query.

Any ideas would be much appreciated!

 

[tiesto]

I've just realised that when I run the pass-through query directly from the Queries pane it is also taking 3-4 mins, it was just showing the results before the query had finished. So my problem is the pass-through query itself running far slower than the exact same thing run in SQL Server.

 

[traingamer]

So my problem is the pass-through query itself running far slower than the exact same thing run in SQL Server.

You'll feel better if you try to run a JET query and see how slow that is, but you simply may not be able to achieve any better results than you're getting (with Access and a network thrown into the mix).

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard