Pass-through query slow when used as data source

So here's my setup. We have a database with several tables that have over 255 fields. So we must use pass-through queries to access some of the data.

Usually no problem.

Here is my pass-through query:

qry001: SELECT db001.table001.field001 from db001.table001

If I execute this, it instantly spits me to datasheet view with all the millions of records ready for viewing.

If I modify it to:

SELECT db001.table001.field001 from db001.table001 WHERE db001.table001.field001 = '123'

It works fine and displays the one record instantly.

Now on to the problem

When I make a new query, using this pass-through query as a data source such as:

SELECT qry001.field001 FROM tblLocal001 LEFT JOIN qry001 ON tblLocal.field001 = qry001.field001;

This query takes forever to run.

And the thing is, tblLocal001 only has one record in it. In production, this table will have thousands so I need to understand why this query is taking so long and fix it if possible

Sponsored Links:

Related Topics

pass thru query
Using access 2003 front end sql server 2008 back end. When opening a form with a subform based on a pass through query, I am getting the following error:

You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport

I am using the pass through because subforms based on regular access query is slow since upsizing to sql server and thought pass through would be faster. I've search the internet but haven't come across any solutions.


Query runs slow
I up sized my 2003backend to SQL 2008. I kept my queries n access frontend. Now my update and append queries run really slow. What can I do. Should I look into converting to stored proc or pass through queries. Would this speed up performance.


Extend Access with Pass-Through Queries
In this article, we will devote our efforts to the latter of the three options, and concentrate on the use of Pass-Through queries as the medium of communication. Our examination of Pass-Through queries will include the following: the nature of Pass-Through queries, and instances in which their use is warranted; the advantages and disadvantages incumbent within the choice to use Pass-Through queries; creation and operation of a Pass-Through query to a MSSQL Server 2000 database.


ORA-01013 error.
When I run a pass-thru query, the data is returned, however if a make-table query is run on the pass-thru, the ORA error is returned. The pass-thru query's time out is set to 0. I don't think this is an oracle error because the pass-thru query works


Update access table from a pass-through query
I want update a table (in Access 2010) based on the results from a pass-through query that pulls data from an old Fox Pro database.
With the following query I push data into my access table (everything works fine):
....

When I try to update the same table using the same pass-through query:
....

I get this error: "Operation must use an updatable query"


Pass parameters to a query from a form
I have a query that returns a count of the number of products a customer currently has. The query has a parameter that asks for the customer_id.

I want to use the query as the control source of a field on a form. I need the form to pass the currently selected customer_id (the one they are looking at) as the query parameter value. How is this done, or is there a better way to do this? The form only looks at a single customer (and therefore customer_id) per page. Query is below:


Parameter Passing to a Report
Here is the situation: (I did not create this report.) I am running a report using the “DoCmd.OutputTo” in a VBA module. The report’s data source is a query which is the result of another query with the input date range prompts on the other query.

The report is always run for the previous work day. In my VBA module I want to figure the previous day’s date and pass it to the report process so that I can automate the report running without having to enter a date range.

How do you pass data to a query prompt in a VBA nodule?


Using filtered form data as record source for report
I have a split form in an Access 2007 database where the data grid shows the records from a query. The user can filter any combination of fields from the the drop down headers. I have a command button above the data grid that opens a report with the same query as its record source. I'm trying to figure out how to pass the filtered data to the report so it will show only those records that the user is seeing in the data grid of the split form.


Pass Through Query Report
I have created a pass through query to show the order volume for a specific group of sales reps. I would like to get a report set up based on that query that will summarize the data (i.e. tell the number of orders entered and the dollar amount for those orders instead of listing all order individually). I tried creating a report, but everytime I try to run it, my database locks up on me. Is there a way to generate a report based on a pass through query


How to modify sub report record source
I have a main report with up to three sub reports depending on what check boxes the user selects on the form that calls the report. There is always a small amount of data on the main report.

If the operator checks a check box for sub report 1, 2, or 3, then they appear as well. I have put the test for the check box in the sub report's underlying record source. However, this technique causes the sub report to open, read all the data from the record source query, and finally reject them all since the check box isn't checked.

This works but makes the report run very slow.


Automate pass-through query to run with different criteria and append to table
I have a pass-through query that pulls data for a certain cluster number from the DB2 IBM database using an OBDC. I can only run the query for two group numbers at a time since there is a large amount of data that is returned.

I also have a make table query and append table query. What I would like to be able to do is connect to the OBDC DB2 server, run the pass-throughquery for the first cluster number, create a table with the make table query, run the pass through query again with second group number, and append this to the table from the make tablequery and so forth until I have gone through the list of cluster numbers.

I have a list of cluster numbers: (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20….etc.) which I need to run the query on and append to the table.

My pass-through query looks something like this:


Access extreemly slow in responding
I've noticed that when I edit a Form or Report that's tied to a table that's linked to an external data source (I.e., another Access database), that Access responds REALLY slow, sometimestaking up to a minute to save/respond.

It's taking me hours just to make a few minor changes!


Slow Database Response
I have a new database which has only three records test data. The size is 422MB no pictures. The database is slow to open forms and extremely slow when closing what can cause this


Very slow Access 2002 query with Windows 7
I need help troubleshooting consistently slow queries in an Access 2002 database with Windows 7. I’m running a temporary query in a local copy of a back end database with only tables. The identical query will take just 2-3 seconds with Windows XP but 30-60 seconds with Windows 7.

Multiple Windows 7 workstations are all slow and all Windows XP machines run the query quickly.

I even used JetShowPlan on both workstations to compare and the plans are identical. Two tables are involved with rushmore used on the first and a full scan on the second table of about 150,000 records.

Even in XP Mode on the Windows 7 machine the query still takes only about 10 seconds. Anyone have any ideas why Access/Jet on Windows 7 can be so slow?


Pass parameters to a query from a form
I have a query that returns a count of the number of products a customer currently has. The query has a parameter that asks for the customer_id.

I want to use the query as the control source of a field on a form. I need the form to pass the currently selected customer_id (the one they are looking at) as the query parameter value. How is this done, or is there a better way to do this? The form only looks at a single customer (and therefore customer_id) per page. Query is below:

PARAMETERS contractor_name Short;
SELECT COUNT(contractor_leads.contractor_id) as [Actual Leads]
FROM contractor_leads
WHERE contractor_leads.contractor_id = contractor_name
AND contractor_leads.active = TRUE


Pass Through Query - not returning rows
I've written some SQL, which when run on SQL Server 2005 returns rows. Plugged this SQL into an Access 2003 pass through query, but when run it reports an error saying pass through query with return records set to true did not return any records.

I've got other pass through queries in this Access database that work fine. The SQL in this query is more complicated and creates a temp table - could this be causing the problem?


Pass-through Queries in Access 2003/XP/2000/97
A Pass-through query allows you to execute an SQL statement directly against the tables in an external database (such as an Oracle, Sybase, or SQL Server database). This method uses your ODBC connection to directly access the data in the external database without having to link to the tables or import the data from the external database.
This tutorial will demonstrate how to create pass-through queries as well as how to integrate pass-through queries with other queries in your Access database.


Access forms - Pass a date to a query
I have a mail merge set up with MS Word connected to "MyLetter" query I need to pass a date to the letters which would be perfect for a parameter query ("What date?") however Word does not recognise parameter queries

pass that date to the query design (as a calculated field), then open the word doc to complete the merge

I don't think you can pass a parameter to the query in the normal way because again Word wont recognise it

I know you can use an SQL command in VB to amend a query design its self


Passthrough Query Performance Slow Using Split Database
Prior to splitting the database, my PassThrough query performance was acceptable.
After splitting the database, my passthrough query has become sluggish and slow where my users are not very happy.

When I run a test prior to splitting my database, it would take about 1-5 seconds to retrieve the records using a passthrough.
But after splitting, now that passthrough query will take anywhere from 3 minutes - 10 minutes.

[CODE]

In my sub CompanyHistoryByPeriodTransactions, I added a couple of message boxes to gauge how long it took to/from the passthrough query. This is where I see my slow down occurring.


Slow performance (REALLY slow!)
My database performs incredibly slow, especially (but not only) in design mode. When I delete a text box from a form, it takes about 30 seconds. The db is compacted and I have a P4 2ghz 256k computer running XP pro.

There is User-level security on the db and I am accessing the db over a network. Nothing else is slow (including other network-based tasks).

Does anyone have any solutions or ideas for troubleshooting