• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

Fix slow running SP

I have a SP that gives me a list of Products NOT in a joiner table, takes a little over 2 seconds to run.  Any idea on how to speed this up?

ALTER PROCEDURE [ultrawellness].[PartnerProductsNotSelected]
      -- Add the parameters for the stored procedure here
      @PartnerID as uniqueidentifier
AS
BEGIN
SELECT * FROM Product
WHERE ProductID NOT IN(SELECT     Product.ProductID
FROM         PartnerProduct INNER JOIN
                      Product ON PartnerProduct.ProductID = Product.ProductID
WHERE     (PartnerProduct.PartnerID = @PartnerID ) ) AND ProductRowID <> 597
END
0
alivemedia
Asked:
alivemedia
1 Solution
 
Serge FournierAnalyst ProgrammerCommented:
add some index on the product key?
(edit the table and add an index on this column)

clusterize this index? (if you are not on a vm)
(in the column properties, you can aligne your index data with the hard disk sectors for readahead)

you can only have one clusterized index per table (you cannot aligne with hard disk sectors 2 different data obviously :P)
0
 
Serge FournierAnalyst ProgrammerCommented:
is your parameters (products) text fileds or regular string?

might have to convert them to string for faster results
0
 
CMYScottCommented:
make sure you have indexes on ProductID in the Product table AND the PartnerProduct table.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Jagdish DevakuSr DB ArchitectCommented:
hi...

create index on Product & PartnerProduct tables...


then try to run the procedure... i think it will definitely improve the performance...
0
 
PockyMasterCommented:

or you could left outer join
something like:

SELECT PR.* FROM Product PR
LEFT OUTER JOIN
PartnerProduct PP ON PP.ProductID = PP.ProductID
WHERE PP.PartnerID = @PartnerID
AND PR.ProductRowID <> 597
AND PP.ProductID IS NULL

or you could use the except statement in sql 2005
;WITH Products AS
(
     SELECT ProductID FROM Product WHERE ProductRowID <> 597
EXCEPT
     SELECT ProductID FROM PartnerProduct WHERE ProductRowID <> 597
       AND PartnerID = @PartnerID
)
SELECT PP.* FROM Product PP INNER JOIN Products P ON PP.ProductID = P.ProductID
 
0
 
alivemediaAuthor Commented:
I ended up just selecting 3 columns instead of all of them from the product table like an idiot and I went from 2 seconds to .02, indexes were already on the tables - thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now