?
Solved

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

Posted on 2010-09-08
33
Medium Priority
?
661 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
32 Comments
 
LVL 8

Assisted Solution

by:kingjely
kingjely earned 800 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 15

Accepted Solution

by:
AmmarR earned 400 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 800 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 8

Assisted Solution

by:kingjely
kingjely earned 800 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 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 800 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 200 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

569 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