February 22, 2010 at 4:31 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 22, 2010 at 5:27 pm
I'm not sure posting your query multiple times would help get your question answered any quicker:-D
February 22, 2010 at 6:52 pm
Sorry, I didn't mean to, actually I didn't even know that the post was posted so many times:w00t::w00t::w00t:, I had trouble on posting it for the whole day, it always returned me a page crash, no matter what browser I used and when, I even tried two different machine.
I even sent a email to this website's contact email asking for resolving this issue for me.
February 22, 2010 at 8:57 pm
Having charindex function will ensure that index on a.account
doesn't get used.
Indexes are not effective when you have 'OR' conditions as well.
Can you please post the query plan that you obtain, so that we comment better.
Regards,
Raj
February 22, 2010 at 9:35 pm
Cast the empnum's as INT and remove the 0000 concatenation / string comparison.
Remove the CHARINDEX function and try "a.account LIKE '%' + @query + '%' "
February 23, 2010 at 7:49 am
foxxo (2/22/2010)
Cast the empnum's as INT and remove the 0000 concatenation / string comparison.Remove the CHARINDEX function and try "a.account LIKE '%' + @query + '%' "
Thanks, I want to cast this field into integer, however, there are so many exceptions, for example, value as "contract", "sadfasd", .... those can't be casted into integer.
How do I determine if a given string can be converted into Integer? is there any function in sql to do that?
Thanks again for the help.
February 23, 2010 at 8:10 am
arr.nagaraj (2/22/2010)
Having charindex function will ensure that index on a.accountdoesn't get used.
Indexes are not effective when you have 'OR' conditions as well.
Can you please post the query plan that you obtain, so that we comment better.
I have attached the query plan screenshot here.
Before that, I've made some changes to the table and sp:
I've converted most of the '00000' employeeid field into integer, threw out all exceptions for now (I do want to include them later on though)
Now the sp is here:
ALTER Procedure [dbo].[spListADEntries](@query varchar(30))
AS
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.eid = a.eid
where a.account like '%' + @query + '%'
So as you can see,asically I want to return a list of matches with the input, is there a more efficient way to this?
Thanks lots to all replies
February 23, 2010 at 2:08 pm
It would appear that the clause:
inner join WhitePage w on w.displayname = a.displayname or w.eid = a.eid
is forcing a Clustered Index Scan on the Whitepage table. Thats where the time is spent. I can't determine the rowcounts from the Clustered Index Plan Operator (try posting it as a .sqlplan next time) but I can see from the thickness of the line that indicates its output that it is outputting quite a few rows.
It could be the 'or' clause forcing the scan. Are there any indexes on those columns?
February 23, 2010 at 7:57 pm
Here is the sqlplan file, thanks to all replies.
February 24, 2010 at 12:20 am
You'll need to respond about whether there are indexes available..
Also try running it with just the empid join, then modify it for just the name string join and see how long it takes for each. Then you'll know if it's the OR condition causing the slowdown.
Also could avoid comparing every name since the empid's are faster and most empid's would likely match; so dont compare name strings for those - ie:
ALTER Procedure [dbo].[spListADEntries](@query varchar(30))
AS
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 AND w.eid <> a.eid) OR w.eid = a.eid
WHERE a.account LIKE '%' + @query + '%'
Also, are you providing the Actual query plan, (rather than Estimated query plan) by running the SELECT seperately with a value in place of "@query"?
February 24, 2010 at 9:02 pm
foxxo (2/24/2010)
You'll need to respond about whether there are indexes available..
Thanks.
Index has been created on: displayname(both), a.account, w.workphonenumber, w.emailaddress
are you providing the Actual query plan, (rather than Estimated query plan) by running the SELECT seperately with a value in place of "@query"?
My sqlplan is the actual query plan
February 25, 2010 at 2:08 am
Its the lookup to WhitePaper that is killing you ?
Are your stats upto date ?
I agree that this is not an actual plan but an estimated one.
Does this perform better ?
SELECT TOP 20 a.employeeid AS [ID], a.account, a.displayname AS [Name], w.workphonenumber AS phone, w.emailaddress AS email,w2.workphonenumber AS phone, w2.emailaddress AS email
FROM activedirectory a
left JOIN WhitePage w ON (w.displayname = a.displayname AND w.eid <> a.eid)
left join WhitePage w2 on w2.eid = a.eid
WHERE a.account LIKE '%' + @query + '%'
February 25, 2010 at 7:56 am
Dave Ballantyne (2/25/2010)
Its the lookup to WhitePaper that is killing you ?
I don't know, querying either of the two is acceptable but not after inner join them
Are your stats upto date ?
How do I know if the stats is up to date?
I agree that this is not an actual plan but an estimated one.
I click on the Estimated Exec Plan icon to generate this one, how do I create an actual plan then?
Does this perform better ?
SELECT TOP 20 a.employeeid AS [ID], a.account, a.displayname AS [Name], w.workphonenumber AS phone, w.emailaddress AS email,w2.workphonenumber AS phone, w2.emailaddress AS emailFROM activedirectory a
left JOIN WhitePage w ON (w.displayname = a.displayname AND w.eid <> a.eid)
left join WhitePage w2 on w2.eid = a.eid
WHERE a.account LIKE '%' + @query + '%'
Much better, thank you.
February 25, 2010 at 7:56 am
1) the killing you SCAN on WhitePaper is performed too many times because of the Nested Loop join used, so try adding :
OPTION(HASH JOIN, MERGE JOIN)
that will result in scanning WhitePaper only once. (with your indexes that will most probably be a HASH join)
2) Try creating a COVERING index on ActiveDirectory:
create noncludtered index ActiveDirectory_account_i_displayname_employeeid on ActiveDirectory(account) include(displayname,employeeid)
3) Play with clustered indexes, yours look no good for that query
4) definetely use LIKE, not the function
February 25, 2010 at 8:15 am
Dave's query should improve the results....
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply