CODEWITHSUNDEEP
sql-serverquery-optimizationsql-execution-plan
Angelina
Angelina

Reputation: 2265

Optimizing queries vs. adding indexes

I have this very old and SLOW query that I am trying to optimize, but I am not sure I can do anything to it, but add more indexes on columns involved in WHERE, JOIN and ORDER BY.

Query:

SELECT TOP 400 jobticket.jobnumber, jobticket.typeform, jobticket.filename, jobticket.req_number, jobticket.reqd_del_date, jobticket.point_of_contact, jobticket.status, jobticket.DapsDate, jobticket.elpod, job_info.IDOrderMaskedStatus, job_info.job_status, job_info.SalesID, job_info.location, job_info.TOMetadataID 
FROM jobticket WITH (NOLOCK) 
INNER JOIN job_info WITH (NOLOCK) ON job_info.jobnumber = jobticket.jobnumber 
WHERE  
(
    NOT(
        (jobticket.status = 'Complete' OR jobticket.status = 'Completed') 
         and (job_info.job_status = 'Actualized' OR job_info.job_status = '' 
              OR job_info.job_status = 'Actualized Credit Billed' 
              OR job_info.job_status = 'DWAS Actualized' OR job_info.job_status = 'DWAS Actualized Credit Billed'
             )
        )  
    or 
    ((SELECT COUNT(job_status) AS Expr1 FROM tblConsolidatedBilling AS tblConsolidatedBilling_1 WITH (NOLOCK)  
      WHERE  (job_status <> 'Actualized' 
      AND  job_status <> 'Actualized Credit Billed') 
      AND (master_jobnumber = jobticket.jobnumber)) > 0) 
) 
and (jobticket.status != 'Waiting Approval' or (jobticket.status = 'Waiting Approval' and jobticket.DPGType is null))  
and jobticket.typeform <> 'todpg'  
and ((job_info.isHidden <> 1 or job_info.isHidden is null) and job_info.isInConcurrentRelease is null)  
and job_info.deleted != '1' 
and jobticket.status != 'New Job'  
and jobticket.status != 'PRFYCLSFD'  

ORDER BY 
job_info.expediencyLevel DESC, 
jobticket.jobnumber DESC

Execution Plan: execution plan

In all honesty I don't know what to do with this query.

Should I add individual nonclustered indexes on all columns involved in WHERE JOIN and ORDER BY?

There are many indexes on these tables, but I am not sure whether they are helpful in this query:

enter image description here

Upvotes: 1

Views: 52

Answers (2)

usr
usr

Reputation: 171178

A simple fix would be to make the indexes on job_info and tblConsolidatedBilling covering because a ton of time is spent in key lookups there. That should give an integer factor speedup. If that's not enough we need to investigate further.

Upvotes: 1

James Z
James Z

Reputation: 12317

Looking at this SQL, I don't really see any clear criteria that is being used to fetch the rows. It looks like it's just excluding a lot of rows with different criteria. My guess is that the tickets usually end up in a state where most of the rows are, and those are not included in the results?

The problem with this is, that it doesn't really have any clear criteria for that, and it has a lot of different rules there, so that's why it ends up doing a clustered index scan + key lookups for all the rows. The scan starts from jobinfo, but I'm not sure if it would make any difference if it would start from jobticket.

Removing most of the indexes is probably a good place to start, but it won't speed up the select at all.

The query looks quite complex, so my guess is that you can't create an index view that would contain this data. That might help assuming this query is executed often and data is not changed that much (and the overhead of maintaining huge number of indexes would have been removed), but this might not be possible.

Another idea would be to investigate the rules when the rows can be excluded, and is there a possibility have more clear rules for that, so it could be indexed, maybe by adding a persisted computed column into the table.

You haven't mentioned how long this actually takes, and how many rows there are in the tables, so everything is basically just a guess. Including more data + statistics io output into the question might help.

ps. I don't personally recommend using NOLOCK except in really special cases, because it can cause problems that are really hard to solve, like reading the same data more than once or skipping rows totally.

Upvotes: 1

Related Questions