Speed of SQL Server vs MS Access

[davebhoy]

Hi there

Can anyone tell me why a make table query runs nearly 4 times quicker in MS Access than the exact same query in Enterprise Manager? The query makes a table containing about 16000 records.

Thanks in advance.

Davebhoy

 

[The_Doc_Man]

You don't specify enough information about the configuration and the steps of your speed test to be able to answer the question. What is connected to what in each leg of the test?

 

[davebhoy]

SQL vs Access

Docman

Hers the SQL

INSERT INTO [Jobs all Pre] ( rel_attr_id, rel_value, dim_value, LastOfdescription )
SELECT dbo.aglrelvalue.rel_attr_id, dbo.aglrelvalue.rel_value, dbo.agldimvalue.dim_value, Max(dbo.agldimvalue.description) AS MaxOfdescription
FROM dbo.agldimvalue INNER JOIN dbo.aglrelvalue ON dbo.agldimvalue.client = dbo.aglrelvalue.client
WHERE (((dbo.agldimvalue.attribute_id)='Z1') AND ((dbo.agldimvalue.client)='L1') AND ((dbo.agldimvalue.dim_value) Between [att_val_from] And [att_val_to]))
GROUP BY dbo.aglrelvalue.rel_attr_id, dbo.aglrelvalue.rel_value, dbo.agldimvalue.dim_value
HAVING (((dbo.aglrelvalue.rel_attr_id)='Z6' Or (dbo.aglrelvalue.rel_attr_id)='Z5' Or (dbo.aglrelvalue.rel_attr_id)='Z4' Or (dbo.aglrelvalue.rel_attr_id)='ZA'));

Thanks again

 

[pdx_man]

Tell us about the tables you are inserting into. Indexes, foreign keys, constraints ...

My immediate guess, if all things were equal, is that you have disk i/o bottleneck on your SQL Server. I've been using both for 10+ years and have never seen Access faster than SQL Server with all things equal.

 

[Susan Owen]

Tell us about the tables you are inserting into. Indexes, foreign keys, constraints ...

My immediate guess, if all things were equal, is that you have disk i/o bottleneck on your SQL Server. I've been using both for 10+ years and have never seen Access faster than SQL Server with all things equal.

Just a wild guess, but if that table is being written to Access (local table) then it would be much faster that way? Also, what about using a stored procedure to do this, passing parameters and using the ADO connection connection object? That should be (theoretically) pretty fast?

 

[davebhoy]

The tables are created in the program that the query is in. Access creates a table in that Access DB, and the stored procedure in SQL creates a table in that SQL DB. So are you saying that Access would be quicker in this case and why?

No indexes and no foreign keys in either case.

Thanks again.

 

[Susan Owen]

The tables are created in the program that the query is in. Access creates a table in that Access DB, and the stored procedure in SQL creates a table in that SQL DB. So are you saying that Access would be quicker in this case and why?

No indexes and no foreign keys in either case.

Thanks again.

Hi Dave

I'm very new with MS-SQL Server, but I'll give it a try anyway. I am thinking that the code you're using between MS-Access and MS-SQL Server might be taking the extra time.

What if you you ran it from MS-Access using code with the ADO connection object? (That is referenced around here somewhere I think, if you can't find it, I?ll look it up).

What if you run the stored procedure directly from MS-SQL Server, how much time does that take?

How are you timing the results, using an API interface call? How much time difference is there, how long does it take to run each version?

 

[Kodo]

You don't specify enough information about the configuration and the steps of your speed test to be able to answer the question. What is connected to what in each leg of the test?

I'm inclined to go with size of data transmission with regards to the insertions as to why the execution is slow(er).

 

[davebhoy]

Just to summarise:

1) The SQL is the exact same
2) The table is created within each program
3) No index's exist

Everything is equal so why is Access 4 times faster (i.e Access takes 4 seconds and SQL takes 16 seconds)

Dave

 

[SQL_Hell]

Hi,

Can you run this is insert statement sql server query analzer and tell me how long it takes there. This will help us ascertain whther the problem is entirely sql server related.

You also say there are no indexes on these columns, so does that mean there is no primary key on this table either? because once I primary is created in sql server then a clustered index is created on that column.

 

[davebhoy]

Hi

It actually takes 22 seconds in Query Analyser, even longer! No primary key in either Access or SQL, thanks.

Dave

 

[SQL_Hell]

thats your problem then

SQL server requires a primary key

 

[SQL_Hell]

If you still are not getting great performance after adding the primary key,

Then paste that query into query analzer again and run the index tuning wizard and also run the estimated execution plan which will tell you how the query is being worked out.

because your table does not have a primary key then it has no index to use to speed up perfomance,


what about the tables you are selecting from? do they not have primary keys or indexes either?

 

[pdx_man]

I would like to see the code for each process. Also, how many rows are you inserting into the new table and what/where is the source for this?

Just running the code in QA once is not going to be a great determination of how SQL Server performs as it is the subsequent runs that an execution plan is used and the performance is realized. Create a Stored Procedure with your code and call it from a Pass-Through query. That way you are running on SQL Server utilizing a cached execution plan. Run this a few times and tell us how it is doing?

 

[SQL_Hell]

Quote:

Just running the code in QA once is not going to be a great determination of how SQL Server performs as it is the subsequent runs that an execution plan is used and the performance is realized.


Err what , please explain why running code is QA is not a good way determining performance?

Have you ever looked at the estimated execution plan?

 

[davebhoy]

There's no primary key in access either and it still takes only 4 seconds. All the tables in the query are in a SQL database. When MS Access creates the table it uses ODBC linkages to these tables.

Someone told me that SQL creates logs that access doesn't, wuld this account for it being 4/5 times slower!!??

The execution plan is as follows:

Sort 17%
Parallelism/Distrubute Streams 2%
Hash Match/Aggregate 42%
Parallelism/Gather Streams 2%
Parallelism/Repartition Streams 12%
Nested Loops/Inner Joins 19%

Thanks again!

Dave

 

[SQL_Hell]

no the logs would have nothing to do with it, trust me sql server is much faster than Access.

If you do not have a primary key on all tables concerned then you will have crap performance and no referential integrity.

In your execution plan you can see it is using no indexes!

 

[davebhoy]

There are unique indexes on both underlyinh tables that the query is looking at but no clustered indexes.

I'm only learning SQL so when you say "primary key" is this not just a unique index?

Ta!

 

[pdx_man]

Quote:

Just running the code in QA once is not going to be a great determination of how SQL Server performs as it is the subsequent runs that an execution plan is used and the performance is realized.


Err what , please explain why running code is QA is not a good way determining performance?

Have you ever looked at the estimated execution plan?

Running it once in QA is not a good way to determine long term performance. It has not put together its execution plan and needs to do this. Creating a Stored Procedure saves the execution plan and SQL will continue to tune it with subsequent runs. Surely you have seen this. First run completes in 15 seconds and the second run completes in two. I believe QA reports it best, Estimated Execution Plan.

 

[Susan Owen]

To pdx man (what's pdx anyway?)
Thanks for all your comments, this is really interesting, I'm glad you have been trying to help Dave, and that he is answering your questions. I'm learning a lot from this discussion.