February 22, 2010 at 4:29 pm
Hi folks,
I have a sp:
select top 20 a.employeeid as [ID], a.account, a.displayname as [Name], w.workphonenumber as phone, w.emailaddress as email
from activedirectory a
inner join WhitePage w on w.displayname = a.displayname or w.empnum = '00000' + a.employeeid
where charindex(@query, a.account) > 0
Basically I want to get the top 20 matches, it's working with no problem, but the performance is not that satisfied, there are 31000 records in activedirectory and 48000 in WhitePage, I wouldn't say they are big, but the query result takes
Index has been created on: displayname(both), a.account, w.workphonenumber, w.emailaddress
How can I improve the query's performance?
Thanks lots.
February 23, 2010 at 12:15 pm
Okay, there are a few things.
1. It would help if we had full table definitions. For example, are they separate indexes on each of the columns you list as being indexed or composite indexes?
2. Whenever you have a function or operation like concatenation you limit the usefulness of indexes. So both the '0000' + a.employeeid and CHARINDEX(@query, a.account) are limiting you to index scans. Possible solutions would be an indexed persisted calculated column that appends the '0000' to employeeid for the first case andif @query is going to be the first N characters of account, you could use WHERE account LIKE @query + '%' and give the Optimizer the option to choosing an INDEX SEEK.
3. Using an 'OR' also limits the usefulness of indexes. The Optimizer has to choose to access 2 indexes and merge those results (requires ordering) or can do a table scan.
4. You are using TOP without an ORDER BY so you are getting almost random results as there is no ordering guaranteed without using ORDER BY.
I know that I didn't provide any specific help, but I hope you can get some ideas of what to look at.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 23, 2010 at 1:13 pm
Jack Corbett (2/23/2010)
3. Using an 'OR' also limits the usefulness of indexes. The Optimizer has to choose to access 2 indexes and merge those results (requires ordering) or can do a table scan.
Two index seeks if both parts of the OR have indexes and are SARGable. Index or table scan if either one is not. Hard to get around, if query logic needs an OR, there aren't too many alternatives
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
February 23, 2010 at 1:29 pm
GilaMonster (2/23/2010)
Jack Corbett (2/23/2010)
3. Using an 'OR' also limits the usefulness of indexes. The Optimizer has to choose to access 2 indexes and merge those results (requires ordering) or can do a table scan.Two index seeks if both parts of the OR have indexes and are SARGable. Index or table scan if either one is not. Hard to get around, if query logic needs an OR, there aren't too many alternatives
Oh, I agree and I don't explain it that well. I need to work on that. Keep correcting and clarifying. 😉
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2010 at 4:06 am
Jack Corbett (2/23/2010)
you could use WHERE account LIKE @query + '%' and give the Optimizer the option to choosing an INDEX SEEK.
Can we have INDEX SEEK while using LIKE operator?:unsure:, because most of time, i found INDEX SCAN
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 4, 2010 at 5:35 am
You can, but generally the match string must not have a wild card at the beginning, for this to work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 4, 2010 at 6:05 am
Bhuvnesh (3/4/2010)
Jack Corbett (2/23/2010)
you could use WHERE account LIKE @query + '%' and give the Optimizer the option to choosing an INDEX SEEK.Can we have INDEX SEEK while using LIKE operator?:unsure:, because most of time, i found INDEX SCAN
Note that I only have a trailing wild card, thus you can have a seek. Leading wild cards cause a scan. Think about it, if I am looking someone named Corbett up in the phone book, but I'm not sure if their name is spelled Corbit or Corbitt or Corbet, etc... I do know that it starts with Corb, so I can seek to the page(s) that contain names that start with those 4 letters, but if I only know that letters 2-4 are orb, I have to scan through the "Chorb" pages, the "Corb" pages and the "Korb" pages. The example uses English where all those combinations could produce the hard "C" sound ("CA")
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply