Post reply

index to improve query time

  • October 8, 2013 at 9:16 am

    #278719

    hi

    i have a query that is looking up 3 different databases, and the performance is horrible..

    Each join also needs collated, which probably is not helping,

    example join issue

    left join

    (select StockCode, Sum(CurGrnValue) as Syspro_GRN from SysproCompanyW.dbo.GrnDetails

    group by StockCode)GRN on GRN.StockCode collate Latin1_General_CI_AS = TR.Code

    it does not matter if i remove the sum, the performance is as bad.

    execution plan states there is 96% Cost on the join lookup

    i tried adding a non clustered Index on StockCode and CurGrnValue, but that only reduced the Cost to 85%

    any one any ideas?

    mal

  • October 8, 2013 at 9:29 am

    #1656797

    ignore me

    the problem was a "where" Date >

    i indexed that and viola, results 🙂

    - i would delete the post but i dont think there is an option to do so?

  • October 8, 2013 at 9:31 am

    #1656798

    If you want further help to possibly make it even faster, please post full query, table definitions, index definitions and execution plan.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 3 (of 3 total)

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