Hi All
We are having problem on indexed views when we try to join to more than one
table that is not part of the original indexed view in a query. Has anybody
seen a similar problem?
Thanks
Atholl
See SQL examples below:
--New Indexed View - fact table grouped by three columns, no joins drop view
dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
WITH SCHEMABINDING
AS
SELECT COUNT_BIG (*) AS Expr1
,a.business_unit_id
,a.date_id
,a.test_id
,sum(a.RECORD_COUNT_FAILED) record_count_failed
from dbo.fact_diagnostic_results a
group by a.business_unit_id, a.date_id, a.test_id
CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
[dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
(business_unit_id, date_id, test_id) ON [Indexes]
--WORKING - joined to 2 dimension tables, uses dimension table id columns in
group by select bu.business_unit_id
,d.date_id
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_id
,d.date_id
--NOT WORKING - joined to 2 dimension tables, uses dimension table columns
in group by select bu.business_unit_srccd
,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
,d.calendar_date_desc
--WORKING - joined to 1 dimension table, uses dimension table columns in
group by select bu.business_unit_srccd
-- ,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
--inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
-- ,d.calendar_date_desc
I'm surprised that third query is using the indexed view -- what is the
execution plan for that?
Can you add business_unit_srccd to the view?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
> Hi All
> We are having problem on indexed views when we try to join to more than
> one
> table that is not part of the original indexed view in a query. Has
> anybody
> seen a similar problem?
> Thanks
> Atholl
> See SQL examples below:
> --New Indexed View - fact table grouped by three columns, no joins drop
> view
> dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> WITH SCHEMABINDING
> AS
> SELECT COUNT_BIG (*) AS Expr1
> ,a.business_unit_id
> ,a.date_id
> ,a.test_id
> ,sum(a.RECORD_COUNT_FAILED) record_count_failed
> from dbo.fact_diagnostic_results a
> group by a.business_unit_id, a.date_id, a.test_id
> CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
> [dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
> (business_unit_id, date_id, test_id) ON [Indexes]
> --WORKING - joined to 2 dimension tables, uses dimension table id columns
> in
> group by select bu.business_unit_id
> ,d.date_id
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_id
> ,d.date_id
> --NOT WORKING - joined to 2 dimension tables, uses dimension table columns
> in group by select bu.business_unit_srccd
> ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> ,d.calendar_date_desc
> --WORKING - joined to 1 dimension table, uses dimension table columns in
> group by select bu.business_unit_srccd
> -- ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> -- inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> -- ,d.calendar_date_desc
>
|||Hi Adam
The execution plan does a Clustered Index Scan on the view; why are you
surprised that it uses the view?
Yes I could add business_unit_srccd to the view but this is just one of
about 30 columns on the business_unit table, I don't want to add them all to
the view.
The interesting thing is that it seems to work as long as I have the ID
columns in the Group By on the query. This is strange because the ID columns
come from the dimension tables and aren't even part of the view so why are
they any different from the other columns on the dimension tables?
Atholl
"Adam Machanic" wrote:
> I'm surprised that third query is using the indexed view -- what is the
> execution plan for that?
> Can you add business_unit_srccd to the view?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Atholl" <Atholl@.discussions.microsoft.com> wrote in message
> news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
>
>
|||"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:3E604176-E361-41C9-9FC6-4677B7DAE514@.microsoft.com...
> Hi Adam
> The execution plan does a Clustered Index Scan on the view; why are you
> surprised that it uses the view?
Even though it's doing a scan on the view, it still needs to do some
sort of lookup operation to get the values for that missing column -- how
expensive is that? I was assuming it would be a fairly expensive operation,
but perhaps the dimension is not large?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
Showing posts with label views. Show all posts
Showing posts with label views. Show all posts
Monday, March 26, 2012
Optimizer not using Indexed View
Hi All
We are having problem on indexed views when we try to join to more than one
table that is not part of the original indexed view in a query. Has anybody
seen a similar problem?
Thanks
Atholl
See SQL examples below:
--New Indexed View - fact table grouped by three columns, no joins drop view
dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
WITH SCHEMABINDING
AS
SELECT COUNT_BIG (*) AS Expr1
,a.business_unit_id
,a.date_id
,a.test_id
,sum(a.RECORD_COUNT_FAILED) record_count_failed
from dbo.fact_diagnostic_results a
group by a.business_unit_id, a.date_id, a.test_id
CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
[dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
(business_unit_id, date_id, test_id) ON [Indexes]
--WORKING - joined to 2 dimension tables, uses dimension table id columns in
group by select bu.business_unit_id
,d.date_id
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_id
,d.date_id
--NOT WORKING - joined to 2 dimension tables, uses dimension table columns
in group by select bu.business_unit_srccd
,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
,d.calendar_date_desc
--WORKING - joined to 1 dimension table, uses dimension table columns in
group by select bu.business_unit_srccd
-- ,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
-- inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
-- ,d.calendar_date_descI'm surprised that third query is using the indexed view -- what is the
execution plan for that?
Can you add business_unit_srccd to the view?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
> Hi All
> We are having problem on indexed views when we try to join to more than
> one
> table that is not part of the original indexed view in a query. Has
> anybody
> seen a similar problem?
> Thanks
> Atholl
> See SQL examples below:
> --New Indexed View - fact table grouped by three columns, no joins drop
> view
> dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> WITH SCHEMABINDING
> AS
> SELECT COUNT_BIG (*) AS Expr1
> ,a.business_unit_id
> ,a.date_id
> ,a.test_id
> ,sum(a.RECORD_COUNT_FAILED) record_count_failed
> from dbo.fact_diagnostic_results a
> group by a.business_unit_id, a.date_id, a.test_id
> CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
> [dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
> (business_unit_id, date_id, test_id) ON [Indexes]
> --WORKING - joined to 2 dimension tables, uses dimension table id columns
> in
> group by select bu.business_unit_id
> ,d.date_id
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_id
> ,d.date_id
> --NOT WORKING - joined to 2 dimension tables, uses dimension table columns
> in group by select bu.business_unit_srccd
> ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> ,d.calendar_date_desc
> --WORKING - joined to 1 dimension table, uses dimension table columns in
> group by select bu.business_unit_srccd
> -- ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> -- inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> -- ,d.calendar_date_desc
>|||Hi Adam
The execution plan does a Clustered Index Scan on the view; why are you
surprised that it uses the view?
Yes I could add business_unit_srccd to the view but this is just one of
about 30 columns on the business_unit table, I don't want to add them all to
the view.
The interesting thing is that it seems to work as long as I have the ID
columns in the Group By on the query. This is strange because the ID column
s
come from the dimension tables and aren't even part of the view so why are
they any different from the other columns on the dimension tables?
Atholl
"Adam Machanic" wrote:
> I'm surprised that third query is using the indexed view -- what is the
> execution plan for that?
> Can you add business_unit_srccd to the view?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Atholl" <Atholl@.discussions.microsoft.com> wrote in message
> news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
>
>|||"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:3E604176-E361-41C9-9FC6-4677B7DAE514@.microsoft.com...
> Hi Adam
> The execution plan does a Clustered Index Scan on the view; why are you
> surprised that it uses the view?
Even though it's doing a scan on the view, it still needs to do some
sort of lookup operation to get the values for that missing column -- how
expensive is that? I was assuming it would be a fairly expensive operation,
but perhaps the dimension is not large?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
We are having problem on indexed views when we try to join to more than one
table that is not part of the original indexed view in a query. Has anybody
seen a similar problem?
Thanks
Atholl
See SQL examples below:
--New Indexed View - fact table grouped by three columns, no joins drop view
dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
WITH SCHEMABINDING
AS
SELECT COUNT_BIG (*) AS Expr1
,a.business_unit_id
,a.date_id
,a.test_id
,sum(a.RECORD_COUNT_FAILED) record_count_failed
from dbo.fact_diagnostic_results a
group by a.business_unit_id, a.date_id, a.test_id
CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
[dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
(business_unit_id, date_id, test_id) ON [Indexes]
--WORKING - joined to 2 dimension tables, uses dimension table id columns in
group by select bu.business_unit_id
,d.date_id
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_id
,d.date_id
--NOT WORKING - joined to 2 dimension tables, uses dimension table columns
in group by select bu.business_unit_srccd
,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
,d.calendar_date_desc
--WORKING - joined to 1 dimension table, uses dimension table columns in
group by select bu.business_unit_srccd
-- ,d.calendar_date_desc
,sum(a.record_count_failed) record_count_1
from dbo.fact_diagnostic_results a
inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
-- inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
-- ,d.calendar_date_descI'm surprised that third query is using the indexed view -- what is the
execution plan for that?
Can you add business_unit_srccd to the view?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
> Hi All
> We are having problem on indexed views when we try to join to more than
> one
> table that is not part of the original indexed view in a query. Has
> anybody
> seen a similar problem?
> Thanks
> Atholl
> See SQL examples below:
> --New Indexed View - fact table grouped by three columns, no joins drop
> view
> dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
> WITH SCHEMABINDING
> AS
> SELECT COUNT_BIG (*) AS Expr1
> ,a.business_unit_id
> ,a.date_id
> ,a.test_id
> ,sum(a.RECORD_COUNT_FAILED) record_count_failed
> from dbo.fact_diagnostic_results a
> group by a.business_unit_id, a.date_id, a.test_id
> CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
> [dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
> (business_unit_id, date_id, test_id) ON [Indexes]
> --WORKING - joined to 2 dimension tables, uses dimension table id columns
> in
> group by select bu.business_unit_id
> ,d.date_id
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_id
> ,d.date_id
> --NOT WORKING - joined to 2 dimension tables, uses dimension table columns
> in group by select bu.business_unit_srccd
> ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> ,d.calendar_date_desc
> --WORKING - joined to 1 dimension table, uses dimension table columns in
> group by select bu.business_unit_srccd
> -- ,d.calendar_date_desc
> ,sum(a.record_count_failed) record_count_1
> from dbo.fact_diagnostic_results a
> inner join dbo.lu_business_unit bu on a.business_unit_id =
> bu.business_unit_id
> -- inner join dbo.lu_date d on a.date_id=d.date_id
> group by bu.business_unit_srccd
> -- ,d.calendar_date_desc
>|||Hi Adam
The execution plan does a Clustered Index Scan on the view; why are you
surprised that it uses the view?
Yes I could add business_unit_srccd to the view but this is just one of
about 30 columns on the business_unit table, I don't want to add them all to
the view.
The interesting thing is that it seems to work as long as I have the ID
columns in the Group By on the query. This is strange because the ID column
s
come from the dimension tables and aren't even part of the view so why are
they any different from the other columns on the dimension tables?
Atholl
"Adam Machanic" wrote:
> I'm surprised that third query is using the indexed view -- what is the
> execution plan for that?
> Can you add business_unit_srccd to the view?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Atholl" <Atholl@.discussions.microsoft.com> wrote in message
> news:6E3F6D63-6A57-4D9F-95B8-A6774E2D858B@.microsoft.com...
>
>|||"Atholl" <Atholl@.discussions.microsoft.com> wrote in message
news:3E604176-E361-41C9-9FC6-4677B7DAE514@.microsoft.com...
> Hi Adam
> The execution plan does a Clustered Index Scan on the view; why are you
> surprised that it uses the view?
Even though it's doing a scan on the view, it still needs to do some
sort of lookup operation to get the values for that missing column -- how
expensive is that? I was assuming it would be a fairly expensive operation,
but perhaps the dimension is not large?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
Friday, March 23, 2012
Optimize "LIKE"
It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.
How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.
Thank you.
You can optimize like by not having a wildcard (% or ?) at the beginning of the search argument.
Using '%word%' will always scan every row in the entire table.
Hope this assists.
Optimize "LIKE"
It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.
How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.
Thank you.
You can optimize like by not having a wildcard (% or ?) at the beginning of the search argument.
Using '%word%' will always scan every row in the entire table.
Hope this assists.
Monday, March 19, 2012
Optimising Query based on Views
I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
Is
that
GROUP
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
quote:
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
quote:
> it worth my while making the main view into a user-defined function so
that
quote:
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
quote:|||Thanks very much for your explanation, Geoff. Looks like there is nothing
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
quote:
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>
Optimising Query based on Views
I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
> it worth my while making the main view into a user-defined function so
that
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
> it worth my while making the main view into a user-defined function so
that
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>
Subscribe to:
Posts (Atom)