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 4:27 am
On the limited amount of informaton provided, it is difficult to say how to improve the performance.
The main problem is the CHARINDEX(@query, a.account) > 0, or the equivalent a.account LIKE '%' + @query + '%', which will require an index or a table scan on activedirectory. Also the implicit CAST on the JOIN will not help.
You could try with the following indexes:
CREATE NONCLUSTERED INDEX IX_ADCover
ON activedirectory (account, employeeid, displayname)
CREATE NONCLUSTERED INDEX IX_WPDisplayName
ON WhitePage (displayname)
CREATE NONCLUSTERED INDEX IX_WPEmpNumDisplayName
ON WhitePage (empnum, displayname)
using an UNION query:
;WITH ADQueryResult
AS
(
SELECT employeeid, account, displayname
-- Do cast here.
,'00000' + employeeid AS empnum
FROM activedirectory
WHERE account LIKE '%' + @query + '%'
)
SELECT TOP 20
A1.employeeid AS [ID], A1.account, A1.displayname AS [Name]
,W1.workphonenumber AS phone, W1.emailaddress AS email
FROM ADQueryResult A1
JOIN WhitePage W1
ON A1.displayname = W1.displayname
UNION ALL
SELECT
A2.employeeid, A2.account, A2.displayname
,W2.workphonenumber, W2.emailaddress
FROM ADQueryResult A2
JOIN WhitePage W2
ON A2.empnum = W2.empnum
AND A1.displayname <> W1.displayname
-- Top without an ORDER BY is non-deterministic
ORDER BY [Name]
February 23, 2010 at 12:23 pm
Okay this is like duplicate post #7 for this problem. I also provided an answer here. Which I would not have wasted my time with had there been only 1 thread started for this problem, since I said basically the same thing without the code.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply