December 14, 2008 at 10:12 pm
Hi,
I'm querying on a tera byte storage data. so i want to know which will be faster the where condition or the inner joins for example
select sales, BP, units from tbl a
where locationkey in ( select locationkey from dbo.ClientTbl_DimStores
where (case when @StoreAttribute ='Country' then [Country]
when @StoreAttribute ='State' then [State]
when @StoreAttribute ='City' then [CityName]
when @StoreAttribute ='Region' then [Region]
when @StoreAttribute ='Store Name' then [StoreName]
end)
= @StoreAttributeValue
)
or
inner join ( select locationkey from dbo.ClientTbl_DimStores
where (case when @StoreAttribute ='Country' then [Country]
when @StoreAttribute ='State' then [State]
when @StoreAttribute ='City' then [CityName]
when @StoreAttribute ='Region' then [Region]
when @StoreAttribute ='Store Name' then [StoreName]
end)
= @StoreAttributeValue) b
on a.locationkey =b.locationkey
expecting a advice on this on a production server which requires the output with in 3 seconds of time.
Thanks,
Regards,
Viji
December 14, 2008 at 10:30 pm
viji (12/14/2008)
Hi,I'm querying on a tera byte storage data. so i want to know which will be faster the where condition or the inner joins for example
select sales, BP, units from tbl a
where locationkey in ( select locationkey from dbo.ClientTbl_DimStores
where (case when @StoreAttribute ='Country' then [Country]
when @StoreAttribute ='State' then [State]
when @StoreAttribute ='City' then [CityName]
when @StoreAttribute ='Region' then [Region]
when @StoreAttribute ='Store Name' then [StoreName]
end)
= @StoreAttributeValue
)
or
inner join ( select locationkey from dbo.ClientTbl_DimStores
where (case when @StoreAttribute ='Country' then [Country]
when @StoreAttribute ='State' then [State]
when @StoreAttribute ='City' then [CityName]
when @StoreAttribute ='Region' then [Region]
when @StoreAttribute ='Store Name' then [StoreName]
end)
= @StoreAttributeValue) b
on a.locationkey =b.locationkey
expecting a advice on this on a production server which requires the output with in 3 seconds of time.
Thanks,
Regards,
Viji
Desired execution speed can be achieve if the size of database is small, but inner joins are faster and accurate too.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
December 14, 2008 at 11:30 pm
On SQL Server 2005 and higher, there should not be any difference between these two. However, it is unlikely that either will perform consistently well.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 15, 2008 at 12:19 am
viji (12/14/2008)
I'm querying on a tera byte storage data. so i want to know which will be faster the where condition or the inner joins for exampleexpecting a advice on this on a production server which requires the output with in 3 seconds of time.
You're querying a terabyte of data and want a result in 3 seconds?
Not overly likely unless you're retrieving small amounts of data, there are good indexes and the query is written to use those indexes, which this one is not.
The two you've posted should run identically. The optimiser will very likely convert them both into the same plan. The join/condition on tbl (locationkey) is SARGable and hence should be able to use indexes, the one on ClientTbl_DimStores is not and will require an index scan or table scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply