1

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:

iamdave
  • 12,023
  • 3
  • 24
  • 53
Syed
  • 69
  • 4
  • 1
    What indexes are on 2016Q4_VITALS_EXPORT_SERVICE? – Jacob H Jun 07 '17 at 15:00
  • Why are you having to cast fields as date? How are these dates stored? – Jonathan Willcock Jun 07 '17 at 15:04
  • 1
    Try to avoid the table scan by creating an index on that. this saves lots of i/o because fat arrows = lots of rows. That RID Lookup can optimized with a clustered index. https://blogs.msdn.microsoft.com/craigfr/2006/06/30/bookmark-lookup/ – coding Bott Jun 07 '17 at 15:36
  • I already indexed SVC_DT & claim_id fields. SVC_DT is a varchar data type and I need to cast it to pull accurate monthly data. – Syed Jun 07 '17 at 15:46
  • Brend - thanks for the suggestion, I can try creating a clust index but it'l eat up so much space with 100+ million rows in it. It just a raw table but will investigate. Thanks. – Syed Jun 07 '17 at 15:49

2 Answers2

2
 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.

StrayCatDBA
  • 2,740
  • 18
  • 25
  • Actually if SVC_DT is a datetime then this isn't an issue. The cast/convert from datetime to date is actually SARGable. :) http://sqlblog.com/blogs/rob_farley/archive/2010/01/22/sargable-functions-in-sql-server.aspx – Sean Lange Jun 07 '17 at 15:39
  • Actually SVC_DT is varchar data type. It's a raw client data. That's the reason for casting. – Syed Jun 07 '17 at 15:44
  • 1
    @Syed this solution will work for string datatype as good as date – Mikhail Lobanov Jun 07 '17 at 15:48
  • Mikhail Lobanov: actually I thought about it but wasn't sure. Thanks for the tips!! – Syed Jun 07 '17 at 15:52
0

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.

Mustafa Erdogan
  • 103
  • 7