I created view with schemabinding:

CREATE VIEW dbo.test with schemabinding as
select p.[ID]as pID,n.DATE_ID,n.weekday,sum(case when p.excluded=1 and
n.excluded=1 then 0 else quantity end) as quantity, count_big(*)as number
from dbo.tableP p INNER JOIN dbo.tableN n
ON p.media_ID=n.ID_MEDIA AND n.DATE_ID between p.od_dateID and p.do_dateID
WHERE n.type in(5,6)
group by p.[ID],n.DATE_ID,n.weekday

Then I create clustered index:

CREATE UNIQUE CLUSTERED INDEX IVW_sumTest
ON dbo.test (pID,date_ID,weekday)

Then I select from this view:

SELECT sum(quantity) from test WHERE pID=172 AND date_ID between 366 AND 730

It takes about 300 mili seconds for this select.

If I create table instead of view and insert all data into that table:

INSERT INTO dbo.tableTest
select p.[ID]as pID,n.DATE_ID,n.weekday,sum(case when p.excluded=1 and
n.excluded=1 then 0 else quantity end) as quantity, count_big(*)as number
from dbo.tableP p INNER JOIN dbo.tableN n
ON p.media_ID=n.ID_MEDIA AND n.DATE_ID between p.od_dateID and p.do_dateID
WHERE n.type in(5,6)
group by p.[ID],n.DATE_ID,n.weekday

and then select from that table:

SELECT sum(quantity) from dbo.tableTest WHERE pID=172 AND date_ID between
366 AND 730

it takes 0 miliseconds.

I have all conditions from where clause in clustered index of my stored
view.
Why is table so much faster than indexed view? Am i missing something?

If so, why not than always use tables insetad of indexed views?
Can someone explain me?

regards,S

Have you compared the two execution plans? What are the indexes on the table?
Are there any other indexes on the indexed view?


ML

---
http://milambda.blogspot.com/

There is no other index on view.

On table I have only Primary key (which is by default clustered index and I
leave it as default).
Primary key is on column pID+date_ID.

I execute:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Still the same results.
Any idea?

I guess view is slower because it has to calculate the quantity from all
inner joins while on the other hand when I select from table, quantity is
already calculated for Primary key and I just have to sum on primary key
base. (I don't know, maybe for view it's also calculated)

If I'm right, when than  would we use stored view instead of table?

Simon

Show quoteHide quote

Is it actually using the view?  When you do a SHOWPLAN, does it use the view
or the underlying tables?  Also, are you using Enterprise Edition or
Developer Edition?  If not, then you'll have to use the NOEXPAND hint.

There is no other index on view.

On table I have only Primary key (which is by default clustered index and I
leave it as default).
Primary key is on column pID+date_ID.

I execute:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Still the same results.
Any idea?

I guess view is slower because it has to calculate the quantity from all
inner joins while on the other hand when I select from table, quantity is
already calculated for Primary key and I just have to sum on primary key
base. (I don't know, maybe for view it's also calculated)

If I'm right, when than  would we use stored view instead of table?

Simon

Show quoteHide quote

Hi,

I forgot  with (NOEXPAND).  Sometimes I'm really ......

Now, both results are in 0 miliseconds.

So, you propose table or view? What is the factor on which should we decided
what to use if both is executed in the same time.

Regards,S


Show quoteHide quote

There are many factors to consider:

1) how frequent are changes to the base table?
2) how "fresh" should the aggregated data be?
3) what is the aggregated data used for?
4) can the indexed view be used in any other queries (to simplify other
execution plans)?


ML

---
http://milambda.blogspot.com/

In addition to that, consider what the indexed view is supposed to give you.
Here, you didn't get an improvement in performance between the two.  What
you paid for that was extra disk space usage and a potential slowing of
updates on  the underlying tables.

There are many factors to consider:

1) how frequent are changes to the base table?
2) how "fresh" should the aggregated data be?
3) what is the aggregated data used for?
4) can the indexed view be used in any other queries (to simplify other
execution plans)?


ML

---
http://milambda.blogspot.com/

Agregated data are frequenntly changed only in one table, on all others are
changed once a day.

Agregated data is used for statistics and reports.

They should not be freshed more than one day.

Indexed view or agregated table can't be use in any other query.

So, I should create aggregated table intead of view?

And view should be created when I need always fresh data.

regards,S

Show quoteHide quote

If the data is only used for reporting purposes, and there can be a delay of
hours (or days, even) before current data is available, then I'd suggest
using a table (perhaps even on a different server). The data can then be
populated via a SQL Agent Job (e.g. once a day, after all business
transactions are complete). This way you can also keep historic data
(aggregated values for each day) and help improve your business intelligence.


ML

---
http://milambda.blogspot.com/

Have you cleared the cache before doing your tests:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

I created view with schemabinding:

CREATE VIEW dbo.test with schemabinding as
select p.[ID]as pID,n.DATE_ID,n.weekday,sum(case when p.excluded=1 and
n.excluded=1 then 0 else quantity end) as quantity, count_big(*)as number
from dbo.tableP p INNER JOIN dbo.tableN n
ON p.media_ID=n.ID_MEDIA AND n.DATE_ID between p.od_dateID and p.do_dateID
WHERE n.type in(5,6)
group by p.[ID],n.DATE_ID,n.weekday

Then I create clustered index:

CREATE UNIQUE CLUSTERED INDEX IVW_sumTest
ON dbo.test (pID,date_ID,weekday)

Then I select from this view:

SELECT sum(quantity) from test WHERE pID=172 AND date_ID between 366 AND 730

It takes about 300 mili seconds for this select.

If I create table instead of view and insert all data into that table:

INSERT INTO dbo.tableTest
select p.[ID]as pID,n.DATE_ID,n.weekday,sum(case when p.excluded=1 and
n.excluded=1 then 0 else quantity end) as quantity, count_big(*)as number
from dbo.tableP p INNER JOIN dbo.tableN n
ON p.media_ID=n.ID_MEDIA AND n.DATE_ID between p.od_dateID and p.do_dateID
WHERE n.type in(5,6)
group by p.[ID],n.DATE_ID,n.weekday

and then select from that table:

SELECT sum(quantity) from dbo.tableTest WHERE pID=172 AND date_ID between
366 AND 730

it takes 0 miliseconds.

I have all conditions from where clause in clustered index of my stored
view.
Why is table so much faster than indexed view? Am i missing something?

If so, why not than always use tables insetad of indexed views?
Can someone explain me?

regards,S

simonZ (simon.zu***@studio-moderna.com) writes:
Show quoteHide quoteCould it just happen to be a caching issue? That is, when you query the
table, all data is in cache, so that there is on need to read from disk.

To find out for sure, run the queries preceded by DBCC DROPCLEANBUFFERS.
(But don't this on a production machine.)

did you check in the query execution plan whether the index you had created
was being used?
If you check in the BOL, a lot of parameters are looked at before using the
index of the view.

Passing a result set to a stored procedure
help on Indexes
Design Question - Suggestions Please
Retrieving rows with minimum values within a column
Help is not working
NEED HELP IN MS SQL SERVER 2005!!!
Calculate The Time To Run SP
Lock requests/sec very high....
Can SQL Database work as normal without the ldf file?
IF funcionality in SQL server views