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

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....
JSW21Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AmmarRConnect With a Mentor Commented:
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
 
kingjelyConnect With a Mentor Commented:
Hi please try;

"Select TOP 1 MAX[DateTime] From [M] " & _
                    "ORDER BY [DateTime] DESC"
0
 
InteqamCommented:
what is this date?
if it is creation date, you can sort the table by ID, just let us know
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
kingjelyCommented:
sorry. Max won't help, didn't read the question clearly
  -1 point  ;)
0
 
auke_tCommented:
Add an index to the datetime column.
0
 
kingjelyCommented:

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
 
JSW21Author Commented:
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
 
JSW21Author Commented:
Oh
One more info... the date are unique...
0
 
kingjelyConnect With a Mentor Commented:
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
 
AmmarRCommented:

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
 
InteqamCommented:
index is your friend then
0
 
auke_tCommented:
@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
 
JSW21Author Commented:
kingjely:
I please give me some time to understand the index concept... your code is quite new for me. :)
0
 
kingjelyConnect With a Mentor Commented:
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
 
InteqamCommented:
it is very similar to the sorted word index at the end of a book
0
 
AmmarRCommented:
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
 
JSW21Author Commented:
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
 
auke_tCommented:
If a select max() on an indexed column takes 14 seconds you really should consider switching to a decent database. ;-)
0
 
kingjelyCommented:

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

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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>I guess i will have to pre-save the max date on another table before calling it.>

this is shurely an option.
0
 
InteqamCommented:
not sure, doe sqlite support views?
0
 
JSW21Author Commented:
yes it support view will it any faster using view?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>will it any faster using view?
no. only the underlying indexing will make it faster.
0
 
kingjelyConnect With a Mentor Commented:
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
 
InteqamConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
JSW21Author Commented:
So I have to store max data in separate table it seem sqlite just not up to the task.
0
 
InteqamCommented:
I didnt know that, Thank you Angelll
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.