Results 1 to 4 of 4

Thread: Query -Tunning

  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: Query -Tunning

    select a.cod_acct_no,
    a.dat_acct_open,
    a.cod_acct_stat,
    a.cod_cust,
    a.bal_available,
    a.amt_od_limit,
    b.flg_demat,
    a.flg_sweepin,
    convert(char,getdate(),107)

    from test1 a
    ,test2 b
    ,test3 c

    where a.cod_acct_no=b.cod_acct_no
    and a.cod_cust=c.cod_cust_id
    and a.flg_mnt_status='A'
    and b.flg_mnt_status='A'
    and c.flg_mnt_status = 'A'
    and substring(a.cod_acct_no,4,3) not in ('199','299')
    and a.cod_acct_stat in (6,7,8)
    order by a.cod_acct_no
    go


    size of test1 : 9.4GB
    size of test2: 631 MB
    size of test3 :6.4GB

    indexes on test1:- unique clustered on cod_acct_no, flg_mnt_status
    indexes on test2:- unique nonclustered on cod_acct_no, flg_mnt_status
    indexes on test3:- unique clustered on cod_cust_id, flg_mnt_status

    In the showplan , the first table i.e test1 is going for a table scan and for rest of the tables it's using indexes

  2. #2
    Join Date
    Jun 2003
    Posts
    140

    Re: Query -Tunning

    can u try including cod_cust column in your composite index

  3. #3
    Join Date
    Aug 2002
    Location
    Omaha, Nebraska
    Posts
    21
    Looks absolutely reasonable to me. What do you _want_ the showplan to look like?

    I don't see any search arguments done for the leading column of the index on test1. I'm not sure what you expect here, but I'd say you have the optimal plan given the index definitions you give.

  4. #4
    Join Date
    Jan 2004
    Posts
    19
    I totally agree with kevin.It will always do table scan on test1 since substring(a.cod_acct_no,4,3) is not a valid sarg

Posting Permissions

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