Solved

Fix slow running SP

Posted on 2008-06-20
6
191 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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 …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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