|
|
Posted by Erland Sommarskog on 07/16/07 22:34
sqlgirl (tootsuite@gmail.com) writes:
> This is not the case at all. What is happening is that TWO separate
> COMPILED PLANs are being created, depending on whether the sp is run
> from QA or from Excel.
Yes, that is actually to be expected. :-)
Here is the scoop: certain SET commands affects the query plan so much,
that different combinations of SET commands yields different cache
entries. When you connect from Excel, you use an API that issues
turns on a number of SET commands. In QA you can set up under Options->
Connection Properties how to connect. But the QA default includes
SET ARITHABORT ON, which is not set when you connect from a client
application.
That explains why you see different plans. It does not explain why
you get such difference in performance. It may just be bad luck, but
there is also a real possibility: somewhere there is an indexed view
or an indexed computed columns. For these indexes, six SET commands
must be on and a seventh must be OFF. And one of the six are SET
ARITHABORT ON.
Try to get your VBA application to issue SET ARITHABORT ON and see
what happens.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
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
Navigation:
[Reply to this message]
|