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_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

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
--

optimize query - how to make it an "Index seek"

create table t1(a varchar(50) , b varchar(50))

create index i1 on t1(a)
create index i2 on t1(b)

create view v1
as
select * from t1 where isnull(a,b) = 'test'

select * from v1

The above SQL "select * from v1" is doing a table scan.
What do I do to make it perform an index seek ??

TIA

- ForXLDBHi!

This query will make use of index i3:

create index i3 on t1(a,b)

Carsten|||Is it possible to have make an "Index seek" ??|||Just found the following behaviour:

When running:
select * from dbo.v1 => SQL Server uses an index scan

select * from dbo.v1 where a='test' => SQL Server uses an index seek

It's quite strange...|||I do use the following...
select * from dbo.v1 where a='test'

So, it works for me I guess.

Thanks !!|||quite strange...

How so?

There's no predicate...|||I'm not sure. But, it shows "Index Seek" in the plan !!|||Hi Brett,

what confused me is the point, that the DBMS changes the execution plan even if there is basically no difference in the constraint (in this case "a = 'test'").

The definition of v1 already contains this WHERE-clause. And that's why I expect SQL Server to generate the same execution plan.

Carsten

How so?

There's no predicate...|||create table t1(a datetime , b datetime)

create view v1
as
select a,b, isnull(a,b) as c from t1

create index i3 on t1(a,b)

select *
from v1
where c = '01/01/2004'

the above sql is using index scan.....can we make it to use index seek ?

--clean up
--drop table t1
--drop view v1
--drop index t1.i3|||One small thing is that you need to have a bunch of data in the table, before the optimizer thinks about the index. If the data portion of the table consists of a single page, then you will always get a table scan. This is not bad, as it is one read. If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.

A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.|||--Change view to:
alter view v1
as
select * from t1 where a = 'test'
union
select * from t1 where b = 'test'
go

--add 2 indexes and drop existing ones:
drop index t1.i1
drop index t1.i2
create index i3 on t1(a,b)
create index i4 on t1(b,a)|||...If you force an index scan (with query hints, say), then you get a read of an index page, then a read of a data page. 2 reads for the price of one.This is not true when you deal with queries that do not address fields that are not part of an index.

...A larger thing is the use of isnull(). Until Microsoft gets the Function Based Index implemented (like Oracle) then this will always generate a table scan. The Optimizer views any function as a black box, and can not estimate how many "hits" the index will have when the function is done. So it defaults to a table scan.Really? Try this using DDL changes from my previous post:

select * from v1 where isnull(a,b)='test'

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

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...

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:

---

> 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...

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.
>

Optimising a query / stored procedure

I have a Stored Procedure based on a View which is running very slowly, so
we need to rewrite it. (Need to reduce from 10sec to 1sec)
Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
However re-Executing with same parameters it only takes 1 sec, it's like the
Query Optimiser has cached or remembered the Execution Plan or something.
(If I use a new set or parameters then it takes 10 secs again)
Unless we can get a consistent result of how long the current SP takes to
run, there's no way of determining whether any changes are beneficial - is
there some way of clearing the "cache" or "un-remembering" the Execution
Plan so that we can get a consistent result to compare with ?
(This is SQL Server 2000 standard edition)Look at
DBCC DROPCLEANBUFFER
DBCC FREEPROCCACHE
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Richard" <richa@.heidmar.co.uk> wrote in message
news:EHNkb.10$to6.7@.newsr2.u-net.net...
> I have a Stored Procedure based on a View which is running very slowly, so
> we need to rewrite it. (Need to reduce from 10sec to 1sec)
> Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
> However re-Executing with same parameters it only takes 1 sec, it's like
the
> Query Optimiser has cached or remembered the Execution Plan or something.
> (If I use a new set or parameters then it takes 10 secs again)
> Unless we can get a consistent result of how long the current SP takes to
> run, there's no way of determining whether any changes are beneficial - is
> there some way of clearing the "cache" or "un-remembering" the Execution
> Plan so that we can get a consistent result to compare with ?
> (This is SQL Server 2000 standard edition)
>|||... and CHECKPOINT in the beginning to get rid of dirty pages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:ei6GoxulDHA.3316@.tk2msftngp13.phx.gbl...
> Look at
> DBCC DROPCLEANBUFFER
> DBCC FREEPROCCACHE
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Richard" <richa@.heidmar.co.uk> wrote in message
> news:EHNkb.10$to6.7@.newsr2.u-net.net...
> > I have a Stored Procedure based on a View which is running very slowly, so
> > we need to rewrite it. (Need to reduce from 10sec to 1sec)
> > Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
> > However re-Executing with same parameters it only takes 1 sec, it's like
> the
> > Query Optimiser has cached or remembered the Execution Plan or something.
> > (If I use a new set or parameters then it takes 10 secs again)
> > Unless we can get a consistent result of how long the current SP takes to
> > run, there's no way of determining whether any changes are beneficial - is
> > there some way of clearing the "cache" or "un-remembering" the Execution
> > Plan so that we can get a consistent result to compare with ?
> >
> > (This is SQL Server 2000 standard edition)
> >
> >
>

Optimiser problem? Takes ages to retrieve next key in 4-segment

Nikki,
You are absolutely correct in your view of this, but unfortunately,
I don't think there is a way in T-SQL to deal with this. It might
become easier with the analytic functions of SQL Server 2005, but
that may not be true and doesn't help you much now... It may also
be that there is a clever way of doing this with cursors that I'm not
seeing, but it would be a shame to have to do that.
If it weren't for the datetime column, you could create (and perhaps
get away with indexing) a computed table column or view column that
concatenated fixed-length string versions of the index columns. Datetime
conversions are considered non-deterministic or imprecise and can't be
indexed, so you would have to use a decimal column or separate serial
date and time columns stored as integers or strings.
Not fun, but if this is a serious concern for you, it might be worth
the trouble of considering. The concatenation is also the closest you can
come to a "clean" way of expressing this, but it won't be as efficient as
it should be:
select top 1 *
from [Requirement Detail]
WHERE
ORDER_11
+ CONVERT(PRTNUM_11,15)
+ CONVERT(CHAR(30),CURDUE_11,121)
+ TYPE_11
>
@.ORDER_11
+ CONVERT(@.PRTNUM_11,15)
+ CONVERT(CHAR(30),@.CURDUE_11,121)
+ @.TYPE_11
ORDER BY ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11
You might get lucky with this, depending on the
distribution of data you have. Adding this or other
conditions you know can use indexes and are true can
help:
ORDER_11 >= @.ORDER_11
At the risk of increasing your frustration more than highlighting your sense
, I'll
point out that if SQL Server implemented row constructors according
to the ANSI SQL standard, it would be very easy:
-- WARNING: DON'T TRY THIS IN T-SQL. :(
-- USING T-SQL TOP also:
SELECT TOP 1 *
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
ORDER BY ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11
ANSI SQL doesn't have TOP, and I don't have my copy handy
to be sure MAX can be used with row constructors, but the
ANSI version might be one of these:
SELECT *
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11) = (
SELECT MIN((ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11))
FROM [Requirement Detail]
WHERE (ORDER_11, PRTNUM_11, CURDUE_11, TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
)
or,
SELECT *
FROM [Requirement Detail] AS RD1
WHERE NOT EXISTS (
SELECT * FROM [Requirement Detail] AS RD2
WHERE (RD2.ORDER_11, RD2.PRTNUM_11, RD2.CURDUE_11, RD2.TYPE_11)
> (@.ORDER_11, @.PRTNUM_11, @.CURDUE_11, @.TYPE_11)
AND (RD2.ORDER_11, RD2.PRTNUM_11, RD2.CURDUE_11, RD2.TYPE_11)
< (RD1.ORDER_11, RD1.PRTNUM_11, RD1.CURDUE_11, RD1.TYPE_11)
)
SK
Nikki Locke wrote:

> Thanks for your reply.
> Unfortunately clustered indexes are out. If we did have a clustered index,
> it would be on the most frequently used key (which is not this one,
> unfortunately).
> Is there a better way or wording the query? All I want it to do is to make
> a key out of the 4 fields provided, look the key up in the index, and
> return either the found record, or the next record in the index if the
> specified one doesn't exist.
> When stated like that, it is obviously a very cheap operation. If I could
> only find a way of telling SQL server that was what I wanted, all would be
> fine.
>SQL Server does support ALL, but Hugo may have been (and I certainly
was) lamenting the fact that SQL Server doesn't support multicolumn
comparisons of the sort (a1,b1,c1) < (a2,b2,c2). Queries that use
ALL can fairly easily be rewritten without ALL, but queries that use
multicolumn comparisons cannot, and the rewrite, in this case
(
(a1 < a2)
OR
((a1 = a2) and (b1 < b2))
OR
((a1 = a2) and (b1 = b2) and (c1 < c2))
)
is not (that I've seen) optimized to take advantage of an index
on (a,b,c), which ought to help out here.
SK
oj wrote:

> Hugo,
> I haven't been following the entire thread. But sqlserver doe support ALL
> (to some extend).
> http://msdn.microsoft.com/library/e..._qd_11_1sz0.asp
>|||Sounds good! Thanks for the followup.
SK
Nikki Locke wrote:

>Thankyou very much for your detailed and useful reply.
>I now have a much better understanding of the problem, and can start to
>approach it from a different angle.
>I already have a query which executes in 3 msecs (as opposed to 3 secs for
>the original), which is as follows...
>declare @.ORDER_11 nchar(10)
>declare @.PRTNUM_11 nvarchar(15)
>declare @.CURDUE_11 smalldatetime
>declare @.TYPE_11 nchar(2)
>declare @.KEY nchar(35)
>set @.ORDER_11 = '5480184500'
>set @.PRTNUM_11 = '548000000000000'
>set @.CURDUE_11 = '2005-04-20 12:00:00'
>set @.TYPE_11 = 'RQ'
>set @.KEY = @.ORDER_11 + @.PRTNUM_11 + CONVERT(nchar(8), @.CURDUE_11, 112) +
>@.TYPE_11
> SELECT TOP 1
> UNQKEY_11,
> Convert(Money, TIMESTAMP_11),
> ORDER_11,
> PRTNUM_11,
> CURDUE_11,
> TYPE_11
> FROM
> [dbo].[Requirement Detail]
> WHERE
> ((ORDER_11>=@.ORDER_11)) -- the major part of the key
> AND
> ORDER_11 + PRTNUM_11 + CONVERT(nchar(8), CURDUE_11, 112) + TYPE_11
>
> ORDER BY
> ORDER_11,
> PRTNUM_11,
> CURDUE_11,
> TYPE_11
>GO
>As far as I can see, provided there aren't thousands of rows where
>ORDER_11>=@.ORDER_11 but the rest of the condition is not satisfied, this is
>pretty optimal. But I'm still going to fiddle to see if I can improve the
>original query to cut down on the amount of rewriting I have to do on the
>stored procedures (there are hundreds of them!).
>[Aside] I used style 113 and length 8 for the date query because I happen
>to know the time part of the date is not significant in the real data.
>
>

Operation on View

Hi All,
It is possilble in some way to make operation in a view?
Example:
SELECT fieldA > fieldB AS TestCompare FROM myTable
ThanksFor example:
SELECT
CASE WHEN a > b THEN 'A' ELSE 'B' END AS test_compare
FROM myTable ;
David Portas
SQL Server MVP
--|||What would you want the comparsion fieldA > fieldB to return? There's no Boo
lean datatype in SQL
Server. How about returning a string, something like:
SELECT
CASE WHEN fieldA > fieldB THEN 'ColA_GT'
ELSE 'ColANotGT'
END AS TestCompare
FROM myTable
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bonato Pierantonio" <pbonato@.interfree.it> wrote in message
news:Ob1Opt0wFHA.3788@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> It is possilble in some way to make operation in a view?
> Example:
> SELECT fieldA > fieldB AS TestCompare FROM myTable
> Thanks
>

OpenXml sub elements

Hi All,
I,m shredding this xml to view the data and ultimate goal is to insert
the data at a later stage but when I shred it with the syntax
mentioned below then I recieve only one record instead of 3 records
with different AnsOptions (last sub element).
Help!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo />
<DVName>Home</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1.?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Answer>No</Answer>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
<Ansoption>
<Options>Time Waiting</Options>
</Ansoption>
<Ansoption>
<Options>Rude/Abrupt</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
--split --
SELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer', 2)
WITH
(DVName varchar (20) '../../../DVName',
DvcodeNo int '../../../DvcodeNo',
CustSurveyNo int '../../CustSurveyNo',
QuestionID int '../QuestionID',
QuestionDesc varchar(50) '../QuestionDesc',
AnswerID int,
Ansoption varchar (30))
EXEC sp_xml_removedocument @.docHandle
Results
Home 1 4 34 Q1.? 13 Unhelpful
Any Ideas ?, I need to finish this procedure as quick as possible,
Please adviseSELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30) 'Options')|||Thanks all sorted!
markc...@.hotmail.com wrote:
> SELECT *
> FROM
> -- Add OPENXML statement for ResponseDetail table
> OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
> Ansoption', 2)
> WITH
> (DVName varchar (20) '../../../../DVName',
> DvcodeNo int '../../../../DvcodeNo',
> CustSurveyNo int '../../../CustSurveyNo',
> QuestionID int '../../QuestionID',
> QuestionDesc varchar(50) '../../QuestionDesc',
> AnswerID int '../AnswerID',
> Ansoption varchar (30) 'Options')|||It Doesn't work again if I add more data to the XML :
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1. Was your call answered prompt and
courteously?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
-- Add OPENXML statement for SalesOrderDetail table INSERT
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandle

OpenXml sub elements

Hi All,
I,m shredding this xml to view the data and ultimate goal is to insert
the data at a later stage but when I shred it with the syntax
mentioned below then I recieve only one record instead of 3 records
with different AnsOptions (last sub element).
Help!
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo />
<DVName>Home</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1.?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Answer>No</Answer>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
<Ansoption>
<Options>Time Waiting</Options>
</Ansoption>
<Ansoption>
<Options>Rude/Abrupt</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
--split --
SELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer', 2)
WITH
(DVName varchar (20) '../../../DVName',
DvcodeNo int '../../../DvcodeNo',
CustSurveyNo int '../../CustSurveyNo',
QuestionID int '../QuestionID',
QuestionDesc varchar(50) '../QuestionDesc',
AnswerID int,
Ansoption varchar (30))
EXEC sp_xml_removedocument @.docHandle
Results
Home1434Q1.?13Unhelpful
Any Ideas ?, I need to finish this procedure as quick as possible,
Please advise
SELECT *
FROM
-- Add OPENXML statement for ResponseDetail table
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30) 'Options')
|||Thanks all sorted!
markc...@.hotmail.com wrote:
> SELECT *
> FROM
> -- Add OPENXML statement for ResponseDetail table
> OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
> Ansoption', 2)
> WITH
> (DVName varchar (20) '../../../../DVName',
> DvcodeNo int '../../../../DvcodeNo',
> CustSurveyNo int '../../../CustSurveyNo',
> QuestionID int '../../QuestionID',
> QuestionDesc varchar(50) '../../QuestionDesc',
> AnswerID int '../AnswerID',
> Ansoption varchar (30) 'Options')
|||It Doesn't work again if I add more data to the XML :
DECLARE @.doc xml
SET @.doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<QuestionDesc>Q1. Was your call answered prompt and
courteously?</QuestionDesc>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @.docHandle int
-- Call stored procedure to create the memory tree
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.doc
SELECT *
FROM
-- Add OPENXML statement for SalesOrderDetail table INSERT
OPENXML(@.docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
QuestionDesc varchar(50) '../../QuestionDesc',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @.docHandle