Solved

Fix slow running SP

Posted on 2008-06-20
6
192 Views
Last Modified: 2010-03-19
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
Comment
Question by:alivemedia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21835823
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
 
LVL 12

Expert Comment

by:Serge Fournier
ID: 21835829
is your parameters (products) text fileds or regular string?

might have to convert them to string for faster results
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21835867
make sure you have indexes on ProductID in the Product table AND the PartnerProduct table.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21836735
hi...

create index on Product & PartnerProduct tables...


then try to run the procedure... i think it will definitely improve the performance...
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 21837016

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
 
LVL 2

Accepted Solution

by:
alivemedia earned 0 total points
ID: 21837483
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question