Re: [sqlite] Data structure for versioned data
Not quite the same issue, but I've set up triggers to generate a journal record whenever a record is added/changed/deleted from a table. This mechanism (triggers) could easily be used to generate a 'version' record. *** Doug F. John Stanton wrote: > We perform some versioning by holding column material in XML and using > RCS to maintain reverse deltas and versions. > > Samuel R. Neff wrote: >> Not specific to SQLite, but we're working on an app that needs to keep >> versioned data (i.e., the current values plus all previous values). The >> versioning is integral to the app so it's more than just an audit >> trail or >> history. >> >> Can anyone share experiences with the database structure for this >> type of >> requirement or point me to helpful resources? >> >> Thanks, >> >> Sam >> >> >> >> --- >> We're Hiring! Seeking a passionate developer to join our team building >> products. Position is in the Washington D.C. metro area. If interested >> contact [EMAIL PROTECTED] >> >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure for versioned data
Hi Sam,
On Wed, 20 Jun 2007 15:33:23 -0400, you wrote:
>Not specific to SQLite, but we're working on an app that needs to keep
>versioned data (i.e., the current values plus all previous values). The
>versioning is integral to the app so it's more than just an audit trail or
>history.
>
>Can anyone share experiences with the database structure for this type of
>requirement or point me to helpful resources?
Joe Celko has a good chapter on temporal data
in his book "SQL for smarties" [1].
Some time ago I found a bash script from Mike Chirico
which maintains a notes database:
http://souptonuts.sourceforge.net/code/n.html
and changed it to my liking as a
demonstration of temporal data [2].
Editing it for publication here may have introduced some
mistakes, testing is left to you ;)
References and quotes:
[1]
Google for ISDN-13: 978-0-12-369379-2
[2]
-- init database
PRAGMA page_size=4096;
PRAGMA cache_size=100;
PRAGMA default_cache_size=100;
-- define schema
BEGIN TRANSACTION;
DROP TABLE IF EXISTS notes;
CREATE TABLE notes (
nkey INTEGER PRIMARY KEY, -- names ROWID
-- category left out in this demo
msg TEXT
);
-- '-12-31 23:59:59' means 'end-of-times'
DROP TABLE IF EXISTS audit;
CREATE TABLE audit (
nkey INTEGER,
-- category left out in this demo
msg TEXT,
timeFrDATETEXT,
timeToDATETEXT DEFAULT '-12-31 23:59:59',
PRIMARY KEY (nkey,timeFr)
);
CREATE TRIGGER audit_insert_notes
AFTER INSERT ON notes FOR EACH ROW
BEGIN
INSERT INTO audit (nkey, msg, timeFr)
VALUES (new.nkey, new.msg, CURRENT_TIMESTAMP);
END;
CREATE TRIGGER audit_update_notes
AFTER UPDATE ON notes FOR EACH ROW
BEGIN
UPDATE audit SET timeTo = CURRENT_TIMESTAMP
WHERE nkey == new.nkey
AND timeTo == '-12-31 23:59:59';
INSERT INTO audit (nkey, msg, timeFr)
VALUES (new.nkey, new.msg, CURRENT_TIMESTAMP);
END;
CREATE TRIGGER audit_delete_notes
AFTER DELETE ON notes FOR EACH ROW
BEGIN
UPDATE audit SET timeTo = CURRENT_TIMESTAMP
WHERE nkey == old.nkey
AND timeTo == '-12-31 23:59:59';
END;
DROP VIEW IF EXISTS sh_audit;
CREATE VIEW sh_audit AS
SELECT *,
strftime('%H:%M:%S',
(strftime('%s',CASE timeTo
WHEN '-12-31 23:59:59' THEN CURRENT_TIMESTAMP
ELSE timeTo
END) - strftime('%s',timeFr)),'unixepoch') AS lifespan
FROM audit
ORDER BY nkey,timeFr;
DROP VIEW IF EXISTS sh_status;
CREATE VIEW sh_status AS
SELECT
COUNT(CASE
WHEN timeTo == '-12-31 23:59:59' THEN 1
ELSE NULL
END) AS active,
COUNT(CASE
WHEN timeTo < CURRENT_TIMESTAMPTHEN 1
ELSE NULL
END) AS deleted
FROM audit
GROUP BY nkey;
COMMIT; -- done definitions
-- test data
INSERT INTO notes (msg) VALUES ('note 1 version 1');
INSERT INTO notes (msg) VALUES ('note 2 version 1');
UPDATE notes SET msg = 'note 1 version 2' WHERE nkey == 1;
UPDATE notes SET msg = 'note 2 version 2' WHERE nkey == 2;
UPDATE notes SET msg = 'note 1 version 3' WHERE nkey == 1;
-- queries
SELECT * FROM sh_audit;
SELECT * FROM sh_status;
SELECT 'notes';
SELECT * FROM notes;
SELECT 'audit';
SELECT * FROM audit;
SELECT 'audit with timediff';
SELECT * FROM sh_audit;
>Thanks,
>
>Sam
>
I hope this helps.
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Data structure for versioned data
We perform some versioning by holding column material in XML and using RCS to maintain reverse deltas and versions. Samuel R. Neff wrote: Not specific to SQLite, but we're working on an app that needs to keep versioned data (i.e., the current values plus all previous values). The versioning is integral to the app so it's more than just an audit trail or history. Can anyone share experiences with the database structure for this type of requirement or point me to helpful resources? Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Data structure for versioned data
Not specific to SQLite, but we're working on an app that needs to keep versioned data (i.e., the current values plus all previous values). The versioning is integral to the app so it's more than just an audit trail or history. Can anyone share experiences with the database structure for this type of requirement or point me to helpful resources? Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
Thank you John Stanton. This has opened new doors for me, and think it would be helpful for others in the list too.. Thanks and Regards Lloyd On Thu, 2007-04-12 at 12:34 -0500, John Stanton wrote: > We use a very simple data retrieval method for smallish datasets. The > data is just stored in memory or as a memory mapped file and a > sequential search used. It sounds crude but when you use a fast search > algorithm like Boyer-Moore it outperforms index methods up to a > surprisingly large number of records. As you can imagine the code > footprint is miniscule and if you add regular expression logic you can > realize very intricate search patterns. > > We use the method in conjunction with a database to achieve an enormous > speed increase on "LIKE" type searches. Grep a few files to get a feel > for the performance. > > Another method which works well for memory resident storage is to > implement self balancing AVL trees. The code is simple and the > performance lightning fast. With a little ingenuity you can use disk > storage. Mini Sql (MSql) is a good example of how this can be effective. > > As Einstein said - "Make it as simple as possible, but not too simple". > Applying Occam's Razor can turn bloated solutions into more > effective lean ones. Typical solutions come in two sizes just like Army > boots - too big and too small. > __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
Lloyd, If you want some code examples contact me and I shall send you some. [EMAIL PROTECTED] Lloyd wrote: Thank you John Stanton. This has opened new doors for me, and think it would be helpful for others in the list too.. Thanks and Regards Lloyd On Thu, 2007-04-12 at 12:34 -0500, John Stanton wrote: We use a very simple data retrieval method for smallish datasets. The data is just stored in memory or as a memory mapped file and a sequential search used. It sounds crude but when you use a fast search algorithm like Boyer-Moore it outperforms index methods up to a surprisingly large number of records. As you can imagine the code footprint is miniscule and if you add regular expression logic you can realize very intricate search patterns. We use the method in conjunction with a database to achieve an enormous speed increase on "LIKE" type searches. Grep a few files to get a feel for the performance. Another method which works well for memory resident storage is to implement self balancing AVL trees. The code is simple and the performance lightning fast. With a little ingenuity you can use disk storage. Mini Sql (MSql) is a good example of how this can be effective. As Einstein said - "Make it as simple as possible, but not too simple". Applying Occam's Razor can turn bloated solutions into more effective lean ones. Typical solutions come in two sizes just like Army boots - too big and too small. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
At 17:35 11/04/2007, you wrote: >Lloyd wrote: >> >>Sorry, I am not talking about the limitations of the system in our side, >>but end user who uses our software. I want the tool to be run at its >>best on a low end machine also. >>I don't want the capabilities of a data base here. Just want to store >>data, search for presence, remove it when there is no more use of it. >>Surely I will check out BerkeleyDB. The data set must be in ram, because >>the total size of it is very small. (Few maga bytes) I just want to >>spped up the search, which is done millions of times. >>Thanks, >> LLoyd >You might discover that you can craft a very effective memory resident storage >system using a compression system like Huffman Encoding and an index method >appropriate to the key you are using for retrieval. That could work very well >in an embedded system, have a small footprint in data and code and be very >fast. There is a book about that (2 books) called Managing Gigabytes (1 and 2 editions) which shows how to use compression techniques with data search. The full source code is open source (or i remember it was). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
We use a very simple data retrieval method for smallish datasets. The data is just stored in memory or as a memory mapped file and a sequential search used. It sounds crude but when you use a fast search algorithm like Boyer-Moore it outperforms index methods up to a surprisingly large number of records. As you can imagine the code footprint is miniscule and if you add regular expression logic you can realize very intricate search patterns. We use the method in conjunction with a database to achieve an enormous speed increase on "LIKE" type searches. Grep a few files to get a feel for the performance. Another method which works well for memory resident storage is to implement self balancing AVL trees. The code is simple and the performance lightning fast. With a little ingenuity you can use disk storage. Mini Sql (MSql) is a good example of how this can be effective. As Einstein said - "Make it as simple as possible, but not too simple". Applying Occam's Razor can turn bloated solutions into more effective lean ones. Typical solutions come in two sizes just like Army boots - too big and too small. Lloyd wrote: Would anybody suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: You might discover that you can craft a very effective memory resident storage system using a compression system like Huffman Encoding and an index method appropriate to the key you are using for retrieval. That could work very well in an embedded system, have a small footprint in data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Data structure
valgrind -Original Message- From: Lloyd [mailto:[EMAIL PROTECTED] Sent: Thursday, April 12, 2007 12:26 AM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Data structure Would anybody suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: > You might discover that you can craft a very effective memory > resident > storage system using a compression system like Huffman Encoding and > an > index method appropriate to the key you are using for retrieval. > That > could work very well in an embedded system, have a small footprint in > data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
You might want to check out kazlib for your data structure lookups. It cantains code to implement Linked List, Hast, and Dictionary access data structures. The hashing code is really quite fast for in memory retrievals plus it is dynamic so that you don't have to preconfigure your hash table size. The linked list code is pretty good, it does have the ability to create Memory Pools (node pools) for the list structures. That way the package is not continually calling malloc and free for every node insert/delete etc.. Lloyd <[EMAIL PROTECTED]> wrote: On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote: > I think, looking from Lloyd's email address, (s)he might be limited to > what CDAC, Trivandrum might be providing its users. > > Lloyd, you already know what size your data sets are. Esp. if it > doesn't change, putting the entire dataset in RAM is the best option. > If you don't need SQL capabilities, you probably can just use > something like BerkeleyDB or DBD::Deep (if using Perl), and that will > be plenty fast. Of course, if it can't be done then it can't be done, > and you will have to recommend more RAM for the machines (the CPU > seems fast enough, just the memory may be a bottleneck). Sorry, I am not talking about the limitations of the system in our side, but end user who uses our software. I want the tool to be run at its best on a low end machine also. I don't want the capabilities of a data base here. Just want to store data, search for presence, remove it when there is no more use of it. Surely I will check out BerkeleyDB. The data set must be in ram, because the total size of it is very small. (Few maga bytes) I just want to spped up the search, which is done millions of times. Thanks, LLoyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
I've used callgrind to get a hierachy of calls, it's good to graphically see where your spending time at in the code. Also you might want to check out oprofile. Its more of a system based profiler. And if you want to spend $$$ Rational Rose (I thinkt its an IBM product now) Purify is an excellent tool. Ken Lloyd <[EMAIL PROTECTED]> wrote: Would anybody suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: > You might discover that you can craft a very effective memory > resident > storage system using a compression system like Huffman Encoding and > an > index method appropriate to the key you are using for retrieval. > That > could work very well in an embedded system, have a small footprint in > data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
Would anybody suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: > You might discover that you can craft a very effective memory > resident > storage system using a compression system like Huffman Encoding and > an > index method appropriate to the key you are using for retrieval. > That > could work very well in an embedded system, have a small footprint in > data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
Thank you all. I got so many new ideas from your replies. Now I just have to derive the best solution for me, thanks :) Lloyd On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: > You might discover that you can craft a very effective memory > resident > storage system using a compression system like Huffman Encoding and > an > index method appropriate to the key you are using for retrieval. > That > could work very well in an embedded system, have a small footprint in > data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
Lloyd wrote: On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote: I think, looking from Lloyd's email address, (s)he might be limited to what CDAC, Trivandrum might be providing its users. Lloyd, you already know what size your data sets are. Esp. if it doesn't change, putting the entire dataset in RAM is the best option. If you don't need SQL capabilities, you probably can just use something like BerkeleyDB or DBD::Deep (if using Perl), and that will be plenty fast. Of course, if it can't be done then it can't be done, and you will have to recommend more RAM for the machines (the CPU seems fast enough, just the memory may be a bottleneck). Sorry, I am not talking about the limitations of the system in our side, but end user who uses our software. I want the tool to be run at its best on a low end machine also. I don't want the capabilities of a data base here. Just want to store data, search for presence, remove it when there is no more use of it. Surely I will check out BerkeleyDB. The data set must be in ram, because the total size of it is very small. (Few maga bytes) I just want to spped up the search, which is done millions of times. Thanks, LLoyd You might discover that you can craft a very effective memory resident storage system using a compression system like Huffman Encoding and an index method appropriate to the key you are using for retrieval. That could work very well in an embedded system, have a small footprint in data and code and be very fast. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
Hi, If you are using C++, then try hash_map. I've used this on strings with more that 50,000 records - in memory. Very fast. Much easier to program than BerkeleyDB. - Original Message - From: "Lloyd" <[EMAIL PROTECTED]> To: Sent: Wednesday, April 11, 2007 11:20 PM Subject: Re: [sqlite] Data structure On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote: I think, looking from Lloyd's email address, (s)he might be limited to what CDAC, Trivandrum might be providing its users. Lloyd, you already know what size your data sets are. Esp. if it doesn't change, putting the entire dataset in RAM is the best option. If you don't need SQL capabilities, you probably can just use something like BerkeleyDB or DBD::Deep (if using Perl), and that will be plenty fast. Of course, if it can't be done then it can't be done, and you will have to recommend more RAM for the machines (the CPU seems fast enough, just the memory may be a bottleneck). Sorry, I am not talking about the limitations of the system in our side, but end user who uses our software. I want the tool to be run at its best on a low end machine also. I don't want the capabilities of a data base here. Just want to store data, search for presence, remove it when there is no more use of it. Surely I will check out BerkeleyDB. The data set must be in ram, because the total size of it is very small. (Few maga bytes) I just want to spped up the search, which is done millions of times. Thanks, LLoyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
I used an approach similar to the Bloom Filter for data retrieval. It could be very fast at retrieving substrings from large data sets but was fairly complex to implement. I would not go with that approach unless you had some very broad retrieval requirements and a very large data set. Lloyd wrote: I was just wondering what the odds were of doing a better job than the filing system pros, how much time/code that would take on your part and how much that time would cost versus speccing a bigger/faster machine. Martin I am not fully clear. I just want my program to run at most efficient and fast even on a low profile end user system. So I am in search for the best data structure. I have heard about bloom filter, but in my case it is not applicable. The final point is, I am just searching for a data structure which is the result of lateral thinking by experts ;) (like bloom filter) Thanks, Lloyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Data structure
There are many important variables to consider. What kind of data? How many tables (if a relational DB approach even makes sense)? How many records in each table? How will this data be queried? Many different, often unperdicatable ways? Or just a couple static ways? There's nothing faster than having the data in RAM (if you can fit all your data in what's available that is). A fast approach might be to write a C program that stores the data in a static array of a data structure, sort that array according to what you expect to be querying on, then use a b-tree search algorithm to query. I've done this myself in the past. It's probably what the relational DB engines do "under the hood". SQLite might be a good solution because it's in-memory, easy to use and, if constructed right, runs fast. It can also deal with unpredictable queries from users. So, you have to be a lot more specific. -dave -Original Message- From: Lloyd [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 11, 2007 11:12 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Data structure > > I was just wondering what the odds were of doing a better job than the > filing system pros, how much time/code that would take on your part and > how much that time would cost versus speccing a bigger/faster machine. > > Martin I am not fully clear. I just want my program to run at most efficient and fast even on a low profile end user system. So I am in search for the best data structure. I have heard about bloom filter, but in my case it is not applicable. The final point is, I am just searching for a data structure which is the result of lateral thinking by experts ;) (like bloom filter) Thanks, Lloyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
If it is just a read-only access to data then storing the data im memory with an index which can be either a hashing method or a binary tree would be the fastest. An easy to handle method is to store the data and index in a flat file and load it into memory. Loading it in virtual memory gives you more flexibility and gets more performance from your available memory because when memory is constrained only your most accessed data stays in memory rather than your application failing. P Kishor wrote: On 4/11/07, Martin Jenkins <[EMAIL PROTECTED]> wrote: Lloyd wrote: > hi Puneet and Martin, > On Wed, 2007-04-11 at 14:27 +0100, Martin Jenkins wrote: >> File system cache and plenty of RAM? >> > > It is meant to run on an end user system (eg. Pentium 4 1GB RAM). If you > mean Swap space as file system cache, it is also limited, may be 2GB. I was just wondering what the odds were of doing a better job than the filing system pros, how much time/code that would take on your part and how much that time would cost versus speccing a bigger/faster machine. I think, looking from Lloyd's email address, (s)he might be limited to what CDAC, Trivandrum might be providing its users. Lloyd, you already know what size your data sets are. Esp. if it doesn't change, putting the entire dataset in RAM is the best option. If you don't need SQL capabilities, you probably can just use something like BerkeleyDB or DBD::Deep (if using Perl), and that will be plenty fast. Of course, if it can't be done then it can't be done, and you will have to recommend more RAM for the machines (the CPU seems fast enough, just the memory may be a bottleneck). - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote: > I think, looking from Lloyd's email address, (s)he might be limited to > what CDAC, Trivandrum might be providing its users. > > Lloyd, you already know what size your data sets are. Esp. if it > doesn't change, putting the entire dataset in RAM is the best option. > If you don't need SQL capabilities, you probably can just use > something like BerkeleyDB or DBD::Deep (if using Perl), and that will > be plenty fast. Of course, if it can't be done then it can't be done, > and you will have to recommend more RAM for the machines (the CPU > seems fast enough, just the memory may be a bottleneck). Sorry, I am not talking about the limitations of the system in our side, but end user who uses our software. I want the tool to be run at its best on a low end machine also. I don't want the capabilities of a data base here. Just want to store data, search for presence, remove it when there is no more use of it. Surely I will check out BerkeleyDB. The data set must be in ram, because the total size of it is very small. (Few maga bytes) I just want to spped up the search, which is done millions of times. Thanks, LLoyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
> > I was just wondering what the odds were of doing a better job than the > filing system pros, how much time/code that would take on your part and > how much that time would cost versus speccing a bigger/faster machine. > > Martin I am not fully clear. I just want my program to run at most efficient and fast even on a low profile end user system. So I am in search for the best data structure. I have heard about bloom filter, but in my case it is not applicable. The final point is, I am just searching for a data structure which is the result of lateral thinking by experts ;) (like bloom filter) Thanks, Lloyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
On 4/11/07, Martin Jenkins <[EMAIL PROTECTED]> wrote: Lloyd wrote: > hi Puneet and Martin, > On Wed, 2007-04-11 at 14:27 +0100, Martin Jenkins wrote: >> File system cache and plenty of RAM? >> > > It is meant to run on an end user system (eg. Pentium 4 1GB RAM). If you > mean Swap space as file system cache, it is also limited, may be 2GB. I was just wondering what the odds were of doing a better job than the filing system pros, how much time/code that would take on your part and how much that time would cost versus speccing a bigger/faster machine. I think, looking from Lloyd's email address, (s)he might be limited to what CDAC, Trivandrum might be providing its users. Lloyd, you already know what size your data sets are. Esp. if it doesn't change, putting the entire dataset in RAM is the best option. If you don't need SQL capabilities, you probably can just use something like BerkeleyDB or DBD::Deep (if using Perl), and that will be plenty fast. Of course, if it can't be done then it can't be done, and you will have to recommend more RAM for the machines (the CPU seems fast enough, just the memory may be a bottleneck). -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
Lloyd wrote: hi Puneet and Martin, On Wed, 2007-04-11 at 14:27 +0100, Martin Jenkins wrote: File system cache and plenty of RAM? It is meant to run on an end user system (eg. Pentium 4 1GB RAM). If you mean Swap space as file system cache, it is also limited, may be 2GB. I was just wondering what the odds were of doing a better job than the filing system pros, how much time/code that would take on your part and how much that time would cost versus speccing a bigger/faster machine. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
hi Puneet and Martin, On Wed, 2007-04-11 at 14:27 +0100, Martin Jenkins wrote: > File system cache and plenty of RAM? > It is meant to run on an end user system (eg. Pentium 4 1GB RAM). If you mean Swap space as file system cache, it is also limited, may be 2GB. Puneet Kishor > you haven't provided any further details about the data itself so I > would venture to say -- get a computer with gobs of RAM (the new Mac > Pro 8-cores can accomodate 16 GB), and use a memory based hash. Query > away. Each node of the data structure will contain only very little data (A structure sizing 70 bytes and the key field would be of size maximum of 20 bytes). (I will see, what is memory based hash) Thanks and Regards, Lloyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Data structure
I'm not sure I understand the question, but I'll take a stab at it anyway. If the data is to be loaded by and queried from the same program execution, you may wnat to consider using a temporary table as opposed to a regular (permanent) one that will go to disk. The time you might save has to do with SQLite not bothering to work with (slow) disk. But beware of taking this approach if you expect to exceed the amount of memory available to you. You can optimize query performance by creating indexes on the field(s) you will be querying on. For example, if the table contains employee data, and one of the columns in that table is the employee's name, and you expect to be retrieving data based on the employee's name, create an index on the name column. Here's a pointer to some good info on performance tuning. It has helped me. http://katastrophos.net/andre/blog/2007/01/04/sqlite-performance-tuning- and-optimization-on-embedded-systems/ Good Luck ! -Original Message- From: Lloyd [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 11, 2007 9:12 AM To: sqlite-users@sqlite.org Subject: [sqlite] Data structure Hi, I don't know whether this is an irrelevant question in SQLite list, but I don't see a better place to ask. Which data structure is best to store and retrieve data very fastly? There is a 95% chance that the searched data to be present in the data structure. There will be 1000s of nodes in the data structure and it will be searched millions of times. The data is not in any ordered form. Thanks for your information, Lloyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
Lloyd wrote: Hi, I don't know whether this is an irrelevant question in SQLite list, but I don't see a better place to ask. Which data structure is best to store and retrieve data very fastly? There is a 95% chance that the searched data to be present in the data structure. There will be 1000s of nodes in the data structure and it will be searched millions of times. The data is not in any ordered form. File system cache and plenty of RAM? Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data structure
On 4/11/07, Lloyd <[EMAIL PROTECTED]> wrote: Hi, I don't know whether this is an irrelevant question in SQLite list, but I don't see a better place to ask. Which data structure is best to store and retrieve data very fastly? There is a 95% chance that the searched data to be present in the data structure. There will be 1000s of nodes in the data structure and it will be searched millions of times. The data is not in any ordered form. you haven't provided any further details about the data itself so I would venture to say -- get a computer with gobs of RAM (the new Mac Pro 8-cores can accomodate 16 GB), and use a memory based hash. Query away. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Data structure
Hi, I don't know whether this is an irrelevant question in SQLite list, but I don't see a better place to ask. Which data structure is best to store and retrieve data very fastly? There is a 95% chance that the searched data to be present in the data structure. There will be 1000s of nodes in the data structure and it will be searched millions of times. The data is not in any ordered form. Thanks for your information, Lloyd __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
