I'm trying to speed up a Azure V12 SQL Server query using indexed views. I have one slow element, which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped to an ID, but it's one to many and I'd want the MIN(ID) and obviously can't put MIN in an indexed view.

I'm not a huge SQL expert and only just learnt about indexed views so wondering if there is a clever way to achieve what I want, changing the underlying database structure isn't an option at the moment.

I'm trying to convert the result of the following into an indexed view:

SELECT MIN(id), COUNT(*), guid
FROM table

I tried a sub-select in the query but also hit the self-reference restriction for indexed views.

Rows per GUID can vary anywhere from 1 to 1000, give or take.

Appreciate any suggestions even if they are "this isn't possible", at least then I can stop trying and think about a future DB structure change!

I stuck the result of the query above into a table of its own with a suitable index. Changing my large query to join the tables on a BIGINT rather than a GUID dropped the query time from 8s to 6s, excluding the join altogether the query takes 4s.

This may not be huge but I'm thinking longer term as the data grows. Currently the query above generates approx 70K rows which are then joined with a table containing approx 500k rows.

For reference a simplified example of my original query which shows the issue is:

    (SELECT COUNT(*) FROM tblPage B WHERE B.PageGUID = A.PageGUID) AS PageCount
    tblPage A
    A.PageNumber = 1  

I actually created a clustered index on a view which has the precomputed page counts on which the above query cleverly uses but with the GUID check the query takes 20+s and taking that line out it takes 4s. I would need to do a reasonable re-architect to change the GUID to an ID which would obviously speed things up and I'm out of creative ideas to speed up the GUID lookup which it's currently doing with a Hash Match join.

At this point I think I'm going to have to think longer term about changing the way the system does things so it doesn't need this GUID based lookup.

PageID is an identity column and for a given file all the pages would share a common GUID and currently on import we don't know the ID of the first page hence why the pages go in linked by the GUID, I used a GUID as there could be multiple apps inserting content concurrently so wanted to make sure things didn't clash.

  • Why do you need an indexed view for this? Seems a simple index on guid, id would satisfy this query, though it would require a scan. For anything with aggregates like MIN and MAX, you would have to maintain your own materialized copy of those specific rows, via triggers (or background jobs depending on how consistent you need the data to be). – Aaron Bertrand Apr 27 '16 at 14:36
  • 1
    This isn't possible for the reasons described here stackoverflow.com/q/2134461/73226. – Martin Smith Apr 27 '16 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.