May 26, 2011 at 6:16 pm
Hello
I have a query hitting my database quite frequently which performs 2 index scans on the same table. I'm trying to create an index for it to use but am having trouble. The query is not very well written but I dont have an option to change it as it is embeded in the application
Here's the table which i would like to create an index on
CREATE TABLE [dbo].[pa_communication_options](
[OWNERID] [varchar](12) COLLATE Latin1_General_CI_AS NOT NULL,
[COMMUNICATIONID] [varchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
[COMMUNICATIONTYPE] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[ADDRESSTYPE] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
[PRIMARYCOMMUNICATION] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
[CONTACTSTRING] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
[NOTES] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[ACTIVE] [varchar](5) COLLATE Latin1_General_CI_AS NULL,
[DATEADDED] [datetime] NOT NULL,
[ADDEDBY] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[DATEUPDATED] [datetime] NULL,
[UPDATEDBY] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[DATEDELETED] [datetime] NULL,
[DELETEDBY] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[DATEFROM] [datetime] NULL,
[DATETO] [datetime] NULL,
[AREACODE] [char](2) COLLATE Latin1_General_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[OWNERID] ASC,
[COMMUNICATIONID] ASC,
[COMMUNICATIONTYPE] ASC,
[DATEADDED] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Here are the other tables invloved in the query
CREATE TABLE [dbo].[pa_communication_types](
[TYPE] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[DESCRIPTION] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[CATEGORY] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
[CLASS] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[pa_communication_types](
[TYPE] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
[DESCRIPTION] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[CATEGORY] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
[CLASS] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TYPE] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Here's the query
Select distinct c.ContactId as "ContactId",
(Select distinct f.ContactString
From pa_communication_options f, pa_communication_types t
Where f.PrimaryCommunication = 'true'
and Upper(f.CommunicationType) = Upper(t.Type) and t.Class = 'PHONE'
and Upper(c.ContactId) = Upper(f.OwnerId) and f.CommunicationId = 'C') as "PhoneNumber",
(Select distinct f.ContactString
From pa_communication_options f, pa_communication_types t
Where f.PrimaryCommunication = 'true' and Upper(f.CommunicationType) = Upper(t.Type)
and t.Class = 'EMAIL' and Upper(c.ContactId) = Upper(f.OwnerId)
and f.CommunicationId = 'C') as "Email", c.ShortName as "ShortName",
c.SocialSecurityNumber as "SocialSecurityNumber",
c.FirstName as "FirstName", c.MiddleName as "MiddleName",
c.LastName as "LastName", convert(char(8),c.DateOfBirth,112) as "DateOfBirth",
c.Suffix as "Suffix", (Select count(*)
from pa_account_link l where l.PartyId=c.ContactIdand l.PartyType = 'C')
as "ContactType", c.PriorityNote as "PriorityNote", a.AddressLine1 as "AddressLine1",
a.AddressLine2 as "AddressLine2", a.AddressLine3 as "AddressLine3",
a.AddressLine4 as "AddressLine4", a.AddressLine5 as "AddressLine5", a.City as "City",
a.StateCode as "StateCode", a.ZipCode as "ZipCode", a.CountryCode as "CountryCode"
from pa_contact c, pa_address a Where UPPER(c.LastName) like UPPER('%pihas%')
and c.PortfolioID = 'LSV' and a.OwnerId =* c.ContactId and a.AddressId = 'C' and a.PrimaryAddress = 'true'
order by c.LastName, c.FirstName, c.MiddleName
May 26, 2011 at 8:44 pm
Aside from that query being a total mess (as you obviously are well aware and unfortunately can't fix), the functions in your where clauses make it not SARGable. Not really sure why you would need to look at UPPER unless it is a case sensitive db. Even so adding a collation to the column in your query would make it work. Without looking much deeper you have a number of index scans due to the functions in your where clauses. The query itself could massively improved for performance but since you are unable to change that you are fighting an uphill battle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 27, 2011 at 2:19 am
+1
Using a function on both sides of the equality negate any index usage on that column. Upper(@a) is OK by itself, but = upper(indexed_column) results in a table scan. No avoiding it.
The only indexing that would help would be on the "straight" parameters passed in the query. (e.g. col = "true", col = "C").
You should put the index on another filegroup and include all the columns this query needs. This will help you monitor & demonstrate the true awfulness of the query, especially when you need a brand new HDD to hold just that poxy index...
May 27, 2011 at 2:35 am
Please post execution plan and existing index definitions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply