Performance Tuning SQL query in Trigger

i am using sql server 2000. I have written update trigger on CORP_CAGE table to log the details in

CORP_CAGE_LOG_HIST table,if any changes in EMP_SEQ_NO column.

please find the structure of CORP_CAGE table:

1.CORP_CAGE_SEQ_NO
2.RECEIVED_DATE
3.EMP_SEQ_NO

CORP_CAGE table is having 50,000 records. the trigger "Check_Update" is fired when i am executing the following

query from application which updates 10,000 records.

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

please find below the trigger,in that, trigger can easily find whether any UPDATE done in EMP_SEQ_NO column by using

UPDATE FUNCTION.
But,when it come to insert part, it takes more time(nearly 1 hour or sometimes it will hang.).For minimum

records,this trigger is working fine.

Create trigger Check_Update ON dbo.CORP_CAGE FOR UPDATE AS
BEGIN
IF UPDATE(EMP_SEQ_NO)
BEGIN
INSERT CORP_CAGE_LOG_HIST
(
CAGE_LOG_SEQ_NUM,
BEFORE_VALUE,
AFTER_VALUE,
ENTRY_USER,
FIELD_UPDATED
)
SELECT
i.CAGE_LOG_SEQ_NUM,
d.RECEIVED_DATE,
i.RECEIVED_DATE,
i.UPDATE_USER,
"EMP_SEQ_NO"
FROM
inserted i,
deleted d
WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM
END

END

please help me on this for performance tuning the below query.

I don't have the schema of your table, which in this case is critical. However, if this statement:

Code Snippet

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

is updating 10,000 records then your join is going to cause an update that is the cross product of 10,000 x 10,000 or 100,000,000 logical records. This cannot be right. Look at your trigger WHERE condition:

Code Snippet

WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM

since you are updating ONLY for SEQ_NO = 111 and you are joining the INSERTED pseudo table -- with 10,000 records -- with the DELETE pseudo table -- also with 10,000 records -- and since all records of the DELETED pseudo and all records of the DELETED pseudo have the same SEQ_NO -- specifically 111 you end up with the cross product. You need to linclude the KEY information as part of the join condition.

sql

Performance question

I have written a stored procedure to obtain businesses in a given zipcode
radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). All
the fields being used in the main stored procedure query are indexed. When I
run the stored procedure for the first time after a long time, it takes a
really long time to return results (2-3 minutes). But any runs after this ar
e
very fast (1 second or less).
Question: Does anyone know why this may be happening (my gut feeling is that
it has to do with indexes being cached in memory, but it would be good if
someone can explain this more clearly). What can I do to make sure that this
query always runs very fast? Is there a way to preload tables or indexes in
the memory as this query is one of the main queries in the application and
will be run a lot.Some more info on this performance issue:
When I run 'estimated execution plan' on the stored procedure in the Query
Analyzer, it seems to be saying there is full table scan on the the table
containing the businesses, even though the zipcode field is indexed.
What does this mean?
THanks,
Ashhad
"Ashhad Syed" wrote:

> I have written a stored procedure to obtain businesses in a given zipcode
> radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). Al
l
> the fields being used in the main stored procedure query are indexed. When
I
> run the stored procedure for the first time after a long time, it takes a
> really long time to return results (2-3 minutes). But any runs after this
are
> very fast (1 second or less).
> Question: Does anyone know why this may be happening (my gut feeling is th
at
> it has to do with indexes being cached in memory, but it would be good if
> someone can explain this more clearly). What can I do to make sure that th
is
> query always runs very fast? Is there a way to preload tables or indexes i
n
> the memory as this query is one of the main queries in the application and
> will be run a lot.
>
>|||On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:

>I have written a stored procedure to obtain businesses in a given zipcode
>radius(eg: Get all chinese restaurants in 10 miles from 94568 zipcode). All
>the fields being used in the main stored procedure query are indexed. When
I
>run the stored procedure for the first time after a long time, it takes a
>really long time to return results (2-3 minutes). But any runs after this a
re
>very fast (1 second or less).
>Question: Does anyone know why this may be happening (my gut feeling is tha
t
>it has to do with indexes being cached in memory, but it would be good if
>someone can explain this more clearly). What can I do to make sure that thi
s
>query always runs very fast? Is there a way to preload tables or indexes in
>the memory as this query is one of the main queries in the application and
>will be run a lot.
>
Hi Ashhad,
Please post the table definitions (as CREATE TABLE statements, including
all constraints and indexes) and the code of the stored proc.
See www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||I will do it later today. Thanks.
"Hugo Kornelis" wrote:

> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||Hugo:
Here is the sql script for all tables and stored procs involved:
Ashhad
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ZIPCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ZIPCodes]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[specialty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[specialty]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[vm_serviceprovider]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[vm_serviceprovider]
GO
CREATE TABLE [dbo].[ZIPCodes] (
[ZIPCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIPType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CityName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CityType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateAbbr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AreaCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Latitude] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Longitude] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[specialty] (
[spid] [int] NULL ,
[law_specialty] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[vm_serviceprovider] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneAreaCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SIC_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SIC_Desc] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MarketingStatement] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[NoOfEmp] [int] NULL ,
[YearsInBusiness] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MobileEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_No] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Expiration] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CSC] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC_Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneNotify] [int] NULL ,
[PhoneNotify_No] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailNotify] [int] NULL ,
[CellNotify] [int] NULL ,
[CellNotify_No] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FaxNotify] [int] NULL ,
[OtherCertification] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[LastReferralDate] [datetime] NULL ,
[Subscriber] [int] NULL ,
[Updated_By] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Last_Updated] [datetime] NULL ,
[timezone] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BusinessHours] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[open_wends] [int] NULL ,
[provide_loner_car] [int] NULL ,
[leadsquota] [int] NULL ,
[pic1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pic2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pic3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PackageID] [int] NULL ,
[websiteurl] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[plan_enrollment_date] [datetime] NULL ,
[next_billing_date] [datetime] NULL ,
[credit] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[adsize] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[frachise] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[msa] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[branch] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sales] [money] NULL ,
[sales_range] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employee_range] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[internalrating] [float] NULL ,
[business_type] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[admin_notes] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[total_calls] [int] NULL ,
[last_call] [datetime] NULL ,
[sendalert] [int] NULL ,
[ph_referredby] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[college] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[marketing_flag] [int] NULL ,
[bar_association] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[monthly_budget] [money] NULL ,
[account_onoff] [int] NULL ,
[call_enabled] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[vm_serviceprovider] WITH NOCHECK ADD
CONSTRAINT [PK_vm_serviceprovider_1] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_ZIPCodes] ON [dbo].[ZIPCodes]([ZIPCode]) ON
[PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_specialty] ON [dbo].[specialty]([zip]) ON
[PRIMARY]
GO
ALTER TABLE [dbo].[vm_serviceprovider] WITH NOCHECK ADD
CONSTRAINT [DF_vm_serviceprovider_total_calls] DEFAULT (0) FOR [total_calls]
GO
CREATE INDEX [IX_vm_serviceprovider_ph] ON
[dbo].[vm_serviceprovider]([Phone]) ON [PRIMARY]
GO
CREATE INDEX [idx_vmsp_username] ON [dbo].[vm_serviceprovider]([UserName])
ON [PRIMARY]
GO
CREATE INDEX [idx_vmsp_password] ON [dbo].[vm_serviceprovider]([Password])
ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_5] ON
[dbo].[vm_serviceprovider]([Company]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_6] ON
[dbo].[vm_serviceprovider]([LName], [FName], [State]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_7] ON
[dbo].[vm_serviceprovider]([LName]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_8] ON
[dbo].[vm_serviceprovider]([Address]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_9] ON
[dbo].[vm_serviceprovider]([City]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_10] ON
[dbo].[vm_serviceprovider]([City]) ON [PRIMARY]
GO
CREATE INDEX [IX_vm_serviceprovider_11] ON
[dbo].[vm_serviceprovider]([Zip]) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[RadiusAssistant]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[RadiusAssistant]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[RadiusAssistant](
@.ZIPCode char(5),
@.Miles decimal(18, 9)
) RETURNS
@.MaxPoints TABLE (
Latitude decimal(10,8),
Longitude decimal(11,8),
MaxLat decimal(10,8),
MinLat decimal(10,8),
MaxLong decimal(11,8),
MinLong decimal(11,8))
AS
BEGIN
DECLARE
@.Latitude decimal(10,8),
@.Longitude decimal(11,8)
SELECT @.Latitude = Latitude,
@.Longitude = Longitude
FROM [dbo].[ZIPCodes]
WHERE ZIPCode = @.ZIPCode
AND CityType = 'D'
IF 0 = @.@.rowcount
RETURN /* invalid zip */
DECLARE @.MilesPerDegree decimal(10,8)
SET @.MilesPerDegree = 69.172
DECLARE
@.MaxLat decimal(10, 8),
@.MinLat decimal(10, 8),
@.MaxLong decimal(11, 8),
@.MinLong decimal(11, 8)
SET @.MaxLat = @.Latitude + @.Miles / @.MilesPerDegree
SET @.MinLat = @.Latitude - (@.MaxLat - @.Latitude)
SET @.MaxLong = @.Longitude + @.Miles / (Cos(@.MinLat * PI() / 180) *
@.MilesPerDegree)
SET @.MinLong = @.Longitude - (@.MaxLong - @.Longitude)
INSERT @.MaxPoints
SELECT @.Latitude, @.Longitude, @.MaxLat, @.MinLat, @.MaxLong, @.MinLong
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[RadiusSearch3]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[RadiusSearch3]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[RadiusSearch3]
@.ZIPCode char(5),
@.Miles decimal(11, 6),
@.BusinessType varchar(50)
AS
/*'<A href=''http://www.mapquest.com/maps/map.adp?address='+ IsNUll(
t.Address,'') + '&city='+ t.City +'&state='+ t.state +'&zipcode='+ t.zip
+'&country=US&cid=lfmaplink'' target=_blank> Map </A>'*/
SELECT top 100 t.id, t.marketing_flag,
[dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) as
distance, '<b>' + t.FNAME + ' '+ t.LNAME + '</b>, ' + t.COMPANY as
companyname, '<b>' + t.FNAME + ' '+ t.LNAME + '</b><br>' + t.COMPANY +
'<br>' + t.ADDRESS + '<br>' + t.CITY + ', '+ t.State + ' ' + t.ZIP +
'<A href=''http://maps.google.com/maps?f=q&hl=en&q='+ IsNUll(
t.Address,'') + ', '+ t.City +', '+ t.state +' '+ t.zip +'''
target=_blank> Map </A>'+ '<b>'+ cast (cast
([dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) as
dec (5, 1)) as varchar) + ' miles away </b><br><br>' +
'<b> Attorney Experience: </b>'+ cast ((DATEPART(yyyy, GETDATE())-
t.YearsInBusiness) as varchar)+ ' years' + '<b> Firm Size: </b>'+
CASE
WHEN NoOfEmp IS NULL THEN 'Not Known'
WHEN NoOfEmp <= 5 THEN 'Small'
WHEN NoOfEmp> 5 and noOfEmp < 30 THEN 'Medium'
WHEN NoOfEmp>= 30 THEN 'Large'
ELSE 'Not Known'
END + '</b><br><br>' + substring ( IsNull( t.MarketingStatement, ''),
1, 200) + '<A href=SP.aspx?ID='+ convert(varchar,t.ID) + ' target=_blank>
More </A>' + '<br><br><b>PRACTICE FOCUS:</b><br>' + t.business_type as
[Attorney Info],
CASE call_enabled
WHEN 'Y' THEN '<A class=link href=CallAttorney.aspx?ID=' +
cast(t.id as varchar) + ' target=_blank> CLICK HERE TO CALL! </A>'
ELSE 'Call feature not available'
END as [call]
/* add any other fields here */
/* Distance Assistant required */
FROM [dbo].[ZIPCodes] z,
[dbo].[RadiusAssistant](@.ZIPCode, @.Miles) r
, [dbo].[specialty] u
, [dbo].[vm_serviceprovider] t
/* if you wanted to join this with your stores/dealers/users table simply
add a line of code here */
/* and see below for the other line of code */
/*
[dbo].[vm_serviceprovider] t
*/
WHERE 1=1
AND z.Latitude BETWEEN r.MinLat AND r.MaxLat
AND z.Longitude BETWEEN r.MinLong AND r.MaxLong
AND z.CityType = 'D' /* only one result per ZIP */
AND z.ZipType <> 'M' /* don't include "Military" ZIPs which don't have a
lat/long */
/* SQL caches the result of this function - so performance is not impacted
even though we run it twice */
AND
[dbo].[DistanceAssistant](z.Latitude,z.Longitude,r.Latitude,r.Longitude) <=
@.Miles
AND u.ZIP = z.ZIPCode
AND u.law_specialty = @.BusinessType
AND t.id = u.spid
/* dealer locator join continued */
/* match the ZIP Code Download ZIPCode column to the ZIP Code column on the
users/stores/dealers table */
/*
AND u.ZIPCode = z.ZIPCode
*/
ORDER BY
t.marketing_flag desc, distance asc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Also the name of the procedure being used to return businesses in a zipcode
radius is Radius3
"Hugo Kornelis" wrote:

> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||I managed to solve the problem. Thanks for your offer to look into this.
"Hugo Kornelis" wrote:

> On Fri, 7 Apr 2006 10:29:01 -0700, Ashhad Syed wrote:
>
> Hi Ashhad,
> Please post the table definitions (as CREATE TABLE statements, including
> all constraints and indexes) and the code of the stored proc.
> See www.aspfaq.com/5006.
> --
> Hugo Kornelis, SQL Server MVP
>|||u need an index on the lattitudes, not the zipcodes.
you are selecting WHERE lattitude.
regards,
doug

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

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related
. The third party system we have is archaic and code is written for SQL Serv
er 6.5. It has indices for multiple fields and all the data and index are in
one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The
system created numerous table locks and one part of the system totally locks
out if a user is deleting from a table and other users are trying to access
the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we h
ave more than 1100 connections. Since its a third party tool we have limited
control over code change and they never do any support. The management is a
sking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices al
so into a file. Move the tempdb into another file and keep in a different ra
id array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almos
t 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Se
arches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results
. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Performance monitoring tools

Hi,
We have performance issues in our system and it is mainly sql server related. The third party system we have is archaic and code is written for SQL Server 6.5. It has indices for multiple fields and all the data and index are in one file. The size s almost 50 GB, most of them occupied by CHAR field without any valuable data. The system created numerous table locks and one part of the system totally locks out if a user is deleting from a table and other users are trying to access the system (TABLOCK s). Searches take long time and system is crawling at peak time (2-3 pm) when we have more than 1100 connections. Since its a third party tool we have limited control over code change and they never do any support. The management is asking us for better results. In the immediate future we want to buy a monitoring tool.
Does anybody know the best monitoring tool around?
We are planning to split the data file into multiple files. Split indices also into a file. Move the tempdb into another file and keep in a different raid array. Please help me with suggestions to improve the server setup.
Thanks. Sorry for writing a long mail because this is getting serious.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Hi,
Are you executing UPDATE STATISTICS on al tables inside the database. If
not please plan for that. As well as use the
DBCC SHOWCONTIG to identify the tables fragmented and use DBCC DBREINDEX to
remove the fragmentation. THis will increase
the performace of you database.
You could use the NT performance monitor tools to identify the DISK QUEUE
LENGTH / DISK (I/O), CPU USage , Memory usage.
If yur Disk I/O is very huge plan to split the fkes in to a diffrent RAID
array.
see the link http://www.sql-server-performance.com/ and check for
PERFORMANCE MONITOR. There you have articles for each counters and usage.
Thanks
Hari
MCDBA
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||sounds like there may be indexing problems...
Try using the index tuning wizard inside SQL profiler. It should be
documented in Books on line... If there are locking, table scan issues, the
physical stuff you are doing might not make much difference.
You can create new indexes ( if you discover it is necessary) without
interfering with the third party company's code...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ivnavin> wrote in message news:O2AnvurcEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have performance issues in our system and it is mainly sql server
related. The third party system we have is archaic and code is written for
SQL Server 6.5. It has indices for multiple fields and all the data and
index are in one file. The size s almost 50 GB, most of them occupied by
CHAR field without any valuable data. The system created numerous table
locks and one part of the system totally locks out if a user is deleting
from a table and other users are trying to access the system (TABLOCK s).
Searches take long time and system is crawling at peak time (2-3 pm) when we
have more than 1100 connections. Since its a third party tool we have
limited control over code change and they never do any support. The
management is asking us for better results. In the immediate future we want
to buy a monitoring tool.
> Does anybody know the best monitoring tool around?
> We are planning to split the data file into multiple files. Split indices
also into a file. Move the tempdb into another file and keep in a different
raid array. Please help me with suggestions to improve the server setup.
> Thanks. Sorry for writing a long mail because this is getting serious.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.