Query takes forever on MSSQL Backend

[kmclane]

I thought it was supposed to be faster. I am testing by creating a duplicate table in MSSQL, then linking this table in a copy of my db. I then run identicl queries against the Access table and the MSSQL table. My Access table returns results in 41 secs. MSSQL takes 2:10 for the same query. Right now I set up a dsn to connect to the sql server. Is there a better way to do this that will return results faster? Will it be fairly simple to have my queries work? Should I just give this idea up? Any suggestions appreciated. Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[lespaul]

I haven't done this personally, but I've seen threads that mention the 'Pass-Through Query' which help improve the query speed. ( Might want to check the Help on that.) Does your MSSQL table have an index on it? That may also speed up the query.

Sorry I couldn't help more!

Les

 

[BNPMike]

Firstly, people who say MS SQL Server is faster usually don't understand how Jet works. MS SQL Server is better but then Jet its pretty good anyway.

Were you running the Jet database on a remote machine, or on your workstation? You can run massively inefficient SQL on a modern pc and it will fly. Maybe you could post the SQL and some details of the data.

 

[cmmrfrds]

Avoid linking the tables if at all possible. Small sql server tables are okay to link since there is not much data to move back to the PC to satisfy the query. If you link the tables the query is most likely done on the PC not on the server to get the advantage of sql server make sure the query is done on the server and only sends the resultset (record set) back to the PC. Pass-through queries are done on the server as are queries that can be executed through ADO. What version of Access are you running since the newer versions give you more options?

 

[glalsop]

To elaborate on the comments of lespaul and cmmrfrds:

You generally will not see the speed of record retrieval improve much if you create queries based on tables that are LINKED to a SQL Server database from an Access front end.

However, oftentimes the retrieval rate will improve dramatically if you use a pass-through query and pass the native SQL.

Good luck

-Gary

 

[kmclane]

Sorry for not responding quicker, been putting out fires. I will try changing to a passthrough query. I was looking for the Up sizer for Access 97, but it no longer exists at Mirosoft as far as I can find. I may see about upgrading to Access 2003, and try converting from there. Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Shamous]

Interesting Threads. I am having a similar problem. I have a small desktop, running MSSQL 2000 and Access 97! Yest Access 97. I have a very complex query running in 97 with linked tables to SQL. The query takes about 7 - 10 mins. Not to bad for what it has to do.

I purchased a much more powerfull dual processor Xeon machine. plently of ram, etc.

Converted my database to Access 2000 and install SQL 2000. Moved all data there. Relink everything to the new server. Tested the same query - took 2 hours....!!!

Any ideas why the speed would downgrade rather than improve.

Thanks

 

[kmclane]

Try to get away from linking tables. I built a connection string and now I create a querydef on the fly, what used to take 2-3 mins now takes a few seconds.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[cheerio]

The key concept here is network traffic.

An Access query on liked tables will fetch the records from the server across the network to the Access front end, inspect and test the data and show you the ones you want.

A pass through query will ask the server to send across the network just the records you need.

 

[Shamous]

Thanks. I understand the principles of the passthrough queries, I don't quite understand the actually mechanical use of them. The description of the query above is this. The query in question is a Make Table query. It contains 11 tables 4 querys that are joined in various ways, all indexed properly. 4 of the tables are from MSSQL 2000 (linked). Now I can see why I would get a big hit on performance due to the sheer complexity, but that still doesn't answer why this exact query runs better on a slower, Access 97/MSSQL 2000 machine than it does on the Turbo Xeno, Access 2000/MSSQL 2000 machine. I can live with the 4 mins. I can't live with the 4 days. (lol)

Rusty

 

[kmclane]

All I know is when I got rid of the link to the table and made a connection direct to the SQL DB things moved much faster. All the work gets done on the server then you get the results. When you have tables linked all the data has to travel back and forth. As to why it takes longer on the new machine, I don't know. But I deal with some very large data files and sometimes if you can break your process up into smaller chunks and build a macro to run it a piece at a time it can help too.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[Shamous]

I made a pass through query to the SQL DB on the biggest table. No help. I am going to do the same for the other 3 smaller tables

 

[cmmrfrds]

Paste your query into Query Analyzer and run directly against SQL Server without going through Access. How many records are you inserting? If it takes a long time directly on SQL Server then it may be how you have set up the SQL Server. Check out things like log files, database growth, and many more options on SQL Server.