SQL Server Performance Forum – Threads Archive

Help! Super slow SP Performance.

Hello all, I asked the same over in the asp.net forums and got no help so I thought maybe some SQL gurus could help out. Basically when we display a list of brands we need to check that the brand is "full" (has groups, items, applications under it. That part of the query runs fast. Then we check to make sure that the Categories, Brands, Groups, Items, and Applications are not in the NonActive table. This part of the query runs SLOW. Using the Not Exists methods chews up 9-10sec for 67rows. Using the Not IN methods chews up 1-2sec for 67 rows. Below is the code … <pre id="code"> ALTER PROCEDURE [dbo].[SpGetCatalogBrands] – Add the parameters for the stored procedure here @ServiceId int = NULL, @BrandId int = NULL, @CategoryId int = NULL, @ParentId int = NULL, @GroupId int = NULL, @ApplicationId int = NULL, @ShowUniversal int = NULL AS BEGIN – SET NOCOUNT ON added to prevent extra result sets from – interfering with SELECT statements. SET NOCOUNT ON; — Insert statements for procedure here SELECT BrandId, ServiceId, IsActive, BrandCode, [Name], <img src=’, Description, DateAdded, DateModified FROM ItmBrands WHERE BrandId In ( SELECT DISTINCT(BrandId) FROM AllActiveIds a WHERE ( (ItmServiceId = 0 Or ItmServiceId = @ServiceId) AND (CategoryServiceId = 0 Or CategoryServiceId = @ServiceId) AND ((@BrandId IS NULL) OR (BrandId = @BrandId)) AND ((@CategoryId IS NULL) OR (CategoryId = @CategoryId)) AND ((@ParentId IS NULL) OR (ParentId = @ParentId)) AND ((@GroupId IS NULL) OR (GroupId = @GroupId)) AND (((@ApplicationId IS NULL) OR (ApplicationId = @ApplicationId)) OR ((@ShowUniversal IS NULL) OR (ApplicationId = @ShowUniversal))) ) –AND(Not Exists (Select ItmId From ItmXtnNonActives b Where (b.ItmId = a.CategoryId OR b.ItmId = a.ParentId) AND b.ServiceId = @ServiceId AND b.Type = ‘C’)) –AND(Not Exists(Select ItmId From ItmXtnNonActives b Where b.ItmId = a.BrandId AND b.ServiceId = @ServiceId AND b.Type = ‘B’)) –AND(Not Exists(Select ItmId From ItmXtnNonActives b Where b.ItmId = a.GroupId AND b.ServiceId = @ServiceId AND b.Type = ‘G’)) –AND(Not Exists(Select ItmId From ItmXtnNonActives b Where b.ItmId = a.ItemId AND b.ServiceId = @ServiceId AND b.Type = ‘I’)) –AND(Not Exists(Select ItmId From ItmXtnNonActives b Where b.ItmId = a.ApplicationId AND b.ServiceId = @ServiceId AND b.Type = ‘A’)) AND(a.CategoryId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = ‘C’)) AND(a.ParentId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = ‘C’)) AND(a.BrandId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = ‘B’)) AND(a.GroupId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = ‘G’)) AND(a.ItemId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = ‘I’)) AND(a.ApplicationId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = ‘A’)) ) ORDER BY [Name] END </pre id="code"> Here is the execution plan: [IMG]

The response times of NOT EXISTS and EXISTS should be complementary – if one is slower, the other will be faster. Question of data distribution. Although (NOT) EXISTS is often recommended over (NOT) IN, you should always compare response times for the NOT version. For NOT EXISTS and NOT IN, you should also check if a LEFT/RIGHT JOIN with IS NULL criteria on the ‘outside’ column doesn’t work better. Also, do not use DISTINCT in a subquery for an IN clause WHERE BrandId In
(SELECT DISTINCT(BrandId) FROM AllActiveIds a … that’s really a waste of resources. On top of that, I’m a little surprised at the table name ‘AllActiveIds’ – assuming you have a Status code on the Brand table, and a Status table, and that the Status value decides active or not, then I would suggest adding an Active bit column on your Status table. Otherwise, I would include the Active bit column on your Brand table, not on another table.

your real problem is the code below
the SQL optimizer has no way of interpreting what to search,
so it does a table scan on the entire AllActiveIds,
then it hash joins to the ItmXtnNonActives for each case SELECT DISTINCT(BrandId) FROM AllActiveIds a
WHERE ((ItmServiceId = 0 Or ItmServiceId = @ServiceId) AND (CategoryServiceId = 0 Or CategoryServiceId = @ServiceId) AND((@BrandId IS NULL) OR (BrandId = @BrandId)) AND((@CategoryId IS NULL) OR (CategoryId = @CategoryId)) AND((@ParentId IS NULL) OR (ParentId = @ParentId)) AND((@GroupId IS NULL) OR (GroupId = @GroupId)) AND(((@ApplicationId IS NULL) OR (ApplicationId = @ApplicationId)) OR ((@ShowUniversal IS NULL) OR (ApplicationId = @ShowUniversal)))
you need to see rules on whether all nulls can be passed in,
or whether 1 or more moust be not-null @ServiceId int = NULL,@BrandId int = NULL,@CategoryId int = NULL,@ParentId int = NULL,@GroupId int = NULL,@ApplicationId int = NULL,@ShowUniversal int = NULL if any single parameter is a good search condition
then do an if block
IF @ServiceId IS NOT NULL BEGIN
END
ELSE IF @BrandId IS NOT NULL BEGIN
if it requires 2 parameters for a meaningful search then require 2
i know you do not want to code this way
but it is the only way or your code will suck performance wise
and you may as well give up then you should use an if

quote:Originally posted by Adriaan The response times of NOT EXISTS and EXISTS should be complementary – if one is slower, the other will be faster. Question of data distribution. Although (NOT) EXISTS is often recommended over (NOT) IN, you should always compare response times for the NOT version. For NOT EXISTS and NOT IN, you should also check if a LEFT/RIGHT JOIN with IS NULL criteria on the ‘outside’ column doesn’t work better. Also, do not use DISTINCT in a subquery for an IN clause WHERE BrandId In
(SELECT DISTINCT(BrandId) FROM AllActiveIds a … that’s really a waste of resources. On top of that, I’m a little surprised at the table name ‘AllActiveIds’ – assuming you have a Status code on the Brand table, and a Status table, and that the Status value decides active or not, then I would suggest adding an Active bit column on your Status table. Otherwise, I would include the Active bit column on your Brand table, not on another table.

Adriaan, Thanks for the input. I’m not sure if I understand your LEFT/RIGHT join comment. I may though so I will try what I think you are talking about and check back in. RE: Distinct. I played with that. Whether it is in or not doesn’t seem to affect performance from what I can tell. I dropped it though per your advice. RE: AllActiveIds. Each "Catalog" table Brands, Categories, Groups, Items has a bit IsActive column. The AllActiveIds table aggregates all the ActiveIds (PK, Int). This way we never show an empty category, brand, group, etc. It’s easier for us than checking bits in all 5 tables everytime we want to display something. Thanks! Kyle

quote:Originally posted by joechang your real problem is the code below
the SQL optimizer has no way of interpreting what to search,
so it does a table scan on the entire AllActiveIds,
then it hash joins to the ItmXtnNonActives for each case SELECT DISTINCT(BrandId) FROM AllActiveIds a
WHERE ((ItmServiceId = 0 Or ItmServiceId = @ServiceId) AND (CategoryServiceId = 0 Or CategoryServiceId = @ServiceId) AND((@BrandId IS NULL) OR (BrandId = @BrandId)) AND((@CategoryId IS NULL) OR (CategoryId = @CategoryId)) AND((@ParentId IS NULL) OR (ParentId = @ParentId)) AND((@GroupId IS NULL) OR (GroupId = @GroupId)) AND(((@ApplicationId IS NULL) OR (ApplicationId = @ApplicationId)) OR ((@ShowUniversal IS NULL) OR (ApplicationId = @ShowUniversal)))
you need to see rules on whether all nulls can be passed in,
or whether 1 or more moust be not-null @ServiceId int = NULL,@BrandId int = NULL,@CategoryId int = NULL,@ParentId int = NULL,@GroupId int = NULL,@ApplicationId int = NULL,@ShowUniversal int = NULL if any single parameter is a good search condition
then do an if block
IF @ServiceId IS NOT NULL BEGIN
END
ELSE IF @BrandId IS NOT NULL BEGIN
if it requires 2 parameters for a meaningful search then require 2
i know you do not want to code this way
but it is the only way or your code will suck performance wise
and you may as well give up then you should use an if

Joe, Thanks for the reply. @ServiceId is the only required parameter and there is no NULL condition on that. The rest could all be null or all have a value. I was with you on you assessment until I started playing with things. If I take out the whole AllActiveIds block the time decrease is minimal. If I take out the nonactive block the query drops to a couple ms. Thanks,
Kyle

]]>