Query Problem

[Ray1127]

I have an Access 2000 Front end to a SQL Server 2000 Database Backend that I inherited from a previous employee. There is 1 query that takes a few seconds to run. There was 2 additional pieces of data that needed to be added 1 from an additional table and 1 from another query. the additional query takes < 5 seconds. After making those changes the query now takes over an hour to run. I've reattached all of the SQL Server DB's and made sure all of the criteria and linked fields were indexed with no improvement. Any ideas on what may be wrong.

Thanks for your assistance.

 

[smedvid]

Just a couple of quick thoughts...

Is all the data stored in SQL Server 2000? Sounds like you may be attempting to join a SQL Server Pass-Through Query to a local MS Access table, as a guess? Or, just the new table and volume of data may impact performance especially if no indexes exist on foriegn key fields or fields used in a WHERE clause.

If possible, use SQL Pass-Thru Queries as much as possible.

Let me know if there are additional details...

Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Ray1127]

All of the Data is stored in SQL Server 2000. When linking to the SQL Server tables from Access I created indexes to match all of the linked fields and criteria for the query. The additional table is in the same SQL Server DB as the rest of the data. The query that is included is also based on SQL Server 2000 Data. All of the join's between the tables are inner joins but the query is joined as a left outer join since not all records are reflected in the query.

 

[smedvid]

Great that all data is located in SQL Server and you have optimized queries via indexes.

You mentioned Linked Tables. Are the queries MS Access based queries, where table names reflect dbo_sometablename?

If so, I would recommend looking into Pass-Through Queries to greatly improve performance. Pass-Through Queries basically will execute the query on the SQL Server Database then only send the results set back to you. This is a huge performance gain I typically use. ~1 hour MS Access query with linked tables down to 1 minute pass-through query.

Using MS Access; all data is loaded into MS Access from SQL Server then the queries are processed in MS Access.

Last thought... You are not accidently producing a Cartesian product by chance?

Perhaps try to develop the query with only as few records returned to confirm it works as designed.

htwh,


Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[lespaul]

All of the join's between the tables are inner joins but the query is joined as a left outer join since not all records are reflected in the query

adding a left join, whether into a query or a table, is going to slow down your results regardless....

Leslie

Have you met Hardy Heron?

 

[Ray1127]

I took out the Left join and the associated query only leaving the tables and got no improvement. The SQL Pass-through queries sound interesting but I've never done one and I'm not sure how. I'll look into it some more this afternoon. Thanks