Access Runs SQL Server Slower

[peridian]

Hello,

A curiosity, why would SQL statements execute faster on SQL Server 2000 through the Enterprise Manager than when sent to the server via ADODB command in Access?

I am developing and testing the code on a stand-alone install of SQL Server 2005 Express, and the live version is on SQL Server 2000 (ver. 8.00.760 (SP3)). No idea if that's an enterprise edition or other, it's definitely not an Express version though.

I used to call Stored Procedures on the server, until I found out about the optimization whereby all SQL statements benefit from caching, at which point I decided to go back to dynamically building SQL statements and calling them directly through the connection (which I prefer).

I tested the performance of the system using both approaches on my SQL Server 2005 installation, and found them to be equal in terms of performance.

However, when I rolled it across to 2000, I've noticed that it seems to run significantly slower. I was surprised, as I knew the old stored procedures used to run just as quick on the server as on 2005. And the 2005 install is Express! With only one core and 1Gb RAM, it outperformed a full install on a dual-core machine with 3Gb RAM. :-?

I tried running a statement through ADODB, and it timed out after 20 minutes. I then tried running the exact same statement through the Enterprise manager, and it finished in just over 5 minutes.

According to BOL, the optimization was added from version 7 onwards, so SQL Server 2000 should be doing the same. Have I missed something?

Regards,
Rob.

[NeoPa]

This seems like a question for the SQL Server team Rob.

Let me know if you'd like me to move it across for you.

[peridian]

This seems like a question for the SQL Server team Rob.

Let me know if you'd like me to move it across for you.

Sure, thanks. I posted here as in the past anything even remotely about Access has been asked to go here rather than SQL area.

Thanks,
Rob.

[NeoPa]

Cross-platform is always a little tricky. In my view though, this belongs in SQL Server rather than Access. We'll see if they can help.

[ck9663]

Tricky, indeed :)

It depends on a lot of things. Your connection that you used to bridge Access and SQL Server, the connection of the machines and if the sys ad tried limit bandwidth that one can use outside of the Management Studio.

-- CK

[peridian]

Tricky, indeed :)

It depends on a lot of things. Your connection that you used to bridge Access and SQL Server, the connection of the machines and if the sys ad tried limit bandwidth that one can use outside of the Management Studio.

-- CK

I've checked with the sys admins, there are no bandwidth limits on my account (as it has most admin rights).

I know that this machine can do this quickly when I was using the Stored Procedures, so let's focus on the connection bridging Access and SQL Server.

I wondered if there are any settings in ADODB that might be on/off by default in Enterprise Manager that I need to explicitly switch on/off when running from Access?

Cheers.

[peridian]

Well, I finally hit upon the right combination of keywords in Google to find the answers.

The answer is the SET ARITHABORT ON/OFF statement.

Apparently, when you turn ANSI_WARNINGS on, it implicitly turns this option on too. However, ARITHABORT is not on by default for all connections in SQL Server 2000.

It is on by default in 2005, which is why my ADO code runs faster on the 2005 server.

It does get switched on by the various Management Studio programs for SQL Server, which is why any query run through these was faster than ADO.

ANSI_WARNINGS was being turned on in my stored procedures as a compile option cropped up if I did not have it in there, hence my stored procedures on SQL Server 2000 were not slow.

I haven't done any extensive testing yet, but for my standalone SQL statements through ADODB, this option is apparently what is missing.

[NeoPa]

Thanks for posting the solution. It seems like an obscure one, so well done for finding out about it too :)