SQL 2K Backend / Access97 Front - Query Time Too Long

[LearningSql]

Good day to All --

First off Thank You for all the advice everybody has given to me. I need to start to mark posts as Helpful as I have not given my dues to those that have assisted me. My apologies for that -- I'll do better.

Second -- I do not know if this is a post for this Forum and/or the MSAccess Forum. It could be for both but as noted below you'll understand why I posted here also.

I am the sole programmer for both a SQL 2K Backend and Access97 Front End for my group (An unfortunate circumstance but a nice challenge from my POV). I have developed a Db Backend with a table holding about 1,000,000+ rows of data with a 3 Field Primary Key (not my design -- I'm taking over this one).

On the Front End I have an Access97 DB with 1 table consisting of the Primary Keys only (the same 3 fields as the SQL 2K Table) and an ODBC link to the associated table.

When I query the SQL db (via INNER JOIN) using only 1 record in the table the process locks up for about 30-45 minutes. When I individually specify the 3 fields in the Query (no INNER JOIN) the 1 record comes up within 5 seconds. I will usually have about 3500+ records to query when I get past this point.

Any pointers I should be looking for in the Back End with SQL Server?

Any information will be useful to me in researching this issue.

Thank you and have a great day!
Pru

 

[SQLSister]

Are you doing the query in Access or in a stored procedure? Stored procedures are much faster than Access queries when running against a big table like this.

Not sure why you have the extra table with the primary keys, it seems to create an unnecessary join.

You may need also to look at the indexes on the table.