Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Will indexed view help this query? Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 3:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 11:18 AM
Points: 380, Visits: 903
Hi
I have a fact table with around 100 million rows. Below is my query to pull top 10 records. ( i understand that this is fact table and I should be talking cubes, but I have the requirement to do it using a tsql query):

SELECT TOP 10 (DLDest.CityCode) AS 'DestCityCode',Count(DLDest.CityCode) AS 'CountDestCityCode',
(DL.CityCode) AS 'OriginCityCode',
(DA.AirlineName) AS 'AirlineName'
FROM dbo.FactFares AS FAF
LEFT OUTER JOIN DimLocation AS DLDest ON FAF.Destination = DLDest.Location_Key
LEFT OUTER JOIN DimLocation AS DL ON FAF.Origin = DL.Location_Key
LEFT OUTER JOIN DimAirlines AS DA ON FAF.Airline = DA.AirlineCode
WHERE (FAF.Tax >= 0)
GROUP BY DLDest.CityCode , DL.CityCode , DA.AirlineName
ORDER BY Count(DLDest.CityCode) DESC

So if i create an indexed view and simply do : select * from view. will that give a performance boost and correct result? Currently the query is taking ages for obvious reason of sorting entire table.(FAF.Tax >= 0 doesn't filter out much records)



-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1347655
Posted Tuesday, August 21, 2012 3:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 11:18 AM
Points: 380, Visits: 903
Just an addition that i will have to remove top, left joins etc to make an indexed view here.

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1347663
Posted Tuesday, August 21, 2012 4:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
-- The query requires a scan of the whole 100M rows to evaluate the ORDER BY.
-- An index with Destination, Origin, Airline and Tax will help.
-- You *probably* don't need to join the lookups until after the aggregate -
-- this help will speed up the query.
;WITH Preaggregate AS (
SELECT TOP 10
FAF.Destination,
[CountDestCityCode] = Count(FAF.Destination),
FAF.Origin,
FAF.Airline
FROM dbo.FactFares AS FAF
WHERE (FAF.Tax >= 0)
GROUP BY FAF.Destination, FAF.Origin, FAF.Airline
ORDER BY Count(FAF.Destination) DESC
)
SELECT
DLDest.CityCode AS [DestCityCode],
FAF.[CountDestCityCode],
DL.CityCode AS [OriginCityCode],
DA.AirlineName AS [AirlineName]
FROM Preaggregate FAF
LEFT OUTER JOIN DimLocation AS DLDest ON FAF.Destination = DLDest.Location_Key
LEFT OUTER JOIN DimLocation AS DL ON FAF.Origin = DL.Location_Key
LEFT OUTER JOIN DimAirlines AS DA ON FAF.Airline = DA.AirlineCode



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1347667
Posted Tuesday, August 21, 2012 4:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 11:18 AM
Points: 380, Visits: 903
Thanks so much for your thoughts. I'll give it a try.

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1347684
Posted Tuesday, August 21, 2012 9:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 11:18 AM
Points: 380, Visits: 903
But the results of this and previous query doesn't match. Seems ordering on different column changed the results.

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1347876
Posted Tuesday, August 21, 2012 9:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
S_Kumar_S (8/21/2012)
But the results of this and previous query doesn't match. Seems ordering on different column changed the results.


The results will differ if any of the DIM... tables introduce more than one row per row in dbo.FactFares. Without data to analyse, it's impossible for me to say. You will have to look into this. If you need some help, try picking up say the first 50 rather than the first 10, from both queries, and posting here.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1347886
Posted Wednesday, August 22, 2012 9:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 11:18 AM
Points: 380, Visits: 903
thanks Chris,I'll look into it myself.

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1348539
Posted Wednesday, August 22, 2012 12:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
Just on a side note, be careful with indexed views, particularly top 10 views and the like. On paper it doesn't look like they'll cost much overhead, they're only monitoring 10 rows. In reality, they're not. Almost every update/insert/delete on any connected table will cause this view to refresh.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1348622
Posted Wednesday, August 22, 2012 11:10 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 11:06 PM
Points: 724, Visits: 353
indexed view works fine with static data (non changing data).

if your dimension changes frequently drop and create index .
Post #1348853
Posted Wednesday, August 22, 2012 11:47 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 12:15 AM
Points: 9,928, Visits: 11,207
Evil Kraig F (8/22/2012)
Just on a side note, be careful with indexed views, particularly top 10 views and the like. On paper it doesn't look like they'll cost much overhead, they're only monitoring 10 rows. In reality, they're not.

Views that contain TOP cannot be indexed directly, so this specific issue does not really arise in these terms.

Almost every update/insert/delete on any connected table will cause this view to refresh.

Maintenance to an indexed view is built in to the query plan of the statement that causes a change to the view. In general, the maintenance is incremental, affecting only those rows in the indexed view that are directly affected by the change. I'm sure you knew this Craig, but I wanted to make it clear for other readers that it is not the whole view that is refreshed whenever a change occurs.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1348864
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse