Re: ADO executing stored procedures for all calls, bad performance

Randy Stroede (RandyStroede@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
We have an application that we changed from native calls to ADO, and the
performance is now terrible. Profiler shows tons of calls to stored
procedures with an audit login and audit logout after each and every one.

We are seeing calls to:

sp_indexes_rowset
sp_primary_keys_rowset
sp_prepare
sp_unprepare
sp_execute

In searching the web, I'm seeing a LOT of people asking why this is
happening, and a lot of people pointing to ADO, but almost all of the
threads have gone unanswered.

Anyone know why ADO must issue calls to all of these procs that
ultimately kills performance of an application? The database and server
configuration remained the same through all of this.

Without seeing your code, it's difficult to say. But it seems that you
are using inline queries against tables. Or may you are not even using
queries at all, but use adCmdTable. In this case, ADO needs to know
about indexes and particular about primary keys of the table. The prepare/
execute stuff is just away of speeding things up with using prepared
queries.

The fact that you see a login/logout each time is worrisome. It would
indicate that connection pooling is out of play, which indeed can kill
performance. What does your connecting string look like?


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages