November 20, 2008 at 12:10 pm
I have the following code:
select h.task_id, h.action_scheduled_date, h.employee_id, h.customer_id, h.appointment_status_id, h.task_desc Regarding
,cd.salesman_employee_id
from dwp_history h WITH (NOLOCK)
join customer_detail cd WITH (NOLOCK) on (cd.customer_id = h.customer_id)
where h.assigned_by_employee_id IN (0)
and h.task_type_id = 1
and h.is_customer = 1
and h.customer_id > 0
and h.action_created >= '2008-11-01 00:00:00'
and h.action_created < '2008-12-01 00:00:00'
When I run this with the following line comment out:
,cd.salesman_employee_id
It run fine returns in less than a second.
But if I run it in it entirety I get a tablescan which locks the table and it takes a long time to return.
Any ideas where to start looking?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
November 20, 2008 at 12:15 pm
Which table is getting locked and scanned?
If you notice, the column you are commenting out is from the second table of your join.
November 20, 2008 at 12:42 pm
The customer_Detail is being locked. I am thinking that the indexes have not been reindexed in over a month and it could be causing the issue.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
November 20, 2008 at 1:54 pm
Arthur.Lorenzini (11/20/2008)
It needs to fetch extra datapages to get cd.salesman_employee_id.
If you comment it out, it will solve the query index only for table customer_detail.
How up to date are your statistics and space info ?
Check out the number of rows, ... of the tables, and then try to detemine an access path yourself, chances are you'll end up doing the same with regards to least IO.
use this to see what it uses ...
set statistics io on
set statistics time on
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 20, 2008 at 1:59 pm
Clustered index on cd.customer_id may help here.
_____________
Code for TallyGenerator
November 20, 2008 at 2:07 pm
What do the tables look like? What are the indexes?
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply