query progress indicator

having sql2000 on win2000
when running long lasting queries [30 minutes f.e.], and running them async,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.
On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...
|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...
|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx
|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>
|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:

> You can use RAISERROR...WITH NOWAIT to send informational progress messages.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
>

query progress indicator

having sql2000 on win2000
when running long lasting queries [30 minutes f.e.], and running them async,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>> You can use RAISERROR...WITH NOWAIT to send informational progress
>> messages. RAISERROR...WITH NOWAIT will flush the output buffer
>> immediately, where SELECT or PRINT will wailt until the out buffer is
>> full.
>> RAISERROR('Start message', 0, 1) WITH NOWAIT
>> WAITFOR DELAY '00:00:02'
>> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
>> WAITFOR DELAY '00:00:02'
>> RAISERROR('End message', 0, 1) WITH NOWAIT
>> --
>> Hope this helps.
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:
> > also, when running sp, supposing sp is composed of complex [many]
> > subqueries, is there some clever method sp can indicate to calling process
> > which part of code is currently being executed [some kind of semaphores
> > between subqueries f.e.]
> You can use RAISERROR...WITH NOWAIT to send informational progress messages.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> > having sql2000 on win2000
> > when running long lasting queries [30 minutes f.e.], and running them
> > async, is there some method to periodicaly check the percentage of
> > completness?
> >
> > i know there is a status indicating when the query is done, but is it
> > possible to know intermediate status?
> > since execution plan has a good knowledge about what have to be done, i
> > think it is not technicaly impossible mission to have some estimation of
> > remaining time to run
> >
> > also, when running sp, supposing sp is composed of complex [many]
> > subqueries, is there some clever method sp can indicate to calling process
> > which part of code is currently being executed [some kind of semaphores
> > between subqueries f.e.]
> >
> > any comments?
> >
> > thnx.
> >
>

query progress indicator

having sql2000 on win2000
when running long lasting queries [30 minutes f.e.], and running them as
ync,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:

> You can use RAISERROR...WITH NOWAIT to send informational progress message
s.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
>

Query Problems - Server Configuration?

Hi All,
I was running the following Query
SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id, c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id = s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path = f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format = 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US' or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) ))) ORDER BY s.i_chronicle_id,s.r_object_id
And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000, CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900 bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.
Can Any one Help to fix this SQL Enterprise Manager?You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> Hi All,
> I was running the following Query
> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id =s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path =f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format ='zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id
> And the Error: "A database error has occurred during the creation of a cursor (' STATE=01000,
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination of large
values, the insert/update operation will fail.
> Can Any one Help to fix this SQL Enterprise Manager?
>|||Perhaps the index indeed is larger that 900 bytes for some rows. This is not configurable. Work with
the vendor of the app and see what can be done to handle this. One possible thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that the intermediate steps
of the query processing creates some work table for which the row length exceeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...
> Hi,
> The Query is generated by Documentum. As you know Documentum is a middleware that generates a
DQL Query (SQL like Query) on the underlying database. As per the error description, it looks like,
I have some Server configuration problem like
> 1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
> 2) Maximum key length = 900 bytes
> what do these signify? How to increase these from EM?
> -- Tibor Karaszi wrote: --
> You could try adding the ROBUST PLAN hint to your query. Read more about it in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Raj" <anonymous@.discussions.microsoft.com> wrote in message
> news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> > Hi All,
> >> I was running the following Query
> >> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,
> c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_folder_path
FROM
> dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_sysobject_r sr2 WHERE
> (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/contact/%')
AND
> sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id => s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Active' AND
> (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT
> r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND
> w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_folder_path => f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND
> w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_format => 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and (
language_code='en_US'
> or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id from
dm_relation r
> where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF' and r.child_id in
> (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where
sys1.language_code
> in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label ='Active' ) )))
> ORDER BY s.i_chronicle_id,s.r_object_id
> >> And the Error: "A database error has occurred during the creation of a cursor ('
STATE=01000,
> CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length
is 900
> bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combination
of large
> values, the insert/update operation will fail.
> >> Can Any one Help to fix this SQL Enterprise Manager?
> >

Query Problems - Server Configuration?

Hi All,
I was running the following Query
SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as cont
ent_id, c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_versio
n_label, f.r_folder_path FROM dm_sysobject_s s, dmr_content_r c, dm_sysobje
ct_r sr, dm_folder_r f
, dm_sysobject_r sr2 WHERE (f.r_folder_path = '/accelera.com/contact' OR f.r
_folder_path like '/accelera.com/contact/%') AND sr2.r_object_id = s.r_objec
t_id AND sr2.i_folder_id = f.r_object_id AND sr.r_object_id = s.r_object_id
AND c.parent_id = s.r_objec
t_id AND sr.r_version_label = 'Active' AND (s.r_modify_date >= DATE('2004-0
2-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT EXISTS (SELECT r_object_id FROM
dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_chronicle_id AND w
.r_object_id = s.r_object_i
d AND w.object_name = s.object_name AND w.r_folder_path = f.r_folder_path AN
D (w.i_full_format = c.i_full_format OR (c.i_full_format = 'html' AND w.i_fu
ll_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full_forma
t = 'zip_pub_html' ) ) ))
and s.a_is_hidden = false and s.a_archive = false and ( language_code='en_US
' or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pa
rent_id from dm_relation r where r.parent_id = s.r_object_id and r.relation_
name = 'DM_TRANSLATION_OF'
and r.child_id in (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_s
ysobject_r sys2 where sys1.language_code in ('en_US',' ') and sys1.r_object_
id = sys2.r_object_id and sys2.r_version_label = 'Active' ) ))) ORDER BY s.i
_chronicle_id,s.r_object_id
And the Error: "A database error has occurred during the creation of a cursor (' STATE
=01000, CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maxi
mum key length is 900 bytes. The index 'RowsetWorkTableSS' has maximum lengt
h
of 1020 bytes. For some combination of large values, the insert/update opera
tion will fail.
Can Any one Help to fix this SQL Enterprise Manager?You could try adding the ROBUST PLAN hint to your query. Read more about it
in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...

quote:

---

> Hi All,
> I was running the following Query
> SELECT s.r_object_id, s.object_name, s.i_chronicle_id, c.r_object_id as content_id,[/
color]

---

c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label,
f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_syso
bject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/acceler
a.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r
_object_id =
s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Act
ive' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT
EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_ch
ronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_fold
er_path =
f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format =
'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full
_format =
'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and
( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pare
nt_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF'
and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 wh
ere sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_
label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id

quote:

---

> And the Error: "A database error has occurred during the creation of a cursor (' STAT
E=01000,

---

CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key
length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For s
ome combination of large
values, the insert/update operation will fail.

quote:

---

> Can Any one Help to fix this SQL Enterprise Manager?
>

---

|||Hi,
The Query is generated by Documentum. As you know Documentum is a middleware
that generates a DQL Query (SQL like Query) on the underlying database. As
per the error description, it looks like, I have some Server configuration p
roblem like
1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
2) Maximum key length = 900 bytes
what do these signify? How to increase these from EM?
-- Tibor Karaszi wrote: --
You could try adding the ROBUST PLAN hint to your query. Read more about it
in Books Online.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...

quote:

---

> Hi All,
c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label,
f.r_folder_path FROM
dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f, dm_syso
bject_r sr2 WHERE
(f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/acceler
a.com/contact/%') AND
sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id AND sr.r
_object_id =
s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label = 'Act
ive' AND
(s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss') OR NOT
EXISTS (SELECT
r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id = s.i_ch
ronicle_id AND
w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w.r_fold
er_path =
f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_format =
'html' AND
w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w.i_full
_format =
'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and
( language_code='en_US'
or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.pare
nt_id from dm_relation r
where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATION_OF'
and r.child_id in
(select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 wh
ere sys1.language_code
in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_
label = 'Active' ) )))
ORDER BY s.i_chronicle_id,s.r_object_id[QUOTE]
CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key
length is 900
bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For s
ome combination of large
values, the insert/update operation will fail.[QUOTE]
>

---

|||Perhaps the index indeed is larger that 900 bytes for some rows. This is not
configurable. Work with
the vendor of the app and see what can be done to handle this. One possible
thing is, of course to
remove the index...
I still suggest you read about what the ROBUST PLAN does. It is likely that
the intermediate steps
of the query processing creates some work table for which the row length exc
eeds the max for SQL
Server.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Raj" <anonymous@.discussions.microsoft.com> wrote in message
news:C6E27085-F620-4B28-8F43-0AD14BD9714B@.microsoft.com...

quote:

---

> Hi,
> The Query is generated by Documentum. As you know Documentum is a middleware that gener
ates a

---

DQL Query (SQL like Query) on the underlying database. As per the error desc
ription, it looks like,
I have some Server configuration problem like

quote:

---

> 1) Maximum length of the index RowSetWorkTableSS = 1020 bytes
> 2) Maximum key length = 900 bytes
> what do these signify? How to increase these from EM?
> -- Tibor Karaszi wrote: --
> You could try adding the ROBUST PLAN hint to your query. Read more ab
out it in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=...>
ic.sqlserver
>
> "Raj" <anonymous@.discussions.microsoft.com> wrote in message
> news:65B68D28-7D07-422A-9938-5E4C5E504FA3@.microsoft.com...
> c.i_full_format, c.page, c.i_rendition, c.page_modifier, sr.r_version_label, f.r_fold
er_path

---

FROM

quote:

---

