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 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 b
y
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 t
o
> 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 highl
y
> 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 ProductID in Stored Procedure

I have created a Stored Procedure:

SELECT ID, Productname, Price, Desc, img_url
FROM Products
WHERE (ID =[ --Products.aspx?ID=x --])

Question: I want to view the product details for that ID in QueryString on my ASP.Net pagewww.myhomepage.com/Product.aspx?ID=2. How do I?...

Using:
FormView1
SqlDataSource
VB/ASP.Net 2005 & SQL Server 2005 Express.

you need to create a store procedure which can take one parameter called product_id..

You need to add this parameter in your storeprocedure and then call the procedure..

|||

Hi!

Can you please spesify an example?

|||

You can use QueryStringParameter in a SqlDataSource, take a look at this article:

http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

Query problem - TIPOS IN ()

Hi,

I have the following query

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE CONSTELEFONICA
@.E1 VARCHAR(50),
@.TIPOS VARCHAR(50),
@.PERINI DATETIME,
@.PERFIM DATETIME,
@.PERINI2 DATETIME,
@.PERFIM2 DATETIME
AS
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI as DATA_INICIAL, @.PERFIM as DATA_FINAL
FROM TELEFONICA WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI AND @.PERFIM
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND CLASSIFICA_TELEFONICA IN (@.TIPOS)
AND VALOR_TEMPO IS NOT NULL
UNION
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI2 as DATA_INICIAL, @.PERFIM2 as DATA_FINAL
FROM TELEFONICA WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI2 AND @.PERFIM2
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND CLASSIFICA_TELEFONICA NOT IN (@.TIPOS)
AND VALOR_TEMPO IS NOT NULL ORDER BY CLASSIFICA_TELEFONICA

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

And from my application I call the query like...

EXEC CONSTELEFONICA '01133722000', 'CONUR, LOCAL', '20060205','20060304 23:59:59.997', '20060203','20060302 23:59:59.997'

But it doesn't work because the CLASSIFICA_TELEFONICA NOT IN (@.TIPOS) does not recognise as CLASSIFICA_TELEFONICA NOT IN ('CONUR', 'LOCAL') how can I make this query ?

Thanks

You can't do it like that, read Arrays and Lists in SQL Server by SQL Server MVP Erland Sommarskog (http://www.sommarskog.se/arrays-in-sql.html)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

I understood but I don't know how to apply in my query can you help me ?

Thanks

|||

I tried doing this

SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI as DATA_INICIAL, @.PERFIM as DATA_FINAL
FROM TELEFONICA T INNER JOIN CHARLIST_TO_TABLE_SP(@.TIPOS) I ON T.CLASSIFICA_TELEFONICA = I.str WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI AND @.PERFIM
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND VALOR_TEMPO IS NOT NULL
UNION
SELECT DATA_HORA, LOCALIDADE, VALOR_TEMPO, VALOR_TARIFA, CLASSIFICA_TELEFONICA, VALOR_TOTAL, NRTELEFONE, NUMERO_E1, @.PERINI2 as DATA_INICIAL, @.PERFIM2 as DATA_FINAL
FROM TELEFONICA T INNER JOIN CHARLIST_TO_TABLE_SP(@.TIPOS) I ON T.CLASSIFICA_TELEFONICA <> I.str WHERE NUMERO_E1 = @.E1
AND DATA_HORA BETWEEN @.PERINI2 AND @.PERFIM2
AND LOCALIDADE IS NOT NULL
AND LEFT(LOCALIDADE, 2) <> '**'
AND TIPO = '2'
AND LEN(NRTELEFONE) > 5
AND VALOR_TOTAL IS NOT NULL
AND VALOR_TEMPO IS NOT NULL ORDER BY CLASSIFICA_TELEFONICA

But I got the message that CHARLIST_TO_TABLE_SP is not a valid function...but in the reference site it's a procedure...and I've created it

|||Look for the function: iter_charlist_to_table for the 2000 way to do it. That was the 7.0 method, since we didn't have functions back then.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>[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

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!<snip>
> > ((dbo.Lead.LeadClosed)=01/01/1900) AND
<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
SELEC
Personal.ID
Personal.Surname1,
Lead.FactfindCompleted
Lead.FactfindCompletedB
FROM Personal
LEFT JOIN Lead ON Personal.ID = Lead.I
LEFT JOIN Mortgage ON Personal.ID = Mortgage.ID
WHER
Lead.FactfindCompleted > 01/01/2004 AN
Lead.DateToSourcing<>01/01/1900 AND
Lead.LeadClosed=01/01/1900 AN
Mortgage.MortgageApplicationClosed Is Nul
ORDER BY Lead.FactfindCompleted
G

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

query performance question

Hello. I have query performance question.
I need to optimize procedure

CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
@.P1 VARCHAR(50),
@.P2 INTEGER
AS
DECLARE @.IORGANIZATIONID INTEGER
EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT

SELECT TSECCOMP.ID,
CASE TSECPROFILEGRP.ACCESSTYPE
WHEN -1 THEN
CASE TSECCLASS.DEFAULTACCESS
WHEN -1 THEN
CASE TSECGROUPCOMP.DEFAULTACCESS
WHEN -1 THEN
TSECCOMP.DEFAULTACCESS
ELSE
TSECGROUPCOMP.DEFAULTACCESS
END

ELSE
TSECCLASS.DEFAULTACCESS
END
ELSE TSECPROFILEGRP.ACCESSTYPE
END AS EXPR1
FROM TSECCOMP
INNER JOIN ((TSECPROFILE
INNER JOIN (TSECCLASS
INNER JOIN TSECPROFILEGRP
ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
WHERE
(
CASE TSECPROFILEGRP.ACCESSTYPE
WHEN -1 THEN
CASE TSECCLASS.DEFAULTACCESS
WHEN -1 THEN
CASE TSECGROUPCOMP.DEFAULTACCESS
WHEN -1 THEN
TSECCOMP.DEFAULTACCESS
ELSE
TSECGROUPCOMP.DEFAULTACCESS
END
ELSE
TSECCLASS.DEFAULTACCESS
END
ELSE TSECPROFILEGRP.ACCESSTYPE
END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
GO
Thank you In advance.Make sure you have indexed the join keys. You can try running the Index
Tuning Wizard for advice.

Gert-Jan

inna wrote:
> Hello. I have query performance question.
> I need to optimize procedure
> CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
> @.P1 VARCHAR(50),
> @.P2 INTEGER
> AS
> DECLARE @.IORGANIZATIONID INTEGER
> EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
> SELECT TSECCOMP.ID,
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END AS EXPR1
> FROM TSECCOMP
> INNER JOIN ((TSECPROFILE
> INNER JOIN (TSECCLASS
> INNER JOIN TSECPROFILEGRP
> ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
> ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
> TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
> ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
> WHERE
> (
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
> AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
> GO
> Thank you In advance.|||Hi

Check out the query execution plan

http://www.sql-server-performance.c...an_analysis.asp

http://www.sql-server-performance.com/transact_sql.asp

John

"inna" <mednyk@.hotmail.com> wrote in message
news:347a408b.0309131204.19c074e8@.posting.google.c om...
> Hello. I have query performance question.
> I need to optimize procedure
> CREATE PROCEDURE dbo.SECUQUSRCOMPACCES
> @.P1 VARCHAR(50),
> @.P2 INTEGER
> AS
> DECLARE @.IORGANIZATIONID INTEGER
> EXECUTE dbo.ORGNQGETORGID @.PORGUNIQUEID = @.IORGANIZATIONID OUTPUT
> SELECT TSECCOMP.ID,
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END AS EXPR1
> FROM TSECCOMP
> INNER JOIN ((TSECPROFILE
> INNER JOIN (TSECCLASS
> INNER JOIN TSECPROFILEGRP
> ON TSECCLASS.UNIQUEID = TSECPROFILEGRP.SECURITYGROUPID)
> ON TSECPROFILE.UNIQUEID = TSECPROFILEGRP.PROFILEID) INNER JOIN
> TSECGROUPCOMP ON TSECCLASS.UNIQUEID = TSECGROUPCOMP.SECURITYGROUPID)
> ON TSECCOMP.UNIQUEID = TSECGROUPCOMP.SECCOMPID
> WHERE
> (
> CASE TSECPROFILEGRP.ACCESSTYPE
> WHEN -1 THEN
> CASE TSECCLASS.DEFAULTACCESS
> WHEN -1 THEN
> CASE TSECGROUPCOMP.DEFAULTACCESS
> WHEN -1 THEN
> TSECCOMP.DEFAULTACCESS
> ELSE
> TSECGROUPCOMP.DEFAULTACCESS
> END
> ELSE
> TSECCLASS.DEFAULTACCESS
> END
> ELSE TSECPROFILEGRP.ACCESSTYPE
> END > 0 ) AND (TSECPROFILE.KEYVALUE=@.P1) AND ( TSECCOMP.TYPE =@.P2)
> AND TSECCOMP.ORGANIZATIONID = @.IORGANIZATIONID
> GO
> Thank you In advance.

Query Performance Problem

The following stored procedure is taking too long (in my opinion). The
problem seems to be the SUM line. When commented out the query takes a
second or two. When included the response time climbs to minute and a
half.

Is my code that inefficient or is SUM and ABS calls just that slow?
Any suggestions to spead this up?

Thanks,
- Jason

SET NOCOUNT ON

DECLARE @.PriceTable TABLE (
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[ClosingPrice] DECIMAL(18, 6)
)

-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Get closing prices from historical positions
INSERT INTO @.PriceTable
SELECT
[Symbol],
[Identity],
[Exchange],
[ClosingPrice]
FROM
Historical.dbo.ClearingPosition
WHERE
[TradeDate] = CONVERT(NVARCHAR(10), @.TradeDate, 101)

-- Query the historical position table
SELECT
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPrice.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN @.PriceTable tblPrice ON (tblTrade.[Symbol] =
tblPrice.[Symbol]AND tblTrade.[Identity] = tblPrice.[Identity])
WHERE
CONVERT(NVARCHAR(10), [TradeTimestamp], 101) = CONVERT(NVARCHAR(10),
@.TradeDate, 101)
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]Jason (JayCallas@.hotmail.com) writes:
> The following stored procedure is taking too long (in my opinion). The
> problem seems to be the SUM line. When commented out the query takes a
> second or two. When included the response time climbs to minute and a
> half.
> Is my code that inefficient or is SUM and ABS calls just that slow?

No, SUM and abs() are not slow. The problem is likely to lie elsewhere:

> WHERE
> CONVERT(NVARCHAR(10), [TradeTimestamp], 101) =
CONVERT(NVARCHAR(10), @.TradeDate, 101)

I would guess that there is an index on TradeTimestamp. Or at least there
should be. Else SQL Server would have to traverse the entire ClearingTrade
table. I have no idea how big it is, but the Historical db name, make me
think it's huge!

The problem with this query is that even if there is an index on
TradeTimestamp, SQL Server cannot use it, because you have embedded
the column in an expression. SQL Server cannot assume that result the
expression agrees with the order in the index.

Thus you should rewrite this query as

TradeTimestamp >= @.TradeDate AND
TradeTimestamp < dateadd(DAY, 1, @.TradeDate)

I'm here assuming that TradeTimestamp is datetime and that @.TradeDate
is a datetime value with 00:00:00.000 in the time portion.

So why does the query run faster without the SUM? I don't know, but
I noitce that you comment away the SUM, the @.prices table is no longer
meaningful in the query, so SQL Server can simply skip reading that
table.

By the way, also the first query in the procedure can benefit from a
similar optimization:

WHERE [TradeDate] = CONVERT(NVARCHAR(10), @.TradeDate, 101)

If TradeDate is datetime, it will here be autoconverted to nvarchar(10),
and any index on the column will be ignored.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Do you need ABS? Do you ever expect a negative quantity? If not, you can
just leave it out. This could be the result:

SELECT
tblTrade.Symbol,
tblTrade.Identity,
tblTrade.Exchange,
tblTrade.Account,
SUM(CASE WHEN tblTrade.Side = 'B'
THEN tblTrade.Quantity * (tblTrade.Price -
tblPrice.ClosingPrice)
ELSE tblTrade.Quantity * (tblPrice.ClosingPrice -
tblTrade.Price)
END) AS Value
FROM Historical.dbo.ClearingTrade tblTrade
LEFT JOIN @.PriceTable tblPrice
ON tblTrade.Symbol = tblPrice.Symbol
AND tblTrade.Identity = tblPrice.Identity
WHERE CONVERT(NVARCHAR(10), TradeTimestamp, 101) = CONVERT(NVARCHAR(10),
@.TradeDate, 101)
GROUP BY
tblTrade.Symbol,tblTrade.Identity,tblTrade.Exchang e,tblTrade.Account

Note that this query cannot use any index on TradeTimestamp because of
the function it is wrapped in.

If possible, you might want to rewrite it so it become something like:
WHERE TradeTimestamp = CONVERT(...)

Final thought: you might want to drop the table variable, and join with
the selection that is used in the current insert statement.

Hope this helps,
Gert-Jan

Jason wrote:
> The following stored procedure is taking too long (in my opinion). The
> problem seems to be the SUM line. When commented out the query takes a
> second or two. When included the response time climbs to minute and a
> half.
> Is my code that inefficient or is SUM and ABS calls just that slow?
> Any suggestions to spead this up?
> Thanks,
> - Jason
> SET NOCOUNT ON
> DECLARE @.PriceTable TABLE (
> [Symbol] VARCHAR(15),
> [Identity] VARCHAR(15),
> [Exchange] VARCHAR(5),
> [ClosingPrice] DECIMAL(18, 6)
> )
> -- Use previous trading date if none specified
> IF @.TradeDate IS NULL
> SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()
> -- Get closing prices from historical positions
> INSERT INTO @.PriceTable
> SELECT
> [Symbol],
> [Identity],
> [Exchange],
> [ClosingPrice]
> FROM
> Historical.dbo.ClearingPosition
> WHERE
> [TradeDate] = CONVERT(NVARCHAR(10), @.TradeDate, 101)
> -- Query the historical position table
> SELECT
> tblTrade.[Symbol],
> tblTrade.[Identity],
> tblTrade.[Exchange],
> tblTrade.[Account],
> SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
> ELSE ABS(tblTrade.[Quantity]) END) * (tblPrice.[ClosingPrice] -
> tblTrade.[Price])) AS [Value]
> FROM
> Historical.dbo.ClearingTrade tblTrade
> LEFT JOIN @.PriceTable tblPrice ON (tblTrade.[Symbol] =
> tblPrice.[Symbol]AND tblTrade.[Identity] = tblPrice.[Identity])
> WHERE
> CONVERT(NVARCHAR(10), [TradeTimestamp], 101) = CONVERT(NVARCHAR(10),
> @.TradeDate, 101)
> GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]|||Gert-Jan Strik <sorry@.toomuchspamalready.nl> wrote in message news:<3F736C4E.5F8F43A3@.toomuchspamalready.nl>...
> Do you need ABS? Do you ever expect a negative quantity? If not, you can
> just leave it out. This could be the result:
> SELECT
> tblTrade.Symbol,
> tblTrade.Identity,
> tblTrade.Exchange,
> tblTrade.Account,
> SUM(CASE WHEN tblTrade.Side = 'B'
> THEN tblTrade.Quantity * (tblTrade.Price -
> tblPrice.ClosingPrice)
> ELSE tblTrade.Quantity * (tblPrice.ClosingPrice -
> tblTrade.Price)
> END) AS Value
> FROM Historical.dbo.ClearingTrade tblTrade
> LEFT JOIN @.PriceTable tblPrice
> ON tblTrade.Symbol = tblPrice.Symbol
> AND tblTrade.Identity = tblPrice.Identity
> WHERE CONVERT(NVARCHAR(10), TradeTimestamp, 101) = CONVERT(NVARCHAR(10),
> @.TradeDate, 101)
> GROUP BY
> tblTrade.Symbol,tblTrade.Identity,tblTrade.Exchang e,tblTrade.Account
>
> Note that this query cannot use any index on TradeTimestamp because of
> the function it is wrapped in.
> If possible, you might want to rewrite it so it become something like:
> WHERE TradeTimestamp = CONVERT(...)
> Final thought: you might want to drop the table variable, and join with
> the selection that is used in the current insert statement.
> Hope this helps,
> Gert-Jan
>
> Jason wrote:
> > The following stored procedure is taking too long (in my opinion). The
> > problem seems to be the SUM line. When commented out the query takes a
> > second or two. When included the response time climbs to minute and a
> > half.
> > Is my code that inefficient or is SUM and ABS calls just that slow?
> > Any suggestions to spead this up?
> > Thanks,
> > - Jason
> > SET NOCOUNT ON
> > DECLARE @.PriceTable TABLE (
> > [Symbol] VARCHAR(15),
> > [Identity] VARCHAR(15),
> > [Exchange] VARCHAR(5),
> > [ClosingPrice] DECIMAL(18, 6)
> > )
> > -- Use previous trading date if none specified
> > IF @.TradeDate IS NULL
> > SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()
> > -- Get closing prices from historical positions
> > INSERT INTO @.PriceTable
> > SELECT
> > [Symbol],
> > [Identity],
> > [Exchange],
> > [ClosingPrice]
> > FROM
> > Historical.dbo.ClearingPosition
> > WHERE
> > [TradeDate] = CONVERT(NVARCHAR(10), @.TradeDate, 101)
> > -- Query the historical position table
> > SELECT
> > tblTrade.[Symbol],
> > tblTrade.[Identity],
> > tblTrade.[Exchange],
> > tblTrade.[Account],
> > SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
> > ELSE ABS(tblTrade.[Quantity]) END) * (tblPrice.[ClosingPrice] -
> > tblTrade.[Price])) AS [Value]
> > FROM
> > Historical.dbo.ClearingTrade tblTrade
> > LEFT JOIN @.PriceTable tblPrice ON (tblTrade.[Symbol] =
> > tblPrice.[Symbol]AND tblTrade.[Identity] = tblPrice.[Identity])
> > WHERE
> > CONVERT(NVARCHAR(10), [TradeTimestamp], 101) = CONVERT(NVARCHAR(10),
> > @.TradeDate, 101)
> > GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

I know for a fact the the problem (whatever it is) is on line :

SUM(CASE WHEN tblTrade.Side = 'B' THEN tblTrade.Quantity *
(tblTrade.Price -
tblPrice.ClosingPrice) ELSE tblTrade.Quantity * (tblPrice.ClosingPrice
-
tblTrade.Price) END) AS Value

When this line is included the query takes about 90 seconds, when it
is commented out the query takes 1 or 2 seconds.

And I have to have the ABS in there. SOME of the clearing firms report
sells as negative quantities.|||After further testing it seems that just the act of making a query
against @.PriceTable is causing the slow performance. Putting an index
on ClearingTrade.TradeDate made no perceptible difference.

But when I removed the variable @.PriceTable and included the reference
to ClearingPosition directly the response time became 2 seconds. (See
new code below)

Are table variables that slow? Should they be avoided whenever
possible? Is there any way to speed them up?

I want to thank everyone who made suggestions on this issue.

- Jason

DECLARE @.TradeDate DATETIME

-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
SELECT
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@.TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @.TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @.TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]|||Jason (JayCallas@.hotmail.com) writes:
> After further testing it seems that just the act of making a query
> against @.PriceTable is causing the slow performance. Putting an index
> on ClearingTrade.TradeDate made no perceptible difference.

In the query you refer to ClearingTrade.TradeTimestamp. If you have
a column ClearingTrade.TradeDate which holds the value of TradeTimestamp
with the time portion cleared, you should probably use this column
insteatd in the query.

This is not the least important if the index you added is non-clustered.
It would however make sense to have the clsutered index on a historical
table on a date or datetime column.

> But when I removed the variable @.PriceTable and included the reference
> to ClearingPosition directly the response time became 2 seconds. (See
> new code below)
> Are table variables that slow? Should they be avoided whenever
> possible? Is there any way to speed them up?

No, table variables are not inherently slow. I had a performance problem
a couple of weeks ago that I was able to solve by replacing a temp
table with a table variable.

Table variable does however not have any statistics. Therefore the
assumptions that SQL Server makes when it builds the query plan for
a table variable may not be accurate.

Exactly what happened in you case, I don't know, since I don't know
how your tables look like, which indexes they have and much
data they contain, and the distribution of that data.

But I'm fairly certain that you get different query plans for slow
and fast queries, and study of these query plans may lead to an
understanding if what's happening.

One important factor here is that with a non-clustered index, it is not
always a good idea to use the index. If there are too many hits in
the index, SQL Server will have to go to the same data page more than
once. Thus, a table scan may be better. Or the optimizer may think so.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query Performance

I am having an issue with a stored procedure I wrote. The stored procedure executes pretty fast if I am calling it once or twice. However my application requires to call the same stored procedure constantly. When called constantly, the CPU spikes at 100% . If I terminate the call, the CPU comes back down to 0%.

To test what could be causing the issue, I put a wrote a "while loop" in SQL to call my stored procedure 1000 times and I was able to narrow the issue to one line.

Select numID from StudentInfo where FirstName = 'FirstName'

Note:

The StudentInfo table has over 1.5 million records.

numID is Primary Key of the table.

To address the issue, I tried to add an index (non clustered) to the "FirstName" column but I cannot see any significant difference. The CPU runs at 95% - 100% even after adding an index to the FirstName column.

If I remove the above line of code, the remaining code in the stored procedure runs at 40% CPU utilisation.

How can I run the above query without having to spike the CPU utilisation?

Thanks in Advance!!

Please post the procedure code.

|||

Could you try

Select numID from StudentInfo WITH (NOLOCK)

where FirstName = 'FirstName'

and see if there is any difference?

|||

Check the execution plan for this query. Whether its index seek or scan. i think this is only part of a join. in that case post the whole script as well as tell us is there any bookmark lookup invloveled. NOLOCK Hint should be applied very carefuly and its nothing but READUncommited issolation level for that statement. there may be dirty read.

Madhu

|||

I would first check the query plan to make sure that the index is even being used. Interesting query - WHERE clause on FirstName. At any rate, you need to dig into this index further; quite possibly you might have to change this up a bit. Look at various system views and procs - sp_lock, sysprocesses, etc. - to see the degree of locking and waits.

www.texastoo.com/sqlblog

|||

Hi All,

i agree with Lee. Really execution plan will help you.

did u tried to create an index that match the search query

find below steps of choosing the most 10 missing indexes

The following query will get the 10 missing indexes would produce the highest anticipated cumulative improvement, in descending order, for user queries.

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

You can get the missing index details in the following way:

The following query determines which missing indexes comprise a particular missing index group, and displays their column details.

For the sake of this example, the missing index group handle is 24.(You will need to change the handle value with handle values which comes up from the earlier query)

SELECT migs.group_handle, mid.*

FROM sys.dm_db_missing_index_group_stats migs

INNER JOIN sys.dm_db_missing_index_groups mig

ON (migs.group_handle = mig.index_group_handle)

INNER JOIN sys.dm_db_missing_index_details mid

ON (mig.index_handle = mid.index_handle)

WHERE migs.group_handle = 24 <<put your handle value here>>

For details on this refer to the following articles:

http://msdn2.microsoft.com/en-us/library/ms345421.aspx

Using Missing Index Information to Write CREATE INDEX Statements

http://msdn2.microsoft.com/en-us/library/ms345405.aspx

|||

Check the execution plan. Also could you try updating the statistics and running DTA.

HTH

Vishal

|||

Hi all I am looking for a SP to get this requirement:

Identify queries that are taking a long time to run on our server. What is the query, how long it took, what is the user name, what is the client machine it ran from.

thanks|||SQL Server 2005 Profiler , select appropriate coulms and events to find out expensive quieries should help.|||

I can do it from Profiler

but i need a SP / Query to do the same or filter the query as per the columns required

waiting for the reply

|||why not run the profiler with the relevant options and save it to a table - then you can run queries on that table for the info you require.................

query parameter

I have to create a stored procedure where the criteria is: "All", specific value and, all except one value

so far this is what I have:

@.Status varchar (50) -- as parameter from a dropdown box

DECLARE @.NewStatusvarchar(50)SET @.NewStatus =CASEWHEN @.Status ='All'AND @.Status <>'All but closed'THEN'%'WHEN @.Status <>'All'AND @.Status <>'All but closed'THEN @.StatusENDand in the storedprocedure.......WHERE Statuslike @.NewStatus

I am a little confused as to how could I return all values except those that have the value "Closed"

Thanks.

in your where clause just add

AND status <> 'closed' or if you had multiple status' status: not in (x, y, z) if that is not helpful, give me a little more info and i will try and help you some more...--jp

|||

Finally I came up with this solution:

DECLARE @.NewStatusvarchar(50)SET @.NewStatus =CASEWHEN @.Status <>'All'AND @.Status <>'All but closed'THEN @.Statuselse'%'ENDDECLARE @.NewStatusNotvarchar(50)SET @.NewStatusNot =CASE @.StatusWHEN'All but closed'THEN'Closed'else'zz'END.........WHEREStatus.Statuslike @.NewStatusANDStatus.Statusnot like @.NewStatusNot

Not sure if this the best solution but it works

It is the same idea that you have suggested.

Query output into a file

Hi,
In a stored procedure how do I output the result of a query to a text file?
Regards,
Bharathram GIt depends on which database engine (DB2, Microsoft, Oracle, etc) you are using, and what language was used to write the stored procedure.

-PatP|||Hi,
The database is SQLSERVER2000 and it uses T-SQL.
Bharathram|||Hi,

In a stored procedure how do I output the result of a query to a text file?

Regards,

Bharathram G
T-SQL by itself has no support for saving the output of queries/stored procedures to text files. But you could achieve this using the command line utilities like isql.exe and osql.exe. You could either invoke these exe files directly from command prompt/batch files or from T-SQL using the xp_cmdshell command. Here are the examples:

From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"

From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'

Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part of a scheduled job, can also be saved to a text file.

BCP and Data Transformation Services (DTS) let you export table data to text files.

I hope this will clear your doubts.
Rudra

query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

query or stored procedure to insert values into pr. key field

Hello,
I have an existing table with the following fields:
tbl_users: this table has all the data
userd_id(primary key)
user_first_name
user_last_name
and the second table:
tbl_notes: this table may or may not have a matching record with
tbl_users
user_id(primary key)
user_notes
user_notices
My question is: How to design a query(or a stored procedure perhaps) on
both tables that will automatically inserts primary key user_id into
tbl_notes from tbl_users when a there is no record in tbl_notes with
such key. I suspect it is something basic probably, i just can't think
of anything.
tbl_users:
user_id user_first_name user_last_name
1 bob dole
2 jim bob
tbl_notes:
user_id user_notes user_notices
1 blah blah
and when query is run I would hope to see the following happen.
user_id user_first_name user_last_name
user_id(shown twice for explanation purposes) user_notes
user_notices
1 bob dole
1
blah blah
2 jim bob
2 (this val is inserted into the user_id field)
bubbahotep wrote:
> Hello,
> I have an existing table with the following fields:
> tbl_users: this table has all the data
> userd_id(primary key)
> user_first_name
> user_last_name
> and the second table:
> tbl_notes: this table may or may not have a matching record with
> tbl_users
> user_id(primary key)
> user_notes
> user_notices
> My question is: How to design a query(or a stored procedure perhaps) on
> both tables that will automatically inserts primary key user_id into
> tbl_notes from tbl_users when a there is no record in tbl_notes with
> such key. I suspect it is something basic probably, i just can't think
> of anything.
> tbl_users:
> user_id user_first_name user_last_name
> 1 bob dole
> 2 jim bob
> tbl_notes:
> user_id user_notes user_notices
> 1 blah blah
> and when query is run I would hope to see the following happen.
> user_id user_first_name user_last_name
> user_id(shown twice for explanation purposes) user_notes
> user_notices
> 1 bob dole
> 1
> blah blah
> 2 jim bob
> 2 (this val is inserted into the user_id field)
If the tables share the same key and will be populated with the same
user_ids then why not just one table instead of 2?
Try:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||the problem is that tbl_users is refreshed almost constantly and
resides on a mainfraime over which i have no control and subsequently
cannot add or change anything. We have some information that strictly
belongs to our department and we use access to import tables from the
sql server on which mainfraim snapshot resides. I was going to design a
form that used a query to connect local info with the info from the
mainfraim table. trouble is that some users don't exist because new
ones are added without our control. If i can write a query that does
these two things(insert nonexisting user_id's and create a form based
on that query) it would be totally cool.
|||bubbahotep (dpodkuik@.gmail.com) writes:
> the problem is that tbl_users is refreshed almost constantly and
> resides on a mainfraime over which i have no control and subsequently
> cannot add or change anything. We have some information that strictly
> belongs to our department and we use access to import tables from the
> sql server on which mainfraim snapshot resides. I was going to design a
> form that used a query to connect local info with the info from the
> mainfraim table. trouble is that some users don't exist because new
> ones are added without our control. If i can write a query that does
> these two things(insert nonexisting user_id's and create a form based
> on that query) it would be totally cool.
The query that David suggested:
INSERT INTO tbl_notes (user_id, user_notes, user_notices)
SELECT user_id, '', ''
FROM tbl_users AS U
WHERE NOT EXISTS
(SELECT *
FROM tbl_notes
WHERE user_id = U.user_id);
does precisely the first thing you are asking for.
As for creating forms - that's probably a question for a forum for
whatever tool you are creating your forms.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Query Or Stored procedure

Should I use query or stored procedure to get results fast.

Your question is very unspecific. Usually the majority of the query time will be returning large volumes of data.

Having a stored procedure is usefull for complex data retrieval or if you want to perform other actions such as logging and for having a precompiled query execution plan.

Personally I prefer to just wrap up my report query in a view to keep the logic out of the report and to make updates via SQL.

I see no harm in using a stored proc though.

|||

Stored Procedures are best.Check out this Link

http://www.sql-server-performance.com/stored_procedures.asp

Regards

Raj Deep.A

Query optimizer issue

Hi,
I have the following problem:
When I call a stored procedure from a COM+ application I get a different query plan than the one I get when calling the same stored procedure within Query Analyzer. The interesting side effect of it is that the COM+ application runs faster as I expected,
since the query takes much longer to execute in Query Analyzer.
I have noticed that the query plan differs, when I call my SP from COM+. Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
One of the cases is the fact that when I run the query in QA, the plan contains a lookup WITH PREFETCH, which takes longer than without prefetch (COM+) case.
I don't know what can be the factor that causes two different query plans for the same stored procedure...
On the other hand, I use an index hint in one of my queries and I noticed that if I don't use the hint, when running in QA the optimizer will use the wrong index, but when the same SP is called from my COM+ app, the optimizer chooses the right index even
if I don't use the hint.
Does anyone have any ideas, thoughts, hints on this?
I'm really confused... :-)
Thanks in advance,
Florin
A lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
The reason for the performance difference stems from a feature called
"parameter sniffing". Consider a stored proc defined as follows:
CREATE PROC proc1 @.p1 int AS
SELECT * FROM table1 WHERE c1 = @.p1
GO
Keep in mind that the server has to compile a complete execution plan for
the proc before the proc begins to execute. In 6.5, at compile time SQL
didn't know what the value of @.p1 was, so it had to make a lot of guesses
when compiling a plan. Suppose all of the actual parameter values for
"@.p1 int" that a user ever passed into this stored proc were unique
integers that were greater than 0, but suppose 40% of the [c1] values in
[table1] were, in fact, 0. SQL would use the average density of the
column to estimate the number of rows that this predicate would return;
this would be an overestimate, and SQL would might choose a table scan
over an index seek based on the rowcount estimates. A table scan would
be the best plan if the parameter value was 0, but unfortunately it
happens that users will never or rarely pass @.p1=0, so performance of the
stored proc for more typical parameters suffers.
In SQL 7.0 or 2000, suppose you executed this proc for the first time
(when the sp plan is not in cache) with the command "EXEC proc1 @.p1 =
10". Parameter sniffing allows SQL to insert the known value of
parameter @.p1 into the query at compile time before a plan for the query
is generated. Because SQL knows that the value of @.p1 is not 0, it can
compile a plan that is tailored to the class of parameters that is
actually passed into the proc, so for example it might select an index
seek instead of a table scan based on the smaller estimated rowcount --
this is a good thing if most of the time 0 is not the value passed as
@.p1. Generally speaking, this feature allows more efficient stored proc
execution plans, but a key requirement for everything to work as expected
is that the parameter values used for compilation be "typical".
In your case, the problem is that you have default NULL values for your
parameters ("@.Today DATETIME = NULL, ...") that are not typical because
the parameter values are changed inside the stored proc before they are
used -- as a result NULL will never actually be used to search the
column. If the first execution of this stored proc doesn't pass in an
explicit value for the @.Today parameter, SQL believes that its value will
be NULL. When SQL compiles the plan for this sp it substitutes NULL for
each occurrence of @.Today that is embedded within a query.
Unfortunately, after execution begins the first thing the stored proc
does is change @.Today to a non-NULL value if it is found to be NULL, but
unfortunately SQL doesn't know about this at compile time. Because NULL
is a very atypical parameter value, the plan that SQL generates may not
be a good one for the new value of the parameter that is assigned at
execution time.
So, the bottom line is that if you assign defaults to your sp parameters
and later use those same parameters in a query, the defaults should be
"typical" because they will be used during plan generation. If you must
use defaults and business logic dictates that they be atypical (as may be
the case here if app modifications are not an option), there are two
possible solutions if you determine that the substitution of atypical
parameter values is causing bad plans:
1. "Disable" parameter sniffing by using local DECLARE'd variables that
you SET equal to the parameters inside the stored proc, and use the local
variables instead of the offending parameters in the queries. This is the
solution that you found yourself. SQL can't use parameter sniffing in
this case so it must make some guesses, but in this case the guess based
on average column density is better than the plan based on a specific but
"wrong" parameter value (NULL).
2. Nest the affected queries somehow so that they run within a different
context that will require a distinct execution plan. There are several
possibilities here. for example:
a. Put the affected queries in a different "child" stored proc. If
you execute that stored proc within this one *after* the parameter @.Today
has been changed to its final value, parameter sniffing will suddenly
become your friend because the value SQL uses to compile the queries
inside the child stored proc is the actual value that will be used in the
query.
b. Use sp_executesql to execute the affected queries. The plan won't
be generated until the sp_executesql stmt actually runs, which is of
course after the parameter values have been changed.
c. Use dynamic SQL ("EXEC (@.sql)") to execute the affected queries.
An equivalent approach would be to put the query in a child stored proc
just like 2.a, but execute it within the parent proc with EXEC WITH
RECOMPILE.
Option #1 seems to have worked well for you in this case, although
sometimes one of the options in #2 is a preferable choice. Here are some
guidelines, although when you're dealing with something as complicated as
the query optimizer experimentation is often the best approach <g>:
- If you have only one "class" (defined as values that have similar
density in the table) of actual parameter value that is used within a
query (even if there are other classes of data in the base table that are
never or rarely searched on), 2.a. or 2.b is probably the best option.
This is because these options permit the actual parameter values to be
used during compilation which should result in the most efficient query
plan for that class of parameter.
- If you have multiple "classes" of parameter value (for example, for
the column being searched, half the table data is NULL, the other half
are unique integers, and you may do searches on either class), 2.c can be
effective. The downside is that a new plan for the query must be
compiled on each execution, but the upside is that the plan will always
be tailored to the parameter value being used for that particular
execution. This is best when there is no single execution plan that
provides acceptable execution time for all classes of parameters.
HTH -
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Andrew J. Kelly SQL MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin

Query optimizer issue

Hi,
I have the following problem:
When I call a stored procedure from a COM+ application I get a different que
ry plan than the one I get when calling the same stored procedure within Que
ry Analyzer. The interesting side effect of it is that the COM+ application
runs faster as I expected,
since the query takes much longer to execute in Query Analyzer.
I have noticed that the query plan differs, when I call my SP from COM+. Doe
s anybody know why? Are there any magic/secrets flags, that COM+ sets?
One of the cases is the fact that when I run the query in QA, the plan conta
ins a lookup WITH PREFETCH, which takes longer than without prefetch (COM+)
case.
I don't know what can be the factor that causes two different query plans fo
r the same stored procedure...
On the other hand, I use an index hint in one of my queries and I noticed th
at if I don't use the hint, when running in QA the optimizer will use the wr
ong index, but when the same SP is called from my COM+ app, the optimizer ch
ooses the right index even
if I don't use the hint.
Does anyone have any ideas, thoughts, hints on this?
I'm really confused... :-)
Thanks in advance,
FlorinA lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
The reason for the performance difference stems from a feature called
"parameter sniffing". Consider a stored proc defined as follows:
CREATE PROC proc1 @.p1 int AS
SELECT * FROM table1 WHERE c1 = @.p1
GO
Keep in mind that the server has to compile a complete execution plan for
the proc before the proc begins to execute. In 6.5, at compile time SQL
didn't know what the value of @.p1 was, so it had to make a lot of guesses
when compiling a plan. Suppose all of the actual parameter values for
"@.p1 int" that a user ever passed into this stored proc were unique
integers that were greater than 0, but suppose 40% of the [c1] values in
[table1] were, in fact, 0. SQL would use the average density of the
column to estimate the number of rows that this predicate would return;
this would be an overestimate, and SQL would might choose a table scan
over an index seek based on the rowcount estimates. A table scan would
be the best plan if the parameter value was 0, but unfortunately it
happens that users will never or rarely pass @.p1=0, so performance of the
stored proc for more typical parameters suffers.
In SQL 7.0 or 2000, suppose you executed this proc for the first time
(when the sp plan is not in cache) with the command "EXEC proc1 @.p1 =
10". Parameter sniffing allows SQL to insert the known value of
parameter @.p1 into the query at compile time before a plan for the query
is generated. Because SQL knows that the value of @.p1 is not 0, it can
compile a plan that is tailored to the class of parameters that is
actually passed into the proc, so for example it might select an index
seek instead of a table scan based on the smaller estimated rowcount --
this is a good thing if most of the time 0 is not the value passed as
@.p1. Generally speaking, this feature allows more efficient stored proc
execution plans, but a key requirement for everything to work as expected
is that the parameter values used for compilation be "typical".
In your case, the problem is that you have default NULL values for your
parameters ("@.Today DATETIME = NULL, ...") that are not typical because
the parameter values are changed inside the stored proc before they are
used -- as a result NULL will never actually be used to search the
column. If the first execution of this stored proc doesn't pass in an
explicit value for the @.Today parameter, SQL believes that its value will
be NULL. When SQL compiles the plan for this sp it substitutes NULL for
each occurrence of @.Today that is embedded within a query.
Unfortunately, after execution begins the first thing the stored proc
does is change @.Today to a non-NULL value if it is found to be NULL, but
unfortunately SQL doesn't know about this at compile time. Because NULL
is a very atypical parameter value, the plan that SQL generates may not
be a good one for the new value of the parameter that is assigned at
execution time.
So, the bottom line is that if you assign defaults to your sp parameters
and later use those same parameters in a query, the defaults should be
"typical" because they will be used during plan generation. If you must
use defaults and business logic dictates that they be atypical (as may be
the case here if app modifications are not an option), there are two
possible solutions if you determine that the substitution of atypical
parameter values is causing bad plans:
1. "Disable" parameter sniffing by using local DECLARE'd variables that
you SET equal to the parameters inside the stored proc, and use the local
variables instead of the offending parameters in the queries. This is the
solution that you found yourself. SQL can't use parameter sniffing in
this case so it must make some guesses, but in this case the guess based
on average column density is better than the plan based on a specific but
"wrong" parameter value (NULL).
2. Nest the affected queries somehow so that they run within a different
context that will require a distinct execution plan. There are several
possibilities here. for example:
a. Put the affected queries in a different "child" stored proc. If
you execute that stored proc within this one *after* the parameter @.Today
has been changed to its final value, parameter sniffing will suddenly
become your friend because the value SQL uses to compile the queries
inside the child stored proc is the actual value that will be used in the
query.
b. Use sp_executesql to execute the affected queries. The plan won't
be generated until the sp_executesql stmt actually runs, which is of
course after the parameter values have been changed.
c. Use dynamic SQL ("EXEC (@.sql)") to execute the affected queries.
An equivalent approach would be to put the query in a child stored proc
just like 2.a, but execute it within the parent proc with EXEC WITH
RECOMPILE.
Option #1 seems to have worked well for you in this case, although
sometimes one of the options in #2 is a preferable choice. Here are some
guidelines, although when you're dealing with something as complicated as
the query optimizer experimentation is often the best approach <g>:
- If you have only one "class" (defined as values that have similar
density in the table) of actual parameter value that is used within a
query (even if there are other classes of data in the base table that are
never or rarely searched on), 2.a. or 2.b is probably the best option.
This is because these options permit the actual parameter values to be
used during compilation which should result in the most efficient query
plan for that class of parameter.
- If you have multiple "classes" of parameter value (for example, for
the column being searched, half the table data is NULL, the other half
are unique integers, and you may do searches on either class), 2.c can be
effective. The downside is that a new plan for the query must be
compiled on each execution, but the upside is that the plan will always
be tailored to the parameter value being used for that particular
execution. This is best when there is no single execution plan that
provides acceptable execution time for all classes of parameters.
HTH -
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Andrew J. Kelly SQL MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin