Professional Web Applications Themes

Query Timeout Problem - Microsoft SQL / MS SQL Server

Hi, We are having a query comprising of joins between 8 tables. The query fetches 30 fields. This query takes 55 seconds to complete executing. If the same query is re-written such that only 3 fields are fetched then it takes 15 seconds to complete. What are the possible problems that causes this difference in execution time? Any solutions for faster performance of queries having several joins? Thanks in advance...

  1. #1

    Default Query Timeout Problem

    Hi,
    We are having a query comprising of joins between 8 tables.
    The query fetches 30 fields.

    This query takes 55 seconds to complete executing.
    If the same query is re-written such that only 3 fields are fetched then it
    takes 15 seconds to complete.

    What are the possible problems that causes this difference in execution
    time?
    Any solutions for faster performance of queries having several joins?

    Thanks in advance


    TS Guest

  2. #2

    Default Re: Query Timeout Problem

    "TS" <tanujabrainsoft.ch> wrote in message
    news:u%23oWt1pRDHA.2148TK2MSFTNGP11.phx.gbl...
    > Hi,
    > We are having a query comprising of joins between 8 tables.
    > The query fetches 30 fields.
    >
    > This query takes 55 seconds to complete executing.
    > If the same query is re-written such that only 3 fields are fetched then
    it
    > takes 15 seconds to complete.
    >
    > What are the possible problems that causes this difference in execution
    > time?
    > Any solutions for faster performance of queries having several joins?
    >

    When you left join to a primary key, or inner join from a non-null column to
    a primary key, and don't select any columns from the joined table, the join
    can logically be ignored since it can neither add nor subtract a row from
    the result.

    SQLServer uses this as an optimization to avoid joining tables it doesn't
    really need. This is usefull when you have a view that joins a number of
    lookup-type foreign keys. When selecting from the view and not returning
    any columns from the joined table you don't pay a price for having joined
    them in the view definition.

    EG of some queries where joins can be ignored

    select t1.a
    from t1
    left join t2
    on t1.c = t2.c

    Where t2.c is unique

    and

    select t1.a
    from t1
    inner join t2
    on t1.c = t2.c

    where t1.c is not null and t2.c is unique.

    So a change in the columns selected can change the query execution
    completely.

    Now for your problem. The basic rule of thumb for yzing execution plans
    involving multiple joins is this:

    Get to your final row number as quickly and cheaply as possible.

    If your execution starts off with 70,000 rows (a fat grey arrow), and ends
    up with 50 rows (a skinny grey arrow), then try to get it to drop to 50 rows
    in the first step (or two) using an index seek, or index scan.

    Once you have narrowed to a small number of rows, joining many additional
    tables is cheap.

    David






    David Browne Guest

  3. #3

    Default Query Timeout Problem

    Keep following things in mind
    * you have got appropriate indexies
    * you are doing index seek rahter than index scan or table
    scan
    * set nocount on in the beginging and set nocount off in
    the last
    *store proc doesnt begin with sp_
    *if query plan is not using approperiate indexies then
    force the query to use them.


    >-----Original Message-----
    >Hi,
    >We are having a query comprising of joins between 8
    tables.
    >The query fetches 30 fields.
    >
    >This query takes 55 seconds to complete executing.
    >If the same query is re-written such that only 3 fields
    are fetched then it
    >takes 15 seconds to complete.
    >
    >What are the possible problems that causes this
    difference in execution
    >time?
    >Any solutions for faster performance of queries having
    several joins?
    >
    >Thanks in advance
    >
    >
    >.
    >
    naveen Guest

Similar Threads

  1. SSL LWP Timeout problem
    By John Ingram in forum PERL Modules
    Replies: 1
    Last Post: September 21st, 06:51 PM
  2. Need php help! Timeout problem??
    By Grant in forum PHP Development
    Replies: 1
    Last Post: December 18th, 06:58 PM
  3. Set LDAP Query Timeout?
    By localhost in forum ASP.NET Security
    Replies: 1
    Last Post: December 16th, 02:22 AM
  4. Replies: 3
    Last Post: July 28th, 09:34 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