Post reply

MAX function

  • June 15, 2010 at 12:29 pm

    #225620

    Hi Guru,

    I have a query that used MAX function in subquery. Based on statistics IO report, sql generated millions of IO due to the max function because the optimizer has to scan the big table. Below is the query:

    select

    NarexAccountId = a.narexAccountID, clientAccountID = isnull(a.clientAccountId,r.clientAccountId)

    from ReconciliationAgencyAcctList r (nolock),NAccountCapOneAF.dbo.accountInfo a,

    NarexAcctIDLookup c(nolock)

    where r.jobid =2336275

    and c.originalAccountNumber=r.debtorAccountID

    and c.narexAccountId = a.narexAccountID

    and c.disabledDate is NULL

    and a.snapshotDate =

    (select max(snapshotDate) from NAccountCapOneAF.dbo.AccountInfo a2 (nolock) where a2.narexAccountID = c.narexAccountID)

    I did create index on snapshotDate but the optimizer still did not use it.

    What are other options to rewrite this subquery without using max function?

    Thanks

  • June 15, 2010 at 12:48 pm

    #1180633

    I am curious what the logic is in this select. Since you are pulling two ID fields only I am not sure what benefit you get from using the max function at all. It would seem that in theory the two ID fields would not change frequently and I can not tell from the where clause why you would even need to know the max date since this is not pulled as part of the dataset. If you are doing this to simply pull a unique set of id's then there is a much better way to do this.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

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

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