indexed views without subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

indexed views without subquery

Author

Topic

KevinKembel
Starting Member

11 Posts

Posted - 2008-03-19 : 12:18:44

Hi, I'm having a problem creating an indexed view of a productprice table

CREATE TABLE [dbo].[ProductPrice](
[ProductPriceGUID] [uniqueidentifier] ROWGUIDCOL,
[ProductGUID] [uniqueidentifier],
[Price] [decimal](7, 2),
[IsSRP] [bit],
[EffectiveDate] [datetime]
)

Each ProductGUID can and should have multiple records in the ProductPrice table. I want to create an indexed view that will group by ProductGUID that will show either the most recent custom price if a (isSRP = 'False') record exists or the most recent srp price if there is only (isSRP = 'True'), then a column stating whether the price column is taken from a (isSRP = 'True') record, and followed by the most recent srp price (isSRP = 'True').

SELECT
[PP].ProductGUID AS [ProductGUID],
CASE
WHEN MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) = 0
THEN MAX(CASE [PP].IsSRP WHEN 'True' THEN 0 ELSE [PP].Price END)
ELSE MAX([PP].Price)
END AS [Price],
MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) AS [PriceIsSRP],
MAX(CASE [PP].IsSRP WHEN 'True' THEN [PP].Price ELSE 0 END) AS [SRP]
FROM dbo.ProductPrice AS [PP]
-- Eliminate all but the most recent custom price, and msrp price
WHERE ([PP].IsSRP = 'True' AND [PP].EffectiveDate =
(SELECT MAX(EffectiveDate) FROM dbo.ProductPrice AS innerPP
WHERE innerPP.ProductGUID = [PP].ProductGUID
AND innerPP.IsSRP = 'True'))
OR ([PP].IsSRP = 'False' AND EffectiveDate =
(SELECT MAX(EffectiveDate) FROM dbo.ProductPrice AS innerPP
WHERE innerPP.ProductGUID = [PP].ProductGUID
AND innerPP.IsSRP = 'False'))
GROUP BY [PP].ProductGUID

This query works, but I can't create an indexed view on any query with a subquery. So, If I had some way of limiting the query to return only the most recent IsSRP = 'True' and the most recent IsSRP = 'False' (if one exists) without using a subquery then this would be aces.

Oh, and every ProductGUID has at least one IsSRP = 'True' record.

Any ideas??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-20 : 01:37:09

Use a derived table instead of a subquery.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

KevinKembel
Starting Member

11 Posts

Posted - 2008-03-20 : 10:44:39

A derived table was a solution I tried already (among many others!), but you can index a view that contains a derived table. I tried creating a non-indexed view that does contain a derived table, and then creating an indexed view that contains that non-indexed view (long shot, I know, but also not allowed). I also found a solution using a union which is also not allowed.

Anyone???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-20 : 13:09:53

I don't have an answer to your question, but why don't you just index the underlying tables properly instead?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-20 : 14:33:05

Looks like you may be able to replace those two correlated subqueries in WHERE clause with this INNER JOIN.
I don't know your table structure or business logic but I think this should be equivalant:

can you have more than 1 row with the same (productGUID, EffectiveDate, isSRP) combination?
If not then it could be simpler still.


SELECT [PP].ProductGUID AS [ProductGUID]
       ,CASE 
              WHEN MIN(
                     CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END
                     ) = 0
                     THEN MAX(
                            CASE [PP].IsSRP WHEN 'True' THEN 0 ELSE [PP].Price END
                            )
                     ELSE MAX([PP].Price)
              END AS [Price]
       ,MIN(CASE [PP].IsSRP WHEN 'True' THEN 1 ELSE 0 END) AS [PriceIsSRP]
       ,MAX(CASE [PP].IsSRP WHEN 'True' THEN [PP].Price ELSE 0 END) AS [SRP]

FROM   dbo.ProductPrice AS [PP]

-- Eliminate all but the most recent custom price, and msrp price
inner join (
       select   productGUID
                ,isSRP
                ,max(EffectiveDate) effectiveDate
       from     ProductPrice
       group by productGUID
               ,isSRP
       ) ed
              on ed.productGUID = pp.productGUID
              and ed.isSRP = pp.isSRP
              and ed.effectiveDate = pp.effectiveDate

GROUP BY [PP].ProductGUID

Be One with the Optimizer
TG

KevinKembel
Starting Member

11 Posts

Posted - 2008-03-20 : 18:58:52

TG

Thank you, that actually will work great for me, I never considered doing any inner join to itself like that to eliminate the older records, that's the solution I was looking for!

KevinKembel
Starting Member

11 Posts

Posted - 2008-03-20 : 19:02:34

Actually, I spoke too soon.

Although it's an option I never considered, it still uses a derived table...

I'm pretty sure this might not be possible to create an indexed view off of.

Tkizer: the base tables are properly indexed, but it's a problem when there's tens of thousands of products, and a 100K plus ProductPrice records, and the db is run on standard pc's and this data view is constantly queried.

I'm sure there's probably some way to get some performance boosts, but indexing the view was the most obvious solution.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-20 : 19:59:06

In my limited experience with attempting indexed views I had decided it's not worth it. There are too many restrictions including underlying tables needed to have been created with a bunch of SET options specified.

table design, index choices, and query optimization can take you a long way. We have 1 million row + tables under heavy contention that return sub-second results.

I realize that big changes are not likely to happen in existing systems but perhaps there are alternatives to an indexed view. If your products don't go inactive too often, maybe you can create and maintain a seperate, summary table for active-products optimized for your queries. Or you can post the table structure any related tables to see if anyone here has any different ideas.

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-20 : 20:34:09

I completely agree with TG. Adding indexes to views is always the very last resort.

The first step is to completely optimize your SQL.

The next step is to completely optimize your underlying tables and index.

The next step is to optimize your SQL some more.

Then, finally, if all else fails, consider indexed views. But know that unless you are indexing lots of calculated expressions and/or aggregations, an indexed view isn't going to help you any more than properly indexed tables will.

You told TG that you "spoke too soon" about his solution. What is the problem? You can't just say "it didn't work", you need to explain what you need specifically and why his code isn't working for you. Sample data and expected results help a lot.

Also, show us your table indexes, which we haven't seen any of yet. We don't even know if your table has a primary key constraint. Does it? If not, then that's the very first thing you need to fix!

Finally, you are comparing the bit column IsSRP to the string literal 'True', which is doing an implicit conversion to a string and will never evaluate that expression to TRUE. Are you sure that you SQL is even returning the results you expect? Optimizing is great, but it does no good to optimize something that does not return the correct results.

- Jeff
http://weblogs.sqlteam.com/JeffS

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-03-21 : 09:57:16

I disagree with TG and Jeff regarding the general value of indexed views (although I'm spending all my time doing warehousing and dimensional model ETL stuff, so I'm tainted). However, your problem does not lend itself to an indexed view solution.

A much better way (most likely) to write your query is with a NOT EXISTS and a correlated sub-query, rather than your equals max predicate.

Jay
to here knows when

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-21 : 09:59:45

I hope we can agree that the very step is to make sure the underlying table at least has a primary key!

I don't think that indexed views have little value, it is just that people start the optimizing process there when you really need to start with the underlying tables and the SQL statements you are executing first.

- Jeff
http://weblogs.sqlteam.com/JeffS

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-21 : 13:30:13

agreed, Jeff :)

Jay, Perhaps my anti indexed view comment was over stated. I guess it's Sql Server's job to provide a robust toolset and it's the developers job to use plan and use the right tool for the right job. I just see indexed views used like a band-aid instead of a design feature. And if you don't know all the restrictions and requirements of implementing one, it can be a frustrating experience.

I'd be curious to hear if there are a lot of people out there implementing indexed views and under what scenarios.

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-21 : 13:48:13

I will say this, pretty conclusively: In the years I've been at SQLTeam, I have never once answered or saw a question about "how to get the indexed view to work!" where the user actually needed to use indexed views. Not once. Perhaps this tells you something: if you cannot figure out how to write SQL that complies with the rules necessary to create an indexed view, you probably should not be using them. I mean this quite seriously and do not intend to sound condescending, but it really is the truth.

- Jeff
http://weblogs.sqlteam.com/JeffS

KevinKembel
Starting Member

11 Posts

Posted - 2008-03-24 : 13:08:10

Sorry for not replying sooner (easter).

I appreciate the feedback!

I definitely understand the advice, I should have included the indexes on the underlying tables, but I was confident in myself that the underlying tables were well indexed and that the best next step for me was to look at an indexed view. The actual problem is using three underlying tables, each with clustered indexed primary keys, nonclustered indexes on those keys that includes the columns queried in my view, as well as indexes on the foreign keys of each table, and indexes on IsSRP, and a descending index on EffectiveDate.

I'm going to accept the feedback you guys have already given as the appropriate solution as I feel that an indexed view on these underlying tables with the desired results is simply not possible, and I'll have to settle for my current non-indexed view, and work on query optimization.

Page47: Thank you for your NOT EXISTS suggestion, I'm going to try and implement that for sure.

Jsmith: I'd mentioned that I 'spoke too soon' about TG's recommendation because although it was clever, it also made use of a derived table (you can't index a view that uses a derived table).

Thank you very much for all of your help, gentlemen!

Haastastic
Starting Member

3 Posts

Posted - 2010-06-11 : 10:32:45

I know this is an old post, but Google sent me here first, so here's my attempt to help those that are considering indexed views with subqueries.

Indexed views are option of last resort. That said, they are an elegant way to optimize a query that requires a join. Everything else is complicated or overkill.

- If you don't need a join, subquery, or derived table, you don't need an indexed view. Instead you need a custered, non-clusted, or non-clustered covering index. If you don't know what these are, forget about indexed views until you understand indexes.
- If you have a join, consider that you may have over-normalized.
- If you have a subquery, try to make it a join.
- If you have a subquery, try using an aggregate function with a group by.
- If you have a subquery with top 1 and an order by, you may need to maintain a bit for your top records.
- If you have an outer join, use a special blank record to make it an inner join. Here is a brief example.

--inserting special blank records
SET IDENTITY_INSERT ParentTable on
INSERT ParentTable(ParentId, Value1, Value2, ...) values(-1, '', '', ...)
SET IDENTITY_INSERT ParentTable off
INSERT ChildTable(ParentId, ChildValue1, ...) values(-1, '', ...)
--joining to non-blank and blank records
SELECT
p.Value1
,p.Value2
...
,MAX(c.ChildValue1) --eliminates the blank record if a non-blank one exists
FROM ParentTable p
INNER JOIN ChildTable c ON c.ParentId in (p.ParentId, -1) --blank and non-blank
GROUP BY
p.Value1
,p.Value2
...

I'm not an expert, but I pretent to be one on the Internet.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-11 : 12:30:42

These are false:
- If you don't need a join, subquery, or derived table, you don't need an indexed view.
- If you have a join, consider that you may have over-normalized.

And the other statements are suspect as being false, just depends on what's required to get the data.

You typicall do NOT need an indexed view, instead you just index the underlying tables. Plain and simple. It has nothing to do with adding a join in there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Haastastic
Starting Member

3 Posts

Posted - 2010-06-11 : 13:41:29

Tara,

I'm more than willing to be and look forward to being wrong, but you stated that 2 of my statements were false without even providing a case when they are false. I'm particularly excited to read why one should never consider denormalizing tables.

Part of your confusion may come from a lack of context. I never said that you HAVE to use an indexed view. The assumption I make is that if your are considering an indexed view, you have a performance problem.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-11 : 14:04:53

I didn't say you shouldn't consider denormalizing tables. But the fact that you've used a join in a query doesn't mean that you have over-normalized. It's just false.

Denormalizing tables is typically done in a data warehouse environment, reporting environment, get around multiple joins in a query, or to get around performance issues in the OLTP environment. It's those last two that I have a problem with.

Using multiple joins in a query is not a problem for SQL Server. It's designed to work well with that as long as you have the indexes to support it.

If denormalizing tables fixes a performance problem, then there are other things wrong with the system such as bad database design, missing indexes, improper SQL settings, hardware bottlenecks. The last time I denormalized a database for performance reason was in SQL Server 2000, so it was several years ago. I've got huge performance requirements, such as all queries completing in under 300 milliseconds in a 1TB database, but denormalizing the tables is not something we implemented.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-11 : 14:06:57

Also, what's the point of rewriting a query to an inner join when an outer join works well? Why are you recommending this?

Using IDENTITY_INSERT is simply a bad practice due to the access that is required and contention it'll cause. Your recommendation would simply not work on large databases with a heavy load. I've got one system that has 3000 transactions per second, there's no way we would implement that recommendation there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Haastastic
Starting Member

3 Posts

Posted - 2010-06-12 : 10:13:18

Tara,

We're getting a little off the thread topic.

quote:
I didn't say you shouldn't consider denormalizing tables. But the fact that you've used a join in a query doesn't mean that you have over-normalized. It's just false.

I said that you should CONSIDER that you MAY have over-normalized. For this to be false, denormalization would be off the table in any case.

quote:
Denormalizing tables is typically done in a data warehouse environment, reporting environment, get around multiple joins in a query, or to get around performance issues in the OLTP environment. It's those last two that I have a problem with.

Why does typical matter? There are well designed, non-OLTP database applications with millions of rows.

quote:
Using multiple joins in a query is not a problem for SQL Server. It's designed to work well with that as long as you have the indexes to support it.

Typically, this is true. Very large tables do not always perform well with properly indexed joins. When you have some free time, create 2 tables with 10 columns and 10 million rows. Index them. Then compare the join execution time to the execution time with an indexed view. You may also want to compare the query plans. Indexed views are a useful optimization tool.

quote:
If denormalizing tables fixes a performance problem, then there are other things wrong with the system such as bad database design, missing indexes, improper SQL settings, hardware bottlenecks. The last time I denormalized a database for performance reason was in SQL Server 2000, so it was several years ago. I've got huge performance requirements, such as all queries completing in under 300 milliseconds in a 1TB database, but denormalizing the tables is not something we implemented.

You have described several things I would also consider when facing a performance problem, but the fact that you refuse to consider denormalization or indexed views doesn't make them useless. Instead you may be upgrading hardware prematurely, which reduces the value you bring to the issue.

quote:
Also, what's the point of rewriting a query to an inner join when an outer join works well? Why are you recommending this?

The title of this thread is "indexed views without subquery". Indexed views don't allow outer joins. You should seriously consider reading up on the new features of the latest versions of SQL Server, and I am not saying that to condescend. You are clearly a regular contributor, and I think that's great. Covering indexes are another addition in 2005 that are even more useful than indexed views.

midavis
Starting Member

23 Posts

Posted - 2010-07-02 : 14:53:13

It seems like a lot of you are saying not to use indexed views, but what about this situation. I have a Company table and a Contact Table. Lets say the contact can belong to more than 1 company. I therefore also have a CompanyContact table setup for my many to many relationship. Quite often I have to run reports that include the first contact for a company. Instead of creating a function that pulls this out of each select statement or doing subqueries should I instead have an indexed view to hold the first contact for each company? The performance on the subquery I am currently using for this is pretty fast, but I am just trying to learn how to make the database faster in general. If I do by chance use an indexed view how would I do this? I can easily do this with a CTE and ROW_NUMBER to create a normal view, but should it even be indexed?

Thanks in advance for the advice.

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources