Query slow. Help improve?

Question

Query slow. Help improve?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

SQLStud 74876 SSC Veteran Points: 247 More actions · Answer 1

I ended up rewriting this after sorting out exactly what it does. It is very simple actually.

It compares an import table to the table UnivEnrollMain08. (Why 08, I don't know about his naming conventions yet! It wasn't 2008) The process either updates or inserts into the enrollment table, then inserts data into a coverage table.

I simply used a merge for the enrollment table, and an insert into the coverage table. I wrote UDFs for all the date and zip code nonsense. Not perfect, but a step in the right direction. I used his tables, so the naming convention is strange.

;MERGE xxx.UnivEnrollMain08 u USING xxx.MainImport mi ON u.ID_Number = mi.ID WHEN MATCHED THEN UPDATE SET u.Last_name = mi.LastName ,u.First_name = mi.FirstName ,u.MI = mi.MiddleInitial ,u.Address1 = mi.Address1 ,u.Address2 = mi.Address2 ,u.City = mi.City ,u.[State] = mi.[State] ,u.Zip = mi.Zip ,u.Home_phone = mi.HomePhone ,u.Gender = mi.Gender ,u.Marital_Status = mi.MaritalStatus ,u.DOB = mi.DateOfBirth ,u.Hire = mi.HireDate ,u.Job_class = mi.JobClass ,u.PayrollHomeOrg = mi.PayrollOrg ,u.Appt_end = mi.ApptEndDate ,u.Employ_status = mi.EmploymentStatus ,u.Status_effect = mi.StatusEffect ,Benefit_eligible = mi.BenefitEligible ,u.gp_pd_status = mi.GPStatus ,u.change_reason = mi.ChangeReason WHEN NOT MATCHED BY TARGET THEN insert ( ID_Number ,Last_name ,First_name ,MI ,Address1 ,Address2 ,City ,[State] ,Zip ,Home_phone ,Gender ,Marital_Status ,DOB ,Hire ,Job_class ,PayrollHomeOrg ,Appt_end ,Employ_status ,Status_effect ,Benefit_eligible ,change_reason ,gp_pd_status ,alter_prem ,alter_who_pays ,isFutureChange ,dtmFutureChange ,strFutureChange ,terminated ) values ( mi.ID ,mi.LastName ,mi.FirstName ,mi.MiddleInitial ,mi.Address1 ,mi.Address2 ,mi.City ,mi.[State] ,mi.Zip ,mi.HomePhone ,mi.Gender ,mi.MaritalStatus ,mi.DateOfBirth ,mi.HireDate ,mi.JobClass ,mi.PayrollOrg ,mi.ApptEndDate ,mi.EmploymentStatus ,mi.StatusEffect ,mi.BenefitEligible ,mi.ChangeReason ,mi.GPStatus ,mi.alter_prem ,mi.alter_who_pays ,mi.isFutureChange ,mi.dtmFutureChange ,mi.strFutureChange ,mi.Terminated );

Coverage table.
-- Deal with the coverage table. insert into xxx.UnivCoverage08 ( Univ_ID ,Enroll_med ,Enroll_dent ,Enroll_vis ,Enroll_self ,self_med ,self_dent ,self_vis ,COBRA ,Ins_privacy ,pdMedPrem ,univMedPrem ,pdDentPrem ,univDentPrem ,pdVisPrem ,univVisPrem ,pdLifePrem ,univLifePrem ,pdContribution ,univContribution ,edit_by ,isDWC ,NewHireDate ) select mi.ID_Number ,p.MedicalPlanCoverage ,'DMO_E' ,'E' ,'E' ,'Y' ,'Y' ,'Y' ,'YES' ,'YES' ,0 ,p.MedicalCost * bmv.BillMonths ,0 ,p.DentalCost * bmv.BillMonths ,0 ,p.VisionCost * bmv.BillMonths ,0 ,p.LifeCost * bmv.BillMonths ,0 ,(p.MedicalCost + p.DentalCost + p.VisionCost + p.LifeCost) * bmv.BillMonths ,'Internal' ,0 ,cast(getdate() as date) from xxx.MainImport mi left join xxx.UnivCoverage08 c08 on c08.Univ_ID = mi.ID_Number outer apply dbo.udfGetUnivRates(mi.Zip, @RateId) p outer apply dbo.udfBillingMonthValue(cast(mi.Job_class as date)) bmv where c08.Univ_ID is null

frederico_fonseca SSCoach Points: 16036 More actions · Answer 2

SQLStud 74876 - Monday, October 15, 2018 12:55 PM
I ended up rewriting this after sorting out exactly what it does. It is very simple actually.
It compares an import table to the table UnivEnrollMain08. (Why 08, I don't know about his naming conventions yet! It wasn't 2008) The process either updates or inserts into the enrollment table, then inserts data into a coverage table.
I simply used a merge for the enrollment table, and an insert into the coverage table. I wrote UDFs for all the date and zip code nonsense. Not perfect, but a step in the right direction. I used his tables, so the naming convention is strange.
;MERGE xxx.UnivEnrollMain08 u USING xxx.MainImport mi ON u.ID_Number = mi.ID WHEN MATCHED THEN UPDATE SET u.Last_name = mi.LastName ,u.First_name = mi.FirstName ,u.MI = mi.MiddleInitial ,u.Address1 = mi.Address1 ,u.Address2 = mi.Address2 ,u.City = mi.City ,u.[State] = mi.[State] ,u.Zip = mi.Zip ,u.Home_phone = mi.HomePhone ,u.Gender = mi.Gender ,u.Marital_Status = mi.MaritalStatus ,u.DOB = mi.DateOfBirth ,u.Hire = mi.HireDate ,u.Job_class = mi.JobClass ,u.PayrollHomeOrg = mi.PayrollOrg ,u.Appt_end = mi.ApptEndDate ,u.Employ_status = mi.EmploymentStatus ,u.Status_effect = mi.StatusEffect ,Benefit_eligible = mi.BenefitEligible ,u.gp_pd_status = mi.GPStatus ,u.change_reason = mi.ChangeReason WHEN NOT MATCHED BY TARGET THEN insert ( ID_Number ,Last_name ,First_name ,MI ,Address1 ,Address2 ,City ,[State] ,Zip ,Home_phone ,Gender ,Marital_Status ,DOB ,Hire ,Job_class ,PayrollHomeOrg ,Appt_end ,Employ_status ,Status_effect ,Benefit_eligible ,change_reason ,gp_pd_status ,alter_prem ,alter_who_pays ,isFutureChange ,dtmFutureChange ,strFutureChange ,terminated ) values ( mi.ID ,mi.LastName ,mi.FirstName ,mi.MiddleInitial ,mi.Address1 ,mi.Address2 ,mi.City ,mi.[State] ,mi.Zip ,mi.HomePhone ,mi.Gender ,mi.MaritalStatus ,mi.DateOfBirth ,mi.HireDate ,mi.JobClass ,mi.PayrollOrg ,mi.ApptEndDate ,mi.EmploymentStatus ,mi.StatusEffect ,mi.BenefitEligible ,mi.ChangeReason ,mi.GPStatus ,mi.alter_prem ,mi.alter_who_pays ,mi.isFutureChange ,mi.dtmFutureChange ,mi.strFutureChange ,mi.Terminated );
Coverage table.
-- Deal with the coverage table. insert into xxx.UnivCoverage08 ( Univ_ID ,Enroll_med ,Enroll_dent ,Enroll_vis ,Enroll_self ,self_med ,self_dent ,self_vis ,COBRA ,Ins_privacy ,pdMedPrem ,univMedPrem ,pdDentPrem ,univDentPrem ,pdVisPrem ,univVisPrem ,pdLifePrem ,univLifePrem ,pdContribution ,univContribution ,edit_by ,isDWC ,NewHireDate ) select mi.ID_Number ,p.MedicalPlanCoverage ,'DMO_E' ,'E' ,'E' ,'Y' ,'Y' ,'Y' ,'YES' ,'YES' ,0 ,p.MedicalCost * bmv.BillMonths ,0 ,p.DentalCost * bmv.BillMonths ,0 ,p.VisionCost * bmv.BillMonths ,0 ,p.LifeCost * bmv.BillMonths ,0 ,(p.MedicalCost + p.DentalCost + p.VisionCost + p.LifeCost) * bmv.BillMonths ,'Internal' ,0 ,cast(getdate() as date) from xxx.MainImport mi left join xxx.UnivCoverage08 c08 on c08.Univ_ID = mi.ID_Number outer apply dbo.udfGetUnivRates(mi.Zip, @RateId) p outer apply dbo.udfBillingMonthValue(cast(mi.Job_class as date)) bmv where c08.Univ_ID is null

