Best Method to pull 54 Million records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Best Method to pull 54 Million records

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-01-12 : 15:44:33

I have a select statement that pulls back prob around 54+ million records. But is taking forever to complete. What would be the best approach to pulling this data?

select
sch.CPT,
sch.modifier,
substring(cpt.cpt_desc,1,25) descp,
sch.office,
sch.facility ,
sch.technical,
sch.default_discount,
sch.professional ,

sch.notations,
sm.payer,
sm.subscriber_product,
sm.applicable_party,
sm.comment,
sm.fee_id ,
(pr.last_name +', ' + pr.first_name + isnull(pr.middle_name,'')) as provider,
pr.provider_id ,
cl.clinic_id,
cl.clinic_name,
sm.effective_dt,
sm.ineffective_dt

into #MyDump
from dm_fee_schedule sch inner JOIN dm_fee_schedule_metadata sm
on sch.fee_id = sm.fee_id AND sm.source_type is null AND sch.fee_id in ( SELECT DISTINCT fee_id FROM [rdc_dm].[dbo].[dm_Fee_schedule_Metadata] WHERE source_type is null)

left outer join cpt_codes cpt on cpt.cpt=sch.cpt

inner join rdc_dm.dbo.dm_cit_fee_schedule_acl acl
on acl.fee_id = sch.fee_id

inner join rdc_dm.dbo.dm_cpm_provider pr
on acl.section_id = pr.section_id
AND pr.source_type = 'PROV'
AND pr.exp_date IS NULL
--AND pr.provider_id = 38
inner join rdc_dm.dbo.dm_cpm_health_care_entity hc
on hc.provider_id=pr.provider_id
AND hc.hc_staff_id is null
AND hc.source_type = 'PROV'
AND hc.exp_date IS NULL

inner join rdc_dm.dbo.dm_cpm_clinic cl
on cl.clinic_id=hc.clinic_id
AND cl.source_type = 'PROV'
AND cl.exp_date IS NULL
group by sch.CPT,
sch.modifier,
substring(cpt.cpt_desc,1,25),
sch.office,
sch.facility,
sch.technical,
sch.default_discount,
sch.professional,
sm.payer,
sm.subscriber_product,
sm.applicable_party,
sm.comment,
sch.CPT,
sm.fee_id,
cl.clinic_id,
cl.clinic_name,
sch.notations,
sm.effective_dt,
sm.ineffective_dt,
pr.last_name+', '+pr.first_name+isnull(pr.middle_name,''),
pr.provider_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-12 : 15:49:30

Why do you need so much data? What are you going to do with #MyDump? Show us the execution plan in XML format.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-12 : 18:02:17

Get rid of the GROUP BY! If that gives you duplicate rows, then adjust the query as needed to avoid them, or, less efficient but still much better than GROUP BY add a DISTINCT to the query.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-01-13 : 01:44:45

This amounts of rows requires some strategic thinking. Read the data loading performance guide for tips http://www.sqlserver-dba.com/2011/11/data-loading-performance-guide.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2015-01-13 : 09:29:10

quote:
Originally posted by tkizer

Why do you need so much data? What are you going to do with #MyDump? Show us the execution plan in XML format.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Tkizer, that amount data is required, I can't change that. I was using the temp table to see if that would decrease time. it's not needed though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources