Group By for Indexed Views

Question

Group By for Indexed Views

ydubinsky

SSC Enthusiast

Points: 157
More actions
June 1, 2010 at 4:40 pm

#387053

Hope someone can help... I have a historical data table as follows:
table1 (
guid uniqueidentifier,
fieldname varchar(80),
value varchar(MAX),
asofdate datetime)
I would like to create an indexed view mostly to improve performance: vLatest. The structure of this view should be similar to the above, the difference being it would ONLY containt the latest value based on {guid and fieldname}. I can do this via the following query:
select guid, fieldname, value, asofdate
from table1 t1
where asofdate = (select max(asofdate) from table1 t2 where t1.guid=t2.guid and t1.fieldname=t2.fieldname)
The issue is that this query isn't allowed to be indexed, which is what I want. I tried stuff like this:
select guid, fieldname, value, asofdate
from table1
group by guid, fieldname, value, asofdate
having asofdate=max(asofdate)
but this gave me each record anyway. I understand why; I'm grouping by the asofdate. If I remove asofdate from either the select clause or group by clause I get the same ol' can't group by error.
Any thoughts?
Thanks!
June 2, 2010 at 2:50 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ydubinsky SSC Enthusiast Points: 157 More actions · Answer 1

Thanks for the feedback. The table seems to be properly indexed -- we have neither reading nor writing issues w.r.t. query plans or performance. Also, the number of records isn't very large (less than 100,000) and the growth is maybe 5% per year.

I want to simplify the queries our middle tier has to execute and it seems an indexed view would further improve performance and scalability by reducing the number of queries executed against that "main" table.

Aparna-1 SSCrazy Points: 2339 More actions · Answer 2

I have had the same issue before with indexed views and I could not never get it working. Looks like this is the limitatiion of indexed views (cannot use derived tables or views)

I tried different work arounds changing the query to use derived tables or created a view which displays max(timestamp) for each unique record or a table valued function and none of them helped me create an indexed view.

My historical table gets filled once a day so I created a table called latest and created a job to populate the latest information every day. This worked in my case but I am not sure about yours.

I would like to hear from others if there is a better way of doing it.

ydubinsky SSC Enthusiast Points: 157 More actions · Answer 3

That's exactly my situation. I have a historical table and a latest table, and would much rather combine them into one. But then my "latest" queries will "suffer" a bit. My issue isn't performance, since I'm generally dealing with few records. And back in the days of having a historical warehouse with global stock, bond, fx, and derivatives' pricing, a single table did the trick -- with A LOT of tuning.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 4

You won't be able to index a view on this, but I can tell you that using an APPLY with a top 1 can be very fast. Here's an example from AdventureWorks that runs in under 1 second on my humble box.

CREATE VIEW vCustLastSale

WITH SCHEMABINDING

AS

SELECT C.CustomerID, C.AccountNumber, C.CustomerType

, S.SubTotal AS LastSaleAmt

, S.ShipDate AS LastShipDate

FROM Sales.Customer C

CROSS APPLY

(SELECT TOP 1 ShipDate, SubTotal

FROM Sales.SalesOrderHeader S

WHERE S.CustomerID = C.CustomerID

ORDER BY S.ShipDate DESC

) AS S

GO

SELECT * FROM vCustLastSale

This is, of course, a very simple view, but I've used this type of view successfully on much larger data sets with many more columns in the view. The main thing is to have your indexing right so it only does indexed seeks in the APPLY part.

Todd Fifield