Post reply

Any tips on speeding this SQL up would be most helpful

  • January 11, 2017 at 9:45 am

    #1921988

    @arjun, I had charge_amount at first - charge_qty does fix the number of records. So both queries give the right results, but ChrisM is a little faster.

    So what is the right way to "Mark as Solution" on this forum? Can I select multiple? If not, do I choose the one with the query in it?

  • January 11, 2017 at 9:57 am

    #1921994

    douglas.t (1/11/2017)

    @Arjun, I had charge_amount at first - charge_qty does fix the number of records. So both queries give the right results, but ChrisM is a little faster.

    So what is the right way to "Mark as Solution" on this forum? Can I select multiple? If not, do I choose the one with the query in it?

    I wouldn't worry about "Mark as Solution" Douglas, so long as it works for you, that's what counts.

    I've not had much time to follow this up, but did spend a while looking at indexing. These two are worth a try:

    CREATE INDEX ix_Stuff01 ON #ub_charge (a_site_id, charge_code, charge_date) INCLUDE (charge_qty, charge_amount, ub_master_id, CPTCode, CPTCode_Description, charge_description)

    CREATE INDEX ix_Stuff02 ON #ub_charge (ub_master_id) INCLUDE (a_site_id, charge_code, charge_qty, charge_amount, charge_date)

    - with two caveats:

    avoid overloading indexes with columns, if it looks like this is happening, you might want to reconsider your choice of cluster key(s).

    avoid overloading your tables with indexes, create and maintain only those which you need.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 31 through 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply