Professional Web Applications Themes

Inconsistantly slow query - Microsoft SQL / MS SQL Server

A stored procedure that produces a single result from a view occasionally takes a very long time to execute. Most of the time, though, it works almost instantaneously. The view from which the results are gathered is a view of another view constructed from the joining of two large tables (~ 1 Million rows each) plus a couple of views of realy tiny tables that contain descriptions for codes. Most of the time the result set contains only one row; occassionally it may contain as many as a hand-full, though I have not encountered the latter, yet. My questions are: ...

  1. #1

    Default Inconsistantly slow query

    A stored procedure that produces a single result from a view occasionally
    takes a very long time to execute. Most of the time, though, it works almost
    instantaneously.

    The view from which the results are gathered is a view of another view
    constructed from the joining of two large tables (~ 1 Million rows each)
    plus a couple of views of realy tiny tables that contain descriptions for
    codes. Most of the time the result set contains only one row; occassionally
    it may contain as many as a hand-full, though I have not encountered the
    latter, yet.

    My questions are:
    Can anyone give me a clue to what this sporadic slowness may be
    attributed?
    Is basing a result set on a view of a view containing joins with other
    views something I should avoid?

    --
    Willy Esteban


    Willy Guest

  2. #2

    Default Re: Inconsistantly slow query

    Willy
    I would go to run SET STATISTICS IO ON command to determine what
    indexes were used or perhaps was scan table.
    Do you have WHERE clause in your SP?
    If you do so check does QA use index to perfom the query?




    "Willy Esteban" <net> wrote in message
    news:#phx.gbl... 
    almost 
    occassionally 


    Uri Guest

  3. #3

    Default Re: Inconsistantly slow query

    Thank you, Bob and Uri for your responses.
    I read the "parameter sniffing" message and it was very interesting. I don't
    think it is the cause of this particular problem but it will certainly help
    me write better scripts is the future.

    I am not allowed be too specific about the schema but it is not too
    different from the following:

    The Products table has
    ProductID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NewID())
    ProductCode CHAR(32)
    Description VARCHAR(100)
    ...other properties

    index on ProductCode
    may have 1 million rows

    The PriceListDetails table has
    PriceListDetailID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NewID())
    PriceListID UNIQUEIDENTIFIER
    ProductID UNIQUEIDENTIFIER
    Price MONEY
    BusinessRuleID UNIQUEIDENTIFIER
    ...other properties

    index on PriceListID (added today after posting of message)
    index on ProductID (added today after posting of message)

    may have ~2-3 million rows

    The BusinessRules table has
    BusinessRuleID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NewID())
    BaseBusinessRuleID UNIQUEIDENTIFIER
    Code CHAR(3)
    Description VARCHAR(100)
    QtyLimit int
    CommissionFactor Decimal(5,4)
    DiscountFactor Decimal(5,4)

    currently not indexed
    may only have about 100-200 rows

    The BaseBusinessRules table has
    BaseBusinessRuleID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NewID())
    Description Varchar(100)

    BTW, all the appropriate relationships have been established Products and
    PriceListDetails, BusinessRules and PriceListDetails and BaseBusinessRules
    and BusinessRules

    The BusinessRulesView is:

    SELECT BusinessRules.*, BaseBusinessRules.Description AS
    BaseBusinessRuleDescription
    FROM BaseBusinessRules INNER JOIN
    BusinessRules ON BaseBusinessRules.BaseBusinessRuleID =
    BusinnessRules.BaseBusinessRuleID

    The PriceListDetailsView is
    SELECT
    PriceListDetails.*,
    Products.ProductCode, Products.Description as ProductDescription,
    Products.SomeOtherAttributes,
    BusinessRulesView.Code as BusinessRuleCode,
    BusinessRules.Description as BusinessRuleDescription,
    BusinessRulesView.QtyLimit, BusinessRuleView.CommissionFactor,
    BusinessRuleView.DiscountFactor
    FROM PriceListDetails INNER JOIN
    Products ON PriceListDetails.ProductID = Products.ProductID LEFT
    OUTER JOIN
    BusinessRulesView ON PriceListsDetails.BusinessRuleID =
    BusinessRulesView.BusinessRuleID


    One SP is
    CREATE PROCEDURE SelectOneProductCodeInOnePriceList((ProductCode
    Varchar(32), PriceListID uniqueidentifier)
    as
    --Return one or more rows for a ProductCode for One PriceList.
    select * from PriceListDetailsView where ProductCode = ProductCode
    and PriceListID = PriceListID

    There is another one similar to the about SP that is for one ProductID and
    one PriceList
    There are other procedures that return as single row based on a
    PriceListDetailID

    Occassionally, the SP shown above will take over a minute to procude one row
    but most of the time it is virtually instantaneous.

    After adding the two indexes indicated above, it appears that the performace
    is even faster than before. I suspect that my problem is going to be a lack
    of appropriate indexes. Furthermore, it seems to me that it would be wise to
    add an index for the view itself.

    If you or anyone has other suggestions, it will be greatly appreciated.
    Thanks!

    Willy

    "Willy Esteban" <net> wrote in message
    news:%phx.gbl... 
    almost 
    occassionally 


    Willy Guest

  4. #4

    Default Re: Inconsistantly slow query

    If you are running SQL Server 7 and do not have SP4 installed, see FIX:
    Poor Cardinality Estimate for Tables with Multiple Qualifying Histogram
    Values

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;298643


    Carl Federl
    Please post DDL (create table) with datatypes, primary and foreign keys.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Carl Guest

  5. #5

    Default Re: Inconsistantly slow query

    Hi Willy,

    If adding indexes can increase the performance, I suggest that you use
    Index Tuning Wizard to fine-tune the indexes.


    Bill Cheng
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
    --------------------
    | Reply-To: "Willy Esteban" <net>
    | From: "Willy Esteban" <net>
    | References: <#phx.gbl>
    | Subject: Re: Inconsistantly slow query
    | Date: Tue, 15 Jul 2003 11:32:08 -0400
    | Lines: 127
    | X-Priority: 3
    | X-MSMail-Priority: Normal
    | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
    | Message-ID: <phx.gbl>
    | Newsgroups: microsoft.public.sqlserver.programming
    | NNTP-Posting-Host: ip-66-80-195-106.atl.megapath.net 66.80.195.106
    | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftn gp13.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:373695
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | Thank you, Bob and Uri for your responses.
    | I read the "parameter sniffing" message and it was very interesting. I
    don't
    | think it is the cause of this particular problem but it will certainly
    help
    | me write better scripts is the future.
    |
    | I am not allowed be too specific about the schema but it is not too
    | different from the following:
    |
    | The Products table has
    | ProductID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NewID())
    | ProductCode CHAR(32)
    | Description VARCHAR(100)
    | ...other properties
    |
    | index on ProductCode
    | may have 1 million rows
    |
    | The PriceListDetails table has
    | PriceListDetailID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NewID())
    | PriceListID UNIQUEIDENTIFIER
    | ProductID UNIQUEIDENTIFIER
    | Price MONEY
    | BusinessRuleID UNIQUEIDENTIFIER
    | ...other properties
    |
    | index on PriceListID (added today after posting of message)
    | index on ProductID (added today after posting of message)
    |
    | may have ~2-3 million rows
    |
    | The BusinessRules table has
    | BusinessRuleID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NewID())
    | BaseBusinessRuleID UNIQUEIDENTIFIER
    | Code CHAR(3)
    | Description VARCHAR(100)
    | QtyLimit int
    | CommissionFactor Decimal(5,4)
    | DiscountFactor Decimal(5,4)
    |
    | currently not indexed
    | may only have about 100-200 rows
    |
    | The BaseBusinessRules table has
    | BaseBusinessRuleID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NewID())
    | Description Varchar(100)
    |
    | BTW, all the appropriate relationships have been established Products and
    | PriceListDetails, BusinessRules and PriceListDetails and BaseBusinessRules
    | and BusinessRules
    |
    | The BusinessRulesView is:
    |
    | SELECT BusinessRules.*, BaseBusinessRules.Description AS
    | BaseBusinessRuleDescription
    | FROM BaseBusinessRules INNER JOIN
    | BusinessRules ON BaseBusinessRules.BaseBusinessRuleID =
    | BusinnessRules.BaseBusinessRuleID
    |
    | The PriceListDetailsView is
    | SELECT
    | PriceListDetails.*,
    | Products.ProductCode, Products.Description as ProductDescription,
    | Products.SomeOtherAttributes,
    | BusinessRulesView.Code as BusinessRuleCode,
    | BusinessRules.Description as BusinessRuleDescription,
    | BusinessRulesView.QtyLimit, BusinessRuleView.CommissionFactor,
    | BusinessRuleView.DiscountFactor
    | FROM PriceListDetails INNER JOIN
    | Products ON PriceListDetails.ProductID = Products.ProductID LEFT
    | OUTER JOIN
    | BusinessRulesView ON PriceListsDetails.BusinessRuleID =
    | BusinessRulesView.BusinessRuleID
    |
    |
    | One SP is
    | CREATE PROCEDURE SelectOneProductCodeInOnePriceList((ProductCode
    | Varchar(32), PriceListID uniqueidentifier)
    | as
    | --Return one or more rows for a ProductCode for One PriceList.
    | select * from PriceListDetailsView where ProductCode =
    ProductCode
    | and PriceListID = PriceListID
    |
    | There is another one similar to the about SP that is for one ProductID and
    | one PriceList
    | There are other procedures that return as single row based on a
    | PriceListDetailID
    |
    | Occassionally, the SP shown above will take over a minute to procude one
    row
    | but most of the time it is virtually instantaneous.
    |
    | After adding the two indexes indicated above, it appears that the
    performace
    | is even faster than before. I suspect that my problem is going to be a
    lack
    | of appropriate indexes. Furthermore, it seems to me that it would be wise
    to
    | add an index for the view itself.
    |
    | If you or anyone has other suggestions, it will be greatly appreciated.
    | Thanks!
    |
    | Willy
    |
    | "Willy Esteban" <net> wrote in message
    | news:%phx.gbl...
    | > A stored procedure that produces a single result from a view
    occasionally
    | > takes a very long time to execute. Most of the time, though, it works
    | almost
    | > instantaneously.
    | >
    | > The view from which the results are gathered is a view of another view
    | > constructed from the joining of two large tables (~ 1 Million rows each)
    | > plus a couple of views of realy tiny tables that contain descriptions
    for
    | > codes. Most of the time the result set contains only one row;
    | occassionally
    | > it may contain as many as a hand-full, though I have not encountered the
    | > latter, yet.
    | >
    | > My questions are:
    | > Can anyone give me a clue to what this sporadic slowness may be
    | > attributed?
    | > Is basing a result set on a view of a view containing joins with
    other
    | > views something I should avoid?
    | >
    | > --
    | > Willy Esteban
    | >
    | >
    |
    |
    |

    Bill Guest

Similar Threads

  1. slow query on big table
    By lambelly@gmail.com in forum MySQL
    Replies: 5
    Last Post: September 15th, 04:46 AM
  2. Help in optimizing a slow query...
    By DDJ in forum MySQL
    Replies: 1
    Last Post: May 13th, 06:13 PM
  3. CreateDirectory working inconsistantly from ASP.net
    By David Davies in forum ASP.NET Security
    Replies: 3
    Last Post: October 26th, 12:51 PM
  4. Slow Query
    By Stuart in forum Informix
    Replies: 3
    Last Post: December 8th, 02:28 PM
  5. Slow MySql query
    By jack in forum PHP Development
    Replies: 2
    Last Post: August 31st, 08:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139