Just curious - how are you dealing with IFSR 17 - way those tables were built would probably make quite hard to deal with.

SQLStud 74876 SSC Veteran Points: 247 More actions · Answer 3

frederico_fonseca - Monday, October 15, 2018 12:59 PM
SQLStud 74876 - Monday, October 15, 2018 12:55 PM
I ended up rewriting this after sorting out exactly what it does. It is very simple actually.
It compares an import table to the table UnivEnrollMain08. (Why 08, I don't know about his naming conventions yet! It wasn't 2008) The process either updates or inserts into the enrollment table, then inserts data into a coverage table.
I simply used a merge for the enrollment table, and an insert into the coverage table. I wrote UDFs for all the date and zip code nonsense. Not perfect, but a step in the right direction. I used his tables, so the naming convention is strange.
;MERGE xxx.UnivEnrollMain08 u USING xxx.MainImport mi ON u.ID_Number = mi.ID WHEN MATCHED THEN UPDATE SET u.Last_name = mi.LastName ,u.First_name = mi.FirstName ,u.MI = mi.MiddleInitial ,u.Address1 = mi.Address1 ,u.Address2 = mi.Address2 ,u.City = mi.City ,u.[State] = mi.[State] ,u.Zip = mi.Zip ,u.Home_phone = mi.HomePhone ,u.Gender = mi.Gender ,u.Marital_Status = mi.MaritalStatus ,u.DOB = mi.DateOfBirth ,u.Hire = mi.HireDate ,u.Job_class = mi.JobClass ,u.PayrollHomeOrg = mi.PayrollOrg ,u.Appt_end = mi.ApptEndDate ,u.Employ_status = mi.EmploymentStatus ,u.Status_effect = mi.StatusEffect ,Benefit_eligible = mi.BenefitEligible ,u.gp_pd_status = mi.GPStatus ,u.change_reason = mi.ChangeReason WHEN NOT MATCHED BY TARGET THEN insert ( ID_Number ,Last_name ,First_name ,MI ,Address1 ,Address2 ,City ,[State] ,Zip ,Home_phone ,Gender ,Marital_Status ,DOB ,Hire ,Job_class ,PayrollHomeOrg ,Appt_end ,Employ_status ,Status_effect ,Benefit_eligible ,change_reason ,gp_pd_status ,alter_prem ,alter_who_pays ,isFutureChange ,dtmFutureChange ,strFutureChange ,terminated ) values ( mi.ID ,mi.LastName ,mi.FirstName ,mi.MiddleInitial ,mi.Address1 ,mi.Address2 ,mi.City ,mi.[State] ,mi.Zip ,mi.HomePhone ,mi.Gender ,mi.MaritalStatus ,mi.DateOfBirth ,mi.HireDate ,mi.JobClass ,mi.PayrollOrg ,mi.ApptEndDate ,mi.EmploymentStatus ,mi.StatusEffect ,mi.BenefitEligible ,mi.ChangeReason ,mi.GPStatus ,mi.alter_prem ,mi.alter_who_pays ,mi.isFutureChange ,mi.dtmFutureChange ,mi.strFutureChange ,mi.Terminated );
Coverage table.
-- Deal with the coverage table. insert into xxx.UnivCoverage08 ( Univ_ID ,Enroll_med ,Enroll_dent ,Enroll_vis ,Enroll_self ,self_med ,self_dent ,self_vis ,COBRA ,Ins_privacy ,pdMedPrem ,univMedPrem ,pdDentPrem ,univDentPrem ,pdVisPrem ,univVisPrem ,pdLifePrem ,univLifePrem ,pdContribution ,univContribution ,edit_by ,isDWC ,NewHireDate ) select mi.ID_Number ,p.MedicalPlanCoverage ,'DMO_E' ,'E' ,'E' ,'Y' ,'Y' ,'Y' ,'YES' ,'YES' ,0 ,p.MedicalCost * bmv.BillMonths ,0 ,p.DentalCost * bmv.BillMonths ,0 ,p.VisionCost * bmv.BillMonths ,0 ,p.LifeCost * bmv.BillMonths ,0 ,(p.MedicalCost + p.DentalCost + p.VisionCost + p.LifeCost) * bmv.BillMonths ,'Internal' ,0 ,cast(getdate() as date) from xxx.MainImport mi left join xxx.UnivCoverage08 c08 on c08.Univ_ID = mi.ID_Number outer apply dbo.udfGetUnivRates(mi.Zip, @RateId) p outer apply dbo.udfBillingMonthValue(cast(mi.Job_class as date)) bmv where c08.Univ_ID is null
Just curious - how are you dealing with IFSR 17 - way those tables were built would probably make quite hard to deal with.

We are rebuilding from the ground up. We have until 2021, right?

frederico_fonseca SSCoach Points: 16036 More actions · Answer 4

SQLStud 74876 - Monday, October 15, 2018 1:07 PM
frederico_fonseca - Monday, October 15, 2018 12:59 PM
Just curious - how are you dealing with IFSR 17 - way those tables were built would probably make quite hard to deal with.
We are rebuilding from the ground up. We have until 2021, right?

kind of. Its for annual reporting periods starting on or after 1 Jan 2021. And it is still likely that the implementation period may be extended for another year.

SQLStud 74876 SSC Veteran Points: 247 More actions · Answer 5

frederico_fonseca - Monday, October 15, 2018 1:14 PM
SQLStud 74876 - Monday, October 15, 2018 1:07 PM
frederico_fonseca - Monday, October 15, 2018 12:59 PM
Just curious - how are you dealing with IFSR 17 - way those tables were built would probably make quite hard to deal with.
We are rebuilding from the ground up. We have until 2021, right?
kind of. Its for annual reporting periods starting on or after 1 Jan 2021. And it is still likely that the implementation period may be extended for another year.

Good to know! Thank you.

SoCal_DBD Hall of Fame Points: 3371 More actions · Answer 6

Jeff Moden - Monday, October 15, 2018 12:43 PM
SQLStud 74876 - Monday, October 15, 2018 12:04 PM
Jeff Moden - Saturday, October 13, 2018 10:25 AM
Heh... I just saw your other post on the monster wide table that violated every form of normalization there is and where every update was preserved without the benefit of proper historical auditing structures (audit tables, etc) not to mention such problems as what if there is more than 5 children (or less than 5 children) and that the SSN columns don't appear to be encrypted, just to dog-ear a couple of the multitude of problems with that table. Few could be both that ignorant and arrogant about T-SQL, PII, and databases in general, especially all at once. So, switching gears and giving you the benefit of the doubt, could it be that maybe, just maybe, you're a whole lot smarter than you're currently letting on to?
Could the following scenario actually be true?
Could it be that you're actually VERY smart about SQL Server and T-SQL and, much more importantly, about the nature of people (especially in a "hostile to the DBA" environment) and that you're stuck with a whole herd of "developers" (lower case very intentional , in this case or, perhaps, an idiot savant contractor that you need to flush out to your management?) that write the kind of awful code that you posted on all three posts and you cannot convince them to do otherwise and that you're actually getting feedback from them about how good they think they are as their self-proclaimed line of defense? And could it be that you actually made a deal with the developers that if you posted the code even biased in their "I'm pretty good at this" direction that folks on this forum would trash the original code and then show the right way to do it, which would absolutely win you the bet and compel the "developers" to start to listen to you?
If so, then you're a very clever and wise person. If not, then you're actually someone that needs some severe help with what a database actually is and how it should be used.
Winner! Well, not VERY smart, but smarter than the "idiot savant" who crapped this out. The poor people who own the company had no idea what was under the hood. They trusted a friend who "knows a great programmer." I have my work cut out for me, but luckily we now have some sharp SQL developers to help out. Thank you for the posts, sorry for the way I went about it.
Heh.... your role reversal was nearly impeccable. One of the giveaways was that I could feel the sarcasm dripping with your "Easy, right?" comment. Your humility about "not VERY smart" and your comment about the "poor people who own the company" make you someone that I'd love to work with.
Well played, good Sir!

Good heavens Jeff.....! Just when I thought you couldn't be more smarty pants, you completely blew me away with your "reading between the lines" here. I am gobsmacked. I bow down to you sir!! 😎