CODEWITHSUNDEEP
sql-serverquery-tuning
Syed
Syed

Reputation: 69

How to increase Simple T-SQL query Performance - taking long hours to complete. Please....!

I have the following simple query with 2 table joins and a WHERE clause.

I already indexed the key joining fields as well as the WHERE clause field.

These 2 tables have about 123 million rows which is pretty huge. It takes about 7/8 hours to complete the full query below. I indexed and not sure what else to do since it's such a simple query with a JOIN & WHERE clauses:

insert into dbo.IMS_CLAIMS(CLAIM_ID
                          ,DX_CLAIM_ID
                          ,SVC_SEQ_NBR
                          ,SVC_DT
                          ,ALLWD_AMT
                          ,BILL_AMT
                          )
select h.CLAIM_ID
      ,h.DX_CLAIM_ID
      ,s.SVC_SEQ_NBR
      ,h.SVC_DT
      ,s.ALLWD_AMT
      ,s.BILL_AMT
from [2016Q4_VITALS_EXPORT_HEADERS] as h
    inner join [2016Q4_VITALS_EXPORT_SERVICE] as s
        on h.claim_id = s.claim_id
where cast(SVC_DT as date) >= '2015-10-01'
      and cast(SVC_DT as date) <= '2016-11-30';

Estimated Execution Plan:

Upvotes: 1

Views: 59

Answers (2)

Mustafa Erdogan
Mustafa Erdogan

Reputation: 113

If you have any indexes on the table: dbo.IMS_CLAIMS, drop them all.

Bulk insert on a heap is much faster, than on a table with indexes.

Upvotes: 0

StrayCatDBA
StrayCatDBA

Reputation: 2880

 WHERE cast(SVC_DT as date) >= '2015-10-01' And cast(SVC_DT as date) <= '2016-11-30'

When you cast SVC_DT to a date, you are forcing SQL Server to read every row, perform the cast, then do the comparison to '2015-10-01'. This precludes the usage of indexes on SVC_DT.

 WHERE SVC_DT >= '2015=10-01'  AND SVC_DT < '2016-12-01'

Note that we have to bump the ending date from 11/30 to 12/01, and that the <= changed to <

The logic is the same as above, but now SQL Server can use an index.

Upvotes: 2

Related Questions