Fetching value of previous record

Question

Fetching value of previous record

saum70

SSC Eights!

Points: 993
More actions
December 21, 2013 at 9:36 am

#283914

Hi,
I am facing a performance issue when fetching a value of previous record.My query goes here
Select a.nTokenNo, MAX(a.nSeqNo)
FROM tmptableA a
LEFT JOIN tmptableB b
on a.nTokenNo = b.nTokenNo
ANd b.nSeqNo < a.nSeqNo
The tmptableA contains around 35000000 records and temptableB contains 4000000 records.
Regards,
Saumik

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

Do you have any indexes to support the query?

Also, since you're doing a LEFT JOIN to "a", selecting columns only from "a", why are you joining to "b"? It's not necessary to get the data you want and forms a terrible "Triangular Join" in the process.

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

The left join is odd indeed. ~~Without allowing for NULLs in the join criteria, I'd imagine that the LEFT join behavior is being overridden to INNER join.~~(edit: removed the silly comment after rereading the query)

I'd consider aggregating the values in b separately and the values in a separately then comparing them (since you're apparently looking for the highest sequence in a as long as the a sequences are higher than in b)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Your code is missing GROUP BY and will throw an error without it.

Tableb places no restriction on which rows of Tablea are selected for output because it's left-joined. As Jeff pointed out, it's irrelevant to your query.

What are you really trying to do here? It's highly unlikely that this query is returning the results you are expecting.

^{“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

saum70 SSC Eights! Points: 993 More actions · Answer 4

Hi all,

Thanks for your replies. Yes Group by was missing but it was not the concern. Anyways, i figured out and got the performance. I used clustered index on table A and B and used OUTER APPLY and got the output within 10 sec. Thanks again

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

saum70 (12/30/2013)
Hi all,
Thanks for your replies. Yes Group by was missing but it was not the concern. Anyways, i figured out and got the performance. I used clustered index on table A and B and used OUTER APPLY and got the output within 10 sec. Thanks again

Can you post up the query please? It provides a good closure of the thread. It also provides feedback to those who helped you and may be useful to others with a similar problem who stumble upon this thread.

GROUP BY wasn't particularly relevant in this case, however it's always good practice to post up the whole query. If you aren't sure why a query isn't working, how can you be absolutely sure that a) the section you omit is irrelevant to those who wish to help you and b) isn't actually the cause of the problem?

^{“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