Pass through Query slow

jmersing

Well-known Member
Joined
Apr 14, 2004
Messages
887
I'm seeing some strange behavior when I run a pass through query. I am passing it through a Teradata database. When I execute the queries the records seem to appear almost instantly in the query reults window, but if you try to go to the last record (ctrl+down arrow) or copy the contents an hourglass appears and I sometimes have to wait for 3-4 mins for it to go to the last record.

I'm just wondering if I have asetting wrong, or string to database setup wrong? I have the access databae in SQL ANSI92 mode.

Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
If you try to go to the last record it will definitely be slow. I think the issue is that Access needs to process all the records individually for you to be able to move through them.

Typically, I use pass-through to get the data I need, but then use a make-table query to create a local copy which I can then manipulate at will. For better performance, the make-table query should operate *only* on data from the remote source, without using any Access-s[ecific functions.

Denis
 
Upvote 0
I have had this problem when I had databases greater than a few 100,000 lines.

Issues are: RAM capacity and chip speed.

My solution if you are the end user and everyhting flows correctly, without purchasing more RAM (but the investment is worth it if you use this info routinely and the databases are large):

Instead of using the end of data feature, type the line number you want to go to, as you do in Excel A56000.

I went in incriments of 100,000
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top