October 29, 2008 at 11:54 am
I'm setting up to use Row_number() for paging - It's ideal for this.
But, I am finding that there is a huge performance difference once I add that final rowID filter:
where rowId between @rowStart and @rowEnd
It is the identical query, with the only exception of that final where clause.
And when I say slow, the elapsed time goes from around 3500 ms to 32,000ms, so maybe 10 times slower.
Anyone else run into this, and work out why it is happening.
I'm trying to get the developers off of API cursors, but the where clause on the row_number negates any optimizations, making the cursors just as fast as a set based approach.
The more you are prepared, the less you need it.
October 29, 2008 at 1:22 pm
Would help to see the complete query.
October 29, 2008 at 1:40 pm
It would also help to know what indexes are on the table being queried.
October 29, 2008 at 1:46 pm
I found the answer, but from what I can tell, it is a true bug.
Below is the proc code below. The fix is to comment out the WITH (NOEXPAND) .
Reviewing the query plan, it still uses the materialized view, but it actually works the way it should.
--CREATE
alter PROCEDURE dbo.proc_VideoRelease_b
(
@keyword nvarchar(255) = ''
,@title nvarchar(255)= ''
,@longDescription nvarchar(2048)= ''
,@shortDescription nvarchar(2048)= ''
,@username nvarchar(255)= ''
,@releaseTemplateName nvarchar(50)= ''
,@sunriseDate datetime= 'Jan 1, 1970'
,@sunsetDate datetime= 'Dec 31, 2199'
,@bizDomainPerm numeric(19,0)= 0
,@bizDomainVideo numeric(19,0)= 0
-- row number values
,@rowStart int= 1
,@rowEnd int= 25
)
as
----------------------------------------------------
select rowID
, id3_, version3_, lastModi3_3_, migratio4_3_, externalId3_, creation6_3_, sunriseD7_3_, sunsetDate3_, ratingCo9_3_
, average10_3_, popularity3_, average12_3_, lastMCS13_3_, airDate3_, distrib15_3_, shortDe16_3_, longDes17_3_, title3_
, audioOnly3_, liveURL3_, bizDoma21_3_, distrib22_3_, derivati2_10_, visible10_, releaseT4_10_, releaseT5_10_
, assetId10_, assetExt7_10_, video11_, thumbnail11_, videoBytes11_, videoSec5_11_, thumbnai6_11_
from
-- outer derived table, apply row_number
(
select row_number() over ( order by airDate3_ desc) as rowId
, id3_, version3_, lastModi3_3_, migratio4_3_, externalId3_, creation6_3_, sunriseD7_3_, sunsetDate3_, ratingCo9_3_
, average10_3_, popularity3_, average12_3_, lastMCS13_3_, airDate3_, distrib15_3_, shortDe16_3_, longDes17_3_, title3_
, audioOnly3_, liveURL3_, bizDoma21_3_, distrib22_3_, derivati2_10_, visible10_, releaseT4_10_, releaseT5_10_
, assetId10_, assetExt7_10_, video11_, thumbnail11_, videoBytes11_, videoSec5_11_, thumbnai6_11_
from
(
-- inner derived table, apply distinct
select distinct videorelea0_2_.[Id] as id3_, videorelea0_2_.version as version3_, videorelea0_2_.lastModificationDate as lastModi3_3_, videorelea0_2_.migrationId as migratio4_3_, videorelea0_2_.externalId as externalId3_, videorelea0_2_.creationDate as creation6_3_, videorelea0_2_.sunriseDate as sunriseD7_3_, videorelea0_2_.sunsetDate as sunsetDate3_, videorelea0_2_.ratingCount as ratingCo9_3_, videorelea0_2_.averageRating as average10_3_, videorelea0_2_.popularity as popularity3_, videorelea0_2_.averagePlayTime as average12_3_, videorelea0_2_.lastMCSUpdateDate as lastMCS13_3_, videorelea0_2_.airDate as airDate3_, videorelea0_2_.distributionDate as distrib15_3_, videorelea0_2_.shortDescription as shortDe16_3_, videorelea0_2_.longDescription as longDes17_3_, videorelea0_2_.title as title3_, videorelea0_2_.audioOnly as audioOnly3_, videorelea0_2_.liveURL as liveURL3_, videorelea0_2_.bizDomainId as bizDoma21_3_, videorelea0_2_.distributorId as distrib22_3_, videorelea0_2_.derivativeAssetErrors as derivati2_10_, videorelea0_2_.visible as visible10_, videorelea0_2_.releaseTemplateName as releaseT4_10_, videorelea0_2_.releaseTemplateId as releaseT5_10_, videorelea0_2_.assetId as assetId10_, videorelea0_2_.assetExternalId as assetExt7_10_, videorelea0_2_.video as video11_, videorelea0_2_.thumbnail as thumbnail11_, videorelea0_2_.videoBytes as videoBytes11_, videorelea0_2_.videoSeconds as videoSec5_11_, videorelea0_2_.thumbnailBytes as thumbnai6_11_
from vw_ContentVideoRelease videorelea0_2_ -- WITH (NOEXPAND)
/* appears to be a bug, so this NOEXPAND has been commented out, not sure why, but the optimizer goes wacko
when it is in place.
*/
inner join vw_GranteePermissionContent as permission1_ WITH (NOEXPAND)
on videorelea0_2_.[Id] = permission1_.contentId
and permission1_.GranteeBizDomainId = @bizDomainPerm
where videorelea0_2_.bizDomainId = @bizDomainVideo
and videorelea0_2_.bizDomainId= @bizDomainVideo
and videorelea0_2_.sunsetDate >= @sunsetDate
and videorelea0_2_.releaseTemplateName = @releaseTemplateName
and videorelea0_2_.[id] IN
(
select distinct [contentId] as [id] from dbo.vw_ContentKeywordsBizDomain as cw with (noexpand) where cw.bizDomainId = @bizDomainVideo and cw.keyword like @keyword escape '\'
UNION
select [id] from dbo.vw_content_string as c with (noexpand) where
c.bizDomainId = @bizDomainVideo
and c.sunriseDate<=@sunriseDate
and (
c.title like @title escape '\'
or c.longDescription like @longDescription escape '\'
or c.shortDescription like @shortDescription escape '\'
)
)
-- end inner derived table
) as innerDerivedTable
) as outerDerivedTable
where rowId between @rowStart and @rowEnd
order by airDate3_ desc
return
The more you are prepared, the less you need it.
October 29, 2008 at 1:57 pm
A case of not to rely on Query Hints all the time
by removing the (NOEXPAND), I think the engine decides to go to the tables directly
http://www.realworldsql.com/post/2008/01/NOEXPAND-query-hint-for-an-indexed-view.aspx
If you have an indexed view (schemabound) in SQL (great for speeding up complex joins) when you use that view in a query SQL will decide to whether to use the view’s index or go directly to the tables based on which it thinks will be faster.
If you add the query hint “NOEXPAND” you can force SQL to use the view.
October 30, 2008 at 6:27 am
Normally I would agree, the optimizer tends to find the best path. But for this project we are running SQL2005 Standard Edition. As documented, with the exception of the Enterprise and developer edition, you must declare the NOEXPAND option to use the view. For simple/typical queries, I have found this to be accurate.
REF: http://msdn.microsoft.com/en-us/library/ms143761(SQL.90).aspx
3. This feature is supported in all editions of SQL Server 2005, except that indexed view-to-query matching by the Query Optimizer is supported only in Enterprise Edition and Developer Edition. Indexed views can be created in all editions of SQL Server 2005, and queried by name using the NOEXPAND hint.
The more you are prepared, the less you need it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply