Click here to Skip to main content
13,350,486 members (73,168 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Table " Ledger" is having huge number of records.
Account ID is FK (not indexed),
ID is PK

which one is the best way to get faster result?
Its taking 00:00:01 seconds for both query

SELECT TOP 1 OpeningBal FROM Ledger
WHERE AccountID=123456 
ORDER BY ID DESC 

OR
SELECT OpeningBal FROM Ledger
 WHERE ID = (SELECT MAX(ID) FROM Ledger WHERE AccountID=123456 );


What I have tried:

SELECT TOP 1 OpeningBal FROM Ledger
WHERE AccountID=123456 
ORDER BY ID DESC 

AND
SELECT OpeningBal FROM Ledger
 WHERE ID = (SELECT MAX(ID) FROM Ledger WHERE AccountID=123456 );
Posted 8-Nov-17 19:45pm
Updated 24-Nov-17 0:50am
Comments
Santosh kumar Pithani 9-Nov-17 1:14am
   
"SET STATISTICS IO ON ;SET STATISTICS TIME ON " which were help you to find out which query is taking less time CPU time and elapsed time.
Sadique KT 9-Nov-17 1:47am
   
Thanks... that's given the idea..
Santosh kumar Pithani 9-Nov-17 2:05am
   
Welcome

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

actually first one is the best

SELECT TOP 1 OpeningBal FROM Ledger
WHERE AccountID=123456 
ORDER BY ID DESC 


and yes I would also suggest to

"SET STATISTICS IO ON ;SET STATISTICS TIME ON
  Permalink  
Comments
Sadique KT 24-Nov-17 12:07pm
   
Thank you...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.180111.1 | Last Updated 24 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100