We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the following query that is run thousands of times a day, with the only parts changing the ABCDEFG12345 in the where clause. Almost everything in the where's are customisable, as it is the query generated by their master job search.

There are about 500,000 records in the dJobs table, and similar numbers of records in the joined tables:

select * from (
    select   top       20 * from (
        select  top       20 
            (       --Start Count
                select count(*) from dJobs
                left join dClients on cltClientID = jobClientId 
                left join dJobStatus ON jbsID = jobJobStatus    

                where ((jobSupervisor=         -1.00000000  or jobCoordinator=         -1.00000000 )  OR 1=1 ) 

                and 
                (
                (jobjobStatus=0 OR (0=0  AND 0=0)) 
                AND (jbsType=0 OR (0=0  AND 0=0)) 
                AND (jobPriority=0 OR (0=0  AND 0=0)) 
                AND (jobEventID=0 OR (0=0 AND 0=0))
                AND (jobSiteCode='' OR (''='' AND ''=''))
                AND (jobjobStatus IN (
                    select jbsid from djobstatusgroupmapping where jsgid = 0
                    )               
                 OR (0=0 AND 0=0))
                   AND jobDateCreated BETWEEN '2004-06-10' AND '2014-06-10'    AND jobBookedDate BETWEEN '1901-01-01 00:00:00' AND '2024-06-10 23:59:00'    
        and
        (('ABCDEFG12345'<>'' 
            AND
            ( 
                (1 = 1 AND 
                    (
                        jobWorkOrderNo like '%ABCDEFG12345%' OR
                        jobSiteName like '%ABCDEFG12345%' OR
                        jobSiteLocationBuilding like '%ABCDEFG12345%' OR
                        jobSiteAddress like '%ABCDEFG12345%' OR
                        jobSiteSuburb like '%ABCDEFG12345%' OR
                        jobSitePostcode like '%ABCDEFG12345%' OR
                        jobWorkToDo like '%ABCDEFG12345%' OR
                        jobSiteClient like '%ABCDEFG12345%' OR
                        jobSiteContact like '%ABCDEFG12345%' OR
                        jobSiteContactPhone like '%ABCDEFG12345%' OR
                        jobSiteContactPhone2 like '%ABCDEFG12345%'
                    )
                ) 
            OR 
                (1 = 1 AND 
                    (
                        cltClientName like '%ABCDEFG12345%' OR
                        cltDivision like '%ABCDEFG12345%' OR
                        cltAddress1 like '%ABCDEFG12345%' OR
                        cltAddress2 like '%ABCDEFG12345%' OR
                        rtrim(cltAddress1)+' '+rtrim(cltAddress2) like '%ABCDEFG12345%' OR
                        cltSuburb like '%ABCDEFG12345%' OR
                        cltPostCode like '%ABCDEFG12345%' OR
                        cltState like '%ABCDEFG12345%' OR
                        cltTelephone like '%ABCDEFG12345%' OR
                        cltContact like '%ABCDEFG12345%' OR
                        cltMobile like '%ABCDEFG12345%' OR
                        cltEmail like '%ABCDEFG12345%'
                    )
                ) 
            )
        ) 
        OR 'ABCDEFG12345'='')                
                )   --End Count
            )  as vRecCount, 
            jobID,
            jobWorkOrderNo,
            jobSequence,                        
            jobSiteClient,
            jobSiteStreetNumber,
            jobSiteAddress,
            jobSiteSuburb,
            jobSiteState,
            jobSitePostcode,                
            jobDateofLoss,
            jobDateofLossTime,
            jobDateCreated,
            jobTargetDate,
            jobClientID,            
            jobPriority,
            jobJobStatus,
            jobEventID,
            regFullname,
            case 
            when (not jobSupervisor=         -1.00000000 ) and jobCoordinator=         -1.00000000  then 1 else 
            case when (jobSupervisor=         -1.00000000 ) and (jobCoordinator=0) then 2 else   
            case when (jobSupervisor=         -1.00000000 ) and (not jobCoordinator=         -1.00000000 ) then 3 else 0
            end end end as 'Coordinator',
            jobSupervisor,
            jobBookedDate,
            jobLat,
            jobLong,
            jobClaimAssistRequest,
            jobBooked,
            jobSiteContact,
            jobSiteContactPhone2,
            jobPCM
        from dJobs 
        left join dClients on cltClientID = jobClientId     
        left join dUsers on regUserId = jobCoordinator  
        left join dJobStatus ON jbsID = jobJobStatus    

        where ((jobSupervisor=         -1.00000000  or jobCoordinator=         -1.00000000 )  OR 1=1 ) 

        and 
                (
                (jobjobStatus=0 OR (0=0  AND 0=0)) 
                AND (jobPriority=0 OR (0=0  AND 0=0)) 
                AND (jbsType=0 OR (0=0  AND 0=0)) 
                AND (jobEventID=0 OR (0=0 AND 0=0))
                AND (jobSiteCode='' OR (''='' AND ''=''))
                AND (jobjobStatus IN (
                    select jbsid from djobstatusgroupmapping where jsgid = 0
                    )               
                 OR (0=0 AND 0=0))                      
                )
                  AND jobDateCreated BETWEEN '2004-06-10' AND '2014-06-10'    AND jobBookedDate BETWEEN '1901-01-01 00:00:00' AND '2024-06-10 23:59:00'    
        and
        (('ABCDEFG12345'<>'' 
            AND
            ( 
                (1 = 1 AND 
                    (
                        jobWorkOrderNo like '%ABCDEFG12345%' OR                 
                        jobSiteName like '%ABCDEFG12345%' OR
                        jobSiteLocationBuilding like '%ABCDEFG12345%' OR
                        jobSiteAddress like '%ABCDEFG12345%' OR rtrim(jobSiteStreetNumber)+' '+rtrim(jobSiteAddress)  LIKE '%ABCDEFG12345%' OR
                        jobSiteSuburb like '%ABCDEFG12345%' OR
                        jobSitePostcode like '%ABCDEFG12345%' OR
                        jobWorkToDo like '%ABCDEFG12345%' OR
                        jobSiteClient like '%ABCDEFG12345%' OR
                        jobSiteContact like '%ABCDEFG12345%' OR
                        jobSiteContactPhone like '%ABCDEFG12345%' OR
                        jobSiteContactPhone2 like '%ABCDEFG12345%'
                    )
                ) 
            OR 
                (1 = 1 AND 
                    (
                        cltClientName like '%ABCDEFG12345%' OR
                        cltDivision like '%ABCDEFG12345%' OR
                        cltAddress1 like '%ABCDEFG12345%' OR
                        cltAddress2 like '%ABCDEFG12345%' OR
                        rtrim(cltAddress1)+' '+rtrim(cltAddress2) like '%ABCDEFG12345%' OR
                        cltSuburb like '%ABCDEFG12345%' OR
                        cltPostCode like '%ABCDEFG12345%' OR
                        cltState like '%ABCDEFG12345%' OR
                        cltTelephone like '%ABCDEFG12345%' OR
                        cltContact like '%ABCDEFG12345%' OR
                        cltMobile like '%ABCDEFG12345%' OR
                        cltEmail like '%ABCDEFG12345%'
                    )
                ) 
            )
        ) 
        OR 'ABCDEFG12345'='')
             order by  jobid desc ,  jobDateCreated desc  
    ) as newtbl   order by  jobid asc ,  jobDateCreated asc 
 ) as newtbl_2 order by  jobid desc, jobDateCreated desc

