Skip to main content
Database Administrators

Return to Question

Post Closed as "Duplicate" by ypercubeᵀᴹ, Philᵀᴹ, mustaccio, LowlyDBA - John M, Tom V
added 472 characters in body
Source Link
John
John
  • 775
  • 3
  • 17

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.

JOYOUS ADDENDUM: Now that I got it working, I successfully tested that it even works in cases where the query contains a left join in those cases where the left join can in fact be optimized away (ie. the on-clause covers a unique index in the joined table).

That is really awesome, because it means that even in cases of a query with left joins one can design the schema in such a way that getting a total row count of everything or specific groupings is fast.

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.

JOYOUS ADDENDUM: Now that I got it working, I successfully tested that it even works in cases where the query contains a left join in those cases where the left join can in fact be optimized away (ie. the on-clause covers a unique index in the joined table).

That is really awesome, because it means that even in cases of a query with left joins one can design the schema in such a way that getting a total row count of everything or specific groupings is fast.

added 196 characters in body
Source Link
John
John
  • 775
  • 3
  • 17

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

EDIT: On the duplicate question - I'm more interested in the GROUP BY and aggregate aspect of indexed views. The answer helped me find a stupid mistake I made, it's now working for me as well.

added 3 characters in body
Source Link
John
John
  • 775
  • 3
  • 17

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.*Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.*, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table.

The query is something like

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

so I created a view

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

with a clustered index

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

And that works, I can now write the original query as

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

and get the desired result much faster.

Alas, it's hardly much use for me, as my queries are usually not hand-crafted. I really need the original query to become faster by using the indexed view as some kind of index for BaseTable - and I think I read somewhere that this can happen in some circumstances, but according to my tests, not in this one.

So my question(s) would be:

  • Can indexed views still help me in this scenario somehow?
  • Can anybody recommend sources/literature than explains in which cases indexed views do get used for optimizations of queries on the tables they are based on?
Source Link
John
John
  • 775
  • 3
  • 17