[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Fix slow running SP

Posted on 2008-06-20
6
Medium Priority
?
196 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

656 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