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 2008 Forums
 Transact-SQL (2008)
 Major Performance issue due to SQL Statement

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,
-Chad


create 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)))))) AVGDAILYANRL3M
from 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 22:00:28
That looks like Oracle or UDB or DB2

Is this really SQL Server..maybe the created a new syntax?

I doubt it

However

Do you know what FULL OUTER JOIN DOES?

And the to do it against like x number of tables?

Table 1 must match every row against all other tables
ect

I don't think that's what's required

WHAT is this suppose to do

Did they give you a requirement?

Can you share THAT with us?




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 22:01:09
Is this a new SQL server syntax?

create table emr_service_user.arcube_result_3yrs as (



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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 1
Incorrect syntax near the keyword 'as'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'primary'.

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 10:17:36
quote:
Originally posted by GilaMonster

No, that's teradata. As the OP said "The query below besides syNtax conversion needs major performance tuning"

--
Gail Shaw
SQL Server MVP



See, there's that pesky having to read things again

First thing I wold do would be to format this code as a start



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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.

Go to Top of Page

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 database

Sorry

If 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 dream





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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 database

Sorry

If 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 dream





Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/





what makes the difference in that case?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 01:06:03
http://www.youtube.com/watch?v=JkhX5W7JoWI



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

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
Go to Top of Page
   

- Advertisement -