January 26, 2012 at 7:51 am
Good morning, in light of the fact that I got such great help with a much simpler query yesterday, I figured I'd "throw" this one up here to see if anyone had any suggestions for tuning it up...This query we wrote for SSRS and it calls data from our AS400. Thanks so much for taking a look.
="SELECT cwsispak.softpreg.regname, ARWFCA.CUST.CCMPNY, CWSISPAK.SOFTPREG.CCNAME, SUM(CASE WHEN ARWFCA.CUST.CSTRDT >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & " AND ARWFCA.CUST.CSTRDT <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & " and arwfca.sale.sactmy <> 0 and arwfca.sale.smnths > 0 THEN ARWFCA.SALE.STOT/arwfca.sale.smnths else 0 END) + SUM(CASE WHEN ARWFCA.CUST.CSTRDT >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & " AND ARWFCA.CUST.CSTRDT <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & " and arwfca.sale.sactmy <> 0 AND ARWFCA.SALE.SMNTHS <= 0 THEN ARWFCA.SALE.STOT else 0 END) + SUM(CASE WHEN ARWFCA.CUST.CSTRDT >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & " AND ARWFCA.CUST.CSTRDT <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & " and arwfca.sale.sactmy is NULL THEN (ARWFCA.cust.cmth$ - ARWFCA.CUST.CTOT4) Else 0 END) AS NEW, " &
"SUM(CASE WHEN ARWFCA.CUST.CSTOP >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & " AND ARWFCA.CUST.CSTOP <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & " and arwfca.sale.smnths > 0 THEN ARWFCA.SALE.STOT/arwfca.sale.smnths Else 0 END) + SUM(CASE WHEN ARWFCA.CUST.CSTOP >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & " AND ARWFCA.CUST.CSTOP <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & " and arwfca.sale.smnths <=0 THEN ARWFCA.SALE.STOT Else 0 END) + SUM(CASE WHEN ARWFCA.CUST.CSTOP >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & " AND ARWFCA.CUST.CSTOP <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & " and arwfca.sale.sactmy is NULL THEN (ARWFCA.cust.cmth$ - ARWFCA.CUST.CTOT4) Else 0 END) AS LOST, count(distinct case when ARWFCA.CUST.CSTRDT >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & " AND ARWFCA.CUST.CSTRDT <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & " then arwfca.cust.ccust# end) as NewCount, count(distinct case when ARWFCA.CUST.CSTop >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & " AND ARWFCA.CUST.CSTop <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & " then arwfca.cust.ccust# end) as LostCount " &
"FROM ARWFCA.CUST FULL OUTER JOIN CWSISPAK.SOFTPREG on ARWFCA.CUST.CCMPNY =CWSISPAK.SOFTPREG.SPCOMP " &
"left outer join ARWFCA.SALE on ARWFCA.CUST.CCMPNY = ARWFCA.SALE.SCOMP AND ARWFCA.CUST.CCUST# = ARWFCA.SALE.SCUST and arwfca.sale.stot > 0 and (ARWFCA.SALE.SGLCD NOT IN ('FUEL', 'FUECR', 'ENVIR', 'FC', 'FC-', 'ST', '4195M' , '1110','4245', '5445') and arwfca.sale.sglcd not like ('4145%') and arwfca.sale.sglcd not like ('BD%') and arwfca.sale.sctyp <> 'P' AND (ARWFCA.SALE.SACTMY = " &
"(SELECT MAX(SACTMY) AS Expr1 " &
"FROM ARWFCA.SALE s2 " &
"WHERE (s2.SCOMP = ARWFCA.SALE.SCOMP) AND (s2.SCUST = ARWFCA.SALE.SCUST) and s2.stot > 0 and ((s2.SACTMY >= " & FORMAT(Dateadd("M", -2, PARAMETERS!PARAMETER1.VALUE), "yyMM") & " ) AND (s2.SACTMY <= " & FORMAT(Dateadd("m", 1, PARAMETERS!PARAMETER2.VALUE), "yyMM") & " ) and s2.SGLCD NOT IN ('FUEL', 'FUECR', 'ENVIR', 'FC', 'FC-', 'ST', '4195M' , '1110','4245', '5445') and s2.sglcd not like ('4145%') and s2.sglcd not like ('BD%') and s2.stot > 0 and s2.sctyp <> 'P')))) " &
"WHERE ((CWSISPAK.SOFTPREG.COMPTYPE = 'HAULING' AND (CWSISPAK.SOFTPREG.ACTIVE = 'Y') AND (CWSISPAK.SOFTPREG.SPCOMP <> 'HT')) AND ARWFCA.CUST.CCYCLE NOT IN ('ZZ','OP','P1') AND (ARWFCA.CUST.CSTRDT >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & ") AND (ARWFCA.CUST.CSTRDT <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & ")) OR " &
"((CWSISPAK.SOFTPREG.COMPTYPE = 'HAULING' AND (CWSISPAK.SOFTPREG.ACTIVE = 'Y') AND (CWSISPAK.SOFTPREG.SPCOMP <> 'HT')) AND ARWFCA.CUST.CCYCLE NOT IN ('ZZ','OP','P1') AND (ARWFCA.CUST.CSTOP >= " & FORMAT(PARAMETERS!PARAMETER1.VALUE, "yyyyMMdd") & ") AND (ARWFCA.CUST.CSTOP <= " & FORMAT(PARAMETERS!PARAMETER2.VALUE, "yyyyMMdd") & ")) " &
"GROUP BY cwsispak.softpreg.regname, ARWFCA.CUST.CCMPNY, CWSISPAK.SOFTPREG.CCNAME"
January 26, 2012 at 8:42 am
Don't construct SQL commands in SSRS. Create a stored procedure and pass the SSRS parameters into the stored procedure.
Do you really need both a FULL OUTER JOIN and a LEFT OUTER JOIN?
Your LEFT OUTER JOIN should be replaced with an OUTER APPLY with a TOP (1) clause.
Every single one of your aggregates filters the data based on the parameters. This should either be in the WHERE clause or you should use an OUTER APPLY.
If you want more, post sample data and re-format your code so that it's easily consumable. Someone should be able to copy and paste your code into SSMS and be able to run it without having to remove equal signs, double quotes, ampersands, SSRS functions, etc.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 26, 2012 at 8:59 am
I will reformat the code....sorry!
January 26, 2012 at 9:11 am
So, you've got an ODBC data source to an AS-400 instance? If so, I think it's unlikely that anyone would have anything particularly constructive to add here as to why it's running slowly, it'd require some expertise on AS-400.
January 26, 2012 at 9:52 am
How would you like to see some sample data? What format etc?
January 26, 2012 at 10:29 am
OK, here is the code stripped down. I do have an ODBC conn to the AS-400.
SELECT cwsispak.softpreg.regname, ARWFCA.CUST.CCMPNY, CWSISPAK.SOFTPREG.CCNAME, SUM(CASE WHEN ARWFCA.CUST.CSTRDT >= 20120101 AND ARWFCA.CUST.CSTRDT <= 20120125 and arwfca.sale.sactmy <> 0 and arwfca.sale.smnths > 0 THEN ARWFCA.SALE.STOT/arwfca.sale.smnths else 0 END) + SUM(CASE WHEN ARWFCA.CUST.CSTRDT >= 20120101 AND ARWFCA.CUST.CSTRDT <= 20120125 and arwfca.sale.sactmy <> 0 AND ARWFCA.SALE.SMNTHS <= 0 THEN ARWFCA.SALE.STOT else 0 END) + SUM(CASE WHEN ARWFCA.CUST.CSTRDT >= 20120101 AND ARWFCA.CUST.CSTRDT <= 20120125 and arwfca.sale.sactmy is NULL THEN (ARWFCA.cust.cmth$ - ARWFCA.CUST.CTOT4) Else 0 END) AS NEW,
SUM(CASE WHEN ARWFCA.CUST.CSTOP >= 20120101 AND ARWFCA.CUST.CSTOP <= 20120125 and arwfca.sale.smnths > 0 THEN ARWFCA.SALE.STOT/arwfca.sale.smnths Else 0 END) + SUM(CASE WHEN ARWFCA.CUST.CSTOP >= 20120101 AND ARWFCA.CUST.CSTOP <= 20120125 and arwfca.sale.smnths <=0 THEN ARWFCA.SALE.STOT Else 0 END) + SUM(CASE WHEN ARWFCA.CUST.CSTOP >= 20120101 AND ARWFCA.CUST.CSTOP <= 20120125 and arwfca.sale.sactmy is NULL THEN (ARWFCA.cust.cmth$ - ARWFCA.CUST.CTOT4) Else 0 END) AS LOST, count(distinct case when ARWFCA.CUST.CSTRDT >= 20120101 AND ARWFCA.CUST.CSTRDT <= 20120125 then arwfca.cust.ccust# end) as NewCount, count(distinct case when ARWFCA.CUST.CSTop >= 20120101 AND ARWFCA.CUST.CSTop <= 20120125 then arwfca.cust.ccust# end) as LostCount
FROM ARWFCA.CUST FULL OUTER JOIN CWSISPAK.SOFTPREG on ARWFCA.CUST.CCMPNY =CWSISPAK.SOFTPREG.SPCOMP
left outer join ARWFCA.SALE on ARWFCA.CUST.CCMPNY = ARWFCA.SALE.SCOMP AND ARWFCA.CUST.CCUST# = ARWFCA.SALE.SCUST and arwfca.sale.stot > 0 and (ARWFCA.SALE.SGLCD NOT IN ('FUEL', 'FUECR', 'ENVIR', 'FC', 'FC-', 'ST', '4195M' , '1110','4245', '5445') and arwfca.sale.sglcd not like ('4145%') and arwfca.sale.sglcd not like ('BD%') and arwfca.sale.sctyp <> 'P' AND (ARWFCA.SALE.SACTMY =
(SELECT MAX(SACTMY) AS Expr1
FROM ARWFCA.SALE s2
WHERE (s2.SCOMP = ARWFCA.SALE.SCOMP) AND (s2.SCUST = ARWFCA.SALE.SCUST) and s2.stot > 0 and ((s2.SACTMY >= 1111) AND (s2.SACTMY <= 1202) and s2.SGLCD NOT IN ('FUEL', 'FUECR', 'ENVIR', 'FC', 'FC-', 'ST', '4195M' , '1110','4245', '5445') and s2.sglcd not like ('4145%') and s2.sglcd not like ('BD%') and s2.stot > 0 and s2.sctyp <> 'P'))))
WHERE ((CWSISPAK.SOFTPREG.COMPTYPE = 'HAULING' AND (CWSISPAK.SOFTPREG.ACTIVE = 'Y') AND (CWSISPAK.SOFTPREG.SPCOMP <> 'HT')) AND ARWFCA.CUST.CCYCLE NOT IN ('ZZ','OP','P1') AND (ARWFCA.CUST.CSTRDT >= 20120101) AND (ARWFCA.CUST.CSTRDT <= 20120125)) OR
((CWSISPAK.SOFTPREG.COMPTYPE = 'HAULING' AND (CWSISPAK.SOFTPREG.ACTIVE = 'Y') AND (CWSISPAK.SOFTPREG.SPCOMP <> 'HT')) AND ARWFCA.CUST.CCYCLE NOT IN ('ZZ','OP','P1') AND (ARWFCA.CUST.CSTOP >= 20120101) AND (ARWFCA.CUST.CSTOP <= 20120125))
GROUP BY cwsispak.softpreg.regname, ARWFCA.CUST.CCMPNY, CWSISPAK.SOFTPREG.CCNAME
January 26, 2012 at 11:30 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply