Can I make it faster?
Well I have a series of sps that use a storage solution that we developed in-house. While it works pretty well for the most part, there is one stored procedure that is very slow. On average, it takes about 30 minutes. I know exactly where the throat of the bottle is, I just don't know how to fix it.
Basically what the stored procedure does is create a series of temp table variables and insert them into those tables. no problem. it then joins the temporary tables and inserts them into another temporary table, twice (the second is slightly different).
it's a statement that joins tables and inserts them into temp table, and is the part that takes forever. any help would be greatly appreciated.
; with Requested as
(
select distinct
PONUMBER as PONumber,
min(REQSTDBY) as RequestedBy
from dw.POP10110
where REQSTDBY <>''
group by PONUMBER
)
insert into @tblTableA
(
PONumber,
ReceiptNumber,
ReceiptLineNumber,
VendorID,
POType,
QuantityShipped,
QuantityInvoiced,
ItemNumber,
ItemDescription,
UofM,
UnitCost,
ExtendedCost,
SiteID,
ProjectNumber,
AccountID,
RequestedBy,
GLPostDate,
VendorName,
CostCategoryID
)
select
a.PONUMBER,
a.POPRCTNM,
a.RCPTLNNM,
a.VENDORID,
a.POPTYPE,
a.QTYSHPPD,
a.QTYINVCD,
b.ITEMNMBR,
b.ITEMDESC,
b.UOFM,
b.UNITCOST,
b.EXTDCOST,
b.LOCNCODE,
b.ProjNum,
case i.CostCategoryID
when 'MISC' then isnull(i.AccountID,'N/A')
else
case j.CostCategoryID
when 'MISC' then
isnull(j.AccountID,'N/A')
else
isnull(c.PurchaseAccount,'N/A')
end
end as AccountID,
d.RequestedBy,
coalesce(e.GLPOSTDT, f.GLPOSTDT, '') as GLPostDate,
coalesce(e.VENDNAME, f.VENDNAME, '') as VENDNAME,
case i.CostCategoryID when 'MISC' then i.CostCategoryID else
case j.CostCategoryID when 'MISC' then j.CostCategoryID else coalesce(g.CostCategoryID, h.CostCategoryID, '') end
end as CostCategoryID
from dw.POP10500 a
inner join dw.POP30310 b
on a.PONUMBER=b.PONUMBER
and a.POPRCTNM=b.POPRCTNM
and a.RCPTLNNM=b.RCPTLNNM
left outer join @gl00100 c
on b.INVINDX=c.ActID
left outer join Requested d
on b.PONUMBER = d.PONumber
left outer join dw.POP30300 e
on b.POPRCTNM=e.POPRCTNM
left outer join dw.POP10300 f
on b.POPRCTNM=f.POPRCTNM
left outer join @pop31310 g
on b.POPRCTNM=g.ReceiptNumber
left outer join @pop11310 h
on b.POPRCTNM=h.ReceiptNumber
left outer join @pop30390 i
on a.POPRCTNM=i.ReceiptNumber
left outer join @pop10390 j
on a.POPRCTNM=j.ReceiptNumber
As far as I know, the corresponding indexes are in place.
a source to share
How many rows are in your temporary tables / table variables?
It looks like you are using table variables, but if they have a lot of rows, consider using temp tables (tables with a name starting with C #) instead. You can add additional indexes to these tables, whereas table variables can only have one clustered index.
i.e. convert table variables @ pop31310, @ pop11310, @ pop30390, @ pop10390
to temporary tables, and give each column index a ReceiptNumber.
a source to share
Caveat: Most of my experience is with Oracle, not SQLServer, but I believe the answer remains.
Typically, you want to minimize the number of tables in the join because the optimizer will have to work through permutations and tends to get confused. With datastore type queries, I would look at no more than three tables in a single join and buy chunks.
Another obvious question is, are these temporary tables indexed? If not, then you will need to do a relatively inefficient concatenation of all rows (either a table scan or a hash join if supported).
a source to share
Your variable tables (the ones that start with @) are probably not indexed.
The only indexing that can be added to the variable table is the primary key of the clustered index.
This physically orders the variable table using the OrderID.
DECLARE @Orders TABLE
(
OrderID int PRIMARY KEY,
CustomerID int
OrderDate datetime
)
This physically orders the @Orders table by CustomerID and breaks ties using OrderID. It is now suitable to connect to CustomerID.
DECLARE @Orders TABLE
(
OrderID int,
CustomerID int
OrderDate datetime,
PRIMARY KEY(CustomerID, OrderID)
)
Also - keep in mind that variable tables do not have statistics during optimization. The optimizer will always treat them as having 0 rows and will use nested loop joins when other join types might be more appropriate.
a source to share