January 13, 2006 at 8:07 am
Hi,
We're taking a strange performance hit when running T-SQL queries against one of our partitioned views.
The tables are partitioned using a check constraint against the field SVC_YR_MTH_DT, and each table has a clustered index on that field.
If I run a query that says
Select * from CLAIM
WHERE SVC_YR_MTH_DT BETWEEN 200410 AND 200510
and look at the query plan, I see that it only looks at the two relevant tables from our set of 15 and it returns data in about 30 seconds.
When I change the query to
DECLARE @lSTART INT, @lEND int
set @lSTART = 200410
set @lEND = 200510
Select * from CLAIM
WHERE SVC_YR_MTH_DT BETWEEN @lSTARTAND @lEND
the actual execution plan looks at all 15 tables, returning zero rows from 13 of them, but with a cost and returns data in about 2 minutes. We tried other variations like putting the date range into a table variable and joining to that variable, but it doesn't seem to make any difference.
So, it seems that something different is happening in the optimizer when we use a constant and when we pass a variable.
Does anyone have any insights on this?
January 13, 2006 at 8:49 am
I'm just guessing here, but are you joining views to views?
On a partitioned system I worked on exhibited this behavior when I joined views together.
Also, the optimizer may be parameter sniffing.
Try
DECLARE @lSTART INT, @lEND int
DECLARE @zSTART INT, @zEND int
set @lSTART = 200410
set @lEND = 200510
set @zSTART = @ISTART
set @zEND = @IEND
Select *
from CLAIM
WHERE SVC_YR_MTH_DT BETWEEN @zSTART AND @zEND
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply