Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fetching value of previous record Expand / Collapse
Author
Message
Posted Saturday, December 21, 2013 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:06 PM
Points: 3, Visits: 25
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
Post #1525270
Posted Saturday, December 21, 2013 11:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:53 PM
Points: 36,795, Visits: 31,257
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1525318
Posted Sunday, December 22, 2013 12:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:11 AM
Points: 7,122, Visits: 15,031
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?
Post #1525324
Posted Monday, December 23, 2013 3:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 7,137, Visits: 13,527
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1525456
Posted Monday, December 30, 2013 12:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 8:06 PM
Points: 3, Visits: 25
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
Post #1526396
Posted Monday, December 30, 2013 2:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 7,137, Visits: 13,527
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1526432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse