Re: [sqlite] translate time comparison statement
[EMAIL PROTECTED] wrote:
Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
SELECT school_name from schools WHERE julianday('now') -
julianday(arrival_date) > 7
Dennis Cote <[EMAIL PROTECTED]> wrote:
SELECT school_name from schools
WHERE date(arrival_date) < date('now', '-7 days');
SELECT school_name from schools
WHERE date(arrival_date) < date('now', 'localtime', '-7 days');
Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
All answers above are correct, as far as I can see at a quick
glance. But here is an efficiency tip: You can move the
constant date calculations into a subquery and thereby only
evaluate them once for the whole statement instead of once
for each row of result. For example:
WHERE (SELECT julianday('now')) - julianday(arrival_day) > 7
WHERE date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
WHERE (SELECT julianday(date('now'))) - julianday(date(arrival_date))>7
This is a dirty trick and it does make the query more difficult
to read, so only use it if it is necessary for performance.
--
D. Richard Hipp <[EMAIL PROTECTED]>
This is a good tip.
FYI - this sort of factoring of repeated or expensive subselects is
exactly what the SQL:1999 standard WITH clause is designed for. In
standard SQL this could be written as:
WITH oldest (date) AS (SELECT date('now', 'localtime', -7 days'))
SELECT school_name FROM schools
WHERE date(arrival_date) < (SELECT date FROM oldest)
This creates a temporary table called oldest with a single field called
date, and fills it with the result of the select expression which is
only executed once. This table can then be referred to in the main
select statement. This is basically the same thing that SQLite is doing
automatically when you factor the constant calculations into a subselect.
As with many of the more advanced standard SQL features, it can be a
little wordy for simple cases such as this. However it is just as
effective for more complicated cases. For example, if there were two
date fields that needed to be tested, the expensive date call would
still only be executed once. This power comes from the fact that the
subqueries are named, and can be referenced by name in multiple places.
WITH oldest (date) AS (SELECT date('now', 'localtime', -7 days'))
SELECT school_name FROM schools
WHERE date(arrival_date) < (SELECT date FROM oldest)
OR date(completed_date) < (SELECT date FROM oldest)
Whereas with the current SQL supported by SQLite the same subselect
would have to be repeated, and executed, twice (At least until the
optimizer is smart enough to detect and factor the common sub
expressions automatically, which may never happen.).
SELECT school_name FROM schools
WHERE date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
OR date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
Note that the WITH clause allows multiple subselect to be explicitly
factored by the user, and that each subquery can refer to any other
subqueries defined earlier. As an example, the steps used to prepare the
limit date could be separated like this.
WITH
now (date) AS (SELECT date('now')),
local_now (date) AS (SELECT date((SELECT date FROM now), 'localtime')),
oldest (date) AS (SELECT date((SELECT date FROM local_now), '-7 days'))
SELECT school_name FROM schools
WHERE date(arrival_date) < (SELECT date FROM oldest)
OR date(completed_date) < (SELECT date FROM oldest)
Re: [sqlite] translate time comparison statement
Mark Wyszomierski wrote:
You may want
WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
Mark,
You should still use the 'localtime' modifier on the 'now' value if your
timestamps are local time since 'now' always returns UTC times.
WHERE julianday(date('now', 'localtime')) - julianday(date(arrival_date)) > 7
Dennis Cote
Re: [sqlite] translate time comparison statement
Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
>
> SELECT school_name from schools WHERE julianday('now') -
> julianday(arrival_date) > 7
>
Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> SELECT school_name from schools
> WHERE date(arrival_date) < date('now', '-7 days');
>
> SELECT school_name from schools
> WHERE date(arrival_date) < date('now', 'localtime', '-7 days');
>
Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
>
> WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
>
All answers above are correct, as far as I can see at a quick
glance. But here is an efficiency tip: You can move the
constant date calculations into a subquery and thereby only
evaluate them once for the whole statement instead of once
for each row of result. For example:
WHERE (SELECT julianday('now')) - julianday(arrival_day) > 7
WHERE date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
WHERE (SELECT julianday(date('now'))) - julianday(date(arrival_date))>7
This is a dirty trick and it does make the query more difficult
to read, so only use it if it is necessary for performance.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] translate time comparison statement
Cool thanks,
Mark
On 1/11/06, Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
>
> You may want
>
> WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
>
> so that time of day isn't part of the comparison; otherwise,
> you're correct.
>
> Regards
>
Re: [sqlite] translate time comparison statement
You may want
WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
so that time of day isn't part of the comparison; otherwise,
you're correct.
Regards
Re: [sqlite] translate time comparison statement
Mark Wyszomierski wrote:
Hi all,
I'm trying to translate some MySQL to sqlite. Ran into a problem with this
time comparison statement. I translated as best I could, it seems to work,
is this alright? (I'm trying to see if any records are more than 7 days old,
based on the value of 'arrival_date' field):
Original:
SELECT school_name from schools WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) >
arrival_date"
To sqlite:
SELECT school_name from schools WHERE julianday('now') -
julianday(arrival_date) > 7
And an example of the date value stored in the 'arrival_date' field looks
like:
'2006-01-10 16:14:19'
Mark,
What you have should work fine but the following might be a little clearer.
SELECT school_name from schools
WHERE date(arrival_date) < date('now', '-7 days');
This assumes your timestamps are UTC (or GMT). If not, you must convert
now to localtime like this.
SELECT school_name from schools
WHERE date(arrival_date) < date('now', 'localtime', '-7 days');
HTH
Dennis Cote
[sqlite] translate time comparison statement
Hi all,
I'm trying to translate some MySQL to sqlite. Ran into a problem with this
time comparison statement. I translated as best I could, it seems to work,
is this alright? (I'm trying to see if any records are more than 7 days old,
based on the value of 'arrival_date' field):
Original:
SELECT school_name from schools WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) >
arrival_date"
To sqlite:
SELECT school_name from schools WHERE julianday('now') -
julianday(arrival_date) > 7
And an example of the date value stored in the 'arrival_date' field looks
like:
'2006-01-10 16:14:19'
Thanks,
Mark
Re: [sqlite] Thread handling in Windows
Imagine how different the IT world would be if Microsoft had hired computer scientists instead of lawyers. We can but dream. [EMAIL PROTECTED] wrote: "Robert Simpson" <[EMAIL PROTECTED]> wrote: Not quite so straightforward in Windows: Seems like nothing ever is. (sigh) My life would be so much simpler if Bill Gates has stayed in school long enough to take just one course in operating systems... -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Thread handling in Windows
Rats! It figures that I'd read this right after hitting the send button: (From MSDN) If a DLL declares any nonlocal data or object as __declspec( thread ), it can cause a protection fault if dynamically loaded. After the DLL is loaded with LoadLibrary, it causes system failure whenever the code references the nonlocal __declspec( thread ) data. Because the global variable space for a thread is allocated at run time, the size of this space is based on a calculation of the requirements of the application plus the requirements of all of the DLLs that are statically linked. When you use LoadLibrary, there is no way to extend this space to allow for the thread local variables declared with __declspec( thread ). Use the TLS APIs, such as TlsAlloc, in your DLL to allocate TLS if the DLL might be loaded with LoadLibrary.
Re: [sqlite] Thread handling in Windows
- Original Message - From: <[EMAIL PROTECTED]> It makes the code smaller and faster to set a flag when malloc fails, rather than trying to test for the failure all the way back up the stack. The flag used to be a global variable. But that was causing problems for embedded device manufacturers. Doesn't *nix have the __declspec(thread) modifier? Your malloc failure flag could still be a global variable with those attributes ...
Re: [sqlite] Thread handling in Windows
Teg <[EMAIL PROTECTED]> wrote: > Hello drh, > > Could you perhaps give us the rational for using TLS? How is it that > TLS is needed on cases where you run out of memory? It makes the code smaller and faster to set a flag when malloc fails, rather than trying to test for the failure all the way back up the stack. The flag used to be a global variable. But that was causing problems for embedded device manufacturers. Specifically, embedded devices run out of memory quite often - a malloc() failure is routine. It is not acceptable to shut down the whole process just because malloc failed. But if the failure flag is in a global variable, there is no way to know which thread took the failure and hence no way to recover gracefully. Everything has to be stopped and restarted. By moving the malloc failure flag into TLS, we can recover gracefully from OOM without impacting other threads at all. TLS is also used to support new features of 3.3.0: Share caches and memory management. Both are off by default. But if you want to use them you will need TLS. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Thread handling in Windows
Hello drh, Could you perhaps give us the rational for using TLS? How is it that TLS is needed on cases where you run out of memory? By using TLS you're essentially making SQlite thread aware where I'd prefer it to know nothing about thread contexts or whether it's running in a thread or not. How is TLS better than a simple malloc and free in this instance? To me the proper solution would be to avoid using TLS in the first place since, you (sqlite) neither create nor destroy the thread. C Wednesday, January 11, 2006, 10:00:02 AM, you wrote: dhc> In the unix world using pthreads, when you allocate dhc> thread-local storage, you specify a callback function dhc> to delete the storage when the thread terminates. dhc> This callback is the second argument to dhc> pthread_key_create(), See, for example, dhc> http://www.mkssoftware.com/docs/man3/pthread_key_create.3.asp dhc> Question: How do you do the same thing on windows? dhc> How do you get a thread to clean up its thread-local-storage dhc> obtained using TlsAlloc() and TlsSetValue() when the dhc> thread terminates? dhc> I need an answer to this question so that I can fix dhc> ticket #1601: http://www.sqlite.org/cvstrac/tktview?tn=1601 dhc> -- dhc> D. Richard Hipp <[EMAIL PROTECTED]> -- Best regards, Tegmailto:[EMAIL PROTECTED]
Re: [sqlite] Porting SQL to run on a proprietary operating system
I gave up on this port. Far more complicated than what I initially thought. Guess I'll have to implement stupid flat files manually. :(( On 12/30/05, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > Yes, most people load the data to persistant memory when shutting > down, or periodically > to save a snapshot, and load it when booting up. It's fast but if your > hardware > can't detect a power failure and write the data with what power > remains in the power supply > capacitors you risk losing some data in case of power failure between > snapshots. > > On 12/30/05, Axel Mammes <[EMAIL PROTECTED]> wrote: > > If I use memory database, the content will be zeroed when I reboot the > > equipment. Only memory that is allocated for the filesystem is > > persistant between power cycles. > > >
Re: [sqlite] Thread handling in Windows
- Original Message - From: <[EMAIL PROTECTED]> No. Thread-local storage is needed to recover from out-of-memory errors. It is not optional. There is one way to do this, but it might be ugly. The way I see it, the problem lies in the fact that you are never notified when a thread terminates. The only way to detect if a thread has terminated is to call GetExitCodeThread() and if the status is not STILL_ACTIVE, then the thread is dead. In order to call that function, you need a thread handle. Which means that whenever you allocate new tls data for a given thread, you must call DuplicateHandle(GetCurrentThread()) and store the copy of the thread handle in a linked list. So that leaves you with periodically scanning through the list looking for threads that have disappeared, and once gone, you raise the callback routine and destroy their tls data and close the thread handle. Perhaps it could be done during sqlite_reset, or sqlite_finalize or sqlite_prepare ... but its not quite as elegant as the *nix way. Robert
Re: [sqlite] Thread handling in Windows
On Jan 11, 2006, at 7:43 AM, [EMAIL PROTECTED] wrote: Seems like nothing ever is. (sigh) My life would be so much simpler if Bill Gates has stayed in school long enough to take just one course in operating systems... LOL! That's about the funniest thing I've ever heard. Do we have permission to use this?
Re: [sqlite] Thread handling in Windows
I'm the one who posted ticket 1601. I have a multithreaded sqlite tcp server, and it ran fine with 2.8.x and 3.x versions, but this "feature" is a stopper for me. I think there is no difference using a dll or not, there is no way to free that thread memory allocated, since that function it uses static variables. Some have stated using DLL_THREAD_DETACH, but this only works (in case it does work) using the dll, if linked statically it keeps leaking memory. As drh stated: "In the unix world using pthreads, when you allocate thread-local storage, you specify a callback function to delete the storage when the thread terminates. This callback is the second argument..." Ok, and what is it suppose to do that callback being that info internal to sqlite, and thus not exposed? The problem arises when sqlite uses (malloc) thread memory but itself does not create/terminate threads. An application that uses sqlite is the one that knows about threads, modules, libraries, etc. ie., when they are created/terminated, thread id, priority, library (dll) loading/unloading, and so on. I've always thought (since I began using sqlite) about a couple of public functions (and exported in the "def" file) like: InitSqliteLib and DeInitSqliteLib. These would take care of all the public variables, hash lists and so forth to make a clean start and exit. Extending this, maybe a couple more but for threads, like (exported in the "def" file): InitSqliteThread and DeInitSqliteThread. So right after creating a thread that is going to use sqlite functions, it would call InitSqliteThread, which would allocate, initialize or whatever it needs for the current thread, and right before terminating the thread it would call DeInitSqliteThread. Same for the InitSqliteLib and DeInitSqliteLib. This way, it would be possible to get it working using pthreads, Windows threads and others.
Re: [sqlite] Thread handling in Windows
"Robert Simpson" <[EMAIL PROTECTED]> wrote: > > I haven't looked too closely at the thread local data stuff recently added > to SQLite, however, is there a way to have it default to off, and opt-in at > runtime? No. Thread-local storage is needed to recover from out-of-memory errors. It is not optional. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Thread handling in Windows
- Original Message - From: <[EMAIL PROTECTED]> In the unix world using pthreads, when you allocate thread-local storage, you specify a callback function to delete the storage when the thread terminates. This callback is the second argument to pthread_key_create(), See, for example, http://www.mkssoftware.com/docs/man3/pthread_key_create.3.asp Question: How do you do the same thing on windows? How do you get a thread to clean up its thread-local-storage obtained using TlsAlloc() and TlsSetValue() when the thread terminates? I need an answer to this question so that I can fix ticket #1601: http://www.sqlite.org/cvstrac/tktview?tn=1601 I haven't looked too closely at the thread local data stuff recently added to SQLite, however, is there a way to have it default to off, and opt-in at runtime? That way if your threads failed to call the "sqlite3_uber_cleanup()" function and leak memory on exit, it'd be your own damn fault and not SQLite's. Robert
RE: [sqlite] Thread handling in Windows
Damn! Then he would have ALL the money, not just a few billion! :-) > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 11, 2006 9:44 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Thread handling in Windows > > > "Robert Simpson" <[EMAIL PROTECTED]> wrote: > > > > Not quite so straightforward in Windows: > > > > Seems like nothing ever is. (sigh) My life would be so much > simpler if Bill Gates has stayed in school long enough > to take just one course in operating systems... > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] Thread handling in Windows
- Original Message - From: <[EMAIL PROTECTED]> "Robert Simpson" <[EMAIL PROTECTED]> wrote: Not quite so straightforward in Windows: Seems like nothing ever is. (sigh) My life would be so much simpler if Bill Gates has stayed in school long enough to take just one course in operating systems... Imagine if Bill had taken that OS course instead of that business course ... then imagine Bill as Linus Torvalds. Then imagine Commodore rules the world! Amiga Forever! Robert
Re: [sqlite] Optimizing for space and speed
Martin O'Leary <[EMAIL PROTECTED]> wrote: > On 1/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > Martin O'Leary <[EMAIL PROTECTED]> wrote: > > > > > > Also, with regard to 3.3.0, the alpha release seems to slow down my > > > example query by about 20% (The virtual machine opcodes are > > > identical). Is this a bug? > > > > > > > A bug is when it gets the wrong answer. > > > > Nevertheless we are concerned about performance. What > > are you comparing 3.3.0 against and what platform are > > you running on? > > Compared to 3.2.8, running on Linux (2.6.10-5-amd64-k8, Ubuntu 5.04) > on a 1.8 GHz machine with 3 GB RAM and ample disk space. > Thanks. We will work the problem. Correctness first, then speed. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Optimizing for space and speed
On 1/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Martin O'Leary <[EMAIL PROTECTED]> wrote: > > > > Also, with regard to 3.3.0, the alpha release seems to slow down my > > example query by about 20% (The virtual machine opcodes are > > identical). Is this a bug? > > > > A bug is when it gets the wrong answer. > > Nevertheless we are concerned about performance. What > are you comparing 3.3.0 against and what platform are > you running on? Compared to 3.2.8, running on Linux (2.6.10-5-amd64-k8, Ubuntu 5.04) on a 1.8 GHz machine with 3 GB RAM and ample disk space. Martin
Re: [sqlite] Optimizing for space and speed
Martin O'Leary <[EMAIL PROTECTED]> wrote: > > Also, with regard to 3.3.0, the alpha release seems to slow down my > example query by about 20% (The virtual machine opcodes are > identical). Is this a bug? > A bug is when it gets the wrong answer. Nevertheless we are concerned about performance. What are you comparing 3.3.0 against and what platform are you running on? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Thread handling in Windows
"Robert Simpson" <[EMAIL PROTECTED]> wrote: > > Not quite so straightforward in Windows: > Seems like nothing ever is. (sigh) My life would be so much simpler if Bill Gates has stayed in school long enough to take just one course in operating systems... -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Re: Thread handling in Windows
[EMAIL PROTECTED] wrote: In the unix world using pthreads, when you allocate thread-local storage, you specify a callback function to delete the storage when the thread terminates. Question: How do you do the same thing on windows? If you are building a DLL, you can use DllMain(DLL_THREAD_DETACH). Otherwise, your pretty much only option is to keep track of everything you have allocated and to free it all at once during final cleanup. This is a very unfortunate deficiency in Windows TLS APIs. Note that DLL_THREAD_DETACH might not be reliable. For example, if your DLL has been loaded dynamically with LoadLibrary it won't get DLL_THREAD_ATTACH notifications for every individual thread that's running at the time - it'll get a single DLL_PROCESS_ATTACH. So you should get and set your TLS data lazily - check whether it's there, if it's not the thread is calling you for the first time so allocate the data, otherwise use the data previously stored. Similarly, when your DLL is explicitly unloaded with FreeLibrary, you won't get DLL_THREAD_DETACH from every thread running at the moment - you'll get a single DLL_PROCESS_DETACH. It is wise to keep an independent list of every piece of TLS data ever allocated and to free it all in DLL_PROCESS_DETACH notification. Igor Tandetnik
RE: [sqlite] Thread handling in Windows
>Question: How do you do the same thing on windows? >How do you get a thread to clean up its thread-local-storage >obtained using TlsAlloc() and TlsSetValue() when the >thread terminates? You may find this example useful: http://www.codeproject.com/threads/tls.asp Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
Re: [sqlite] Thread handling in Windows
- Original Message - From: <[EMAIL PROTECTED]> In the unix world using pthreads, when you allocate thread-local storage, you specify a callback function to delete the storage when the thread terminates. This callback is the second argument to pthread_key_create(), See, for example, http://www.mkssoftware.com/docs/man3/pthread_key_create.3.asp Question: How do you do the same thing on windows? How do you get a thread to clean up its thread-local-storage obtained using TlsAlloc() and TlsSetValue() when the thread terminates? I need an answer to this question so that I can fix ticket #1601: http://www.sqlite.org/cvstrac/tktview?tn=1601 Not quite so straightforward in Windows: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/thread_local_storage.asp Excerpt of #4: When each thread no longer needs to use a TLS index, it must free the memory whose pointer is stored in the TLS slot. When all threads have finished using a TLS index, use the TlsFree function to free the index. For example, use the following code in your DllMain during DLL_THREAD_DETACH: lpvBuffer = TlsGetValue(gdwTlsIndex); LocalFree((HLOCAL) lpvBuffer); and the following code during DLL_PROCESS_DETACH: TlsFree(gdwTlsIndex);
Re: [sqlite] Thread handling in Windows
Every Windows DLL can provide a DllMain function. This function gets called
whenever a thread gets created or destroyed.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dllproc/base/dllmain.asp
Some example code:
/**
\brief
*/
BOOL WINAPI DllMain(
HINSTANCE hinstDLL,
DWORD fdwReason,
LPVOID /*lpReserved*/)
{
// Perform actions based on the reason for calling.
switch( fdwReason )
{
case DLL_PROCESS_ATTACH:
// Initialize once for each new process.
// Return FALSE to fail DLL load.
myDllHandle = hinstDLL;
// Allocate a TLS index.
if((myTlsIndex = TlsAlloc()) == 0x)
return FALSE;
break;
case DLL_THREAD_ATTACH:
// Do thread-specific initialization.
break;
case DLL_THREAD_DETACH:
// Do thread-specific cleanup.
// Release the allocated memory for this thread.
myFreeUserMessageBufferOfThread();
break;
case DLL_PROCESS_DETACH:
NI_DEBUG_ASSERT(myRefCount == 0); // usi not correctly
deinitialized
myFreeUserMessageBufferOfThread();
TlsFree(myTlsIndex);
myTlsIndex = myTlsIndexInvalid;
break;
}
return TRUE; // Successful DLL_PROCESS_ATTACH.
}
[EMAIL PROTECTED]
11.01.2006 16:00 To
sqlite-users@sqlite.org
cc
Please respond to
[EMAIL PROTECTED] Subject
te.org [sqlite] Thread handling in Windows
In the unix world using pthreads, when you allocate
thread-local storage, you specify a callback function
to delete the storage when the thread terminates.
This callback is the second argument to
pthread_key_create(), See, for example,
http://www.mkssoftware.com/docs/man3/pthread_key_create.3.asp
Question: How do you do the same thing on windows?
How do you get a thread to clean up its thread-local-storage
obtained using TlsAlloc() and TlsSetValue() when the
thread terminates?
I need an answer to this question so that I can fix
ticket #1601: http://www.sqlite.org/cvstrac/tktview?tn=1601
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Optimizing for space and speed
[EMAIL PROTECTED] wrote: > > The query as you have specified it runs in O(NlogN) time > where N is the number of rows in the table. Actually, the original query runs in O(N) time. I was mistaken. But I still think I am right in saying that my "manual" scheme runs in O(MlogN) time. So the manual scheme will only be faster if O(N)>O(MlogN). So your choice of algorithm will depend a lot on the values of M and N. > > I suggest you do the query "manually". To begin with, use > SQLite version 3.3.0 (which supports DESC indices) and create > your table with a separate index like this: > > CREATE TABLE user_actions ( > uid INTEGER NOT NULL, > actionid INTEGER NOT NULL, > time INTEGER NOT NULL, > status INTEGER NOT NULL > ); > CREATE INDEX user_actions_pk > ON user_actions(uid,actionid,time DESC,status); > > The DESC attribute on the time field of the index means that > index entries will be in descending time order instead of > the default ascending. Hence, the first entry in the index > for a particular uid+actionid will be the one with the largest > time value. > > Find the first record in O(logN) time like this: > > SELECT * FROM user_actions >ORDER BY uid, actionid, time DESC >LIMIT 1; > > Find all other actions for the same user this way: > > SELECT * FROM user_actions >WHERE uid=:previous_uid AND actionid>:previous_actionid >ORDER BY uid, actionid, time DESC >LIMIT 1; > > Once you have all records for a single user, advance to the > next user this way: > > SELECT * FROM user_actions >WHERE uid>:previous_uid >ORDER BY uid, actionid, time DESC >LIMIT 1 > > Repeat until done. Runtime is O(MlogN) where N is the number > of rows in the table and M is the number of rows of output. > Since M is likely much less than N, this approach will be much > faster. > -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Thread handling in Windows
In the unix world using pthreads, when you allocate thread-local storage, you specify a callback function to delete the storage when the thread terminates. This callback is the second argument to pthread_key_create(), See, for example, http://www.mkssoftware.com/docs/man3/pthread_key_create.3.asp Question: How do you do the same thing on windows? How do you get a thread to clean up its thread-local-storage obtained using TlsAlloc() and TlsSetValue() when the thread terminates? I need an answer to this question so that I can fix ticket #1601: http://www.sqlite.org/cvstrac/tktview?tn=1601 -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Optimizing for space and speed
Martin O'Leary <[EMAIL PROTECTED]> wrote: > Hi guys, > > I have a table like the following: > > CREATE TABLE user_actions ( > uid INTEGER NOT NULL, > actionid INTEGER NOT NULL, > time INTEGER NOT NULL, > status INTEGER NOT NULL, > PRIMARY KEY (uid, actionid, time, status) > ); > > And I want to carry out a query something like this: > > SELECT uid, actionid, MAX(time), status FROM user_actions GROUP BY > uid, actionid; Are you thinking that the value of "status" returned will be the one which has the maximum value for "time"? SQL doesn't work that way. To understand why not, consider this query: SELECT uid, actionid, MAX(time), MIN(time), status FROM user_actions GROUP BY uid, actionid; > > Here, I find two problems. Firstly, because my table doesn't have an > INTEGER PRIMARY KEY, I get an autogenerated index on my compound key. > No problem there. However, and I may be missing something, it seems > that there's a lot of data duplication going on. All the data from my > main table is available in the index (status shouldn't really be part > of the key, but I added it in order to increase query performance). As > far as I can tell, this means that the main table is never consulted, > but just sits there, doubling the size of my database. Is there any > way around this? All data is duplicated - it appears in both the table and in the index. There is no way around that. > > Secondly, query performance is quite slow. It seems to me that no > optimisation is being carried out on the MAX(time) expression. Is this > the case, and if so, why not? Surely it's possible to do this in a > nice, logarithmic way. > The query as you have specified it runs in O(NlogN) time where N is the number of rows in the table. Perhaps an enterprise-class RDBMS with a really big and really expensive query optimizer can do better, but SQLite isn't that smart. I suggest you do the query "manually". To begin with, use SQLite version 3.3.0 (which supports DESC indices) and create your table with a separate index like this: CREATE TABLE user_actions ( uid INTEGER NOT NULL, actionid INTEGER NOT NULL, time INTEGER NOT NULL, status INTEGER NOT NULL ); CREATE INDEX user_actions_pk ON user_actions(uid,actionid,time DESC,status); The DESC attribute on the time field of the index means that index entries will be in descending time order instead of the default ascending. Hence, the first entry in the index for a particular uid+actionid will be the one with the largest time value. Find the first record in O(logN) time like this: SELECT * FROM user_actions ORDER BY uid, actionid, time DESC LIMIT 1; Find all other actions for the same user this way: SELECT * FROM user_actions WHERE uid=:previous_uid AND actionid>:previous_actionid ORDER BY uid, actionid, time DESC LIMIT 1; Once you have all records for a single user, advance to the next user this way: SELECT * FROM user_actions WHERE uid>:previous_uid ORDER BY uid, actionid, time DESC LIMIT 1 Repeat until done. Runtime is O(MlogN) where N is the number of rows in the table and M is the number of rows of output. Since M is likely much less than N, this approach will be much faster. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Optimizing for space and speed
Hi guys, I have a table like the following: CREATE TABLE user_actions ( uid INTEGER NOT NULL, actionid INTEGER NOT NULL, time INTEGER NOT NULL, status INTEGER NOT NULL, PRIMARY KEY (uid, actionid, time, status) ); And I want to carry out a query something like this: SELECT uid, actionid, MAX(time), status FROM user_actions GROUP BY uid, actionid; i.e. finding the last time each user performed each action (numbers of users and distinct actions are small in comparison to number of times each user performs each action). Here, I find two problems. Firstly, because my table doesn't have an INTEGER PRIMARY KEY, I get an autogenerated index on my compound key. No problem there. However, and I may be missing something, it seems that there's a lot of data duplication going on. All the data from my main table is available in the index (status shouldn't really be part of the key, but I added it in order to increase query performance). As far as I can tell, this means that the main table is never consulted, but just sits there, doubling the size of my database. Is there any way around this? Secondly, query performance is quite slow. It seems to me that no optimisation is being carried out on the MAX(time) expression. Is this the case, and if so, why not? Surely it's possible to do this in a nice, logarithmic way. Anyone have any thoughts? Thanks, Martin
[sqlite] RE: How to compile sqlite with VC6?
There is a package in the SqliteWrappers WIKI to download that does all this - http://www.pivotal-solutions.co.uk/downloads/company/pssqlite.zip It's a nice VC6 DLL wrapper for VB6 & Java with source code. Dynamically binds to the sqlite.dll and sqlite3.dll at runtime so you don't need to recompile everytime a new sqlite version comes along (why aren't all the wrappers like this???) I've inlcuded the Java wrapper classes with test code and also the VB6 test code. The JNI implementation returns proper native Java types has also been tested on Linux as a shared library. It has been used for ages on various projects here - do what you want with it even if it just serves as some sample code for you. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg] On Behalf Of Michael Sizaki Sent: 07 January 2006 01:44 To: sqlite-users@sqlite.org Subject: [RBL] [sqlite] How to compile sqlite with VC6? Hi, I want to use sqlite from java on windows. I want to create my own jni binding (different from http://www.ch-werner.de/javasqlite/overview-summary.html). I have VC6 but I'm not a C programmer. How to compile sqlite? Essentially I would like to add a few simple functions to sqlite written in C Michael
