CODEWITHSUNDEEP
sqlsql-serverperformancet-sql
ActiveX
ActiveX

Reputation: 1166

Slow Query Execution: Takes long time to execute => Very Strange Why?

I am using SQL Server 2012 Express from Microsoft. Database/data size is small to intermediate with only like 50 tables, although some tables do have 100k - 200k records. The query that gives me trouble does run across one of the larger tables (time_list).

Tables involved:

BAD NEWs: The current tables do not have any indexes besides PK/FK

PROBLEM:

I have a query (see below) that runs very slow and the execution speed depends on the date filter applied to the query, but the reason why it runs slow is counter intuitive.

Observations based on my test cases below:

  1. It appears that once I try to query anything within the year 2019, it just kills the performance.

  2. It is interesting to note that I can query huge date ranges and everything runs fine, but a very specific range in year 2019 causes the query just to stall. I even ran the query by removing the date filter completely and it executed in 681 ms.

  3. This problem is observable on LIVE server and test server and is reproducible by playing with the date filter in the query. Both servers observe very similar time delays although hardware is a bit different.

    Let me present the test cases (date filter is applied at the bottom of the query):

    • Case 1) Execution time: 681 ms <= LIGHTNING FAST

    No date filter, the query returns the most records in the result set.

    • Case 2) Execution time: 1.1 sec <= FAST!

    Date filter range: and t.date between '1900-01-01' AND '2019-03-31'

    Time Range: ~119 years

    • Case 3) Execution time: 1:40 min(s) <= SLOW

    Date filter range: and t.date between '2019-01-01' and '2019-03-06'

    Time Range: ~ 3 months

    • Case 4) Execution time: Had to stop the query after 6 min(s), it just kept going <= SUPER SLOW

    Date filter range: and t.date between '2018-03-01' AND '2019-03-31'

    Time Range: ~1 year

Actual Query:

select 
    result.JobId as ProjectId, job.file_number as FileNumber, 
    job.street_name1 as ProjectAddress, addr.StreetMetadata as AddressMetadata, 
    client.client_number as ClientNumber, 
    pln.Lot as PlanLot, pln.plan_number as PlanNumber,
    result.TimeDate as TimeDate
from
    (select distinct
         job.entity_id as JobId, job.client_entity_id as ClientId, 
         (select top 1 t2.date 
          from time_list t2
          inner join Code_Department_Types dept2 on dept2.entity_id = t2.department_id
          inner join Code_Billing_Types bill2 on bill2.entity_id = t2.billing_id
          where (t2.job_id = job.entity_id and ((bill2.name='L/O on GRADE' and dept2.name='F')))
          order by t2.date desc) as TimeDate
    from 
        time_list t
    inner join 
        Code_Department_Types dept on dept.entity_id = t.department_id
    inner join 
        Code_Billing_Types bill on bill.entity_id = t.billing_id
    inner join 
        Job_Info job on t.job_id = job.entity_id
    where 
         EXISTS (select t.entity_id FROM time_list t1
                inner join Code_Department_Types dept on dept.entity_id = t1.department_id
                inner join Code_Billing_Types bill on bill.entity_id = t1.billing_id
                where 
                    t1.job_id = job.entity_id and (bill.name='L/O on GRADE' and dept.name='F')
        ) and not 
         EXISTS (select t.entity_id FROM time_list t1
                inner join Code_Department_Types dept on dept.entity_id = t1.department_id
                inner join Code_Billing_Types bill on bill.entity_id = t1.billing_id
                where 
                    t1.job_id = job.entity_id and (bill.name='SRPR' and dept.name='F')
        )
            and t.date between '1900-01-01' AND '2019-03-31' -- FAST! (1.1 sec)
            --and t.date between '2019-01-01' and '2019-03-06'  -- SLOW (1:40)
                --and t.date between '2018-03-01' AND '2019-03-31'  -- SLOW (> 6 mins, and still going, had to stop it)
        ) as result

        inner join Companies client on client.entity_id = result.ClientId
        inner join Job_Info job on job.entity_id = result.JobId
        inner join Job_Address addr on addr.JobId = job.entity_id
        inner join Job_Plans pln on pln.job_id = result.JobId
        order by FileNumber asc;

Upvotes: 1

Views: 2175

Answers (2)

ActiveX
ActiveX

Reputation: 1166

As pointed out by the posts, adding an index (on the date field) immediately fix the problem. I will monitor this but for now marking this post as "Answered".

Upvotes: 1

Dakota
Dakota

Reputation: 474

Your comment specifying run time deltas on updating your date parameters suggests there's some data integrity issues. Does this help at all?

CAST(t.[Date] AS DATE) BETWEEN '1900-01-01' AND '2019-03-31'

Upvotes: 0

Related Questions