> dm_sysobject_s s, dmr_content_r c, dm_sysobject_r sr, dm_folder_r f,
dm_sysobject_r sr2 WHERE
> (f.r_folder_path = '/accelera.com/contact' OR f.r_folder_path like '/accelera.com/con
tact/%')

---

AND

quote:

---

> sr2.r_object_id = s.r_object_id AND sr2.i_folder_id = f.r_object_id A
ND sr.r_object_id =
> s.r_object_id AND c.parent_id = s.r_object_id AND sr.r_version_label
= 'Active' AND
> (s.r_modify_date >= DATE('2004-02-02 11.57.34','yyyy-mm-dd hh.mi.ss')
OR NOT EXISTS (SELECT
> r_object_id FROM dm_dbo.dm_webc_80000555_s w WHERE w.i_chronicle_id =
s.i_chronicle_id AND
> w.r_object_id = s.r_object_id AND w.object_name = s.object_name AND w
.r_folder_path =
> f.r_folder_path AND (w.i_full_format = c.i_full_format OR (c.i_full_f
ormat = 'html' AND
> w.i_full_format = 'pub_html' ) OR (c.i_full_format = 'zip_html' AND w
.i_full_format =
> 'zip_pub_html' ) ) )) and s.a_is_hidden = false and s.a_archive = false and ([/color
]

---

language_code='en_US'

quote:

---

[color=darkr
ed]
> or ( language_code in ('en_US',' ') and s.r_object_id not in ( select r.parent_id fro
m

---

dm_relation r

quote:

---

> where r.parent_id = s.r_object_id and r.relation_name = 'DM_TRANSLATI
ON_OF' and r.child_id in
> (select sys1.i_chronicle_id from dm_sysobject_s sys1, dm_sysobject_r sys2 where[/colo
r]

---

sys1.language_code

quote:

---

> in ('en_US',' ') and sys1.r_object_id = sys2.r_object_id and sys2.r_version_label =[/
color]

---

'Active' ) )))

quote:

---

> ORDER BY s.i_chronicle_id,s.r_object_id
STATE=01000,[QUOTE]
> CODE=1945, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Warning! The maximum key length[/c
olor]

---

is 900

quote:

---

> bytes. The index 'RowsetWorkTableSS' has maximum length of 1020 bytes. For some combi
nation

---

of large[QUOTE]
> values, the insert/update operation will fail.

Query problems

I am running a the following query:

SELECT [LICENSE_STATE], [LICENSE_PLATE], [TICKET_NUMBER], [DATE_ISSUED], [TIME_ISSUED], [MAKE_TYPE]
FROM dmv;

When I run the query I get multiple violations on an individual license plate. I only want it to display the license plate once even if it has multiple violations. I thought I could use a distinct statement to filter out the multiple plate entries but it either filters the plates and does not show any of the other information or it doesn't filter the results at all.

Here is the distict satement I am using that filters out everything except for the plates.

SELECT DISTINCT dmv.LICENSE_PLATE
FROM
(
SELECT [LICENSE_STATE], [LICENSE_PLATE], [TICKET_NUMBER], [DATE_ISSUED], [TIME_ISSUED], [MAKE_TYPE]
FROM dmv
);

Any help would be appreciated.Never Mind I figured it out

Query Plans differs

Why shoudl the query plan derived by an SP and one on Query Analyser would
differ widely?
I have a system that has been running stably for a while now when one of the
queries started behaving badly. The SP version of the query comes out with
an estimated cost of 0.311 whereas if I take the same TSQL and paste it into
Query Analyser it estimates as 0.153.
Unfortuately, the query plan cost isn't the whole story as when estimated
the SP performs about 5m page reads whereas the explicit code only takes 800
page reads - this makes a big difference in execution time :-)
Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
doesn't effect the QP choosen.
Regards
PaulPaul,
The two pieces of code, TSQL script and stored procedure, are not optimized
the same because of the knowledge available at the time of optimization.
For example, the TSQL script has the variables set with the values that you
want to run.
Here is one thing that causes stored procedures to be less than optimal:
If internally to a stored proc you set a variable, the optimizer (which has
no value for the variable when it optimizes) may have selected a suboptimal
plan. So, if this is your case, rather than:
DECLARE @.MyVal INT
SET @.MyVal = 123
SELECT ... WHERE MyCol = @.MyVal
You could do:
DECLARE @.MyVal INT
SET @.MyVal = 123
EXEC AnotherStoredProcedure @.MyVal
The called stored procedure will optimize with the value supplied in @.MyVal.
Remember, of course, that recompiles of stored procedures are common, so
this is not as simple as black and white.
And, of course, there are other factors that could mislead the optimizer.
RLF
"Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
> Why shoudl the query plan derived by an SP and one on Query Analyser would
> differ widely?
> I have a system that has been running stably for a while now when one of
> the
> queries started behaving badly. The SP version of the query comes out with
> an estimated cost of 0.311 whereas if I take the same TSQL and paste it
> into
> Query Analyser it estimates as 0.153.
> Unfortuately, the query plan cost isn't the whole story as when estimated
> the SP performs about 5m page reads whereas the explicit code only takes
> 800
> page reads - this makes a big difference in execution time :-)
> Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
> doesn't effect the QP choosen.
> Regards
> Paul
>|||Thanks, there was one such variable, so I changed the code as per your
suggestion - reduced it from 5m reads to around 4.5m :-)
The optimiser normally does such a good job that it's just a pain when it
gets it wrong. Given the nature of this query, it's almost impossible to put
any useful hints that wouldn't cause much more pain elsewhere in the system.
I'll have to see if I can program around it with a different query just to
get it back running - previously it took around 100ms now it takes 10s+
Paul
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Paul,
> The two pieces of code, TSQL script and stored procedure, are not
optimized
> the same because of the knowledge available at the time of optimization.
> For example, the TSQL script has the variables set with the values that
you
> want to run.
> Here is one thing that causes stored procedures to be less than optimal:
> If internally to a stored proc you set a variable, the optimizer (which
has
> no value for the variable when it optimizes) may have selected a
suboptimal
> plan. So, if this is your case, rather than:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> SELECT ... WHERE MyCol = @.MyVal
> You could do:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> EXEC AnotherStoredProcedure @.MyVal
> The called stored procedure will optimize with the value supplied in
@.MyVal.
> Remember, of course, that recompiles of stored procedures are common, so
> this is not as simple as black and white.
> And, of course, there are other factors that could mislead the optimizer.
> RLF
> "Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
> news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
would
with
estimated
it
>|||Paul,
Maybe if you post the query in question (accompanied with the relevant
DDL) someone might be able to give a more specific advice...
Gert-Jan
Paul Hatcher wrote:
> Thanks, there was one such variable, so I changed the code as per your
> suggestion - reduced it from 5m reads to around 4.5m :-)
> The optimiser normally does such a good job that it's just a pain when it
> gets it wrong. Given the nature of this query, it's almost impossible to p
ut
> any useful hints that wouldn't cause much more pain elsewhere in the syste
m.
> I'll have to see if I can program around it with a different query just to
> get it back running - previously it took around 100ms now it takes 10s+
> Paul
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> optimized
> you
> has
> suboptimal
> @.MyVal.
> would
> with
> estimated
> it|||Gert-Jan
Thanks for the offer, but it's too complicated - as an example I would guess
that the query pans out to about a 30 table join, mostly self-joins as it's
a triple-based schema, but still fairly hairy.
I've now managed to solve it by a different structured query.
Paul
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422766C1.A0FE26A1@.toomuchspamalready.nl...
> Paul,
> Maybe if you post the query in question (accompanied with the relevant
> DDL) someone might be able to give a more specific advice...
> Gert-Jan
>
> Paul Hatcher wrote:
it
put
system.
to
optimization.
that
optimal:
(which
so
optimizer.
one of
out
it
takes
butsql

Query Plan Question

I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
I have the following query:
declare @.FromDate as DATETIME
SET @.FromDate = '2004-03-10'
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= @.FromDate
Select Count(*)
From URLS
Where (URLString like '%homepage%')
And AddedOn >= '2004-03-10'
The first query which uses @.FromDate does a table scan. The second query th
at has the date hard coded uses the index on the AddedOn date field.
My question is why doesn't the first query also use the index? There are 10
Million + rows in the table.
Thanks,
StephenIf you use a variable in a WHERE clause, then the optimizer doesn't know
what value you are looking for (the optimizer optimizes statement by
statement). So, it will have to guess number of rows to be returned. I don't
recall the values it guesses (you find them in the Inside SQL Server book),
but I think that it is either 10% or 25% for greater then. Say you have 10
million rows, this means that SQL Server will read 1 million rows. Say you
have an NC index, then SQL Server will potentially need to jump to a data
page for each row. This means 1 million data page accesses.
Above is just to give you an understand about how the optimizer works. Note
that using a variable and a stored procedure parameter are two different
things!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:4A78A446-AC10-4F41-9EED-A048B3B5864D@.microsoft.com...
> I'm running SQL Server 2000 Std SP3 on Windows 2000 Standard SP4.
> I have the following query:
> declare @.FromDate as DATETIME
> SET @.FromDate = '2004-03-10'
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= @.FromDate
>
> Select Count(*)
> From URLS
> Where (URLString like '%homepage%')
> And AddedOn >= '2004-03-10'
> The first query which uses @.FromDate does a table scan. The second query
that has the date hard coded uses the index on the AddedOn date field.
> My question is why doesn't the first query also use the index? There are
10 Million + rows in the table.
> Thanks,
> Stephen|||It guesses?
So if I have 1 record of 10 million where the AddedOn date is equal to '2004
-03-10' and I pass this value as a variable it will do a table scan? That s
eems to me to be the wrong thing to do. I've updated the statistics on the
AddedOn date field using th
e FULLSCAN option and it still does a table scan which I find very disturbin
g. I'm now wondering how many other queries that use variables as a paramet
er are choosing the wrong query plan due to this.
Thanks,
Stephen|||Stephen Schissler wrote:

> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to '2004-03-10
' and I pass this value as a variable it will do a table scan? That seems to me to
be the wrong thing to do. I've updated the statistics on the AddedOn date field usi
ng
the FULLSCAN option and it still does a table scan which I find very disturbing. I'm now w
ondering how many other queries that use variables as a parameter are choosing the wrong qu
ery plan due to this.
> Thanks,
> Stephen
>
If you run it as a stored procedure and use the where clause as a
parameter you will notice different results.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/|||> It guesses?
What else can it do? Well, not a wild guess, it has its rules. The optimizer
does not know the value of the variable, as it optimizes statement by
statement. (Yes, one could question why that it, but it is the way SQL
Server work.) As I said, for different predicates, SQL Server estimates to
return different percentage of rows. Details in Inside SQL Server.
Statistics has nothing to do with this.
If you use a constant or a stored procedure parameter, it s a different
thing, though!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stephen Schissler" <anonymous@.discussions.microsoft.com> wrote in message
news:23380C0C-0820-4155-BA43-D574741DC19C@.microsoft.com...
> It guesses?
> So if I have 1 record of 10 million where the AddedOn date is equal to
'2004-03-10' and I pass this value as a variable it will do a table scan?
That seems to me to be the wrong thing to do. I've updated the statistics
on the AddedOn date field using the FULLSCAN option and it still does a
table scan which I find very disturbing. I'm now wondering how many other
queries that use variables as a parameter are choosing the wrong query plan
due to this.
> Thanks,
> Stephen

Query Performance from Reporting Services

Hi,
Has anyone had any problems with running queries from Reporting Services
that perform 300x slower than from other sources? Any Ideas of what may
cause this?
I have a query that uses an Indexed View and runs in 8 seconds if I run it
from QA, but RS takes 30 minutes. All indications from running Profiler
shows that RS is indeed using the indexed view.
Any thoughts of what to check would be appreciated.
ThanksCan you use a stored procedure? There have been instances where the query
used in Reporting Services did not use the same query plan as would be used
if the query was within query analyzer. One query plan used a particular
index and the other did not. Hence the differences in performance. If you
use a stored procedure you would be guaranteed that regardless of where
invoked (query analyzer or RS) they would use the same query plan.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> Hi,
> Has anyone had any problems with running queries from Reporting Services
> that perform 300x slower than from other sources? Any Ideas of what may
> cause this?
> I have a query that uses an Indexed View and runs in 8 seconds if I run it
> from QA, but RS takes 30 minutes. All indications from running Profiler
> shows that RS is indeed using the indexed view.
> Any thoughts of what to check would be appreciated.
> Thanks
>|||Yes, I tried using a stored Proc and that didnt improve things.
Interestingly with a stored proc it seems to not even bother caching the
results, so that if you repreview it takes another 30 minutes to see the 8sec
query. I did notice it was passing the stored proc to another stored proc
(sp_procedure_params_rowset) to get the rowset values, but I didn't see any
parameters passed. I'm not sure if this could be causing the huge delay or
not.
But then again, even when I was using a text query straight from RS, I gave
it a direct index hint and if I previewed the data in the Data tab it ran in
8 secs, but if I previewed the report, it took 30 minutes.
Thanks
"Bruce L-C [MVP]" wrote:
> Can you use a stored procedure? There have been instances where the query
> used in Reporting Services did not use the same query plan as would be used
> if the query was within query analyzer. One query plan used a particular
> index and the other did not. Hence the differences in performance. If you
> use a stored procedure you would be guaranteed that regardless of where
> invoked (query analyzer or RS) they would use the same query plan.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> > Hi,
> >
> > Has anyone had any problems with running queries from Reporting Services
> > that perform 300x slower than from other sources? Any Ideas of what may
> > cause this?
> >
> > I have a query that uses an Indexed View and runs in 8 seconds if I run it
> > from QA, but RS takes 30 minutes. All indications from running Profiler
> > shows that RS is indeed using the indexed view.
> >
> > Any thoughts of what to check would be appreciated.
> >
> > Thanks
> >
>
>|||How many rows are being returned? Are you using any filters?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
> Yes, I tried using a stored Proc and that didnt improve things.
> Interestingly with a stored proc it seems to not even bother caching the
> results, so that if you repreview it takes another 30 minutes to see the
> 8sec
> query. I did notice it was passing the stored proc to another stored proc
> (sp_procedure_params_rowset) to get the rowset values, but I didn't see
> any
> parameters passed. I'm not sure if this could be causing the huge delay
> or
> not.
> But then again, even when I was using a text query straight from RS, I
> gave
> it a direct index hint and if I previewed the data in the Data tab it ran
> in
> 8 secs, but if I previewed the report, it took 30 minutes.
> Thanks
>
> "Bruce L-C [MVP]" wrote:
>> Can you use a stored procedure? There have been instances where the query
>> used in Reporting Services did not use the same query plan as would be
>> used
>> if the query was within query analyzer. One query plan used a particular
>> index and the other did not. Hence the differences in performance. If you
>> use a stored procedure you would be guaranteed that regardless of where
>> invoked (query analyzer or RS) they would use the same query plan.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
>> > Hi,
>> >
>> > Has anyone had any problems with running queries from Reporting
>> > Services
>> > that perform 300x slower than from other sources? Any Ideas of what
>> > may
>> > cause this?
>> >
>> > I have a query that uses an Indexed View and runs in 8 seconds if I run
>> > it
>> > from QA, but RS takes 30 minutes. All indications from running
>> > Profiler
>> > shows that RS is indeed using the indexed view.
>> >
>> > Any thoughts of what to check would be appreciated.
>> >
>> > Thanks
>> >
>>|||The row count is 18, but it is summarizing about 100 million records using an
indexed view. I am using report parameters to pass to the query to filter
the results from within the Query statement. Basically a date range
selection, along with a history type. I did try hard coding some values
within the query statement to see if it was faster, but the results were the
same.
"Bruce L-C [MVP]" wrote:
> How many rows are being returned? Are you using any filters?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
> > Yes, I tried using a stored Proc and that didnt improve things.
> > Interestingly with a stored proc it seems to not even bother caching the
> > results, so that if you repreview it takes another 30 minutes to see the
> > 8sec
> > query. I did notice it was passing the stored proc to another stored proc
> > (sp_procedure_params_rowset) to get the rowset values, but I didn't see
> > any
> > parameters passed. I'm not sure if this could be causing the huge delay
> > or
> > not.
> >
> > But then again, even when I was using a text query straight from RS, I
> > gave
> > it a direct index hint and if I previewed the data in the Data tab it ran
> > in
> > 8 secs, but if I previewed the report, it took 30 minutes.
> >
> > Thanks
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Can you use a stored procedure? There have been instances where the query
> >> used in Reporting Services did not use the same query plan as would be
> >> used
> >> if the query was within query analyzer. One query plan used a particular
> >> index and the other did not. Hence the differences in performance. If you
> >> use a stored procedure you would be guaranteed that regardless of where
> >> invoked (query analyzer or RS) they would use the same query plan.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Jace" <Jace@.discussions.microsoft.com> wrote in message
> >> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
> >> > Hi,
> >> >
> >> > Has anyone had any problems with running queries from Reporting
> >> > Services
> >> > that perform 300x slower than from other sources? Any Ideas of what
> >> > may
> >> > cause this?
> >> >
> >> > I have a query that uses an Indexed View and runs in 8 seconds if I run
> >> > it
> >> > from QA, but RS takes 30 minutes. All indications from running
> >> > Profiler
> >> > shows that RS is indeed using the indexed view.
> >> >
> >> > Any thoughts of what to check would be appreciated.
> >> >
> >> > Thanks
> >> >
> >>
> >>
> >>
>
>|||Very odd. How many fields are being returned? Usually with RS being slow it
is because the resultset is large. This is not the case for you. In this
case I would expect the performance to be the same as with the data tab ( or
at most a second or two longer).
This is a long shot but just in case you are seeing an issue with the
development environment, try deploying it and seeing how long it takes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jace" <Jace@.discussions.microsoft.com> wrote in message
news:D8B81128-ABF4-4C50-A639-E6C86CD16BBD@.microsoft.com...
> The row count is 18, but it is summarizing about 100 million records using
> an
> indexed view. I am using report parameters to pass to the query to filter
> the results from within the Query statement. Basically a date range
> selection, along with a history type. I did try hard coding some values
> within the query statement to see if it was faster, but the results were
> the
> same.
>
> "Bruce L-C [MVP]" wrote:
>> How many rows are being returned? Are you using any filters?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> news:ED4E90BD-7079-4E2A-8DB2-A783AE0BCC47@.microsoft.com...
>> > Yes, I tried using a stored Proc and that didnt improve things.
>> > Interestingly with a stored proc it seems to not even bother caching
>> > the
>> > results, so that if you repreview it takes another 30 minutes to see
>> > the
>> > 8sec
>> > query. I did notice it was passing the stored proc to another stored
>> > proc
>> > (sp_procedure_params_rowset) to get the rowset values, but I didn't see
>> > any
>> > parameters passed. I'm not sure if this could be causing the huge
>> > delay
>> > or
>> > not.
>> >
>> > But then again, even when I was using a text query straight from RS, I
>> > gave
>> > it a direct index hint and if I previewed the data in the Data tab it
>> > ran
>> > in
>> > 8 secs, but if I previewed the report, it took 30 minutes.
>> >
>> > Thanks
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Can you use a stored procedure? There have been instances where the
>> >> query
>> >> used in Reporting Services did not use the same query plan as would be
>> >> used
>> >> if the query was within query analyzer. One query plan used a
>> >> particular
>> >> index and the other did not. Hence the differences in performance. If
>> >> you
>> >> use a stored procedure you would be guaranteed that regardless of
>> >> where
>> >> invoked (query analyzer or RS) they would use the same query plan.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >> "Jace" <Jace@.discussions.microsoft.com> wrote in message
>> >> news:90C41F38-87EC-4B16-9672-A75079887BE7@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > Has anyone had any problems with running queries from Reporting
>> >> > Services
>> >> > that perform 300x slower than from other sources? Any Ideas of what
>> >> > may
>> >> > cause this?
>> >> >
>> >> > I have a query that uses an Indexed View and runs in 8 seconds if I
>> >> > run
>> >> > it
>> >> > from QA, but RS takes 30 minutes. All indications from running
>> >> > Profiler
>> >> > shows that RS is indeed using the indexed view.
>> >> >
>> >> > Any thoughts of what to check would be appreciated.
>> >> >
>> >> > Thanks
>> >> >
>> >>
>> >>
>> >>
>>

Query performance difference between Sql Server 2005 and 2000

Hi,

I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.

The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).

