October 26, 2010 at 8:41 am
I have a report that runs off the following stored procedure, because i can not join the tables i need to do a union query or a nested query to return the shipments. any one know how i can speed this up be chaging the stored procedure to use a nested query on the last portion of the query (under union)?
SELECT DISTINCT
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.DIVISION as COMPANY,
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.SEASON AS SEASON,
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.STYLE as STYLE,
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.MONTH_END_DATE AS ME_DATE,
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.BEGINNING_BALANCE AS BEG_BAL,
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.CURRENT_COST AS CRNT_COST,
dbo.RECVSKU#.RCQTY AS REC_QTY,
0 AS SHIP_QTY,
dbo.RECVSKU#.RCDATE AS REC_DATE,
'' AS SHIP_DATE
FROM
dbo.INV_BEG_BALANCE_JAN_JUNE_2010 LEFT OUTER JOIN
dbo.RECVSKU# ON
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.SEASON = dbo.RECVSKU#.RCSEAS AND
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.STYLE = dbo.RECVSKU#.RCSTYL AND
dbo.INV_BEG_BALANCE_JAN_JUNE_2010.DIVISION = dbo.RECVSKU#.RCDIVN
WHERE
(dbo.INV_BEG_BALANCE_JAN_JUNE_2010.MONTH_END_DATE = '20100228')
AND (dbo.RECVSKU#.RCDATE >= '20100301') AND (dbo.RECVSKU#.RCDATE <= '20100331')
AND dbo.INV_BEG_BALANCE_JAN_JUNE_2010.DIVISION = 'AAA'
union
SELECT
dbo.SHIPSKU#.SCDIVN AS COMPANY,
dbo.SHIPSKU#.SCSEAS AS SEASON,
dbo.SHIPSKU#.SCSTYL AS STYLE,
'' AS ME_DATE,
0 AS BEG_BALE,
dbo.MITMAS.MMPUPR AS CRNT_COST,
0 AS REC_QTY,
dbo.SHIPSKU#.SCQTY AS SHIP_QTY,
'' AS REC_DATE,
dbo.SHIPSKU#.SCDATE AS SHIP_DATE
FROM dbo.SHIPSKU#
LEFT OUTER JOIN dbo.MITMAS ON dbo.SHIPSKU#.SCSKU# = dbo.MITMAS.MMITNO
WHERE dbo.SHIPSKU#.SCDATE>='20100301' AND dbo.SHIPSKU#.SCDATE <='20100331'
AND dbo.SHIPSKU#.SCDIVN = 'AAA'
October 26, 2010 at 9:07 am
Hi Sharon
Try to get into the habit of using table aliases, they make code like this very much more readable:
SELECT DISTINCT
b.DIVISION as COMPANY,
b.SEASON AS SEASON,
b.STYLE as STYLE,
b.MONTH_END_DATE AS ME_DATE,
b.BEGINNING_BALANCE AS BEG_BAL,
b.CURRENT_COST AS CRNT_COST,
s.RCQTY AS REC_QTY,
0 AS SHIP_QTY,
s.RCDATE AS REC_DATE,
'' AS SHIP_DATE
FROM dbo.INV_BEG_BALANCE_JAN_JUNE_2010 b
LEFT OUTER JOIN dbo.RECVSKU# s ON
b.SEASON = s.RCSEAS AND
b.STYLE = s.RCSTYL AND
b.DIVISION = s.RCDIVN
WHERE
(b.MONTH_END_DATE = '20100228')
AND (s.RCDATE >= '20100301') AND (s.RCDATE <= '20100331')
AND b.DIVISION = 'AAA'
UNION
SELECT
k.SCDIVN AS COMPANY,
k.SCSEAS AS SEASON,
k.SCSTYL AS STYLE,
'' AS ME_DATE,
0 AS BEG_BALE,
m.MMPUPR AS CRNT_COST,
0 AS REC_QTY,
k.SCQTY AS SHIP_QTY,
'' AS REC_DATE,
k.SCDATE AS SHIP_DATE
FROM dbo.SHIPSKU# k
LEFT OUTER JOIN dbo.MITMAS m ON k.SCSKU# = m.MMITNO
WHERE k.SCDATE>='20100301'
AND k.SCDATE <='20100331'
AND k.SCDIVN = 'AAA'
If you run those two unioned queries separately, how long does each of them take?
Try using UNION ALL. UNION on its own will eliminate dupes from the results of the second query. Do you need DISTINCT in the first query?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 26, 2010 at 9:19 am
the queries took to long and were timing out the reports. the union or union all query returns to rows of data for one style
90101 20100228 20476594 8.400000 77 0
90101 0 0 8.400000 0 158
how would i get it to return the value of 158 next to the 77 and only get one row of data.
is that even possible?
October 26, 2010 at 9:30 am
sharonmtowler (10/26/2010)
the queries took to long and were timing out the reports. the union or union all query returns to rows of data for one style
If the whole query returns the same number of rows whether you use UNION or UNION ALL, then use UNION ALL because it's quicker. Do you need the DISTINCT?
Are all of the columns used in the joins indexed?
sharonmtowler (10/26/2010)
90101 20100228 20476594 8.400000 77 090101 0 0 8.400000 0 158
how would i get it to return the value of 158 next to the 77 and only get one row of data.
is that even possible?
Each of the two queries in your original post outputs 10 columns, you only show 6 in the 2 rows above. Is this from the same query?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 26, 2010 at 9:38 am
If the whole query returns the same number of rows whether you use UNION or UNION ALL, then use UNION ALL because it's quicker. Do you need the DISTINCT? -- in process of testing right now.
Are all of the columns used in the joins indexed?-- yes
Each of the two queries in your original post outputs 10 columns, you only show 6 in the 2 rows above. Is this from the same query?
i removed some columns because i wanted to line up the example in here. it returns 10
October 26, 2010 at 11:47 am
Sharon, can you post up the actual execution plan for this query?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 26, 2010 at 1:11 pm
it is a query that captures a beg inventory balance, receipts and shipments in a time frame
the beg balance is associated with the previous months end date
in jan i had a beginning inventory balance on style 1 of 1000 units
in receive 20 units, shipped 800 leaves and ending balance of 220 units (beg + rec-ships)
then when it is run for the next month it picks up the 220 units as its beg balance- adds receipts subtracts shipments to get new ending balance.
these values stem from parameters. the calculations are done in the report.
the way the union query is setup (not me, someone else) it takes forever to get the data, and it brings in to much data.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply