Web Development

Ask something
Discussion Locked

Access 97 to SQL 7 - speed

0 helpful
11 replies

by bob sellman

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a number of Access 97 applications that I would like to convert to SQL 7 by first converting the back end data to SQL (easy to do) while modifying the Access application to use the SQL database instead of the Access database. Later I will consider a re-write in VB.

I am testing this with a database with a primary data table with about 1.3 million rows of data with two indexed columns, one a date column and the other a text column. I'm using pass through queries to connect to SQL (nolinking of tables).

Access here means the Access back end data, SQL means the same data is in the SQL 7 database. Both databases are located on servers.

Comparison of operation speed:
Open the table (Access) or query (SQL) with no selection criteria:
Access about 5 seconds to display anything, SQL about 1 second. Winner: SQL

Open using a query (SQL is a pass through query with proper syntax for SQL) with date column selected as greater than a date and the results sorted by the indexed text column:
Access about 9 seconds to display anything, SQL about 31 seconds.

SQL should be as fast or faster. What might I be doing wrong? Has anyone experience with this type of conversion?

11 replies

by sl-campbell

You can also check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q110352

The querys must be optimized correctly.

A few other factors to consider are are:

>Cursor type - Client or server (use server whenever possible)
>IndexType -clustered/non-clustered (depending on the number of records returned)

Try using the option settings:

-ShowPlan/SqlTrace for SQL

-ShowPlan for Jet
-SqlTraceMode for ODBC

With these options you can see what actually is being sent tothe query proceesor and if the queries are optimized or not, if they use/can use Rushmore, etc. Then you can compare how/how far each query sent is being optimized by the native processors.

Not knowing what is being compared here (Data amount, table widths, user count) I would like to offer the following:
Accessing a back-end MDB DATABASE using the Jet processor via ADO/DAO (DAO-Jet especially) MAY be faster under certain conditions than using SQL server db - I have found this to be often true(remember: DAO/Jet is native to the MDB of course). You have to compare the right things. A small file db with 1 or 2 users may be faster with the jet processor - when tuned right. That is not the advantage of SQL server but the advantage of usinga file db. The real performance gain is accessing the data in a multi user enviroment - this is where the power of SQL server comes in. Jet fails and is slower in this case begining with 2 to 10 users, database size/table size dependent.

Continued...

by sl-campbell

Using a small fast automobile to load AND transport a case of beer(number of users or size/width of Table or number of records) 100 miles is faster than using a truck. Transporting a PALLET of beer isn't. Either you have to make several trips with the car, loosing time, or the car breaks down under the weight of the load......In this case the truck is "faster"...

To compare right, being fair to both ADO/DAO-Jet and Sql server, first make sure both are optimize 100% to their native code. Second, determine what the db is to be used for, how much data will it have on the average and how many users will access the db at the same time (real average- not potential) - most important, taking into consideration a possible future increase with both. Then set the enviroments according to the criteria, which should be the same for both, and do the testing.

by sl-campbell

So the problem must be in the statement/criteria not being optimized or the settings of the SQL server, therefore, use the tools/options that I mentioned to see what is actually being passed in the first place.

It may be the indexes.

-How manyrecords are we talking about?
-How wide is the table or how many fields are in the table and how many are being queried and are any fields being queried of type Long Binary?
-How many indexes are in the table. If you remove all indexes except the primary search field, how is preformance then?
-adding a Sort order will slow down the return of results alot. What is the actual syntax that you are using for the whole statement (or at least for the date criteria)?

by bob sellman

Thanks. Based on the article, I created a clustered index. The test query ran (even through the Access front end) in about 3 seconds, rather than 31(!). Now I just have to solve two other problems: can you format query results from SQL 7 7 (likeyou can from Access) and how do you get a list of indexes (sp_helpindex kept giving me error messages in Query Builder).

by bob sellman

After posting this question, I ran another test for the speed in which I posted the SQL query being used in Access to the SQL Query manager in SQL to determine if interfacing through Access was causing any performance degradation. The queries ran with the same times as mentioned above, even though I was running them through the SQL Query Manager.

I don't know if this might also have an effect (I can't image why, since the query is supposed to run on the SQL server with just the results passed along to the workstation), but here's our network setup:

Primary log in is to a Novell cluster.
Then log in automatically to the NT (4.0) server where the SQL resides.

I'm mentioning the network situation simply because the testing with SQLQuery Analyzer seems to rule out major problems in going through an Access pass-through query.

I also tried variations on the query (using the Query Analyzer) with only the selection and no sorting and it still took over 15 seconds to return results. A query with only ORDER BY took a bit longer.

by bob sellman

Eliminating the spaces did the job to get to the referenced article.

Here's the SQL query I'm using that includes the selection and sorting:

SELECT * FROM [TransLabor] WHERE [Weekend] >= '1/5/2002' ORDER BY [Weekend] ASC, [MgmtCent] ASC;

Based on what I've seen, the date is supposed to be surrounded by single quotes (as above), rather than the pound (#) sign as it is in Access. This is the SQL syntax that worked in the query analyzer.

Weekend is a date field (with the ending date for each week).
MgmtCent is a text field that contains the management center number (5 characters).

This discussion is closed to new replies.