I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.

Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.

Cheers,

Chris

Chris,

You may find Umachandar's response in this thread informative.

|||

Hi Arnie,

Thanks for your reply. I'd already tried most of the things Umachandar suggests, and the performance for each query remains fairly consistent.

Thanks anyway, though.

Chris

|||

Chris,

Have you tried to compare the SQL Server 2000 and the SQL Server 2005 execution plans?

Given that the performance difference is so dramatic (1s --> 2-3min) I would expect to see some differences in the use of indices or join types. If you know what those are,you could influence the plan choice using a hint or we could look into why plan choice is different in SQL 2005. Please make sure that you are using the same database and same set of parameter values when you do this experiment.

Regards,

Leo

|||

Hi Leo,

I have not yet compared plans on the two servers. My main interest was on why the results varied so dramatically when just using sql server 2005 and adding a more stringent filter in the where clause, so I focussed on the difference between these plans (and to be honest they actually made very little sense to me!). I will try to rerun the queries and summarise the difference in plans, and if you could cast an eye over it I'd be very grateful.

Cheers,

Chris

Query performance damaged due to an OR operator

Hi,
I am running the following query:
declare
@.SiteLogId int
set @.SiteLogId = 2833
SELECT
PCT_RPT_Mashines_V.MashineRemarks,
PCT_RPT_Mashines_V_1.MashineSDPDescription
PCT_RPT_Mashines_V_1.MashineRemarks
PCT_RPT_Mashines_V.PerId,
PCT_RPT_Mashines_V.PerOrderNumber
FROM
PCT_RPT_Mashines_V LEFT OUTER JOIN
PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
PCT_RPT_Mashines_V.MashinePrevMashineId =
PCT_RPT_Mashines_V_1.MashineId
WHERE
PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId OR @.SiteLogId = -1
This query takes about 50 seconds to run. If I remove the OR part from the
WHERE clause, the running time is reduced to 5 seconds, although there
shouldn't be any significance to the OR, since @.SiteLogId was assigned a
value (2833).
Do you have any explanation for this ?
How can I use the OR part, and still keep a good performance ?
And another thing:
If I remove these two lines:
PCT_RPT_Mashines_V_1.MashineSDPDescription
PCT_RPT_Mashines_V_1.MashineRemarks
From the SELECT clause, the query takes 50 seconds to run, with or without
the OR. Why is that happening ?
Thanks,
AvishayAvishay,shalom
> If I remove these two lines:
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
Take a look at an execution plan . Probably , you don't have indexes on
these columns and SQL Server is not be able to use
"covering indexes" to create a more efficient plan.
Can you try
IF @.SiteLogId = -1
--Here is your SELECT
What happened ?
"Avishay Ben-Zvi" <avishay@.community.nospam> wrote in message
news:706D354E-8C96-465C-A951-DEAE0B5DE433@.microsoft.com...
> Hi,
> I am running the following query:
> declare
> @.SiteLogId int
> set @.SiteLogId = 2833
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId OR @.SiteLogId = -1
>
> This query takes about 50 seconds to run. If I remove the OR part from the
> WHERE clause, the running time is reduced to 5 seconds, although there
> shouldn't be any significance to the OR, since @.SiteLogId was assigned a
> value (2833).
> Do you have any explanation for this ?
> How can I use the OR part, and still keep a good performance ?
> And another thing:
> If I remove these two lines:
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> From the SELECT clause, the query takes 50 seconds to run, with or without
> the OR. Why is that happening ?
> Thanks,
> Avishay
>|||examnotes (avishay@.community.nospam) writes:
> declare
> @.SiteLogId int
> set @.SiteLogId = 2833
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId OR @.SiteLogId
= -1
>
> This query takes about 50 seconds to run. If I remove the OR part from the
> WHERE clause, the running time is reduced to 5 seconds, although there
> shouldn't be any significance to the OR, since @.SiteLogId was assigned a
> value (2833).
I matters a whole lot, because SQL Server builds the query plan for the
entire batch, and it has no idea of what the actual run-time value
@.SiteLogId will have. Thus, it must assume the worst.
If you want the index to be used if you have a value for @.SiteLogId,
the you need to write two queries, one with and one without the condition.
This changes in SQL 2005. Here you can add the hint OPTION (RECOMPILE)
which forces a statement recompile, and this enables SQL Server to look
at the actual parameter value.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Avishay,
SELECT
PCT_RPT_Mashines_V.MashineRemarks,
PCT_RPT_Mashines_V_1.MashineSDPDescription
PCT_RPT_Mashines_V_1.MashineRemarks
PCT_RPT_Mashines_V.PerId,
PCT_RPT_Mashines_V.PerOrderNumber
FROM
PCT_RPT_Mashines_V LEFT OUTER JOIN
PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
PCT_RPT_Mashines_V.MashinePrevMashineId =
PCT_RPT_Mashines_V_1.MashineId
WHERE
@.SiteLogId = -1
union all
SELECT
PCT_RPT_Mashines_V.MashineRemarks,
PCT_RPT_Mashines_V_1.MashineSDPDescription
PCT_RPT_Mashines_V_1.MashineRemarks
PCT_RPT_Mashines_V.PerId,
PCT_RPT_Mashines_V.PerOrderNumber
FROM
PCT_RPT_Mashines_V LEFT OUTER JOIN
PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
PCT_RPT_Mashines_V.MashinePrevMashineId =
PCT_RPT_Mashines_V_1.MashineId
WHERE
PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId
Ilya
"Avishay Ben-Zvi" <avishay@.community.nospam> wrote in message
news:706D354E-8C96-465C-A951-DEAE0B5DE433@.microsoft.com...
> Hi,
> I am running the following query:
> declare
> @.SiteLogId int
> set @.SiteLogId = 2833
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId OR @.SiteLogId = -1
>
> This query takes about 50 seconds to run. If I remove the OR part from the
> WHERE clause, the running time is reduced to 5 seconds, although there
> shouldn't be any significance to the OR, since @.SiteLogId was assigned a
> value (2833).
> Do you have any explanation for this ?
> How can I use the OR part, and still keep a good performance ?
> And another thing:
> If I remove these two lines:
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> From the SELECT clause, the query takes 50 seconds to run, with or without
> the OR. Why is that happening ?
> Thanks,
> Avishay
>|||Hi Uri,
Thanks for the prompt reply.
I tried also using IF on the beginning of the query. This way it took about
10 seconds (still quite long compared to omitting the OR). In addition, it
will be very hard to manage the query this way (every line of code will be
written twice - or more if I do the same for each one of the parameters).
Other suggestions?
Avishay
"Uri Dimant" wrote:

> Avishay,shalom
> Take a look at an execution plan . Probably , you don't have indexes on
> these columns and SQL Server is not be able to use
> "covering indexes" to create a more efficient plan.
> Can you try
> IF @.SiteLogId = -1
> --Here is your SELECT
>
> What happened ?
>
>
> "Avishay Ben-Zvi" <avishay@.community.nospam> wrote in message
> news:706D354E-8C96-465C-A951-DEAE0B5DE433@.microsoft.com...
>
>|||Hi Ilya,
Unfortunately, putting these two queries in Union, executes even slower than
using IF on the beginning of the query (>40 seconds). It also forces
duplicate management of the query and having this method for lots of
parameters is not an option.
Thanks for your reply.
Avishay
"Ilya Margolin" wrote:

> Avishay,
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> @.SiteLogId = -1
> union all
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId
>
> Ilya
> "Avishay Ben-Zvi" <avishay@.community.nospam> wrote in message
> news:706D354E-8C96-465C-A951-DEAE0B5DE433@.microsoft.com...
>
>|||Union all? Those aren't cummulative conditions. Plain old "union" will do.
ML|||ML,
Union without all option forces distinct values.
Ilya
"ML" <ML@.discussions.microsoft.com> wrote in message
news:FEF8E63F-394A-4509-9410-8D635649240F@.microsoft.com...
> Union all? Those aren't cummulative conditions. Plain old "union" will do.
>
> ML|||Let me demonstrate:
use pubs
go
-- OR
select *
from dbo.sales
where (stor_id = 7067 or title_id = 'PS2091')
-- UNION ALL
select *
from dbo.sales
where (stor_id = 7067)
union all
select *
from dbo.sales
where (title_id = 'PS2091')
-- UNION
select *
from dbo.sales
where (stor_id = 7067)
union
select *
from dbo.sales
where (title_id = 'PS2091')
Compare the results. :)
ML|||> In addition, it
> will be very hard to manage the query this way (every line of code will be
> written twice - or more if I do the same for each one of the parameters).
you can create a viewsql

Query Performance

I have a vendor application running on SQL 2000 sp4. I've got a query
that isn't using any indexes:
select hsi.itemdata.itemnum, hsi.itemdata.itemname,
hsi.itemdata.batchnum, hsi.itemdata.status,
hsi.itemdata.itemtypegroupnum,
hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
hsi.itemdata.datestored, hsi.itemdata.usernum,
hsi.itemdata.deleteusernum,
hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
hsi.itemdata.institution, hsi.itemdata.maxdocrev
from hsi.itemdata
where hsi.itemdata.status + 0 = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
order by 8 desc
The table has 3,105,135 records in test. (There are 22,905,590 in
production.)
CREATE TABLE [hsi].[itemdata] (
[itemnum] [int] NOT NULL ,
[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[batchnum] [int] NULL ,
[status] [int] NULL ,
[itemtypegroupnum] [int] NULL ,
[itemtypenum] [int] NULL ,
[itrevnum] [int] NULL ,
[itemdate] [datetime] NULL ,
[datestored] [datetime] NULL ,
[usernum] [int] NULL ,
[deleteusernum] [int] NULL ,
[securityvalue] [int] NULL ,
[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[institution] [int] NULL ,
[maxdocrev] [int] NULL
) ON [DBSpace2]
GO
CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
[DBSpace2i]
GO
CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
[itemnum]) ON [DBSpace2]
GO
I would have thought it would use one of the existing indexes but it
just does a table scan.
I also tried:
CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
[itemtypenum]) ON [PRIMARY]
GO
But that didn't work either.
Any suggestions?
Thank you,
Toni
*** Sent via Developersdex http://www.codecomments.com ***
The information provided raises more questions than it allows answers.
Indexes will only be used if the optimizer calculates that it will be
more efficient than a table scan. This generally depends on how
selective the indexes are, particularly for the values used in the
query.
How selective is itemtypenum? How selective are the specific values
of itemtypenum? What percentage of the table will the numbers
returned by the query represent?
SELECT hsi.itemdata.itemtypenum, count(*) as Rows
FROM hsi.itemdata
WHERE hsi.itemdata.itemtypenum IN (101, 102, 103, 329)
GROUP BY hsi.itemdata.itemtypenum
How selective is the test " hsi.itemdata.status + 0 = 0"? What data
type is hsi.itemdata.status? Why the + 0? It is possible that IF a
relatively small percentage of rows have a status = 0 that including
status in an index might help - but not while the + 0 is there.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 07:24:39 -0800, Toni <teibner@.allinasql.com>
wrote:

>I have a vendor application running on SQL 2000 sp4. I've got a query
>that isn't using any indexes:
>select hsi.itemdata.itemnum, hsi.itemdata.itemname,
>hsi.itemdata.batchnum, hsi.itemdata.status,
>hsi.itemdata.itemtypegroupnum,
>hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
>hsi.itemdata.datestored, hsi.itemdata.usernum,
>hsi.itemdata.deleteusernum,
>hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
>hsi.itemdata.institution, hsi.itemdata.maxdocrev
>from hsi.itemdata
>where hsi.itemdata.status + 0 = 0 and
>( hsi.itemdata.itemtypenum = 101 or
>hsi.itemdata.itemtypenum = 102 or
>hsi.itemdata.itemtypenum = 103 or
>hsi.itemdata.itemtypenum = 329 )
>order by 8 desc
>The table has 3,105,135 records in test. (There are 22,905,590 in
>production.)
>CREATE TABLE [hsi].[itemdata] (
>[itemnum] [int] NOT NULL ,
>[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[batchnum] [int] NULL ,
>[status] [int] NULL ,
>[itemtypegroupnum] [int] NULL ,
>[itemtypenum] [int] NULL ,
>[itrevnum] [int] NULL ,
>[itemdate] [datetime] NULL ,
>[datestored] [datetime] NULL ,
>[usernum] [int] NULL ,
>[deleteusernum] [int] NULL ,
>[securityvalue] [int] NULL ,
>[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>[institution] [int] NULL ,
>[maxdocrev] [int] NULL
>) ON [DBSpace2]
>GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
>[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
>[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
>[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
>[DBSpace2i]
>GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
>[itemnum]) ON [DBSpace2]
>GO
>I would have thought it would use one of the existing indexes but it
>just does a table scan.
>I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
>[itemtypenum]) ON [PRIMARY]
>GO
>But that didn't work either.
>Any suggestions?
>Thank you,
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***
|||what is the ORDER By 8?
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Toni" <teibner@.allinasql.com> wrote in message
news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||On Thu, 8 Mar 2007 17:03:46 -0000, "Jack Vamvas"
<DEL_TO_REPLY@.del.com> wrote:

>what is the ORDER By 8?
That says to order the eighth column in the SELECT list. My
understanding is that this syntax is deprecated in the SQL standard,
and it certainly has no place in production code, but it is a handy
shortcut when doing quick and dirty SQL.
Roy Harvey
Beacon Falls, CT
|||On Thu, 08 Mar 2007 12:22:58 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:

>That says to order the eighth column in the SELECT list.
Make that:
That says to order BY the eighth column in the SELECT list.
|||This is code from a vendor application. I don't have any control over
it other than to recommend they make changes. As to the why of some of
the questions, I can answer some of them.
I don't know why the vendor is doing the calculation of
hsi.itemdata.status + 0 = 0, unfortunately.
I guess it's not real selective at all. (There are only 613 different
values in the table in test.) For this specific query, this is what is
in the table:
itemtypenum rows
101 127282
102 100347
103 6901
329 387
I apologize for my lack of knowledge on this. I'm not very experienced
with query tuning.
It sounds like the whole query needs to be rewritten in order to be
efficient and make use of any indexes that might exist on the table. Is
that correct?
I appreciate all the responses!
Thank you so much
Toni
*** Sent via Developersdex http://www.codecomments.com ***
|||Since there are 3.1 million rows in the table, and the query returns
almost 0.3 million, the selectivity on itemtypenum is roughly ten
percent. That really isn't selective enough for an index on
itemtypenum to help this query. Had status been selective, and the
query written without the silly + 0, an index on (status, itemtypenum)
might have been of some use. As it stands I do not see any
oportunities for improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 10:02:41 -0800, Toni <teibner@.allinasql.com>
wrote:

>This is code from a vendor application. I don't have any control over
>it other than to recommend they make changes. As to the why of some of
>the questions, I can answer some of them.
>I don't know why the vendor is doing the calculation of
>hsi.itemdata.status + 0 = 0, unfortunately.
>I guess it's not real selective at all. (There are only 613 different
>values in the table in test.) For this specific query, this is what is
>in the table:
>itemtypenum rows
>101 127282
>102 100347
>103 6901
>329 387
>I apologize for my lack of knowledge on this. I'm not very experienced
>with query tuning.
>It sounds like the whole query needs to be rewritten in order to be
>efficient and make use of any indexes that might exist on the table. Is
>that correct?
>I appreciate all the responses!
>Thank you so much
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***