Author |
Topic |
Charlow80
Starting Member
9 Posts |
Posted - 2012-03-17 : 14:45:52
|
My dept. is engaged in Exec summary dashboard reporting project for the entire enterprise, and they used consultants who were responsible for creating and populating the data objects for an AR denormalized data set; they used teradata which will be converting to SQL SERVER after months of advising management that MS SQL SERVER is 10 times better and this is wasting time and money. The query below besides syNtax conversion needs major performance tuning, I see the obvious but with the amount of code I know their are many different ways to skin this cat. So if any one has any suggestions or notices something that would make a huge difference I would really appreciate it. The garbage query written by a teradata mastermind is below, remember it will be converted to SQL; Global temp table and any other obvious items will be changed, I just want some expert insight on the best angle to attack this as soon as possible this person will never create a solution without someone else behind him. The last part of the query is the majority of the performance bottleneck I believe.Thanks,-Chadcreate table emr_service_user.arcube_result_3yrs as (select coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0, pa111.Fact_Lvl_Code0, pa112.Fact_Lvl_Code0, pa113.Fact_Lvl_Code, pa114.Fact_Lvl_Code, pa115.Fact_Lvl_Code0, pa117.Fact_Lvl_Code) Fact_Lvl_Code, coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code, pa111.Parent_Code, pa112.Parent_Code, pa113.Parent_Code, pa114.Parent_Code, pa115.Parent_Code, pa117.Parent_Code) Parent_Code, max(a122.Parent_Desc) Parent_Name, coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type, pa111.Service_Type, pa112.Service_Type, pa113.Service_Type, pa114.Service_Type, pa115.Service_Type, pa117.Service_Type) Service_Type, coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code, pa111.Fact_Lvl_Code, pa112.Fact_Lvl_Code, pa113.Fact_Lvl_Code0, pa114.Fact_Lvl_Code0, pa115.Fact_Lvl_Code, pa117.Fact_Lvl_Code0) Fact_Lvl_Code0, max(case when a122.Fact_Lvl_Code=1 then 'Parallon Total' when a122.Fact_Lvl_Code=2 then 'Corporate' when a122.Fact_Lvl_Code=3 then 'Company' when a122.Fact_Lvl_Code=4 then 'Group' when a122.Fact_Lvl_Code=5 then 'Division' when a122.Fact_Lvl_Code=6 then 'Market' when a122.Fact_Lvl_Code=7 then 'Facility' else ' NA' END) CustCol_2, a120.Year_Id Year_Id, coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1, pa112.CustCol_1, pa113.CustCol_1, pa114.CustCol_1, pa115.CustCol_1, pa117.CustCol_1) CustCol_1, max(a120.Month_Id_Desc_S) Month_Id_Desc_S, max(a121.Month_Desc_S) Month_Desc_S, max(a120.Month_Num) Month_Num, a120.Month_Num Month_Num_Desc_S, max(a120.Month_Num_Desc_S) Month_Num_Desc_S0, max(pa11.AR) AR, max(pa12.ARLM) ARLM, max(pa13.ARSMLY) ARSMLY, max(pa11.BADDEBTWRITEOFFS) BADDEBTWRITEOFFS, max(pa12.BADDEBTWRITEOFFSLM) BADDEBTWRITEOFFSLM, max(pa13.BADDEBTWRITEOFFSSMLY) BADDEBTWRITEOFFSSMLY, max(pa11.MEDICAREARDAYS) MEDICAREARDAYS, max(pa12.MEDICAREARDAYSLM) MEDICAREARDAYSLM, max(pa13.MEDICAREARDAYSSMLY) MEDICAREARDAYSSMLY, max(pa11.BADDEBT) BADDEBT, max(pa11.CHARITY) CHARITY, max(pa11.UNINSURED) UNINSURED, max(pa14.WJXBFS1) TOTALMEDICARELM, max(pa12.ADJNETARLM) ADJNETARLM, max(pa15.WJXBFS1) MEDICARE60DAYSLM, max(pa16.INS90DAYSLM) INS90DAYSLM, max(pa11.GROSSREVENUE) GROSSREVENUE, max(pa13.GROSSREVENUESMLY) GROSSREVENUESMLY, max(pa11.MEDICAIDTOTALCHARGES) MEDICAIDTOTALCHARGES, max(pa12.MEDICAIDTOTALCHARGESLM) MEDICAIDTOTALCHARGESLM, max(pa13.MEDICAIDTOTALCHARGESSMLY) MEDICAIDTOTALCHARGESSMLY, max(pa13.SELFPAY3MONTHAVGSMLY) SELFPAY3MONTHAVGSMLY, max(pa11.UNCOMPENSATEDCARE) UNCOMPENSATEDCARE, max(pa13.UNCOMPENSATEDCARESMLY) UNCOMPENSATEDCARESMLY, max(pa13.ADJNETARSMLY) ADJNETARSMLY, max(pa12.UNCOMPENSATEDCARELM) UNCOMPENSATEDCARELM, max(pa17.WJXBFS1) MEDICARE60DAYSSMLY, max(pa18.INS90DAYS) INS90DAYS, max(pa12.SELFPAY3MONTHAVGLM) SELFPAY3MONTHAVGLM, ZEROIFNULL((((ZEROIFNULL(max(pa13.ANRSMLY)) + ZEROIFNULL(max(pa19.ANRSMLYLM))) + ZEROIFNULL(max(pa110.ANRSMLYL2M))) / NULLIFZERO(((ZEROIFNULL(max(pa13.MONTHDAYSSMLY)) + ZEROIFNULL(max(pa19.MONTHDAYSSMLYLM))) + ZEROIFNULL(max(pa110.MONTHDAYSSMLYL2M)))))) AVGDAILYANRL3MSMLY, ZEROIFNULL((((ZEROIFNULL(max(pa12.ANRLM)) + ZEROIFNULL(max(pa111.ANRL2M))) + ZEROIFNULL(max(pa112.ANRL3M))) / NULLIFZERO(((ZEROIFNULL(max(pa12.MONTHDAYSLM)) + ZEROIFNULL(max(pa111.MONTHDAYSL2M))) + ZEROIFNULL(max(pa112.MONTHDAYSL3M)))))) AVGDAILYANRL3MLM, max(pa113.WJXBFS1) MEDICARE60DAYS, max(pa11.ADJNETAR) ADJNETAR, max(pa114.WJXBFS1) TOTALMEDICARESMLY, max(pa11.SELFPAY3MONTHAVG) SELFPAY3MONTHAVG, max(pa115.INS90DAYSSMLY) INS90DAYSSMLY, max(pa16.TOTALINSURANCELM) TOTALINSURANCELM, max(pa117.WJXBFS1) TOTALMEDICARE, max(pa18.TOTALINSURANCE) TOTALINSURANCE, max(pa12.GROSSREVENUELM) GROSSREVENUELM, max(pa115.TOTALINSURANCESMLY) TOTALINSURANCESMLY, ZEROIFNULL((((ZEROIFNULL(max(pa11.ANR)) + ZEROIFNULL(max(pa12.ANRLM))) + ZEROIFNULL(max(pa111.ANRL2M))) / NULLIFZERO(((ZEROIFNULL(max(pa11.MONTHDAYS)) + ZEROIFNULL(max(pa12.MONTHDAYSLM))) + ZEROIFNULL(max(pa111.MONTHDAYSL2M)))))) AVGDAILYANRL3Mfrom emr_service_user.ZZMD02 pa11 full outer join emr_service_user.ZZMD05 pa12 on (pa11.CustCol_1 = pa12.CustCol_1 and pa11.Fact_Lvl_Code = pa12.Fact_Lvl_Code and pa11.Fact_Lvl_Code0 = pa12.Fact_Lvl_Code0 and pa11.Parent_Code = pa12.Parent_Code and pa11.Service_Type = pa12.Service_Type) full outer join emr_service_user.ZZMD08 pa13 on (coalesce(pa11.CustCol_1, pa12.CustCol_1) = pa13.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code) = pa13.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0) = pa13.Fact_Lvl_Code0 and coalesce(pa11.Parent_Code, pa12.Parent_Code) = pa13.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type) = pa13.Service_Type) full outer join emr_service_user.ZZOP09 pa14 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1) = pa14.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code) = pa14.Fact_Lvl_Code0 and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0) = pa14.Fact_Lvl_Code and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code) = pa14.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type) = pa14.Service_Type) full outer join emr_service_user.ZZOP0A pa15 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1) = pa15.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0) = pa15.Fact_Lvl_Code0 and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code) = pa15.Fact_Lvl_Code and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code) = pa15.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type) = pa15.Service_Type) full outer join emr_service_user.ZZMD0B pa16 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1) = pa16.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0) = pa16.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code) = pa16.Fact_Lvl_Code0 and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code) = pa16.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type) = pa16.Service_Type) full outer join emr_service_user.ZZOP0C pa17 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1) = pa17.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code) = pa17.Fact_Lvl_Code0 and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0) = pa17.Fact_Lvl_Code and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code) = pa17.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type) = pa17.Service_Type) full outer join emr_service_user.ZZMD0D pa18 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1) = pa18.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0) = pa18.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code) = pa18.Fact_Lvl_Code0 and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code) = pa18.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type) = pa18.Service_Type) full outer join emr_service_user.ZZMD0E pa19 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1) = pa19.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code) = pa19.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0) = pa19.Fact_Lvl_Code0 and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code) = pa19.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type) = pa19.Service_Type) full outer join emr_service_user.ZZMD0F pa110 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1) = pa110.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code) = pa110.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0) = pa110.Fact_Lvl_Code0 and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code) = pa110.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type) = pa110.Service_Type) full outer join emr_service_user.ZZMD0G pa111 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1) = pa111.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code) = pa111.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0) = pa111.Fact_Lvl_Code0 and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code) = pa111.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type) = pa111.Service_Type) full outer join emr_service_user.ZZMD0H pa112 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1) = pa112.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code, pa111.Fact_Lvl_Code) = pa112.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0, pa111.Fact_Lvl_Code0) = pa112.Fact_Lvl_Code0 and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code, pa111.Parent_Code) = pa112.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type, pa111.Service_Type) = pa112.Service_Type) full outer join emr_service_user.ZZOP0I pa113 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1, pa112.CustCol_1) = pa113.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code, pa111.Fact_Lvl_Code, pa112.Fact_Lvl_Code) = pa113.Fact_Lvl_Code0 and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0, pa111.Fact_Lvl_Code0, pa112.Fact_Lvl_Code0) = pa113.Fact_Lvl_Code and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code, pa111.Parent_Code, pa112.Parent_Code) = pa113.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type, pa111.Service_Type, pa112.Service_Type) = pa113.Service_Type) full outer join emr_service_user.ZZOP0J pa114 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1, pa112.CustCol_1, pa113.CustCol_1) = pa114.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code, pa111.Fact_Lvl_Code, pa112.Fact_Lvl_Code, pa113.Fact_Lvl_Code0) = pa114.Fact_Lvl_Code0 and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0, pa111.Fact_Lvl_Code0, pa112.Fact_Lvl_Code0, pa113.Fact_Lvl_Code) = pa114.Fact_Lvl_Code and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code, pa111.Parent_Code, pa112.Parent_Code, pa113.Parent_Code) = pa114.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type, pa111.Service_Type, pa112.Service_Type, pa113.Service_Type) = pa114.Service_Type) full outer join emr_service_user.ZZMD0K pa115 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1, pa112.CustCol_1, pa113.CustCol_1, pa114.CustCol_1) = pa115.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code, pa111.Fact_Lvl_Code, pa112.Fact_Lvl_Code, pa113.Fact_Lvl_Code0, pa114.Fact_Lvl_Code0) = pa115.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0, pa111.Fact_Lvl_Code0, pa112.Fact_Lvl_Code0, pa113.Fact_Lvl_Code, pa114.Fact_Lvl_Code) = pa115.Fact_Lvl_Code0 and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code, pa111.Parent_Code, pa112.Parent_Code, pa113.Parent_Code, pa114.Parent_Code) = pa115.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type, pa111.Service_Type, pa112.Service_Type, pa113.Service_Type, pa114.Service_Type) = pa115.Service_Type) full outer join emr_service_user.ZZOP0L pa117 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1, pa112.CustCol_1, pa113.CustCol_1, pa114.CustCol_1, pa115.CustCol_1) = pa117.CustCol_1 and coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code, pa111.Fact_Lvl_Code, pa112.Fact_Lvl_Code, pa113.Fact_Lvl_Code0, pa114.Fact_Lvl_Code0, pa115.Fact_Lvl_Code) = pa117.Fact_Lvl_Code0 and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0, pa111.Fact_Lvl_Code0, pa112.Fact_Lvl_Code0, pa113.Fact_Lvl_Code, pa114.Fact_Lvl_Code, pa115.Fact_Lvl_Code0) = pa117.Fact_Lvl_Code and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code, pa111.Parent_Code, pa112.Parent_Code, pa113.Parent_Code, pa114.Parent_Code, pa115.Parent_Code) = pa117.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type, pa111.Service_Type, pa112.Service_Type, pa113.Service_Type, pa114.Service_Type, pa115.Service_Type) = pa117.Service_Type) join Edwpf_Views.LU_MONTH a120 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1, pa112.CustCol_1, pa113.CustCol_1, pa114.CustCol_1, pa115.CustCol_1, pa117.CustCol_1) = TRIM(BOTH ' ' FROM a120.Month_Id)) join Edwpbs_Views.LU_Year_Month_Past a121 on (coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1, pa112.CustCol_1, pa113.CustCol_1, pa114.CustCol_1, pa115.CustCol_1, pa117.CustCol_1) = TRIM(BOTH ' ' FROM a121.Month_Id)) join Edwpbs_Views.Dim_RCM_Org_Level a122 on (coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code, pa111.Fact_Lvl_Code, pa112.Fact_Lvl_Code, pa113.Fact_Lvl_Code0, pa114.Fact_Lvl_Code0, pa115.Fact_Lvl_Code, pa117.Fact_Lvl_Code0) = a122.Fact_Lvl_Code and coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0, pa111.Fact_Lvl_Code0, pa112.Fact_Lvl_Code0, pa113.Fact_Lvl_Code, pa114.Fact_Lvl_Code, pa115.Fact_Lvl_Code0, pa117.Fact_Lvl_Code) = a122.Fact_Lvl_Code and coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code, pa111.Parent_Code, pa112.Parent_Code, pa113.Parent_Code, pa114.Parent_Code, pa115.Parent_Code, pa117.Parent_Code) = a122.Parent_Code and coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type, pa111.Service_Type, pa112.Service_Type, pa113.Service_Type, pa114.Service_Type, pa115.Service_Type, pa117.Service_Type) = a122.Service_Type_Name)where a120.Year_Id in (2009, 2010, 2011)group by coalesce(pa11.Fact_Lvl_Code0, pa12.Fact_Lvl_Code0, pa13.Fact_Lvl_Code0, pa14.Fact_Lvl_Code, pa15.Fact_Lvl_Code, pa16.Fact_Lvl_Code0, pa17.Fact_Lvl_Code, pa18.Fact_Lvl_Code0, pa19.Fact_Lvl_Code0, pa110.Fact_Lvl_Code0, pa111.Fact_Lvl_Code0, pa112.Fact_Lvl_Code0, pa113.Fact_Lvl_Code, pa114.Fact_Lvl_Code, pa115.Fact_Lvl_Code0, pa117.Fact_Lvl_Code), coalesce(pa11.Parent_Code, pa12.Parent_Code, pa13.Parent_Code, pa14.Parent_Code, pa15.Parent_Code, pa16.Parent_Code, pa17.Parent_Code, pa18.Parent_Code, pa19.Parent_Code, pa110.Parent_Code, pa111.Parent_Code, pa112.Parent_Code, pa113.Parent_Code, pa114.Parent_Code, pa115.Parent_Code, pa117.Parent_Code), coalesce(pa11.Service_Type, pa12.Service_Type, pa13.Service_Type, pa14.Service_Type, pa15.Service_Type, pa16.Service_Type, pa17.Service_Type, pa18.Service_Type, pa19.Service_Type, pa110.Service_Type, pa111.Service_Type, pa112.Service_Type, pa113.Service_Type, pa114.Service_Type, pa115.Service_Type, pa117.Service_Type), coalesce(pa11.Fact_Lvl_Code, pa12.Fact_Lvl_Code, pa13.Fact_Lvl_Code, pa14.Fact_Lvl_Code0, pa15.Fact_Lvl_Code0, pa16.Fact_Lvl_Code, pa17.Fact_Lvl_Code0, pa18.Fact_Lvl_Code, pa19.Fact_Lvl_Code, pa110.Fact_Lvl_Code, pa111.Fact_Lvl_Code, pa112.Fact_Lvl_Code, pa113.Fact_Lvl_Code0, pa114.Fact_Lvl_Code0, pa115.Fact_Lvl_Code, pa117.Fact_Lvl_Code0), a120.Year_Id, coalesce(pa11.CustCol_1, pa12.CustCol_1, pa13.CustCol_1, pa14.CustCol_1, pa15.CustCol_1, pa16.CustCol_1, pa17.CustCol_1, pa18.CustCol_1, pa19.CustCol_1, pa110.CustCol_1, pa111.CustCol_1, pa112.CustCol_1, pa113.CustCol_1, pa114.CustCol_1, pa115.CustCol_1, pa117.CustCol_1), a120.Month_Num) with data primary index (Fact_Lvl_Code,service_type, parent_code); |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-17 : 14:48:54
|
i dont think anybody will have time to go through the whole query and suggest anything without having any tables or sample data info. Why dont you check the execution plan for this and provide us details on costly parts of query and then we can analyse and come up with better approach for them------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Charlow80
Starting Member
9 Posts |
Posted - 2012-03-19 : 16:30:39
|
Ok, after taking you advice its definitely the full outer joins that are a good percentage of the problem. The other part I did not include was the other half of the sql statement which consisted of about 7 or so Global Temp table that's used in the final insert statement of the query. So assuming we address the joins and apply some indexes would you recommend using a Parameter table, CTE, or something where we could index they is joined multiple times or use the temp tables. And as far as joins their has to be a faster alternative like using Apply, Intercept, Union etc... that's the other decision which would you recommend.Thanks,-Chad |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-19 : 16:32:32
|
whats the right outer joins for? can you explain your scenario with a smaller sample dataset?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-20 : 04:44:16
|
No, that's teradata. As the OP said "The query below besides syNtax conversion needs major performance tuning"--Gail ShawSQL Server MVP |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-03-20 : 05:01:44
|
quote: Originally posted by X002548 Is this a new SQL server syntax?create table emr_service_user.arcube_result_3yrs as (
create table KristenTest as( SELECT 1 AS [MyCol1])with data primary index (MyCol1);Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'as'.Server: Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'primary'. |
|
|
X002548
Not Just a Number
15586 Posts |
|
Charlow80
Starting Member
9 Posts |
Posted - 2012-03-20 : 17:35:14
|
It is from teradata which I have never have worked with I know its similar but there are some differences, in order to tune it I was looking for some input to tell are Teradata Developers and DBA's that they need to modify x, then y. An example would be use the a parameter table or whatever works like one in teradata instead of temp table if that is the best option like in many cases it is in SQL SERVER, create sub-queries instead of full outer joins, utilize the apply operator, drop or create indexes things of that nature. I am a BI Analyst working with SQL SERVER and Microstrategy but we do have some dashboards that use teradata datasets. I did not write the code but was trying to help the teradata team, which appear to be lacking on the development side. So if anyone can offer suggestions, code, links, or any other resources on teradata or SQL that would be helpful. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-03-20 : 19:35:04
|
>> So if anyone can offer suggestions, code, links, or any other resources on teradata or SQL that would be helpful.Hire an earth mover?Sorry, just trying to add some levity..but it is VERY poorly written code which APPEARS to based on a very POORLY Designed databaseSorryIf they assigned it to me, I would ask them for the original spec, and go from there...I would not even try to rework this monster..unless I was a consultant, then it would be a dreamBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-20 : 20:03:51
|
quote: Originally posted by X002548 >> So if anyone can offer suggestions, code, links, or any other resources on teradata or SQL that would be helpful.Hire an earth mover?Sorry, just trying to add some levity..but it is VERY poorly written code which APPEARS to based on a very POORLY Designed databaseSorryIf they assigned it to me, I would ask them for the original spec, and go from there...I would not even try to rework this monster..unless I was a consultant, then it would be a dreamBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/
what makes the difference in that case?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
X002548
Not Just a Number
15586 Posts |
|
Charlow80
Starting Member
9 Posts |
Posted - 2012-03-21 : 16:47:34
|
No, I completely agree I am on another project and was asked to take a look at it due to my MSSQL Development background but it’s not MSSQL then when I realized there was enough differences between SQL and Teradata I wanted an outside objective opinion before I concluded that it was crap. It almost looks as if it needs to be in an OLAP environment where it can utilize MDX.Thanks for the feedback, sorry for the lengthy code I do know how to post but I did not think anything but seeing for oneself would believe what I did.-Chad |
|
|
|
|
|