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
LVL 2
alivemediaAsked:
Who is Participating?
 
alivemediaConnect With a Mentor Author 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
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
CMYScottCommented:
make sure you have indexes on ProductID in the Product table AND the PartnerProduct table.
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.