SQL Server performance vs. Access

[faccorp]

I have an application that uses an Access97 database. The clients connect to the db either locally or via a network share. I am using DAO with VisualC++ to get to the Access Data.

I am now re-writing the app using VS2005 and SQL Server 2005. My new app uses OLEDB to access Views and Stored Procedures on SQL Server.

It appears though, especially with a networked client, that the Access 97 version is significantly faster than SQL Server. How can this be?

On SQL Server, I have run the Performance Tuning Wizard and applied all recommendations but it's still slower than Access97.

One difference beside the obvious is that on the Access 97 version, I open the database and leave a connection open at all times. Whereas, with the SQL Server version, I only open the View or SP when I need to get or update data, and then I close it.

Are there any tips or tricks to accessing the SQL Server via a network. Is there anything special about my connection string that could improve performance?

The connection string I'm using is the same on a networked client as it is on the local client.


Thanks in advance.

 

[gmmastros]

>> Access 97 version is significantly faster than SQL Server

Since you said significantly, I will guess that you are missing some indexes.

My advice to you is...

Open the Management Studio
Open the slow performing query.
Click Query -> Include Actual Execution Plan

Run the query.

Near the middle of your screen (above where the results are displayed), there will be a tab for the Execution Plan. Click it.

Look through the execution plan to see where the performance issues are. Specifically look for table scans (the worst) and index scans, clustered index scans, and index seeks.

A clustered index seek is the best, followed by clustered index scan, index scan, and table scans.

If you find any table scans, this is bad, but can usually be corrected by creating an index on the data being scanned.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Open up SQL profiler. This will tell you how long the SQL commands are actually taking to execute. If this shows long run times then the problem is internal to SQL Server and you'll need to look at the indexes, data structor, and the commands that you are using to get the data.

If it shows fast run times that you need to look outside of the SQL Server. Network speed, client issues, front end issues taking a while to open the connection to the database, etc.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[faccorp]

Thank you for your responses.

After making some changes to indexes and relationships, the system appears faster, however, it doesn't seems too much faster than my Access database.

A typical query on a VIEW that is accessing 3000-4000 records is taking about 1-2 seconds. The view is joining 2 tables and also doing a SUM on 4 columns. Is this normal?

I was expecting SQL to be much much faster.

 

[gmmastros]

Have you looked at the execution plan like I suggested in a previous post. Do you have table scans, index scans, etc...?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cmmrfrds]

If you only have small tables in Access, you will not notice much speed difference. Try comparing to 300-400 thousand row tables or 3-4 million row tables and sql server will be much much faster.

 

[mrdenny]

When you look at the execution plan it will tell you if the slow down is io or cpu related. If it's IO related then you need more spindles to make it faster. If it's CPU related then you need more or faster CPUs. It will also depend on what portion of the query plan is taking which kind of load.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com