CODEWITHSUNDEEP
sqlsql-serverindexingviewindexed-view
S. Elmar
S. Elmar

Reputation: 23

Indexed views. Query ignoring view and uses table instead

My task is to optimize this query:

Declare @sumBalance NUMERIC = (select SUM(CURRENT_BALANCE) as Balance
from dbo.ACCOUNT_DETAILS)
select @sumBalance

I've read that the best solution for aggregation functions is using indexed views instead of tables.

I've created the view with SCHEMABINDING:

CREATE VIEW dbo.CURRENT_BALANCE_VIEW
WITH SCHEMABINDING
AS
    SELECT id,CURRENT_BALANCE
    FROM dbo.ACCOUNT_DETAILS

After that I've created 2 indexes:

The first for ID

 CREATE UNIQUE CLUSTERED INDEX index_ID_VIEW ON dbo.View(ID);

The second for CURRENT_BALANCE my second one column

CREATE NONCLUSTERED INDEX index_CURRENT_BALANCE_VIEW
ON dbo.CURRENT_BALANCE_VIEW(ID);

And here I got troubles with new query:

Declare @sumBalance NUMERIC = (select SUM(CURRENT_BALANCE) as Balance
from dbo.CURRENT_BALANCE_VIEW)
select @sumBalance

  1. New query doesn't use view

    https://i.sstatic.net/jlPEd.png

  2. Somehow my indexes were added to the folder Statistics

    Look in another post

  3. I don't understand why I can see index 'index_current_balance' cause there is no such an index in the table

Look in another post

P.S. Already tried create index in the table and it helped. It made query works faster from 0.2 Es.operator cost to 0.009 but anyway it must be faster.

p.s.s Sorry for making you click on the link, my reputation doesn't allow me to past images properly =\

p.s.s.s Working with SQL Server 2014

p.s.s.s.s Just realized that I don't need to sum 0-s. Expected them grom function.

Thanks in advance.

Upvotes: 1

Views: 295

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This query:

Declare @sumBalance NUMERIC = (select SUM(CURRENT_BALANCE) as Balance
                               from dbo.ACCOUNT_DETAILS);
select @sumBalance;

is not easy to optimize. The only index that will help it is:

create index idx_account_details_current_balance on account_details(current_balance);

This is a covering index for the query, and can be used for the SUM(). However, the index still needs to be scanned to do the SUM(). Scanning the index should be faster than scanning the table because it is likely to be much smaller.

SQL Server 2012+ has a facility called columnstore indexes that would have the same effect.

The advice for using indexed views for aggregation functions doesn't seem like good advice. For instance, if the above query used MIN() or MAX(), then the above index should be the optimal index for the query, and it should run quite fast.

EDIT:

Your reference article is quite reasonable. If you want to create an indexed view for this purpose, then create it with aggregation. 

CREATE VIEW dbo.CURRENT_BALANCE_VIEW
WITH SCHEMABINDING
AS
    SELECT SUM(CURRENT_BALANCE) as bal, COUNT_BIG(CURRENT_BALANCE) as cnt
    FROM dbo.ACCOUNT_DETAILS;

This is a little weird, because it returns one row. I think the following will work:

create index idx_account_details on current_balance_view(bal);

If not, you may need to introduce a dummy column for the index.

Then:

select *
from dbo.current_balance_view;

should have the precomputed value.

Upvotes: 1

CPMunich
CPMunich

Reputation: 725

if you use Standard Edition of SQL-Server you have to use the NOEXPAND-Hint in order to use the index of a view.

For example:

SELECT *
FROM dbo.CURRENT_BALANCE_VIEW (NOEXPAND);

Upvotes: 2

Related Questions