Post reply

query takes too long to execute needs to improve query...

  • January 15, 2010 at 11:00 pm

    #219380

    Hello guys,,

    i have two table named tableA and tableB

    i wann records from tableA which are depend on tableB's records

    i use this query

    select * from tableA inner join tableB on tableA.id=tableB.id and tableA.date=(select max(date) from tableB where id=tableA.id)

    it takes too long to execute because each time it finds the max date

    from tableB and tableB having large data...

    can u guys help me how can improve it....

  • January 15, 2010 at 11:25 pm

    #1104560

    samirprogrammer (1/15/2010)

    select * from tableA inner join tableB on tableA.id=tableB.id and tableA.date=(select max(date) from tableB where id=tableA.id)

    Hi,

    try this

    select a.* from tableA a

    inner join

    (select id,max([date]) [date]

    from tableB

    group by id)as b

    on a.id= b.id

    and a.date= b.date

  • January 16, 2010 at 1:44 am

    #1104570

    Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    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 2 (of 2 total)

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