Solved

how to get top 1 date really fast from a large table

Posted on 2010-09-08
33
590 Views
Last Modified: 2012-05-10
Hello
I have got a table range from 10k to 200k rows...
When I query for the latest date ...

"Select TOP 1 [DateTime] From [M] " & _
                    "ORDER BY [DateTime] DESC"

It tooks a lot of time ... is there a way i can make a process faster?
any idea at all will be great.

Well i am doing it in sqlite so but any idea would be great....
0
Comment
Question by:JSW21
  • 8
  • 8
  • 6
  • +3
33 Comments
 
LVL 8

Assisted Solution

by:kingjely
kingjely earned 200 total points
ID: 33633861
Hi please try;

"Select TOP 1 MAX[DateTime] From [M] " & _
                    "ORDER BY [DateTime] DESC"
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33633862
what is this date?
if it is creation date, you can sort the table by ID, just let us know
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33633923
sorry. Max won't help, didn't read the question clearly
  -1 point  ;)
0
 
LVL 15

Accepted Solution

by:
AmmarR earned 100 total points
ID: 33633932
i tried 3 methods with a very huge table i have more then million records

1
SELECT TOP 1 [BusinessDate] FROM Table1
ORDER BY [BusinessDate] DESC  
-------------

2
SELECT max([BusinessDate]) FROM Table1
-------------

3
SELECT TOP 1 [BusinessDate] FROM Table1
ORDER BY convert(int,[BusinessDate],112) DESC  


the first 2 methods nearly take the same time but the 3rd method was the fastest.

try it

Regards
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33633942
Add an index to the datetime column.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33633983

If you want to select the latest, datetimefield, in your table which would be the first, and limit 1 which makes sence.. then use this

CREATE UNIQUE INDEX id_indexDATE_TIME_INDEX ON YOURTABLE(DATE_TIMEFIELD);

Select TOP 1 MAX[DateTime] From [M]
                    ORDER BY [DateTime] DESC;

0
 

Author Comment

by:JSW21
ID: 33633985
what is this date?
if it is creation date, you can sort the table by ID, just let us know ///

Date is just date of data... i need to know the Max date because then I can use this date to get more data from other table.

Add an index to the datetime column. ////

What is this mean... is it the same as adding PK Key?

3
SELECT TOP 1 [BusinessDate] FROM Table1
ORDER BY convert(int,[BusinessDate],112) DESC  

What is the 112 there? I have tried it but wont work in sqlite....

the rest still did not improve speed...

Thanks,
0
 

Author Comment

by:JSW21
ID: 33633993
Oh
One more info... the date are unique...
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33633995
0
 
LVL 8

Assisted Solution

by:kingjely
kingjely earned 200 total points
ID: 33634002
Please try my responce ID:  33633983  should be close...


If you want to select the latest, datetimefield, in your table which would be the first, and limit 1 which makes sence.. then use this

CREATE UNIQUE INDEX id_index DATE_TIME_INDEX ON YOURTABLE(DATE_TIMEFIELD);

Select TOP 1 MAX[DateTime] From [M]
                    ORDER BY [DateTime] DESC;
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33634003

the 112 will convert the date to ISODATE format, which is a number, this will make it faster in sorting

read about the convert function in SQL
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33634036
index is your friend then
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33634068
@ammar

The OP has posted the question in the wrong zone, it's an Sqlite question, not a SQL server question.

Datetime is always a number in SQL server where the date is the integer part and the time is the fraction is the time. So converting to integer will only lose the time.

With the right index SELECT max([BusinessDate]) FROM Table1 should be the fastest query.
0
 

Author Comment

by:JSW21
ID: 33634088
kingjely:
I please give me some time to understand the index concept... your code is quite new for me. :)
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33634102
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33634110
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Assisted Solution

by:kingjely
kingjely earned 200 total points
ID: 33634142
No worries, do some testing, the way i think of it, Indexing, is like... if you had a big pile of papers on the floor, then, putting them Into a file. so you, (or the computer) knows where to find all the specific papers, or 'date times records' just go straight to that index.. makes it alot easier and faster to find
 
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33634165
it is very similar to the sorted word index at the end of a book
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 33634184
Thanks auke_t:

what you said might be true, but i tried the script and it was faster.

any way since its sqllite, so its of no use.

regards
0
 

Author Comment

by:JSW21
ID: 33635087
on SQLite
I select the MAX or TOP 1 with index from table with 300k rows it tooks...
14s and 18s respectively...

I guess i will have to pre-save the max date on another table before calling it.

Is this a good ideas?
0
 
LVL 9

Expert Comment

by:auke_t
ID: 33635191
If a select max() on an indexed column takes 14 seconds you really should consider switching to a decent database. ;-)
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33635214

Can i ask why you have 300k+ rows with dates, maybe you can archive some to a different table ?
Just curious.
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33635266

Just a suggestion,
Maybe you could create views Or temp tables of date ranges, where datediff is between now() - 1 year so
you have tables of data in groups of years, or whatever you need so you don't need to wait 18 secs for records to print, If that's the problem..
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 33635300
>I guess i will have to pre-save the max date on another table before calling it.>

this is shurely an option.
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33641125
not sure, doe sqlite support views?
0
 

Author Comment

by:JSW21
ID: 33664237
yes it support view will it any faster using view?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 33664500
>will it any faster using view?
no. only the underlying indexing will make it faster.
0
 
LVL 8

Assisted Solution

by:kingjely
kingjely earned 200 total points
ID: 33668606
It was just a suggestion, ANd i already suggersted indexing the field but,
Hrrm,

If there is 5 years worth of data.. Couldn't you make a view for each year.. isn't that kind of like making an index of sorts? 5 views, 05,06,07,08,09,10  for the years data, then you could query them. may not be practical, i dont know how or what the database is for, but maybe could archive some years data, to make it faster to look up current data.. was just a thought.
0
 
LVL 7

Assisted Solution

by:Inteqam
Inteqam earned 50 total points
ID: 33669511
a view will get the calculations ready when you need it, and you can make the view be ready to hold only one record, the record you want.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 150 total points
ID: 33669526
well, from what I know: a view does NOT precalculate any data.. it does the calculation when you use the view ...
except if, for example with MS SQL Server or Oracle, you have a materialized view (aka indexed)
0
 

Author Closing Comment

by:JSW21
ID: 33670841
So I have to store max data in separate table it seem sqlite just not up to the task.
0
 
LVL 7

Expert Comment

by:Inteqam
ID: 33670927
I didnt know that, Thank you Angelll
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now