CODEWITHSUNDEEP
sqlsql-serverjoin
isorfir
isorfir

Reputation: 771

SQL Server joining temp table takes a very long time

I'm trying to add a column to the end of my results that shows if any item in a PO is stocked (with a 'Yes' or 'No'). opPOTRACK is my table with POs and other info that I want to display, inItemLocation is the table that has a row per item. They are (loosely) connection via opPOTRACK.cpono = inItemLocation.PONO after some text manipulation.

So far I've figured that the best way would be to put the results in a temporary table and then join that table with my normal query. Two of the tables join on a column where I have to derive the PO number (I didn't design the database and can't change the schema). Doing the string manipulation doesn't slow down my first join, so I'm not sure if it is affecting my second join. When I added the second join it just chugs for several minutes (canceled after 4 minutes; not sure how long it would really take).

I'm sure there is something messed up with how I'm joining.

IF OBJECT_ID('tempdb..#StockedTbl') IS NOT NULL
BEGIN
    DROP TABLE #StockedTbl
END

CREATE TABLE #StockedTbl
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    PONo varchar(10),
    Stocked varchar(5)
)


INSERT INTO #StockedTbl
(
    PONo,
    Stocked
)
SELECT item.PONo, "Stocked" = CASE 
                      WHEN item.Loc LIKE 'ABC%' THEN 'No'
                      ELSE 'Yes'
                      END 
                      FROM inItemLocation item 

CREATE CLUSTERED INDEX IDX_C_StockedTbl_UserID ON #StockedTbl(PONo)


SELECT  *
FROM  (SELECT  RIGHT(LEFT(item.PONo,6), 5) AS POTrunc, item.WhseLoc, po.cpono, po.Buyer, po.Vendno, po.OrderDate, po.StockDate, 
                      po.StockNote, po.Status, po.StatusChangeDate,
                      ROW_NUMBER() OVER (PARTITION BY po.cpono ORDER BY po.cpono) AS RowNumber,
                      st.Stocked
FROM          dbo.inItemLocation item
                    LEFT JOIN dbo.opPOTRACK po ON RIGHT(LEFT(item.PONo,6), 5) = po.cpono
                    LEFT JOIN #StockedTbl st ON RIGHT(LEFT(item.PONo,6), 5) = RIGHT(LEFT(st.PONo,6), 5)
) AS a
WHERE   a.RowNumber = 1 

DROP TABLE #StockedTbl

Upvotes: 2

Views: 6925

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

I would suggest this instead. First, why does the #temp table need an IDENTITY column, and why does it need to be the PRIMARY KEY? Second, why not make a column that facilitates the join better?

CREATE TABLE #StockedTbl
(
    PONo varchar(10),
    Stocked varchar(5),
    PONoMatch AS CONVERT(CHAR(5), RIGHT(LEFT(PONo,6),5)) PERSISTED
);
CREATE CLUSTERED INDEX x ON #StockedTbl(PONoMatch);

Now at least you can take those calculations out of some of your clauses, but depending on where the work is being done (the execution plan can tell us that), you may want to consider computed columns on your base tables as well.

Upvotes: 3

Related Questions