The SQL Server Database Tuning Advisor has no suggestions for me - so it looks like the developers have done a good job at creating appropriate indexes and statistics for the query.

Until they can refactor things at their end, which could take 6+ months, what's left for us to do? Just throw more money at the SQL server (SSDs, more RAM, perhaps Enterprise Edition for partitioning)?

Breakdown of transaction cost is:

  • Total CPU Time (ms.): 12,237.70
  • # Total Logical IO: 2,331,089
  • # Avg. Logical IO: 2,331,089.00
  • # Logical Reads: 2,331,089

SQL Server 2008 R2 Standard running inside a VMWare Virtual Machine.


Interesting part of the execution plan below. Most time spent on a clustered index scan.

enter image description here


Sorry for the delay; the following stats were taking from our staging server, which has 1/10th the number of records as production, but is also on lower class hardware. Execution Plan XML, STATISTICS IO:

Table 'dClients'. Scan count 5, logical reads 705780, physical reads 9, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'dJobs'. Scan count 10, logical reads 23386, physical reads 16, read-ahead reads 10601, lob logical reads 186928, lob physical reads 578, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'dUsers'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • Have you tried creating any indexes on [dClient]? Unfortunately, since they are using a big string of "OR LIKE %%"s in the WHERE clause, and including (what looks like) MOST of the columns in each table, this query is going to perform horribly. – Mark Wilkinson Jun 10 '14 at 13:41
  • 3
    The only thing that I can think of that might help is some sort of full text index on all of those predicates with "like '%ABCDEFG12345%'" - it's a stop gap, but worth a try? What are the data types for these? @Aaron Bertrand. Total agreement about spaghetti and what should be done with people/systems who produce stuff like this. – Vérace Jun 10 '14 at 13:50
  • Please show us the full STATISTICS IO output and post the full XML plan. That nested loops table scan of dClients (which is a heap!) is highly suspect. There's a row misestimate somewhere here, for sure, and there's no way the percentages are accurate. – Jon Seigel Jun 10 '14 at 14:12
  • @Vérace - I will give full text indexes a try. They are all varchar(50) fields. – Mark Henderson Jun 10 '14 at 20:43
  • 1
    Exactly what I thought. There's a 3-order-of-magnitude misestimate coming out of the CI scan in the bottom branch. If stats are up to date, HASH join hint dClients and you'll see a massive improvement in execution time. Beyond that, we'll need to see the schema and index definitions to make better recommendations. – Jon Seigel Jun 13 '14 at 3:37

Fulltext isn't going to help without refactoring to use the full text functions ( CONTAINS, FREETEXT or their table equivalents ). It also doesn't really work with leading wildcard. Hacks are available, but basically you're going to struggle to write a semantically equivalent query for fulltext. For the future consider redesigning for fulltext which has stemming ( run, runner, running ) and thesaurus ( jogger ) which could serve your searches much better than two wildcards.

SSD is unlikely to help you unless you are memory bound. Your tables (at only 500k records) are probably in-memory most of the time. Can you confirm the size of the dJobs table, and server RAM?

Enterprise Edition could help where the limitation of 64GB RAM / lesser of 4 sockets or 16 cores goes up to 8, but you're going to need a really powerful box to notice a difference. For example, the 4 really means you could have something like 4 quad-core processors totalling 16 cores, with HT enabled, you're already at 32 logical processors. The general recommended server maxdop for this type of OLTP machine would be 8 anyway. I think this unlikely to benefit because your query has more fundamental problems but you never know.

Non-clustered indexes (particularly on dJobs) are unlikely to help because the query has so many columns from this table in the SELECT and many criteria in the WHERE clause. A non-clustered would have to be so wide to cover it would be practically a duplicate of the clustered index, therefore overly expensive to maintain. As the query sorts by jobID DESC, I considered a descending index but haven't trialled this.

Partitioning, (Enterprise only) is really a great feature, but again is unlikely to help you. I did a quick investigation of partitioning on dbo.dJobs.jobJobStatus column, eg I imagine you only have a small percentage of Jobs 'active' at any one time, eg a few hundred, even a few thousand from the 500,000 records. Partition elimination would probably be cancelled out by the OR OR OR approach. Parallel scans of multiple partitions are also an Enterprise feature:

This would probably work:

SELECT TOP 20 *
FROM dJobs
    LEFT JOIN dClients on cltClientID = jobClientId
    LEFT JOIN dUsers on regUserId = jobCoordinator
    LEFT JOIN dJobStatus ON jbsID = jobJobStatus
WHERE
    (
    jobjobStatus IN ( SELECT jbsid FROM djobstatusgroupmapping WHERE jsgid = 0 )
    )
ORDER BY jobID DESC

This probably won't work:

SELECT TOP 20 *
FROM dJobs
    LEFT JOIN dClients on cltClientID = jobClientId
    LEFT JOIN dUsers on regUserId = jobCoordinator
    LEFT JOIN dJobStatus ON jbsID = jobJobStatus
WHERE
    (
    jobjobStatus IN ( SELECT jbsid FROM djobstatusgroupmapping WHERE jsgid = 0 )
    OR ( 0=0 ) OR ( 0=0 )   --<< this 'OR always true' means 'get the whole table'
    )
ORDER BY jobID DESC

This leads me into the query. The OR OR OR approach basically means 'always get the whole table'. The TOP 20 masks this design problem. The TOP also probably pushed the plan towards Nested Loops which Jon suggested was suspect. What also stands out to me about this nightmareish "scan all columns" constructed query is that you bascially have two copies of the same query (and therefore tables), one to count, one for the resultset. This might be more efficient if the data went into an intermediate table and the count was done from there for example.

Finally, this brings me to the only only thing that would actually help you (without a large-scale refactor of the code): data deletion or archiving. As mentioned, I imagine you only have a small percentage of Jobs 'active' at any one time. Carve off the 'inactive' ones into a different table. Create a view over the top of the two tables for reporting. Set up a nightly job to copy out the old records.

Having only a few thousand active jobs in your main table will most likely transform your query performance.

Some recommended reading:

Erland Sommarskog's article on these "search all columns" queries Dynamic Search Conditions in T-SQL http://www.sommarskog.se/dyn-search-2008.html

Querying Multiple Columns (Full-Text Search) http://technet.microsoft.com/en-us/library/ms142488(v=sql.105).aspx

I hope that helps!

  • Wow. This is a fantastic answer. Thanks so much for actually addressing my issues. I am totally going to pass your answer on to the developers. Basic answer is: Nothing I can do right now. The server has 48GB RAM and 6 cores allocated (virtualised). The database is 72GB in size and that table is about 1GB from memory. There are maybe 7,000 active records in a given day. – Mark Henderson Jun 11 '14 at 21:39
  • TIL that taking a query and adding OR 1=1 anywhere inside the WHERE clause changes the execution time from 350ms to 8,500ms – Mark Henderson Jun 11 '14 at 22:08
  • @MarkHenderson I would expect that to significantly increase the number of rows returned, too, since it basically makes any and all filtering / predicates no-ops. – Aaron Bertrand Jun 12 '14 at 17:30
  • @AaronBertrand - the number of records returned seems to be the same, as the 1=1's are inside parenthesis that logically just cancel out a single part of the where. The number of records scanned seems to be 100%. It also explains why this wasn't a problem 3 years ago with 1/10th the number of records. – Mark Henderson Jun 12 '14 at 21:58
  • 1
    Yeah, sorry, of course returned won't changed because of the top. But the underlying query has to return all candidate rows to the top, and if that includes or 1=1, that's all the rows. If you're adding or 1=1 you may as well remove all the other predicates... – Aaron Bertrand Jun 12 '14 at 22:03

I think you should still be able to get your reads lower; have you tried to turn that clustered index scan into a nonclustered one on the dJobs table? Hard to tell exactly how you'd need to go about doing that from just the picture ( jobBookedDate, jobDateCreated at least ) and it may go either way in terms of overall performance but if you can't or do and it gets worse, partitioning or a faster IO subsystem look like they'll be your only option.

  • Given all the predicates against dJobs (I assume the fields whose names start with "job" come from that table), surely it's stuck with a table scan. Even if you indexed all the filtered fields, they'd need to be scanned in full, you wouldn't save anything unless there are lots of wide fields which are not part of the query. The CREATE TABLE script would answer that. – Jon of All Trades Jun 11 '14 at 21:44
  • Agreed 100%, I'm am merely offering a suggestion - the current clustered scan, which I can only assume is against the dbo.dJobs.jobID, may be ignored in favor of an index on dJobs.jobBookedDate, which again may offer a performance gain. I'm looking forward to the CREATE TABLE or even an XML query plan. Either way, he's out of luck for a seek though, so a refactor is likely the only "Correct" answer here haha – Avarkx Jun 11 '14 at 22:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.