March 4, 2008 at 11:18 am
I have this query that gets called from the code that runs too slow. It initially took 2mins 53 secs. After adding clustered index on State and InActive: columns, it came down to 2 mins 38 secs. I need to get it down even further. This query returns the count of 38462 out of 1229885 records from this table.
SELECT COUNT(*) FROM
( SELECT 24 AS [MODSEQ],
[Incident #] AS [RECSEQ],
0 AS [FORMSEQ],
CAST((CAST(24 as nvarchar)+ CAST([Incident #] as nvarchar)) as float(53)) AS [PRSEQ],
[Incident #] AS [Incident],
[Status Description:] AS [Status],
[Urgency ID:] AS [Urgency ID],
[Incident Description] AS [Incident Description],
[Due Date & Time:] AS [Due Date],
[Client ID] AS [Client ID],
[Subject ID] AS [Category ID],
[Group Name] AS [Group],
[Login ID Assigned To] AS [Assigned To],
[Open Date & Time] AS [Open Date],
[State:] AS [State:],[InActive:] AS [InActive:]
FROM [MAGICSSHD].[Incident] WITH (NOLOCK) ) BASE
WHERE (( [State:] = N'C') AND
( [InActive:] = 0)AND
( [Incident Description] LIKE (N'%test%')) )
For the same above query, I change the State to 'O' that returns 867 records out of 1229885 from this table came down from 1 min 16 secs to only 17-18 secs.
The execution plan shows using the clustered index. What else can I do to improve the performance of the above query to bring it down from 2mins 38 secs.
March 4, 2008 at 11:22 am
Please... take just a minute and at least add some carriage returns to your code...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2008 at 11:34 am
Besides formating your code, we could also use the DDL of the base table, including any and all indexes you have defined.
😎
March 4, 2008 at 11:39 am
Here is the DDL script for the table in the customer's database that they have customized.
CREATE TABLE [_SMDBA_].[_TELMASTE_] (
[SEQUENCE] [int] NOT NULL ,
[LASTMODIFIED] [datetime] NOT NULL ,
[LASTUSER] [nvarchar] (30) NULL ,
[_GROUP_] [int] NULL ,
[_OWNER_] [int] NULL ,
[_OWNERPERMS_] [int] NULL ,
[CLIENT] [int] NULL ,
[STATUS] [nvarchar] (1) NULL ,
[SENT TO] [int] NULL ,
[SUBJECT] [int] NULL ,
[DATE OPEN] [datetime] NOT NULL ,
[CONFIG] [int] NULL ,
[COMPANY] [int] NULL ,
[DEPT] [int] NULL ,
[OPEN BY] [int] NULL ,
[CLOSED BY] [int] NULL ,
[CLOSED ON] [datetime] NULL ,
[PHONE] [nvarchar] (13) NULL ,
[CALLS] [smallint] NULL ,
[CTRPART] [int] NULL ,
[FOLLOWUP] [smallint] NULL ,
[FUDATE] [datetime] NULL ,
[_OWNERGROUP_] [int] NULL ,
[_EMAILID_] [nvarchar] (510) NULL ,
[SEVERITY] [nvarchar] (40) NULL ,
[SLA_ID] [int] NULL ,
[WHITEBOARD_ID] [int] NULL ,
[HD_CCDT01] [datetime] NULL ,
[HD_CCDT02] [datetime] NULL ,
[HD_CCINT01] [int] NULL ,
[HD_CCINT02] [int] NULL ,
[HD_CCTXT01] [nvarchar] (80) NULL ,
[HD_CCTXT02] [nvarchar] (80) NULL ,
[HD_CCTXT03] [nvarchar] (80) NULL ,
[HD_CCTXT04] [nvarchar] (80) NULL ,
[HD_CCTXT05] [nvarchar] (80) NULL ,
[HD_CCTXT06] [nvarchar] (80) NULL ,
[HDTYPE] [nvarchar] (30) NULL ,
[SEQ_ZAC] [int] NULL ,
[MACHINE] [nvarchar] (80) NULL ,
[ACTTYP] [nvarchar] (12) NULL ,
[PRIORITY] [nvarchar] (2) NULL ,
[nvarchar] (7) NULL ,
[CHNGTYPE] [nvarchar] (45) NULL ,
[CHNGIMPRISK] [nvarchar] (6) NULL ,
[ALERTID] [nvarchar] (20) NULL ,
[CAUSECODE] [nvarchar] (11) NULL ,
[CHANGEREQ] [nvarchar] (50) NULL ,
[SEQ_UDSTATUS:] [int] NULL ,
[SEQ_SEVERITY:] [int] NULL ,
[DUE_DATE:] [datetime] NULL ,
[RELNAME] [nvarchar] (75) NULL ,
[PLATFORMTYPE] [nvarchar] (75) NULL ,
[RELTYPE] [nvarchar] (75) NULL ,
[RELCHGTYPE] [nvarchar] (75) NULL ,
[PLATFORMTYPES] [nvarchar] (13) NULL ,
[RELTYPES] [nvarchar] (19) NULL ,
[TYPEOFCHGVAL] [nvarchar] (11) NULL ,
[RELTYPEVAL] [nvarchar] (18) NULL ,
[JOBNAME] [nvarchar] (15) NULL ,
[ABENDCNTR] [smallint] NULL ,
[SSONUM] [nvarchar] (2) NULL ,
[SEQ_PM:] [int] NULL ,
[_INACTIVE_:] [smallint] NOT NULL ,
[CLK_STOPTIME:] [datetime] NULL ,
[SEQ_IMPACT:] [int] NULL ,
[SEQ_PRIORITY:] [int] NULL ,
[DURATION:] [float] NULL ,
[FCR:] [smallint] NULL ,
[PROJDIR] [nvarchar] (40) NULL ,
[PROJMAN] [nvarchar] (40) NULL ,
[SEQ_SSHD_ROUTE] [int] NULL ,
[SUBCLIENT_ID] [nvarchar] (32) NULL ,
[ACCOUNT_#] [nvarchar] (32) NULL ,
[BUILDING] [nvarchar] (32) NULL ,
[ANTICIPATED_START_DATE] [datetime] NULL ,
[PARTTIME_PERSONNEL] [nvarchar] (16) NULL ,
[TYPE_OF_ACTIVITY] [nvarchar] (24) NULL ,
[PMO] [nvarchar] (3) NULL ,
[DATA1] [nvarchar] (75) NULL ,
[ONTIME] [nvarchar] (6) NULL ,
[SCOPE] [nvarchar] (6) NULL ,
[RESOURCES] [nvarchar] (6) NULL ,
[CLIENTREAD] [nvarchar] (6) NULL ,
[CLIENT2] [int] NULL ,
[PMOTYPE] [nvarchar] (14) NULL ,
[DELTASTATUS] [nvarchar] (32) NULL ,
[RELCODE] [nvarchar] (4) NULL ,
[RESPONSE_DATE:] [datetime] NULL ,
[RECOMMENDEDFIX_DATE:] [datetime] NULL ,
[LINK_MSCRM_CASENO] [nvarchar] (50) NULL ,
[HD_CCTXT07:] [nvarchar] (80) NULL ,
[HD_CCTXT08:] [nvarchar] (80) NULL ,
[HD_CCTXT09:] [nvarchar] (80) NULL ,
[HD_CCTXT10:] [nvarchar] (80) NULL ,
[HD_CCTXT11:] [nvarchar] (80) NULL ,
[HD_CCTXT12:] [nvarchar] (80) NULL ,
[HD_CCTXT13:] [nvarchar] (80) NULL ,
[HD_CCTXT14:] [nvarchar] (80) NULL ,
[HD_CCTXT15:] [nvarchar] (80) NULL ,
[OPENED_GROUP:] [nvarchar] (30) NULL ,
[CLOSED_GROUP:] [nvarchar] (30) NULL ,
[CONTACT_TYPE] [nvarchar] (120) NULL ,
[INCIDENTOWNER] [int] NULL ,
[PARENTINCIDENT] [int] NULL ,
[ntext] NULL ,
[DESCRIPTION] [ntext] NULL ,
[RESOLUTION] [ntext] NULL ,
[TOOLS_REQUIRED] [ntext] NULL ,
[MATERIAL_VENDOR_LOG] [ntext] NULL ,
[ANS_SUGGEST] [ntext] NULL ,
[RESP_DATE] [datetime] NULL ,
[SEQ_SERVICE] [int] NULL ,
[SLAMILESTONESCOMPLETE] [smallint] NOT NULL ,
[SNMP_EVENT_ID] [nvarchar] (1000) NULL ,
[FUNCTIONAL_AREA] [nvarchar] (23) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [_dta_index__TELMASTE__c_7_1264409086__K67_K9] ON [_SMDBA_].[_TELMASTE_]([_INACTIVE_:], [STATUS]) ON [PRIMARY]
GO
ALTER TABLE [_SMDBA_].[_TELMASTE_] ADD
CONSTRAINT [DF_TELMASTE_LASTMODIFIED] DEFAULT (getdate()) FOR [LASTMODIFIED],
CONSTRAINT [DF_TELMASTE_LASTUSER] DEFAULT (suser_sname()) FOR [LASTUSER],
CONSTRAINT [DF_TELMASTE_STATUS] DEFAULT ('O') FOR [STATUS],
CONSTRAINT [DF_TELMASTE_DATE OPEN] DEFAULT (getdate()) FOR [DATE OPEN],
CONSTRAINT [DF_TELMASTE_CALLS] DEFAULT (1) FOR [CALLS],
CONSTRAINT [DF_TELMASTE_FOLLOWUP] DEFAULT (0) FOR [FOLLOWUP],
CONSTRAINT [DF_TELMASTE_ACTTYP] DEFAULT ('ACTIVE') FOR [ACTTYP],
CONSTRAINT [DF_TELMASTE_PRIORITY] DEFAULT ('3') FOR [PRIORITY],
CONSTRAINT [DF_TELMASTE_CODE] DEFAULT (N'N/A') FOR ,
CONSTRAINT [DF_TELMASTE_CHNGTYPE] DEFAULT ('N/A') FOR [CHNGTYPE],
CONSTRAINT [DF_TELMASTE_CHNGIMPRISK] DEFAULT (N'N/A') FOR [CHNGIMPRISK],
CONSTRAINT [DF_TELMASTE_CAUSECODE] DEFAULT (N'N/A') FOR [CAUSECODE],
CONSTRAINT [DF_TELMASTE_CHANGEREQ] DEFAULT ('Choose a Requirement') FOR [CHANGEREQ],
CONSTRAINT [DF_TELMASTE_PLATFORMTYPES] DEFAULT ('WEB') FOR [PLATFORMTYPES],
CONSTRAINT [DF_TELMASTE_RELTYPES] DEFAULT ('N/A') FOR [RELTYPES],
CONSTRAINT [DF_TELMASTE_TYPEOFCHGVAL] DEFAULT ('APPLICATION') FOR [TYPEOFCHGVAL],
CONSTRAINT [DF_TELMASTE_RELTYPEVAL] DEFAULT ('SCHEDULED ADHOC') FOR [RELTYPEVAL],
CONSTRAINT [DF_TELMASTE_SSONUM] DEFAULT ('10') FOR [SSONUM],
CONSTRAINT [DF_TELMASTE__INACTIVE_:] DEFAULT (0) FOR [_INACTIVE_:],
CONSTRAINT [DF_TELMASTE_FCR:] DEFAULT (0) FOR [FCR:],
CONSTRAINT [DF_TELMASTE_TYPE_OF_ACTIVITY] DEFAULT ('Service Call') FOR [TYPE_OF_ACTIVITY],
CONSTRAINT [DF_TELMASTE_PMO] DEFAULT ('N/A') FOR [PMO],
CONSTRAINT [DF_TELMASTE_ONTIME] DEFAULT ('N/A') FOR [ONTIME],
CONSTRAINT [DF_TELMASTE_SCOPE] DEFAULT ('N/A') FOR [SCOPE],
CONSTRAINT [DF_TELMASTE_RESOURCES] DEFAULT ('N/A') FOR [RESOURCES],
CONSTRAINT [DF_TELMASTE_CLIENTREAD] DEFAULT ('N/A') FOR [CLIENTREAD],
CONSTRAINT [DF_TELMASTE_PMOTYPE] DEFAULT ('Not Applicable') FOR [PMOTYPE],
CONSTRAINT [DF_TELMASTE_SLAMILESTONESCOMPLETE] DEFAULT (0) FOR [SLAMILESTONESCOMPLETE],
CONSTRAINT [DF_TELMASTE_FUNCTIONAL_AREA] DEFAULT (N'Account Open') FOR [FUNCTIONAL_AREA],
CONSTRAINT [PK_TELMASTE_] PRIMARY KEY NONCLUSTERED
(
[SEQUENCE]
) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE__GROUP_] ON [_SMDBA_].[_TELMASTE_]([_GROUP_], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE__OWNER_] ON [_SMDBA_].[_TELMASTE_]([_OWNER_], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_CLIENT] ON [_SMDBA_].[_TELMASTE_]([CLIENT], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SENT TO] ON [_SMDBA_].[_TELMASTE_]([SENT TO], [SEQUENCE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SUBJECT] ON [_SMDBA_].[_TELMASTE_]([SUBJECT], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_CONFIG] ON [_SMDBA_].[_TELMASTE_]([CONFIG], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_COMPANY] ON [_SMDBA_].[_TELMASTE_]([COMPANY], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_DEPT] ON [_SMDBA_].[_TELMASTE_]([DEPT], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_OPEN BY] ON [_SMDBA_].[_TELMASTE_]([OPEN BY], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_CLOSED BY] ON [_SMDBA_].[_TELMASTE_]([CLOSED BY], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_CTRPART] ON [_SMDBA_].[_TELMASTE_]([CTRPART], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE__OWNERGROUP_] ON [_SMDBA_].[_TELMASTE_]([_OWNERGROUP_], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SLA_ID] ON [_SMDBA_].[_TELMASTE_]([SLA_ID], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_WHITEBOARD_ID] ON [_SMDBA_].[_TELMASTE_]([WHITEBOARD_ID], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SEQ_UDSTATUS:] ON [_SMDBA_].[_TELMASTE_]([SEQ_UDSTATUS:], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SEQ_SEVERITY:] ON [_SMDBA_].[_TELMASTE_]([SEQ_SEVERITY:], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SEQ_PM:] ON [_SMDBA_].[_TELMASTE_]([SEQ_PM:], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SEQ_IMPACT:] ON [_SMDBA_].[_TELMASTE_]([SEQ_IMPACT:], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SEQ_PRIORITY:] ON [_SMDBA_].[_TELMASTE_]([SEQ_PRIORITY:], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SEQ_SSHD_ROUTE] ON [_SMDBA_].[_TELMASTE_]([SEQ_SSHD_ROUTE], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_CLIENT2] ON [_SMDBA_].[_TELMASTE_]([CLIENT2], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_INCIDENTOWNER] ON [_SMDBA_].[_TELMASTE_]([INCIDENTOWNER], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_PARENTINCIDENT] ON [_SMDBA_].[_TELMASTE_]([PARENTINCIDENT], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [FK_TELMASTE_SEQ_SERVICE] ON [_SMDBA_].[_TELMASTE_]([SEQ_SERVICE], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [IX__INACTIVE__TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([_INACTIVE_:], [SEQUENCE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_DATEOPEN_TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([DATE OPEN], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [IX_DUEDATE_TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([DUE_DATE:], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [IX_HDTYPE_TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([HDTYPE], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [STATLINE] ON [_SMDBA_].[_TELMASTE_]([STATUS], [_GROUP_], [SENT TO], [SEQUENCE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_PRIORITY_TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([PRIORITY], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [IX_DURATION_TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([DURATION:], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [IX_LASTUSER_TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([LASTMODIFIED], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [IX_CLOSEDBY_TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([CLOSED BY], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [IX_SSONUM_TELMASTE_] ON [_SMDBA_].[_TELMASTE_]([SSONUM], [SEQUENCE]) ON [PRIMARY]
GO
CREATE INDEX [SENTTO] ON [_SMDBA_].[_TELMASTE_]([SENT TO], [SEQUENCE]) ON [PRIMARY]
GO
ALTER TABLE [_SMDBA_].[_TELMASTE_] ADD
CONSTRAINT [FK_TELMASTE_CLIENT2] FOREIGN KEY
(
[CLIENT2]
) REFERENCES [_SMDBA_].[_CUSTOMER_] (
[SEQUENCE]
),
CONSTRAINT [FK_TELMASTE_PARENTINCIDENT] FOREIGN KEY
(
[PARENTINCIDENT]
) REFERENCES [_SMDBA_].[_TELMASTE_] (
[SEQUENCE]
)
GO
March 4, 2008 at 11:42 am
Also, what's wrong with just doing this:
SELECT
count(*)
FROM
[MAGICSSHD].[Incident] WITH (NOLOCK)
WHERE
[State:] = N'C'
AND ([InActive:] = 0)
AND ([Incident Description] LIKE (N'%test%'))
It saves creating the derived table to then select the count from.
😎
March 4, 2008 at 11:45 am
Your table DDL doesn't match your query unless I missed something in all the code.
😎
March 4, 2008 at 11:51 am
Here is the view that the query is retrieving from. The earlier script is from DDL of the table that this view is using
CREATE view "MAGICSSHD"."Incident" AS SELECT "Incident #","LastModified","LastUser","Seq.Group","Seq.Owner","OwnerPermissions","Seq.Client","State:","Seq.Assigned To","Seq.Subject","Open Date & Time","Seq.Configuration","Seq.Company","Seq.Department","Seq.Open By","Seq.Closed By","Close Date & Time","Phone","#","Seq.Inv.Item","Follow Up","Follow Up Date & Time","Seq.OwnerGroup","EMail Originator","Priority:","Seq.SLA","Seq.Whiteboard","Start Date","HelpDeskDate02","HelpDeskInteger01","HelpDeskInteger02","Program-Function","Tracking Number","TSU-Tracking","HelpDeskText04","HelpDeskText05","HelpDeskText06","Incident Type","Seq.ZAC","Machine Name","Activity State","Priority","TEST","Change Type","Impact Risk","Alert ID","Cause Code","Change Requirements","Seq.UDStatus:","Seq.Urgency:","Due Date & Time:","Release Name","Platform Type","Release Type","Release Change Type","Platform Types","Quality Control","Type of Changes","Release Type Values","Job Name","Abend Counter","SSONUM","Seq.ProbMgmt:","InActive:","Clock StopTime:","Seq.Impact:","Seq.Priority:","Duration:","First Call Resolution:","Project Director","Project Manager","Seq.SSHD Route","Subclient ID","Account #","Building","Anticipated Start Date","PartTime Personnel","Type of Activity","PMO","Data1","On Time","Scope","Resources","Client Readiness","Seq Client 2","PMO Type","Delta Status","Release Code","Response Date & Time:","Recommended Fix Date & Time:","Link MSCRM CaseNo","HelpDeskText07:","HelpDeskText08:","HelpDeskText09:","HelpDeskText10:","HelpDeskText11:","HelpDeskText12:","HelpDeskText13:","HelpDeskText14:","HelpDeskText15:","Opened Group:","Closed Group:","Contact Type","Incident Owner","Parent Incident","Note","Incident Description","Incident Resolution","Tools Required","Material Vendor Log","Answer Suggestion","Responded Date & Time","Seq.Service","SLA Milestones Complete","SNMP Event ID","Functional Area","Open Date","Open Time","Close Date","Close Time","CAST Incident Description","CAST Incident Resolution","CountOpenWO","CountAttachments","Group Name","HD Type Code","Forms","Group Comments","Login ID Owner","Cl.Phone #","Cl. Ext.","Cl.Room","Cl.Building","Cl.Position","Charge Back ID","Postal Code","Intl Phone #","Intl Fax #","Building Locations","Client ID","First Name","Last Name","Site Number","Link MSCRM ContactId","Client Email","Phone 2","Ext. 2","Address 2","City 2","Zip 2","State 2","Postal Code 2","Account Number 2","Assigned Advisor 2","Campaign 2","Client ID 2","First Name 2","Last Name 2","Login ID Closed By","First Name Closed By","Last Name Closed By","Full Name Closed By","Strataweb Database","Retrieval Database","Company Division","Company Main Contact","SSHD Support Login ID","AcctRep Login ID","MCS Client","SEI System","Contract Effective Date","Contract Expiration Date","Contract #","Contract Type","Company Name","Company ID","Company Parent Company","Managing Directors","Account Representative","Account Director","Relationship Manager","Master ID","Live Database","Job Prefix","TSO ID","Revenue ID","Relationship Team","Custody ID","GL Number","EOM Database","Configuration #","Asset/Tag #","Part #","Serial #","Warranty Exp. Date","Part Description","Asset Name","Department ID","Department Name","Login ID Incident Owner","Full Name Owner","First Name Opened By","Full Name Open By","Login ID Opened By","Last Name Opened By","Parent Incident Description","Parent State:","Parent Status ID:","Parent Open Date & Time","Parent Close Date & Time","Parent Subject ID","Parent Group Name","Parent Incident Type","Parent Incident Number","Parent Due Date & Time:","Last Name Assigned To","First Name Assigned To","Phone Assigned To","Ext Assigned To","Login ID Assigned To","Full Name Assign To","Impact ID:","Priority ID:","Service Name","Service Description","Urgency ID:","Urgency Duration:","SSHD Workgroup","SSHD Route Description","SSHD Login ID","Routing Code","Status Description:","Status ID:","SLA ID","SLA Description","Subject Description","Subject ID","Work Item","Subject Type Code","WhiteboardID","Whiteboard Note","Product GL" FROM "_SMDBA_"."Incident"
March 4, 2008 at 11:53 am
I ran this query that was mentioned earlier in the one of the responses
SELECT count(*)FROM [MAGICSSHD].[Incident] WITH (NOLOCK)WHERE [State:] = N'C' AND ([InActive:] = 0) AND ([Incident Description] LIKE (N'%test%'))
It took 2 mins 59 secs
March 4, 2008 at 11:55 am
Haritha Kodali (3/4/2008)
I have this query that gets called from the code that runs too slow. It initially took 2mins 53 secs. After adding clustered index on State and InActive: columns, it came down to 2 mins 38 secs. I need to get it down even further. This query returns the count of 38462 out of 1229885 records from this table.SELECT COUNT(*) FROM
( SELECT 24 AS [MODSEQ],
[Incident #] AS [RECSEQ],
0 AS [FORMSEQ],
CAST((CAST(24 as nvarchar)+ CAST([Incident #] as nvarchar)) as float(53)) AS [PRSEQ],
[Incident #] AS [Incident],
[Status Description:] AS [Status],
[Urgency ID:] AS [Urgency ID],
[Incident Description] AS [Incident Description],
[Due Date & Time:] AS [Due Date],
[Client ID] AS [Client ID],
[Subject ID] AS [Category ID],
[Group Name] AS [Group],
[Login ID Assigned To] AS [Assigned To],
[Open Date & Time] AS [Open Date],
[State:] AS [State:],[InActive:] AS [InActive:]
FROM [MAGICSSHD].[Incident] WITH (NOLOCK) ) BASE
WHERE (( [State:] = N'C') AND
( [InActive:] = 0)AND
( [Incident Description] LIKE (N'%test%')) )
For the same above query, I change the State to 'O' that returns 867 records out of 1229885 from this table came down from 1 min 16 secs to only 17-18 secs.
The execution plan shows using the clustered index. What else can I do to improve the performance of the above query to bring it down from 2mins 38 secs.
Why does the subquery need all of those fields? Lynn is definitely onto something there. Start with his syntax - looks to me that you're doing an awful lot of work for nothing.
Also - for all of the indexes you have - not one is adequate to cover this query. What about an index that includes the state,inact and incidentdescription in one index? You will need to find out which field is most selective and make that the left edge of this index.
Have you looked to see if most of these indexes are used at all? I have a gut feeling most of those don't get used at all.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 11:56 am
Haritha Kodali (3/4/2008)
Here is the view that the query is retrieving from. The earlier script is from DDL of the table that this view is usingCREATE view "MAGICSSHD"."Incident" AS SELECT "Incident #","LastModified","LastUser","Seq.Group","Seq.Owner","OwnerPermissions","Seq.Client","State:","Seq.Assigned To","Seq.Subject","Open Date & Time","Seq.Configuration","Seq.Company","Seq.Department","Seq.Open By","Seq.Closed By","Close Date & Time","Phone","#","Seq.Inv.Item","Follow Up","Follow Up Date & Time","Seq.OwnerGroup","EMail Originator","Priority:","Seq.SLA","Seq.Whiteboard","Start Date","HelpDeskDate02","HelpDeskInteger01","HelpDeskInteger02","Program-Function","Tracking Number","TSU-Tracking","HelpDeskText04","HelpDeskText05","HelpDeskText06","Incident Type","Seq.ZAC","Machine Name","Activity State","Priority","TEST","Change Type","Impact Risk","Alert ID","Cause Code","Change Requirements","Seq.UDStatus:","Seq.Urgency:","Due Date & Time:","Release Name","Platform Type","Release Type","Release Change Type","Platform Types","Quality Control","Type of Changes","Release Type Values","Job Name","Abend Counter","SSONUM","Seq.ProbMgmt:","InActive:","Clock StopTime:","Seq.Impact:","Seq.Priority:","Duration:","First Call Resolution:","Project Director","Project Manager","Seq.SSHD Route","Subclient ID","Account #","Building","Anticipated Start Date","PartTime Personnel","Type of Activity","PMO","Data1","On Time","Scope","Resources","Client Readiness","Seq Client 2","PMO Type","Delta Status","Release Code","Response Date & Time:","Recommended Fix Date & Time:","Link MSCRM CaseNo","HelpDeskText07:","HelpDeskText08:","HelpDeskText09:","HelpDeskText10:","HelpDeskText11:","HelpDeskText12:","HelpDeskText13:","HelpDeskText14:","HelpDeskText15:","Opened Group:","Closed Group:","Contact Type","Incident Owner","Parent Incident","Note","Incident Description","Incident Resolution","Tools Required","Material Vendor Log","Answer Suggestion","Responded Date & Time","Seq.Service","SLA Milestones Complete","SNMP Event ID","Functional Area","Open Date","Open Time","Close Date","Close Time","CAST Incident Description","CAST Incident Resolution","CountOpenWO","CountAttachments","Group Name","HD Type Code","Forms","Group Comments","Login ID Owner","Cl.Phone #","Cl. Ext.","Cl.Room","Cl.Building","Cl.Position","Charge Back ID","Postal Code","Intl Phone #","Intl Fax #","Building Locations","Client ID","First Name","Last Name","Site Number","Link MSCRM ContactId","Client Email","Phone 2","Ext. 2","Address 2","City 2","Zip 2","State 2","Postal Code 2","Account Number 2","Assigned Advisor 2","Campaign 2","Client ID 2","First Name 2","Last Name 2","Login ID Closed By","First Name Closed By","Last Name Closed By","Full Name Closed By","Strataweb Database","Retrieval Database","Company Division","Company Main Contact","SSHD Support Login ID","AcctRep Login ID","MCS Client","SEI System","Contract Effective Date","Contract Expiration Date","Contract #","Contract Type","Company Name","Company ID","Company Parent Company","Managing Directors","Account Representative","Account Director","Relationship Manager","Master ID","Live Database","Job Prefix","TSO ID","Revenue ID","Relationship Team","Custody ID","GL Number","EOM Database","Configuration #","Asset/Tag #","Part #","Serial #","Warranty Exp. Date","Part Description","Asset Name","Department ID","Department Name","Login ID Incident Owner","Full Name Owner","First Name Opened By","Full Name Open By","Login ID Opened By","Last Name Opened By","Parent Incident Description","Parent State:","Parent Status ID:","Parent Open Date & Time","Parent Close Date & Time","Parent Subject ID","Parent Group Name","Parent Incident Type","Parent Incident Number","Parent Due Date & Time:","Last Name Assigned To","First Name Assigned To","Phone Assigned To","Ext Assigned To","Login ID Assigned To","Full Name Assign To","Impact ID:","Priority ID:","Service Name","Service Description","Urgency ID:","Urgency Duration:","SSHD Workgroup","SSHD Route Description","SSHD Login ID","Routing Code","Status Description:","Status ID:","SLA ID","SLA Description","Subject Description","Subject ID","Work Item","Subject Type Code","WhiteboardID","Whiteboard Note","Product GL" FROM "_SMDBA_"."Incident"
With all due respect, I am not going to reformat the above to try and see what it is doing.
Here is the code you originally posted reformated using my preferred formatting methodology:
SELECT
COUNT(*)
FROM
( SELECT
24 AS [MODSEQ],
[Incident #] AS [RECSEQ],
0 AS [FORMSEQ],
CAST((CAST(24 as nvarchar)+ CAST([Incident #] as nvarchar)) as float(53)) AS [PRSEQ],
[Incident #] AS [Incident],
[Status Description:] AS [Status],
[Urgency ID:] AS [Urgency ID],
[Incident Description] AS [Incident Description],
[Due Date & Time:] AS [Due Date],
[Client ID] AS [Client ID],
[Subject ID] AS [Category ID],
[Group Name] AS [Group],
[Login ID Assigned To] AS [Assigned To],
[Open Date & Time] AS [Open Date],
[State:] AS [State:],
[InActive:] AS [InActive:]
FROM
[MAGICSSHD].[Incident] WITH (NOLOCK) ) BASE
WHERE
(( [State:] = N'C')
AND ( [InActive:] = 0)
AND ( [Incident Description] LIKE (N'%test%')) )
😎
March 4, 2008 at 11:59 am
Would you please post the execution plan for the query. Oh, and no I don't know how you should do it, as I have never tried it myself, I just know it can be done. Perhaps Jeff or Matt can assist with that if you need help.
😎
March 4, 2008 at 11:59 am
Haritha Kodali (3/4/2008)
Here is the view that the query is retrieving from. The earlier script is from DDL of the table that this view is usingCREATE view "MAGICSSHD"."Incident" AS SELECT "Incident #","LastModified","LastUser","Seq.Group","Seq.Owner","OwnerPermissions","Seq.Client","State:","Seq.Assigned To","Seq.Subject","Open Date & Time","Seq.Configuration","Seq.Company","Seq.Department","Seq.Open By","Seq.Closed By","Close Date & Time","Phone","#","Seq.Inv.Item","Follow Up","Follow Up Date & Time","Seq.OwnerGroup","EMail Originator","Priority:","Seq.SLA","Seq.Whiteboard","Start Date","HelpDeskDate02","HelpDeskInteger01","HelpDeskInteger02","Program-Function","Tracking Number","TSU-Tracking","HelpDeskText04","HelpDeskText05","HelpDeskText06","Incident Type","Seq.ZAC","Machine Name","Activity State","Priority","TEST","Change Type","Impact Risk","Alert ID","Cause Code","Change Requirements","Seq.UDStatus:","Seq.Urgency:","Due Date & Time:","Release Name","Platform Type","Release Type","Release Change Type","Platform Types","Quality Control","Type of Changes","Release Type Values","Job Name","Abend Counter","SSONUM","Seq.ProbMgmt:","InActive:","Clock StopTime:","Seq.Impact:","Seq.Priority:","Duration:","First Call Resolution:","Project Director","Project Manager","Seq.SSHD Route","Subclient ID","Account #","Building","Anticipated Start Date","PartTime Personnel","Type of Activity","PMO","Data1","On Time","Scope","Resources","Client Readiness","Seq Client 2","PMO Type","Delta Status","Release Code","Response Date & Time:","Recommended Fix Date & Time:","Link MSCRM CaseNo","HelpDeskText07:","HelpDeskText08:","HelpDeskText09:","HelpDeskText10:","HelpDeskText11:","HelpDeskText12:","HelpDeskText13:","HelpDeskText14:","HelpDeskText15:","Opened Group:","Closed Group:","Contact Type","Incident Owner","Parent Incident","Note","Incident Description","Incident Resolution","Tools Required","Material Vendor Log","Answer Suggestion","Responded Date & Time","Seq.Service","SLA Milestones Complete","SNMP Event ID","Functional Area","Open Date","Open Time","Close Date","Close Time","CAST Incident Description","CAST Incident Resolution","CountOpenWO","CountAttachments","Group Name","HD Type Code","Forms","Group Comments","Login ID Owner","Cl.Phone #","Cl. Ext.","Cl.Room","Cl.Building","Cl.Position","Charge Back ID","Postal Code","Intl Phone #","Intl Fax #","Building Locations","Client ID","First Name","Last Name","Site Number","Link MSCRM ContactId","Client Email","Phone 2","Ext. 2","Address 2","City 2","Zip 2","State 2","Postal Code 2","Account Number 2","Assigned Advisor 2","Campaign 2","Client ID 2","First Name 2","Last Name 2","Login ID Closed By","First Name Closed By","Last Name Closed By","Full Name Closed By","Strataweb Database","Retrieval Database","Company Division","Company Main Contact","SSHD Support Login ID","AcctRep Login ID","MCS Client","SEI System","Contract Effective Date","Contract Expiration Date","Contract #","Contract Type","Company Name","Company ID","Company Parent Company","Managing Directors","Account Representative","Account Director","Relationship Manager","Master ID","Live Database","Job Prefix","TSO ID","Revenue ID","Relationship Team","Custody ID","GL Number","EOM Database","Configuration #","Asset/Tag #","Part #","Serial #","Warranty Exp. Date","Part Description","Asset Name","Department ID","Department Name","Login ID Incident Owner","Full Name Owner","First Name Opened By","Full Name Open By","Login ID Opened By","Last Name Opened By","Parent Incident Description","Parent State:","Parent Status ID:","Parent Open Date & Time","Parent Close Date & Time","Parent Subject ID","Parent Group Name","Parent Incident Type","Parent Incident Number","Parent Due Date & Time:","Last Name Assigned To","First Name Assigned To","Phone Assigned To","Ext Assigned To","Login ID Assigned To","Full Name Assign To","Impact ID:","Priority ID:","Service Name","Service Description","Urgency ID:","Urgency Duration:","SSHD Workgroup","SSHD Route Description","SSHD Login ID","Routing Code","Status Description:","Status ID:","SLA ID","SLA Description","Subject Description","Subject ID","Work Item","Subject Type Code","WhiteboardID","Whiteboard Note","Product GL" FROM "_SMDBA_"."Incident"
There's no excuse for this... I'm SO done with this person... must have a "porkchop death wish"... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2008 at 11:59 am
As far as why we require the other fields in the query just to get the count, I have sent this question to the developers since this query is being called from the code.
I did use the 2nd query that has no fields, it still did not reduce the time.
March 4, 2008 at 12:03 pm
Matt was on to something else. I looked at the indexes as well, and he is correct. I think you will see an improvement if you built an index on the three columns in the where clause of my query. Be sure to read Matt's post for a few more details.
😎
March 4, 2008 at 12:09 pm
The reason, Incident Description is not included in index is that it is ntext data type.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply