would like to create a nested query on the stored procedure

  • 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'

  • 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?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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

    9010120100228204765948.400000 77 0

    901010 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?

  • 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)


    9010120100228204765948.400000 77 0

    901010 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?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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

  • Sharon, can you post up the actual execution plan for this query?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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