Query longer in SP: queries

Showing posts with label queries. Show all posts

Friday, March 30, 2012

Query question

Hi I have a large stored procedure that performs several queries
conditionally on a database depending on values that are passed into the
procedure. Anyhow I am using a string search and have set up
SELECT @.stringvar='%' + @.stringvar + '%'
then have
case when @.stringvar IS NOT NULL then
'AND (table.field LIKE @.stringvar)'
ELSE ''
It works fine just could not remember why I needed to have the
SELECT @.stringvar='%' + @.stringvar + '%' statement.
thanks,
--
Paul G
Software engineer.The CASE statement is there to include or exclude that condition in the
query; however, it is a lousy implementation. You are obviously using
dynamic sql inside of a stored procedure. Other than a convenient place to
put it, dyanmical sql inside a proc reduces the effectiveness of using
stored procedure.
The '%' before and after the passed in parameter are wildcard characters
that allow any string as a substitute. So, any string plus parameter plus
any string becomes the search condition. You are gauranteed to do a table
scan or clustered index scan as that criteria could never be supported by an
index.
As a better solution, try something more like this:
SELECT Col1, Col2, ..., Coln
FROM Tab1 JOIN Tab2
ON Tab1.Key1 = Tab2.Key1
AND Tab1.Key1 = Tab2.Key2
...
AND Tab1.Keyn = Tab2.Keyn
...
...
JOIN Tabn
ON ...
WHERE criterion1 AND criterion2 ... AND criterionN
AND (@.stringvar IS NULL
OR TabX.ColX LIKE (@.stringvar + '%')
)
This is executed directly. There is no need for a variable nor the use of
the EXEC(@.var) function. TabX.ColX can be indexed and used if it is highly
selectable. The query execution plan can be reused.
Hope this helps.
Sincerely,
Anthony Thomas
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@.microsoft.com...
Hi I have a large stored procedure that performs several queries
conditionally on a database depending on values that are passed into the
procedure. Anyhow I am using a string search and have set up
SELECT @.stringvar='%' + @.stringvar + '%'
then have
case when @.stringvar IS NOT NULL then
'AND (table.field LIKE @.stringvar)'
ELSE ''
It works fine just could not remember why I needed to have the
SELECT @.stringvar='%' + @.stringvar + '%' statement.
thanks,
--
Paul G
Software engineer.|||Hi thanks for the response. It did seem like there would be a better way
other than the dynamic sql. Someone had suggested to me from this newsgroup
to use it so I went that route. No official SQL training so just learning by
trail and error.
"AnthonyThomas" wrote:
> The CASE statement is there to include or exclude that condition in the
> query; however, it is a lousy implementation. You are obviously using
> dynamic sql inside of a stored procedure. Other than a convenient place to
> put it, dyanmical sql inside a proc reduces the effectiveness of using
> stored procedure.
> The '%' before and after the passed in parameter are wildcard characters
> that allow any string as a substitute. So, any string plus parameter plus
> any string becomes the search condition. You are gauranteed to do a table
> scan or clustered index scan as that criteria could never be supported by an
> index.
> As a better solution, try something more like this:
> SELECT Col1, Col2, ..., Coln
> FROM Tab1 JOIN Tab2
> ON Tab1.Key1 = Tab2.Key1
> AND Tab1.Key1 = Tab2.Key2
> ...
> AND Tab1.Keyn = Tab2.Keyn
> ...
> ...
> JOIN Tabn
> ON ...
> WHERE criterion1 AND criterion2 ... AND criterionN
> AND (@.stringvar IS NULL
> OR TabX.ColX LIKE (@.stringvar + '%')
> )
> This is executed directly. There is no need for a variable nor the use of
> the EXEC(@.var) function. TabX.ColX can be indexed and used if it is highly
> selectable. The query execution plan can be reused.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B39C178F-3ABF-4EB2-99F1-3BEA896DC966@.microsoft.com...
> Hi I have a large stored procedure that performs several queries
> conditionally on a database depending on values that are passed into the
> procedure. Anyhow I am using a string search and have set up
> SELECT @.stringvar='%' + @.stringvar + '%'
> then have
> case when @.stringvar IS NOT NULL then
> 'AND (table.field LIKE @.stringvar)'
> ELSE ''
> It works fine just could not remember why I needed to have the
> SELECT @.stringvar='%' + @.stringvar + '%' statement.
> thanks,
> --
> Paul G
> Software engineer.
>
>

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

Wednesday, March 28, 2012

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
the query works fine in access!
"mark" <mark@.remove.com> wrote in message
news:iyjrc.19$_07.10@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
> the query works fine in access!
>
nm im going mad - i blame the heat............ apostrophes my dear
watson!
|||<snip>[vbcol=seagreen]
<snip>
> nm im going mad - i blame the heat............ apostrophes my dear
> watson!
And preferably a different date format. Maybe one that will be
interpreted correctly, regardless of language settings, such as
dbo.Lead.LeadClosed = '19000101'
Gert-Jan
(Please reply only to the newsgroup)
|||try this:
SELECT
Personal.ID,
Personal.Surname1,
Lead.FactfindCompleted,
Lead.FactfindCompletedBy
FROM Personal
LEFT JOIN Lead ON Personal.ID = Lead.ID
LEFT JOIN Mortgage ON Personal.ID = Mortgage.ID
WHERE
Lead.FactfindCompleted > 01/01/2004 AND
Lead.DateToSourcing<>01/01/1900 AND
Lead.LeadClosed=01/01/1900 AND
Mortgage.MortgageApplicationClosed Is Null
ORDER BY Lead.FactfindCompleted;
GO

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!

> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
|||You need to enclose date in single quotes. If you don't, you are doing integer arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>, <, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>
sql

Monday, March 26, 2012

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
--
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||You need to enclose date in single quotes. If you don't, you are doing integer arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>, <, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>sql

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
the query works fine in access!"mark" <mark@.remove.com> wrote in message
news:iyjrc.19$_07.10@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
> the query works fine in access!
>
nm im going mad - i blame the heat............ apostrophes my dear
watson!|||<snip>
<snip>[vbcol=seagreen]
> nm im going mad - i blame the heat............ apostrophes my dear
> watson!
And preferably a different date format. Maybe one that will be
interpreted correctly, regardless of language settings, such as
dbo.Lead.LeadClosed = '19000101'
Gert-Jan
(Please reply only to the newsgroup)|||try this:
SELECT
Personal.ID,
Personal.Surname1,
Lead.FactfindCompleted,
Lead.FactfindCompletedBy
FROM Personal
LEFT JOIN Lead ON Personal.ID = Lead.ID
LEFT JOIN Mortgage ON Personal.ID = Mortgage.ID
WHERE
Lead.FactfindCompleted > 01/01/2004 AND
Lead.DateToSourcing<>01/01/1900 AND
Lead.LeadClosed=01/01/1900 AND
Mortgage.MortgageApplicationClosed Is Null
ORDER BY Lead.FactfindCompleted;
GO

query problem

not sure whats up with this stored procedure, i basically do my queries in
access and port them over
CREATE PROCEDURE factfindnotsourced AS
SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
dbo.Lead.FactfindCompletedBy
FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LEFT
JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
((dbo.Lead.DateToSourcing)<>01/01/1900) AND
((dbo.Lead.LeadClosed)=01/01/1900) AND
((dbo.Mortgage.MortgageApplicationClosed) Is Null))
ORDER BY dbo.Lead.FactfindCompleted;
GO
dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
displaying all the records for some reason!
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID)
LEFT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
--
How about changing it to:
dbo.LeadFactfindCompleted Between 01/01/2004 and 31/12/9999
Does it change the result? Is there an index on the LeadFactfindCompleted?
If so, does reindexing change the result?
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.|||You need to enclose date in single quotes. If you don't, you are doing integ
er arethmics, leading up to an
integer, then converting that integer to datetime and do the comparison (>,
<, or whatever you do). Also,
please is a language neutral datetime format. See:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:1kjrc.17$_07.4@.newsfe4-win...
> not sure whats up with this stored procedure, i basically do my queries in
> access and port them over
> CREATE PROCEDURE factfindnotsourced AS
> SELECT dbo.Personal.ID, dbo.Personal.Surname1, dbo.Lead.FactfindCompleted,
> dbo.Lead.FactfindCompletedBy
> FROM (dbo.Personal LEFT JOIN dbo.Lead ON dbo.Personal.ID = dbo.Lead.ID) LE
FT
> JOIN dbo.Mortgage ON dbo.Personal.ID = dbo.Mortgage.ID
> WHERE (((dbo.Lead.FactfindCompleted) > 01/01/2004) AND
> ((dbo.Lead.DateToSourcing)<>01/01/1900) AND
> ((dbo.Lead.LeadClosed)=01/01/1900) AND
> ((dbo.Mortgage.MortgageApplicationClosed) Is Null))
> ORDER BY dbo.Lead.FactfindCompleted;
> GO
> dbo.Lead.FactfindCompleted > 01/01/2004 is just being ignored - its
> displaying all the records for some reason!
>
>

Friday, March 23, 2012

Query Plan Guides don't seem to work

We have a couple of ad hoc queries in our app that we need to force parameterization. There queries can't be put in sp's because they come from a 3rd party app. The goal is to reduce the amount of cache being accumulated. We are on sql server 2005. The queries in question look like this:

select * from TABLE1 where SEQUENCE_NUMBER = '<some literal>'

When i query syscacheobjects i see thousands of compiled plans for that single query with different literal values in the WHERE clause. I want to force parameterization and make it reuse one plan. Seems to best way to do this is to turn FORCED PARAMETERIZATION on for the db or to create plan guides. Both don't seem to be working.

I turned FORCED PARAMETERIZATION on for the db, then cleared proc cache (DBCC FREEPROCCACHE). I query syscacheobjects and it's empty. I run a test script with 10 queries similiar to the one above, passing a different literal into the where clause. I would expect there to be only one compiled plan for the paramitized version of the query but again there are 10 compiled plans for that query.

I then turned SIMPLE PARAMETERIZATION on for the db and created a plan guide for the query:

DECLARE @.stmt nvarchar(max);
DECLARE @.params nvarchar(max);
EXEC sp_get_query_template
N'select * from TABLE1 where SEQUENCE_NUMBER = ''%''',
@.stmt OUTPUT,
@.params OUTPUT;
EXEC sp_create_plan_guide
N'Templat1',
@.stmt,
N'TEMPLATE',
NULL,
@.params,
N'OPTION(PARAMETERIZATION FORCED)';

I again clear the cache and syscacheobjects is empty. I run my test script. This time i can a row for the plan guide BUT there is still 10 rows for the queries in my test script. I expected to just see the row for the plan guide, indicating that it's using that compiled plan for all the queries but this isn't the case.

Has anyone used query plan guides? Is my testing correct, should i expect LESS rows to accumulate in syscacheobjects? I can query sys.plan_guides and see that my plan guide is created and enabled but from my tests it doesn't seem like it's being used when i run my tests. Any advise on how to get this going?

thanks,
Dave

Did you see any entry where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'?

If yes, then it is working. Check the number for column [usecounts], it should be 10.

If you are not qualifying the table with the schema / owner, then the plan generated will not be share among multiple users. You can check column [uid] and see if it is different from -2.

AMB

|||thanks for the response hunchback! I AM seeing an entry in syscacheobjects where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'

Below are the results of:
select cacheobjtype, objtype, usecounts, sql from syscacheobjects
where sql like '%TABLE1%' order by sql

cacheobjtype objtype usecount sql
Compiled Plan Prepared 10 (@.0 varchar(8000))select * from dbo.TABLE1 where SEQUENCE_NUMBER = @.0
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '1'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '10'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '2'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '3'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '4'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '5'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '6'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '7'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '8'
Compiled Plan Adhoc 1 select * from dbo.TABLE1 where SEQUENCE_NUMBER = '9'

Any idea why is it compiling a plan for each query even though it's using the Plan Guide? When i run Profiler and check out the ExplainPlan XML i don't see any references to the TemplatePlan which was also making me think that it wasn't using the plan guide.
|||

What about the [usecounts] associated to the ('Compiled Plan', 'Prepared')?

SQL Server has to compile the Adhoc query anyway, in order to get the query tree and be able to separate the constant values that will be pass to the parameters. Based on the resources available and cost of compilation, SQL Server could save or not those compiled plans.

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Plan Cache Concepts Explained

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/08/plan-cache-concepts-explained.aspx

AMB

|||I'm running into the same behavior as tenatiousd is reporting.

I create the template plan guide and setup tests to watch SQL Server behavior (syscacheobjects). When I execute a SQL statement that matches the plan guide, it seems to be "referencing" the Prepared Plan (refcount goes up), but creates an Ad Hoc compiled plan and (re)uses that instead. Everything else that matters for plan resuse is the same (user id, execution context, qualified table names, etc.), but it doesn't reuse the Prepared Plan defined from the template plan guide. If I change the parameters, it references the Prepared Plan, but compiles a new Ad Hoc plan and continues to reuse the ad hoc plan over and over (as long as the parameters are the same).

The resulting behavior is that the Prepared Plan that matches the SQL statement is not reused and the plan cache would continue to grow with new ad hoc plans being added as the parameter values change.

Why isn't the Prepared Plan being reused? Will the Prepared Plan only be reused if there is enough pressure on the Plan Cache and it can't/won't store the ad hoc plan?
|||

Hi Nickvalo,

What about [usecounts] for the "Prepared" execution plan, is it being incremented?

AMB

|||The first time I execute a SQL statement that has a template plan guide (after issuing a DBCC freeproccache), this is what happens:

1. The usecount for the Prepared Plan goes up by one and it's refcount goes up by 2 (or 3).
2. The usecount for the ad hoc plan goes up by one (a new one gets created for each unique set of parameter values).

Then if I execute that same SQL statement (same parameter values) 5 times in a row (without clearing the plan cache), the usecount for the *ad hoc* plan goes up by 5. So, essentially it appears that the prepared plan is used somehow the first time, but so is an ad hoc plan for that same statement ... then only the ad hoc plan is used when the same parameters are used in the SQL statement. My expectation was that there would not be an ad hoc plan prepared and cached if the template plan guide matched the SQL statement and forced parameterization.
|||

Nickvalo,

I which somebody from the Microsoft SQL Server group, in charge of "plan guides", could answer this question.

My guess is that the cost of generating an execution plan for that statement is low and the resources available are high, so putting the plan in the cache can help. See if you can download a stress tool and do the test with more connections (create more strees, consume more, and reduce resources available).

Support Tools Available For Stress Testing & Performance Analysis

http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en

AMB

|||

Hello,

maybe this page: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

especially the Appendix A section can help you to find out why there is no parametrisation

regards,

Peter

sql

Query Plan Guides don't seem to work

select * from TABLE1 where SEQUENCE_NUMBER = '<some literal>'

I then turned SIMPLE PARAMETERIZATION on for the db and created a plan guide for the query:

thanks,
Dave

Did you see any entry where cacheobjtype = 'Compiled Plan' and objtype = 'Prepared' and sql = '(@.0 varchar(8000) )select * from TABLE1 where SEQUENCE_NUMBER = @.0'?
If yes, then it is working. Check the number for column [usecounts], it should be 10.
If you are not qualifying the table with the schema / owner, then the plan generated will not be share among multiple users. You can check column [uid] and see if it is different from -2.

AMB

What about the [usecounts] associated to the ('Compiled Plan', 'Prepared')?

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Plan Cache Concepts Explained

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/08/plan-cache-concepts-explained.aspx

AMB

|||I'm running into the same behavior as tenatiousd is reporting.

Why isn't the Prepared Plan being reused? Will the Prepared Plan only be reused if there is enough pressure on the Plan Cache and it can't/won't store the ad hoc plan?
|||

Hi Nickvalo,

What about [usecounts] for the "Prepared" execution plan, is it being incremented?

AMB

|||The first time I execute a SQL statement that has a template plan guide (after issuing a DBCC freeproccache), this is what happens:

Nickvalo,

I which somebody from the Microsoft SQL Server group, in charge of "plan guides", could answer this question.

Support Tools Available For Stress Testing & Performance Analysis

http://www.microsoft.com/downloads/details.aspx?familyid=5691ab53-893a-4aaf-b4a6-9a8bb9669a8b&displaylang=en

AMB

|||

Hello,

maybe this page: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

especially the Appendix A section can help you to find out why there is no parametrisation

regards,

Peter

query plan caching?

SQL Server has the concept of caching the query plan. Parameterized queries can take advantage of this feature.

I'm wondering if there's an equivalent feature in SSAS. If I use MDX parameters (i.e. using @.Param in the query and setting the Parameters object of the AdomdCommand object) and run the same query twice with similar parameter values, will SSAS be able to reuse the query plan at all?

What I'm wondering is whether it is better (a) to use parameters and then StrToSet to convert those parameters into MDX objects... OR (b) to just run dynamic MDX with no parameters. (Yes, I'm referring to how Reporting Services does MDX queries. Wondering if it would be worth suggesting to the SSRS team that they take the parameter values and build dynamic MDX instead of using MDX parameters.)

There is no equivalent for cached query plans in the SSAS 2005 version.

> What I'm wondering is whether it is better (a) to use parameters and then StrToSet to convert those parameters into MDX objects... OR (b) to just run dynamic MDX with no parameters.

In SSAS 2005, building MDX on the fly is always better than using StrToSet and parameters.

|||

Thanks Mosha. That answered my question. I reported this as a suggestion for Katmai SSRS:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263472

|||I diagree that this is SSRS issue. SSRS is absolutely correct using StrToSet( @.param, CONSTRAINED ) for many reason, one being security. This should be reported as suggestion for Katmai SSAS - to optimize StrToXXX( , CONSTRAINED) family of functions.

query performance with large tables

Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?
If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>

query performance with large tables

Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexe
s
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>sql

query performance with large tables

Hi,
I am currently facing two problems: one general and one more specific.
My general issue is improving the performance of queries involving a very
large table. I know that the most efficient optimization is to create indexes
that match the type of queries I run. Are there any other solutions? Can I
gain from splitting the table into several smaller ones?
The more specific problem is related to index creation. I am trying to
create an index that makes sense, but SQL Server 2005 gives a timeout error
after less than a minute. I suppose this is due to the size of the table and
cost of clustering (I set a primary key) but I can't find in BOL how to
increase the timeout value...
Any ideas regarding any of these two questions?If you have proper indexes you should not need to split the table. You can
partition the table but that will not help you if you still don't have
proper indexes.
Don't use the gui to create the index. Use the query Editor and issue a
CREATE INDEX statement instead. The editor defaults to 0 timeout so it will
stay connected as long as it needs to.
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:2A0250DC-54ED-4FDC-A377-3D5F72BFEB1F@.microsoft.com...
> Hi,
> I am currently facing two problems: one general and one more specific.
> My general issue is improving the performance of queries involving a very
> large table. I know that the most efficient optimization is to create
> indexes
> that match the type of queries I run. Are there any other solutions? Can I
> gain from splitting the table into several smaller ones?
> The more specific problem is related to index creation. I am trying to
> create an index that makes sense, but SQL Server 2005 gives a timeout
> error
> after less than a minute. I suppose this is due to the size of the table
> and
> cost of clustering (I set a primary key) but I can't find in BOL how to
> increase the timeout value...
> Any ideas regarding any of these two questions?
>

query performance tuning

Hi
Where can I find good resources re: peformance tuning of queries / indexes ,
using EXPLAIN etc ?
Thanks
BruceBecause you have EXPLAIN in capitals I am going to presume you either
1. Want the SQL Server equivalent to Oracle's EXPLAIN PLAN
2. Have the wrong Newsgroup
Presuming #1 you can look at.
SET SHOWPLAN_ALL ON
once you have that then you can start to look to articles like this
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
or anything on the subject by Kalen Delaney.
--
--
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
"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
news:eWvSyqJuDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi
> Where can I find good resources re: peformance tuning of queries / indexes
,
> using EXPLAIN etc ?
> Thanks
> Bruce
>|||www.sql-server-performance.com will be a good resource.
Suresh
>--Original Message--
>Because you have EXPLAIN in capitals I am going to
presume you either
>1. Want the SQL Server equivalent to Oracle's EXPLAIN
PLAN
>2. Have the wrong Newsgroup
>
>Presuming #1 you can look at.
>SET SHOWPLAN_ALL ON
>once you have that then you can start to look to articles
like this
>http://www.sql-server-
performance.com/query_execution_plan_analysis.asp
>or anything on the subject by Kalen Delaney.
>--
>--
>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
>"Bruce Baker" <bruceb@.ardex.com.au> wrote in message
>news:eWvSyqJuDHA.2448@.TK2MSFTNGP12.phx.gbl...
>> Hi
>> Where can I find good resources re: peformance tuning
of queries / indexes
>,
>> using EXPLAIN etc ?
>> Thanks
>> Bruce
>>
>
>.
>

Wednesday, March 21, 2012

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

Tuesday, March 20, 2012

Query performance

Can anyone tell me if there‘s a difference how the queries below are treated
by the optimizer
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
Where A.cmpcode = 'Microsoft' -- this is the diff
And B.cmpcode = 'Microsoft'
Select A.cmpcode, B.cmpcode
From dbo.oas_docline B
Join dbo.oas_dochead A
On A.cmpcode = B.cmpcode
And A.cmpcode = 'Microsoft'
And B.cmpcode = 'Microsoft'
SUnny
Hi Sanjay
both the queries has the same performance
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Sanjay" wrote:

> Can anyone tell me if there‘s a difference how the queries below are treated
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny
|||Same same. It doesn't matter if you specify a filter in the FROM or WHERE clause for an inner joins.
It will produce the same result and the optimizer know that, so the optimizer has the same
flexibility to product query plans...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:6B3328C5-B4EC-4A7F-8CDD-3D69BF745067@.microsoft.com...
> Can anyone tell me if there's a difference how the queries below are treated
> by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
>
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
> --
> SUnny
|||Sanjay wrote on Thu, 26 May 2005 03:25:17 -0700:

> Can anyone tell me if theres a difference how the queries below are
> treated by the optimizer
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> Where A.cmpcode = 'Microsoft' -- this is the diff
> And B.cmpcode = 'Microsoft'
> Select A.cmpcode, B.cmpcode
> From dbo.oas_docline B
> Join dbo.oas_dochead A
> On A.cmpcode = B.cmpcode
> And A.cmpcode = 'Microsoft'
> And B.cmpcode = 'Microsoft'
>
If you ever want to compare the query plans, put both into QA with a go
after each statement, click the Show Execution Plan on the Query menu, and
run them both. Then look at the Execution Plan tab to see how they compare.
There are probably better ways to check, but I'm still learning the
intricacies of SQL Server DBA.
Dan

Query Performance

HI to All!
Is there anyway to improve the performance of Queries involving
Substring clause. i have indexes on the query columns but performance is
not good. e.g if i use a query without substring it gives me result in
less than 1 seconds over a more than 1.3 million rows and when i use
substring it takes more than 10 seconds.
Regards
Farid
*** Sent via Developersdex http://www.examnotes.net ***Sometimes there is and sometimes there is not. Either way - it starts with
you posting DDL and sample data.
Maybe you don't even need the substring function. How can we tell?
ML|||
Dear ML
i have to use Substring function because i have data like
600600-02-12345 here i 600600 means my center. i have to substring this
to identify records related to 600600 center.
i m issuing following query
select count(distinct(substring(output_ref,1,6)
)) as ns123
from tbl_Main_Con where day(load_date)= '7' and month(load_date)= '9'
and year(load_date)= '2005'
this query returns result in more than 10 seconds. how can i improve its
performance.
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Change your WHERE clause to:
where
load_date >= '20050907'
and load_date < '20050908'
You may want to create an index on load_date or (load_date, output_ref).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:OVWKl75sFHA.1256@.TK2MSFTNGP09.phx.gbl...
Dear ML
i have to use Substring function because i have data like
600600-02-12345 here i 600600 means my center. i have to substring this
to identify records related to 600600 center.
i m issuing following query
select count(distinct(substring(output_ref,1,6)
)) as ns123
from tbl_Main_Con where day(load_date)= '7' and month(load_date)= '9'
and year(load_date)= '2005'
this query returns result in more than 10 seconds. how can i improve its
performance.
Regards,
*** Sent via Developersdex http://www.examnotes.net ***|||Try using the LEFT function, although I doubt it would make any difference.
Are frequently sought columns indexed?
If you can, consider redesigning the table to store this piece of
information separately. Maybe even in a computed column, e.g. using the
following expression: left(output_ref, 6).
Read Tom's reply for another suggestion.
ML|||Just check with this
Initialize the parameter to NULL
SET @.Auto = 0
Detail.Automated = ISNULL(@.Auto ,Detail.Automated)
NOTE : Constraint Detail.Automated is not null
Detail.Automated = ISNULL(@.Auto ,Detail.Automated) and Detail.Automated IS
NULL
NOTE : Constraint Detail.Automated can have NULL values.
and Also check whether any index on the search arguments in the where caluse
.
HTH
Rajesh Peddireddy
"Jim Abel" wrote:

> The folling query works but is it the most efficient way to write it from
a
> performance aspect?
> The parameter can be set with the values (0,1 and %) these can be changed
if
> needed for the final query version.
> The Detail.Automated database field is a datatype of bit
> Should I be writing this to avoid the LIKE keyword?
>
> -- only used for testing
> DECLARE @.Auto AS char (1)
> SET @.Auto = '%'
> -- end test
> SELECT DISTINCT Status.ID,
> Info.ID,
> Info.Text,
> Detail.Automated,
> Status.Status
> FROM Status INNER JOIN Info
> ON Status.ID = Info.ID
> INNER JOIN Detail
> ON Info.ID = Detail.ID
> WHERE (Status.ID = 4)
> AND (Detail.Automated LIKE @.Auto|||check also indexes on the join fields
HTH
Rajesh Peddireddy
"Rajesh" wrote:
> Just check with this
> Initialize the parameter to NULL
> SET @.Auto = 0
> Detail.Automated = ISNULL(@.Auto ,Detail.Automated)
> NOTE : Constraint Detail.Automated is not null
> Detail.Automated = ISNULL(@.Auto ,Detail.Automated) and Detail.Automated I
S
> NULL
> NOTE : Constraint Detail.Automated can have NULL values.
> and Also check whether any index on the search arguments in the where calu
se.
> HTH
> Rajesh Peddireddy
> "Jim Abel" wrote:
>

Friday, March 30, 2012

Query question

query progress indicator

query progress indicator

query progress indicator

Wednesday, March 28, 2012

query problem

query problem

Monday, March 26, 2012

query problem

query problem

query problem

query problem

Friday, March 23, 2012

Query Plan Guides don't seem to work

Query Plan Guides don't seem to work

query plan caching?

query performance with large tables

query performance with large tables

query performance with large tables

query performance tuning

Wednesday, March 21, 2012

Query Performance from Reporting Services

Tuesday, March 20, 2012

Query performance

Query Performance

Query longer in SP

Blog Archive

About Me