Professional Web Applications Themes

speed challenge - Microsoft SQL / MS SQL Server

can anyone help me make this query run faster?? UPDATE tpayment SET status_id = 3, modified_date = getdate(), modified_user_id = user_id WHERE payment_id = (SELECT MIN(payment_id) FROM tPayment WHERE contract_id = contract_id AND status_id IS NULL AND posted_batch IS NULL AND scheduled_date = (SELECT MIN(scheduled_date) FROM tPayment WHERE contract_id = contract_id AND status_id IS NULL AND posted_batch IS NULL)) TIA jt...

  1. #1

    Default speed challenge

    can anyone help me make this query run faster??

    UPDATE tpayment
    SET status_id = 3, modified_date = getdate(),
    modified_user_id = user_id
    WHERE payment_id = (SELECT MIN(payment_id) FROM
    tPayment
    WHERE contract_id = contract_id
    AND status_id IS NULL
    AND posted_batch IS NULL
    AND scheduled_date =
    (SELECT MIN(scheduled_date)
    FROM tPayment
    WHERE contract_id =
    contract_id
    AND status_id IS NULL
    AND posted_batch IS NULL))



    TIA

    jt
    JT Guest

  2. #2

    Default Re: speed challenge

    Without a repro, it is hard to test & see if it can be made efficient. Based
    on some guesses on your schema, I would suggest you to create an index in
    payment_id & do:

    UPDATE tpayment
    SET status_id = 3,
    modified_date = CURRENT_TIMESTAMP,
    modified_user_id = user_id
    WHERE payment_id = (SELECT TOP 1 payment_id
    FROM tPayment
    WHERE contract_id = contract_id
    AND status_id IS NULL
    AND posted_batch IS NULL
    ORDER BY scheduled_date) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: speed challenge

    JT,

    The query formulation is optimal. I haven't seen any DDL, so maybe your
    indexing can be improved.

    Make sure you have properly indexed the table. You might want to
    experiment with some indexes, and/or run the Index Tuning Wizard. An
    index on (contract_id, status_id, posted_batch) comes to mind.

    Hope this helps,
    Gert-Jan


    JT wrote: 
    Gert-Jan Guest

Similar Threads

  1. Help plz! Who's up for the challenge?
    By mr.tibbs webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: January 17th, 09:18 AM
  2. Challenge 35
    By Grant_Dixon@adobeforums.com in forum Adobe Photoshop Elements
    Replies: 20
    Last Post: October 6th, 05:01 PM
  3. Replies: 1
    Last Post: August 15th, 11:50 AM
  4. Little challenge for ya
    By Bill Ray in forum Macromedia Fireworks
    Replies: 0
    Last Post: July 11th, 09:43 PM
  5. Challenge #20
    By Pete D in forum Adobe Photoshop Elements
    Replies: 83
    Last Post: June 27th, 09:34 AM

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