September 7, 2011 at 4:02 am
We are using SQL Server 2008 (Build 10.0.1600) Developer edition (Enterprise edition in production).
SELECT
Ile.[Item No_],
SUM(Ile.[Quantity]) AS Invent
FROM [MyDatabase].dbo.[My Company$Item Ledger Entry] AS Ile
WHERE (Ile.[Location Code]='XXXXX')
GROUP BY Ile.[Location Code],Ile.[Item No_]
OPTION (MAXDOP 0,OPTIMIZE FOR UNKNOWN)
If I run this select in SSMS it is fast because SQL uses an indexed view (Location Code,Item No_,SUM(Quantity)) that exists on [MyDatabase].dbo.[My Company$Item Ledger Entry] even if I didn't say to use it. I am counting on this Enterprise-feature to speed up the query because in production I never know what will be the name of the view. So far so good.
If I run the same query using bcp.exe (and also sqlcmd.exe) I noticed that SQL does NOT use the Indexed view but uses an Index on table [CompanyName$Item Ledger Entry] and does key lookups to get the Quantity. And, of course, that is very slow with lots of reads and CPU.
Does someone know WHY a query through bcp does not use the indexed view and what I can do to make bcp use that indexed view (remember I do not know the name of the view)?
September 7, 2011 at 4:39 am
I would check:
1) Do the statements match EXACTLY?
2) Do you have the same exact SET options ON in SSMS and sqlcmd?
Can you post the actual execution plans? You should see the SET options somewhere in the plan.
-- Gianluca Sartori
September 7, 2011 at 5:55 am
1) they match exactly. I copied/pasted them to be sure of it.
2) You gave me an idea here. I checked it out and I found that in SSMS this SET was done "set quoted_identifier on;" along with some others. Experimenting with different settings, I found out that putting "set quoted_identifier on;" in the BCP-command just before my select FIXED THE PROBLEM!
To be sure I have no plans in the cache, I even did a "DBCC FREEPROCCACHE" (I am on a test server!).
Remains only: WHY does the "set quoted_identifier on;" changes the queryplan so much?
September 7, 2011 at 6:10 am
See http://msdn.microsoft.com/en-us/library/dd171921(SQL.100).aspx :
Using SET Options to Obtain Consistent Results
Evaluating the same expression can produce different results in SQL Server if different SET options are enabled for the current session when the query is executed. For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL. But after CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression produces 'abc'. Indexed views require fixed values for several SET options for the current session and for objects referenced by the view to ensure that the views can be maintained correctly and return consistent results.
The SET options ANSI_NULLS and QUOTED_IDENTIFIER of the current session must both be set to ON at the time a view on which you wish to build an index is created. This is because these two options are stored with the view definition in the system catalogs.
The SET options of the current session must be set to the values shown in the Required value column for the current session whenever these operations occur:
An index is created on a view.
There is any INSERT, UPDATE, or DELETE operation performed on any table participating in the indexed view.
The indexed view is used by the query optimizer to produce the query plan.
This last sentence should clear things up.
-- Gianluca Sartori
September 7, 2011 at 6:46 am
Thanks. I checked the definition of the view and in it where these statements:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
Thanks for your help!
September 7, 2011 at 7:31 am
You're welcome.
Glad I could help.
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply