Performance problem

Hi All,
I'm investigating a performance problem and I don't really know if the
following stored proc is ok or if it could be written otherwise. One of the
parameter is a list of eventypes in XML used as filter. If the xml is empty,
then it means "all events", explaining the reason of the second part of the
where clause.
Another way is to use a join with the tempory table but then I still need to
deal with the "all events" case with a UNION or a test on the emptiness of
the temporary table
My question is : is it ok as it is written even in case of large tables ?
How could it be written otherwise ?
PS : the xml passed as parameter is always relatively short, containing only
max 30 entries.
Here is the SP :
CREATE PROCEDURE [dbo].[anet]
@.NetID uniqueidentifier
,@.EventTypesXML ntext
AS
--=====================
-- Prepare event types
--=====================
DECLARE @.docEventTypes int
EXEC sp_xml_preparedocument @.docEventTypes OUTPUT, @.EventTypesXML
SET NOCOUNT ON
DECLARE @.RMC_EventTypes TABLE(
EVNT_RT_EVTY smallint NOT NULL)
INSERT INTO @.RMC_EventTypes
SELECT EVNT_RT_EVTY
FROM OPENXML (@.docEventTypes, 'EventTypes/EventType')
WITH (EVNT_RT_EVTY smallint '@.number')
SET NOCOUNT OFF
EXEC sp_xml_removedocument @.docEventTypes
--=========================
-- Retrieve requested data
--=========================
SET ROWCOUNT 100
SELECT EVNT_RT_EVTY AS EvntType
,ISNULL(PROD_CUSTNAME, PROD_RT_PRTY) AS ProdName
FROM Event AS RMCEvent
INNER JOIN Product ON ((PROD_ID = EVNT_LT_PROD) AND (PROD_ID = @.NetID))
WHERE (EXISTS ( -- Filter on event types
SELECT 1
FROM @.RMC_EventTypes AS RMC_EventTypes
WHERE (RMCEvent.EVNT_RT_EVTY = RMC_EventTypes.EVNT_RT_EVTY)
)
OR NOT EXISTS (
SELECT 1
FROM @.RMC_EventTypes AS RMC_EventTypes
WHERE (RMCEvent.EVNT_RT_EVTY <> RMC_EventTypes.EVNT_RT_EVTY)
)
)
ORDER BY EVNT_TIMESTAMP DESC
FOR XML AUTO, ELEMENTS
SET ROWCOUNT 0
GO"Christian Staffe" <x@.y.z> wrote in message
news:43ca2b25$0$29458$ba620e4c@.news.skynet.be...
> Hi All,
> I'm investigating a performance problem and I don't really know if the
> following stored proc is ok or if it could be written otherwise. One of
> the parameter is a list of eventypes in XML used as filter. If the xml is
> empty, then it means "all events", explaining the reason of the second
> part of the where clause.
> Another way is to use a join with the tempory table but then I still need
> to deal with the "all events" case with a UNION or a test on the emptiness
> of the temporary table
> My question is : is it ok as it is written even in case of large tables ?
> How could it be written otherwise ?
>
For performance it is generally better to test for the "all events" case
with an IF statement and run a different query. That way the "all events"
case and the "some events" case can be compiled and optimized seperately.
When you cram both into one query you will probably get a pretty expensive
plan.
David|||Christian Staffe (x@.y.z) writes:
> I'm investigating a performance problem and I don't really know if the
> following stored proc is ok or if it could be written otherwise. One of
> the parameter is a list of eventypes in XML used as filter. If the xml
> is empty, then it means "all events", explaining the reason of the
> second part of the where clause.
> Another way is to use a join with the tempory table but then I still
> need to deal with the "all events" case with a UNION or a test on the
> emptiness of the temporary table
> My question is : is it ok as it is written even in case of large tables ?
> How could it be written otherwise ?
That NOT EXISTS bit certainly looks weird. Simpler would be:
SELECT EVNT_RT_EVTY AS EvntType
,ISNULL(PROD_CUSTNAME, PROD_RT_PRTY) AS ProdName
FROM Event AS RMCEvent
INNER JOIN Product ON ((PROD_ID = EVNT_LT_PROD) AND (PROD_ID = @.NetID))
WHERE (EXISTS ( -- Filter on event types
SELECT 1
FROM @.RMC_EventTypes AS RMC_EventTypes
WHERE (RMCEvent.EVNT_RT_EVTY = RMC_EventTypes.EVNT_RT_EVTY)
)
OR NOT EXISTS (SELECT 1 FROM @.RMC_EventTypes)
ORDER BY EVNT_TIMESTAMP DESC
FOR XML AUTO, ELEMENTS
But I don't know what effects that would have on performance.
Are the event types defined in a lookup table somewhere? How many
are they? One alternative would be to fill the table variable with
from the lookup table if the XML document is empty.
Best bet for performance, though, is to have separate queries for
the two cases.
Also, I don't know would would happen if you replaced SET ROWCOUNT
with a SELECT TOP.
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|||OK, here is the solution I came with following the two comments that were
made here. I hope it's ok (and better !)
Side question : after the result set, I also receive this when the xml is
empty, that is, when passing '<EventTypes/> :
[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server
Can anyone tell me what it means ?
CREATE PROCEDURE [dbo].[anet2]
@.NetID uniqueidentifier
,@.EventTypesXML ntext
AS
--=====================
-- Prepare event types
--=====================
DECLARE @.docEventTypes int
EXEC sp_xml_preparedocument @.docEventTypes OUTPUT, @.EventTypesXML
SET NOCOUNT ON
DECLARE @.RMC_EventTypes TABLE(
EVNT_RT_EVTY smallint NOT NULL)
INSERT INTO @.RMC_EventTypes
SELECT EVNT_RT_EVTY
FROM OPENXML (@.docEventTypes, 'EventTypes/EventType')
WITH (EVNT_RT_EVTY smallint '@.number')
SET NOCOUNT OFF
EXEC sp_xml_removedocument @.docEventTypes
--=========================
-- Retrieve requested data
--=========================
SET ROWCOUNT 100
IF NOT EXISTS (SELECT 1 FROM @.RMC_EventTypes)
BEGIN
SELECT RMCEvent.EVNT_RT_EVTY AS EvntType
,ISNULL(PROD_CUSTNAME, PROD_RT_PRTY) AS ProdName
FROM Event AS RMCEvent
INNER JOIN Product ON ((PROD_ID = EVNT_LT_PROD) AND (PROD_ID = @.NetID))
ORDER BY EVNT_TIMESTAMP DESC
FOR XML AUTO, ELEMENTS
END
ELSE
BEGIN
SELECT RMCEvent.EVNT_RT_EVTY AS EvntType
,ISNULL(PROD_CUSTNAME, PROD_RT_PRTY) AS ProdName
FROM Event AS RMCEvent
INNER JOIN Product ON ((PROD_ID = EVNT_LT_PROD) AND (PROD_ID = @.NetID))
INNER JOIN @.RMC_EventTypes ET ON ET.EVNT_RT_EVTY = RMCEvent.EVNT_RT_EVTY
ORDER BY EVNT_TIMESTAMP DESC
FOR XML AUTO, ELEMENTS
END
SET ROWCOUNT 0
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974CA18CE3122Yazorman@.127.0.0.1...
> Christian Staffe (x@.y.z) writes:
> That NOT EXISTS bit certainly looks weird. Simpler would be:
> SELECT EVNT_RT_EVTY AS EvntType
> ,ISNULL(PROD_CUSTNAME, PROD_RT_PRTY) AS ProdName
> FROM Event AS RMCEvent
> INNER JOIN Product ON ((PROD_ID = EVNT_LT_PROD) AND (PROD_ID = @.NetID))
> WHERE (EXISTS ( -- Filter on event types
> SELECT 1
> FROM @.RMC_EventTypes AS RMC_EventTypes
> WHERE (RMCEvent.EVNT_RT_EVTY = RMC_EventTypes.EVNT_RT_EVTY)
> )
> OR NOT EXISTS (SELECT 1 FROM @.RMC_EventTypes)
> ORDER BY EVNT_TIMESTAMP DESC
> FOR XML AUTO, ELEMENTS
> But I don't know what effects that would have on performance.
> Are the event types defined in a lookup table somewhere? How many
> are they? One alternative would be to fill the table variable with
> from the lookup table if the XML document is empty.
> Best bet for performance, though, is to have separate queries for
> the two cases.
> Also, I don't know would would happen if you replaced SET ROWCOUNT
> with a SELECT TOP.
> --
> 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|||Christian Staffe (x@.y.z) writes:
> OK, here is the solution I came with following the two comments that were
> made here. I hope it's ok (and better !)
> Side question : after the result set, I also receive this when the xml is
> empty, that is, when passing '<EventTypes/> :
> [Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server
> Can anyone tell me what it means ?
This means that the client got something from SQL Server that did not
comply to the specification of the TDS specification. (TDS is the protocol
that client APIs use to speak with SQL Server.) Or that the client API
is confusion of TDS.
That is, a bug in SQL Server or in the ODBC SQL Server Driver.
Sometimes this message indicates that there was a crash on the SQL
Server side. Have a look at the SQL Server error log, and see if there
is a stack dump that can be correlated with this message.
Unfortunately, the only way to resolve this issue is to change the
procedure to narrow down exactly what causes it. I would first try
removing SET ROWCOUNT 100.
(OK, there is one more way: try applying the latest service pack, in
case the issue has been fixed.)
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