Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Posts
    3

    Unanswered: Why is this query faster?

    I have two queries in SQL. The first one takes 19 seconds... the second 9 minutes and 56 seconds.

    declare @refdate datetime
    set @refdate = '6/4/02'
    SELECT oh.ord_num, oh.cus_num, oh.dep_num, oh.shp_nam, oh.cus_fd1,
    oh.cus_fd2, oh.cus_fd3 AS Del_type, oh.sto_num, oh.ent_dte, oh.cus_fd3, oh.ord_sts,
    oh.scn_dt2, oh.act_lan, ol.cus_fd3 AS prt_des, oh.inv_dte, oh.ord_grp
    FROM ord_hdr_tbl As oh, ord_lin_tbl As ol
    WHERE oh.ord_num = ol.ord_num
    AND inv_dte = @refdate
    AND ord_grp = 'REG'AND ord_sts NOT IN ('D')
    ORDER BY oh.sto_num, oh.shp_num, oh.ord_num

    declare @refdate datetime
    set @refdate = '6/4/02'
    SELECT oh.ord_num, oh.cus_num, oh.dep_num, oh.shp_nam, oh.cus_fd1,
    oh.cus_fd2, oh.cus_fd3 AS Del_type, oh.sto_num, oh.ent_dte, oh.cus_fd3, oh.ord_sts,
    oh.scn_dt2, oh.act_lan, ol.cus_fd3 AS prt_des, oh.inv_dte, oh.ord_grp
    FROM ord_hdr_tbl As oh, ord_lin_tbl As ol
    WHERE oh.ord_num = ol.ord_num
    AND inv_dte = @refdate
    AND ord_grp = 'REG'AND ord_sts IN ('D')
    ORDER BY oh.sto_num, oh.shp_num, oh.ord_num

    The differance is that there is a ton more 'D' records than any other. There is 500,000 records and about 485,000 have a 'D' status. Should this make a differance? Should it still take over 9 minutes?

    Please help this is beginning to frustrate me like u would not believe.

  2. #2
    Join Date
    Jun 2002
    Location
    Hoffman Estates, IL
    Posts
    14

    Why is this query faster?

    Have you tried rebuilding the statistics on ord_hdr_tbl and ord_lin_tbl ?

    Also, is there an index on ord_hdr_tbl.ord_num and ord_lin_tbl.ord_num ?

    Do these columns both allow duplicate values?

    If you have an index on ord_hdr_tbl.ord_sts you should drop it.

  3. #3
    Join Date
    Jun 2002
    Posts
    3

    Your reply

    Thanks for your reply

    I have not tried rebuilding the statistics but in the main ord_hdr_tbl table the ord_num is the primary key. The ord_lin_tbl is the many of the one to many relationship for ord_num and it is an indexed field. Should I get rid of this index?

    Thanks again,

    Jugger

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Take a look at the execution plan in query analyzer and compare the difference. What is probably happening is since there are a large number of records, sql server is doing a sequential scan of the table. The majority of your time is being spent on data i/o which is very expensive. If you calculate that it took 19 seconds to pull 15000 records, then it would take 633 seconds to pull 485000 records. How much memory does you sql box have allocated to it - this would have an impact on your order by ?

  5. #5
    Join Date
    Jun 2002
    Location
    Hoffman Estates, IL
    Posts
    14
    You definitely need the index on ord_hdr_tbl.ord_num and ord_lin_tbl.ord_num.

    As rnealejr's calculation showed, it seems that the index is not being used, so you would expect it to take around 9 minutes.

    Using query analyzer will probably confirm that you are doing a full table scan.

    Once again, the most likely reason for an index being ignored is that the statistics are wrong. Even with Auto Create Statistics on, I still rebuild them regularly.

    Bob S.

  6. #6
    Join Date
    Jun 2002
    Posts
    3

    Thank you!

    Thanks for all your replies. I will definately look into that. This is the first time I have ever had this problem so I was totally stumped hehee.

    Thanks,

    Jugger

Posting Permissions

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