Professional Web Applications Themes

Problem query - Microsoft SQL / MS SQL Server

We recently migrated an application that had run for years on a WinNT4/SQL 7.0 system to a Win2K/SQL 2000 system, and have a query that simply will not run. The SQL 2000 system is running Win2K SP 3, and SQL 2000 SP 2 with the security update of 10/16/02 (SQL Slammer security patch). If you look at the execution plans for each query, the cost is inordinately high on SQL 7 (13027), but it runs in about 35 minutes, and does not appreciably increase the size of tempdb. On the SQL 2000 box, the cost for the query is significantly ...

  1. #1

    Default Problem query

    We recently migrated an application that had run for years on a
    WinNT4/SQL 7.0 system to a Win2K/SQL 2000 system, and have a query
    that simply will not run. The SQL 2000 system is running Win2K SP 3,
    and SQL 2000 SP 2 with the security update of 10/16/02 (SQL Slammer
    security patch).

    If you look at the execution plans for each query, the cost is
    inordinately high on SQL 7 (13027), but it runs in about 35 minutes,
    and does not appreciably increase the size of tempdb. On the SQL 2000
    box, the cost for the query is significantly lower (486), but it runs
    for hours, and has never run to completion successfully. During one
    test, tempdb grew to over 22GB and exhausted its available disk space,
    which killed the process.

    Can anyone give me some insight in the how the change between the two
    platforms could result in this query no longer being able to
    successfully run? I tried creating a view as intermediate step, but
    got the same results (although it took longer to fail) ;-)

    Here is the query:

    select count(*)
    from
    plan_monthly_customer_summary p,
    address a,
    address_class ac,
    level_class lcc,
    level_children lch,
    level_class lcp,
    item_uom iu,
    item i,
    op_group og,
    item_combined ic,
    item_combo_matrix icm,
    SalesIncentive_Summary sis,
    plan_item_standard pis,
    plan_status ps
    WHERE p.customer_number = a.customer_number
    AND p.shipto_number = a.shipto_number
    AND a.current_ind = 1
    AND a.customer_number = ac.customer_number
    AND a.address_number = ac.address_number
    AND ac.current_ind = 1
    AND ac.additive_ind = 1
    AND ac.level_id = lcc.level_id
    AND ac.level_eff_date = lcc.level_eff_date
    AND lcc.current_ind = 1
    AND lcc.level_id = lch.child_id
    AND lcc.level_eff_date = lch.child_eff_date
    AND lch.level_id = lcp.level_id
    AND lch.level_eff_date = lcp.level_eff_date
    AND lcp.current_ind = 1
    AND ( lcp.level_id <> 6207 or ac.primary_ind = 1 )
    AND p.item_number = iu.item_number
    AND p.plan_eff_date = iu.eff_date
    AND iu.sale_uom_ind = 1
    AND iu.item_number = ic.item_number
    AND iu.eff_date = ic.eff_date
    AND iu.item_number = i.item_number
    AND iu.eff_date = i.eff_date
    AND i.group_number = og.group_number
    AND ic.item_number = icm.item_number
    AND ic.eff_date = icm.eff_date
    AND ic.breakdown_id = icm.breakdown_id
    AND p.units_mtd <> 0
    AND sis.division = og.group_description
    AND sis.region_level_id = lcp.level_id
    AND sis.year = p.year
    AND sis.month >= p.month
    AND iu.item_Number = pis.item_number
    AND iu.eff_date = pis.eff_date
    AND pis.plan_id = ps.plan_id
    AND ps.current_budget_ind = 1
    AND icm.category is not null
    AND icm.category not in ('Accessories','Misc.Mat''l', 'Tobacs',
    'Papers', 'Smokers Alternative')

    Thanks in advance,


    Scott Ashby
    DBA
    Swedish Match North America
    [email]scott.ashbysmna.com[/email]
    Scott Ashby Guest

  2. #2

    Default Problem query

    I would re-write the query using inner joins for a start.
    See if this makes a difference.

    Mark Baekdal
    dbghost.com
    >-----Original Message-----
    >We recently migrated an application that had run for
    years on a
    >WinNT4/SQL 7.0 system to a Win2K/SQL 2000 system, and
    have a query
    >that simply will not run. The SQL 2000 system is
    running Win2K SP 3,
    >and SQL 2000 SP 2 with the security update of 10/16/02
    (SQL Slammer
    >security patch).
    >
    >If you look at the execution plans for each query, the
    cost is
    >inordinately high on SQL 7 (13027), but it runs in about
    35 minutes,
    >and does not appreciably increase the size of tempdb.
    On the SQL 2000
    >box, the cost for the query is significantly lower
    (486), but it runs
    >for hours, and has never run to completion
    successfully. During one
    >test, tempdb grew to over 22GB and exhausted its
    available disk space,
    >which killed the process.
    >
    >Can anyone give me some insight in the how the change
    between the two
    >platforms could result in this query no longer being
    able to
    >successfully run? I tried creating a view as
    intermediate step, but
    >got the same results (although it took longer to fail) ;-
    )
    >
    >Here is the query:
    >
    >select count(*)
    >from
    > plan_monthly_customer_summary p,
    > address a,
    > address_class ac,
    > level_class lcc,
    > level_children lch,
    > level_class lcp,
    > item_uom iu,
    > item i,
    > op_group og,
    > item_combined ic,
    > item_combo_matrix icm,
    > SalesIncentive_Summary sis,
    > plan_item_standard pis,
    > plan_status ps
    >WHERE p.customer_number = a.customer_number
    > AND p.shipto_number = a.shipto_number
    > AND a.current_ind = 1
    > AND a.customer_number = ac.customer_number
    > AND a.address_number = ac.address_number
    > AND ac.current_ind = 1
    > AND ac.additive_ind = 1
    > AND ac.level_id = lcc.level_id
    > AND ac.level_eff_date = lcc.level_eff_date
    > AND lcc.current_ind = 1
    > AND lcc.level_id = lch.child_id
    > AND lcc.level_eff_date = lch.child_eff_date
    > AND lch.level_id = lcp.level_id
    > AND lch.level_eff_date = lcp.level_eff_date
    > AND lcp.current_ind = 1
    > AND ( lcp.level_id <> 6207 or ac.primary_ind = 1 )
    > AND p.item_number = iu.item_number
    > AND p.plan_eff_date = iu.eff_date
    > AND iu.sale_uom_ind = 1
    > AND iu.item_number = ic.item_number
    > AND iu.eff_date = ic.eff_date
    > AND iu.item_number = i.item_number
    > AND iu.eff_date = i.eff_date
    > AND i.group_number = og.group_number
    > AND ic.item_number = icm.item_number
    > AND ic.eff_date = icm.eff_date
    > AND ic.breakdown_id = icm.breakdown_id
    > AND p.units_mtd <> 0
    > AND sis.division = og.group_description
    > AND sis.region_level_id = lcp.level_id
    > AND sis.year = p.year
    > AND sis.month >= p.month
    > AND iu.item_Number = pis.item_number
    > AND iu.eff_date = pis.eff_date
    > AND pis.plan_id = ps.plan_id
    > AND ps.current_budget_ind = 1
    > AND icm.category is not null
    > AND icm.category not in
    ('Accessories','Misc.Mat''l', 'Tobacs',
    >'Papers', 'Smokers Alternative')
    >
    >Thanks in advance,
    >
    >
    >Scott Ashby
    >DBA
    >Swedish Match North America
    >scott.ashbysmna.com
    >.
    >
    mark baekdal Guest

  3. #3

    Default Re: Problem query

    Mark,

    Re-writing the query using INNER JOINS didn't make any difference...
    Tempdb still bloated, and the query died when it could no longer
    expand.

    Any other ideas?

    Scott

    "mark baekdal" <nobodyherenowhere.com> wrote in message news:<037b01c33f44$d1ac1e70$a301280aphx.gbl>...
    > I would re-write the query using inner joins for a start.
    > See if this makes a difference.
    >
    > Mark Baekdal
    > dbghost.com
    >
    > >-----Original Message-----
    > >We recently migrated an application that had run for
    > years on a
    > >WinNT4/SQL 7.0 system to a Win2K/SQL 2000 system, and
    > have a query
    > >that simply will not run. The SQL 2000 system is
    > running Win2K SP 3,
    > >and SQL 2000 SP 2 with the security update of 10/16/02
    > (SQL Slammer
    > >security patch).
    > >
    > >If you look at the execution plans for each query, the
    > cost is
    > >inordinately high on SQL 7 (13027), but it runs in about
    > 35 minutes,
    > >and does not appreciably increase the size of tempdb.
    > On the SQL 2000
    > >box, the cost for the query is significantly lower
    > (486), but it runs
    > >for hours, and has never run to completion
    > successfully. During one
    > >test, tempdb grew to over 22GB and exhausted its
    > available disk space,
    > >which killed the process.
    > >
    > >Can anyone give me some insight in the how the change
    > between the two
    > >platforms could result in this query no longer being
    > able to
    > >successfully run? I tried creating a view as
    > intermediate step, but
    > >got the same results (although it took longer to fail) ;-
    > )
    > >
    > >Here is the query:
    > >
    > >select count(*)
    > >from
    > > plan_monthly_customer_summary p,
    > > address a,
    > > address_class ac,
    > > level_class lcc,
    > > level_children lch,
    > > level_class lcp,
    > > item_uom iu,
    > > item i,
    > > op_group og,
    > > item_combined ic,
    > > item_combo_matrix icm,
    > > SalesIncentive_Summary sis,
    > > plan_item_standard pis,
    > > plan_status ps
    > >WHERE p.customer_number = a.customer_number
    > > AND p.shipto_number = a.shipto_number
    > > AND a.current_ind = 1
    > > AND a.customer_number = ac.customer_number
    > > AND a.address_number = ac.address_number
    > > AND ac.current_ind = 1
    > > AND ac.additive_ind = 1
    > > AND ac.level_id = lcc.level_id
    > > AND ac.level_eff_date = lcc.level_eff_date
    > > AND lcc.current_ind = 1
    > > AND lcc.level_id = lch.child_id
    > > AND lcc.level_eff_date = lch.child_eff_date
    > > AND lch.level_id = lcp.level_id
    > > AND lch.level_eff_date = lcp.level_eff_date
    > > AND lcp.current_ind = 1
    > > AND ( lcp.level_id <> 6207 or ac.primary_ind = 1 )
    > > AND p.item_number = iu.item_number
    > > AND p.plan_eff_date = iu.eff_date
    > > AND iu.sale_uom_ind = 1
    > > AND iu.item_number = ic.item_number
    > > AND iu.eff_date = ic.eff_date
    > > AND iu.item_number = i.item_number
    > > AND iu.eff_date = i.eff_date
    > > AND i.group_number = og.group_number
    > > AND ic.item_number = icm.item_number
    > > AND ic.eff_date = icm.eff_date
    > > AND ic.breakdown_id = icm.breakdown_id
    > > AND p.units_mtd <> 0
    > > AND sis.division = og.group_description
    > > AND sis.region_level_id = lcp.level_id
    > > AND sis.year = p.year
    > > AND sis.month >= p.month
    > > AND iu.item_Number = pis.item_number
    > > AND iu.eff_date = pis.eff_date
    > > AND pis.plan_id = ps.plan_id
    > > AND ps.current_budget_ind = 1
    > > AND icm.category is not null
    > > AND icm.category not in
    > ('Accessories','Misc.Mat''l', 'Tobacs',
    > >'Papers', 'Smokers Alternative')
    > >
    > >Thanks in advance,
    > >
    > >
    > >Scott Ashby
    > >DBA
    > >Swedish Match North America
    > >scott.ashbysmna.com
    > >.
    > >
    Scott Ashby Guest

Similar Threads

  1. Query problem, please help.
    By Raphael Emportu in forum MySQL
    Replies: 2
    Last Post: April 27th, 10:14 AM
  2. Problem with query...
    By Leszek in forum MySQL
    Replies: 4
    Last Post: December 30th, 11:41 AM
  3. Query of Query problem
    By JakeFlynn in forum Macromedia ColdFusion
    Replies: 18
    Last Post: August 11th, 08:15 AM
  4. Query on Query and CF casting problem
    By obxlefty in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 18th, 12:44 PM
  5. Sub Query problem
    By Buda123 in forum Coldfusion Database Access
    Replies: 3
    Last Post: April 28th, 08:55 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