Performance problem  
Author Message
Ox





PostPosted: Sun Jan 15 09:25:34 CST 2006 Top

SQL Server Developer >> 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
--=====================



SET NOCOUNT ON


EVNT_RT_EVTY smallint NOT NULL)


SELECT EVNT_RT_EVTY



SET NOCOUNT OFF


--=========================
-- Retrieve requested data
--=========================
SET ROWCOUNT 100

SELECT EVNT_RT_EVTY AS EvntType
,ISNULL(PROD_CUSTNAME, PROD_RT_PRTY) AS ProdName
FROM Event AS RMCEvent

WHERE (EXISTS ( -- Filter on event types
SELECT 1

WHERE (RMCEvent.EVNT_RT_EVTY = RMC_EventTypes.EVNT_RT_EVTY)
)
OR NOT EXISTS (
SELECT 1

WHERE (RMCEvent.EVNT_RT_EVTY <> RMC_EventTypes.EVNT_RT_EVTY)
)
)
ORDER BY EVNT_TIMESTAMP DESC
FOR XML AUTO, ELEMENTS

SET ROWCOUNT 0
GO

SQL Server6  
 
 
David





PostPosted: Sun Jan 15 09:25:34 CST 2006 Top

SQL Server Developer >> 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 ?
>

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




 
 
Erland





PostPosted: Sun Jan 15 09:52:51 CST 2006 Top

SQL Server Developer >> Performance problem
> 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

WHERE (EXISTS ( -- Filter on event types
SELECT 1

WHERE (RMCEvent.EVNT_RT_EVTY = RMC_EventTypes.EVNT_RT_EVTY)
)

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.

--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
Christian





PostPosted: Sun Jan 15 13:31:05 CST 2006 Top

SQL Server Developer >> Performance problem 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
--=====================



SET NOCOUNT ON


EVNT_RT_EVTY smallint NOT NULL)


SELECT EVNT_RT_EVTY



SET NOCOUNT OFF


--=========================
-- Retrieve requested data
--=========================
SET ROWCOUNT 100


BEGIN
SELECT RMCEvent.EVNT_RT_EVTY AS EvntType
,ISNULL(PROD_CUSTNAME, PROD_RT_PRTY) AS ProdName
FROM Event AS RMCEvent

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


ORDER BY EVNT_TIMESTAMP DESC
FOR XML AUTO, ELEMENTS
END

SET ROWCOUNT 0





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

> WHERE (EXISTS ( -- Filter on event types
> SELECT 1

> WHERE (RMCEvent.EVNT_RT_EVTY = RMC_EventTypes.EVNT_RT_EVTY)
> )

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

>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


 
 
Erland





PostPosted: Sun Jan 15 17:10:24 CST 2006 Top

SQL Server Developer >> Performance problem
> 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.)


--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx