SQL Server Performance

Help! Super slow SP Performance.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by kswest, May 28, 2007.

  1. kswest New Member

    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.<br /><br />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.<br /><br />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.<br /><br />Using the Not Exists methods chews up 9-10sec for 67rows.<br /><br />Using the Not IN methods chews up 1-2sec for 67 rows.<br /><br />Below is the code ... <br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />ALTER PROCEDURE [dbo].[SpGetCatalogBrands]<br />-- Add the parameters for the stored procedure here<br />@ServiceId int = NULL,<br />@BrandId int = NULL,<br />@CategoryId int = NULL,<br />@ParentId int = NULL,<br />@GroupId int = NULL,<br />@ApplicationId int = NULL,<br />@ShowUniversal int = NULL<br />AS<br />BEGIN<br />-- SET NOCOUNT ON added to prevent extra result sets from<br />-- interfering with SELECT statements.<br />SET NOCOUNT ON;<br /> -- Insert statements for procedure here<br />SELECT BrandId, ServiceId, IsActive, BrandCode, [Name], <img src=', Description, DateAdded, DateModified <br /><br />FROM ItmBrands<br /><br />WHERE BrandId In <br /><br />(<br /><br />SELECT DISTINCT(BrandId) FROM AllActiveIds a<br /><br />WHERE<br />(<br />(ItmServiceId = 0 Or ItmServiceId = @ServiceId) AND <br />(CategoryServiceId = 0 Or CategoryServiceId = @ServiceId) AND<br />((@BrandId IS NULL) OR (BrandId = @BrandId)) AND<br />((@CategoryId IS NULL) OR (CategoryId = @CategoryId)) AND<br />((@ParentId IS NULL) OR (ParentId = @ParentId)) AND<br />((@GroupId IS NULL) OR (GroupId = @GroupId)) AND<br />(((@ApplicationId IS NULL) OR (ApplicationId = @ApplicationId)) OR ((@ShowUniversal IS NULL) OR (ApplicationId = @ShowUniversal)))<br />)<br /><br />--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'))<br />--AND(Not Exists(Select ItmId From ItmXtnNonActives b Where b.ItmId = a.BrandId AND b.ServiceId = @ServiceId AND b.Type = 'B'))<br />--AND(Not Exists(Select ItmId From ItmXtnNonActives b Where b.ItmId = a.GroupId AND b.ServiceId = @ServiceId AND b.Type = 'G'))<br />--AND(Not Exists(Select ItmId From ItmXtnNonActives b Where b.ItmId = a.ItemId AND b.ServiceId = @ServiceId AND b.Type = 'I'))<br />--AND(Not Exists(Select ItmId From ItmXtnNonActives b Where b.ItmId = a.ApplicationId AND b.ServiceId = @ServiceId AND b.Type = 'A'))<br /><br />AND(a.CategoryId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = 'C'))<br />AND(a.ParentId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = 'C'))<br />AND(a.BrandId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = 'B'))<br />AND(a.GroupId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = 'G'))<br />AND(a.ItemId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = 'I'))<br />AND(a.ApplicationId NOT IN(Select ItmId From ItmXtnNonActives b Where b.ServiceId = @ServiceId AND b.Type = 'A'))<br /><br />) <br /><br />ORDER BY [Name]<br /><br />END<br /><br /></font id="code"></pre id="code"><br /><br /><br />Here is the execution plan:<br /><br />[IMG]
  2. Adriaan New Member

    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.
  3. joechang New Member

    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
  4. kswest New Member

    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
  5. kswest New Member

    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

Share This Page