Re: [sqlite] Considerations with in-memory SQLite3
Dennis Cote wrote: > That looks right to me. > Thanks for the quick check, Dennis. > There is no need to create an index on the temporary id_list, since you > are going to be doing a full table scan of that table anyway. > >select * >from id_list >join mt.table on mt.table.id = id_list.id >order by id_list.id > > The only benefit of an index would be if you want the results returned > in id order, then the index would be used to optimize the order by > clause. If that is the case you can get the same effect by declaring the > id column as "integer primary key" in the id_list table. This will > eliminate the index and its redundant duplicate storage of the id_list data. > This is a very good point that I would not have guessed. My feeling would have been that an index would be needed since I was going to do a join on the data next. But, I see what you mean - something precious learned. Thanks! > For fastest operation the mt.table.id should also be an "integer primary > key" column as this will eliminate a rowid lookup operation if it is an > indexed column. > Yes, this I knew - I always keep an single field integer primary key for almost all my tables! Thanks for the reminder. Best regards Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Considerations with in-memory SQLite3
Mohit Sindhwani wrote:
>
> Since the database is to be created and deleted in a thread itself, I
> think I may need to do something like:
> * Create in-memory database (":memory:")
> * Attach the main database (from file) as 'mt'
> * Create the temporary table for id_list
> * Insert the user entered IDs
> * Create an index on it
> * Join and get the records of interest (between idlist.id and mt.table.id)
> * Do the processing
>
> Then, when I close the database, the in-memory stuff is all gone. Does
> that sound right?
>
That looks right to me.
There is no need to create an index on the temporary id_list, since you
are going to be doing a full table scan of that table anyway.
select *
from id_list
join mt.table on mt.table.id = id_list.id
order by id_list.id
The only benefit of an index would be if you want the results returned
in id order, then the index would be used to optimize the order by
clause. If that is the case you can get the same effect by declaring the
id column as "integer primary key" in the id_list table. This will
eliminate the index and its redundant duplicate storage of the id_list data.
For fastest operation the mt.table.id should also be an "integer primary
key" column as this will eliminate a rowid lookup operation if it is an
indexed column.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Considerations with in-memory SQLite3
Ken wrote:
> I'll take a stab at this.
>
> Each connection would have its own private memory database.
>
> Create an additional temp table id_list , Insert the user id set values into
> this table.
>
> Then
> INSERT INTO temptab tt SELECT mt.* FROM maintab mt, id_list
> WHERE mt.id = idl.id ;
>
> Then delete the id_list table or drop it.
>
Hi Ken,
Since the database is to be created and deleted in a thread itself, I
think I may need to do something like:
* Create in-memory database (":memory:")
* Attach the main database (from file) as 'mt'
* Create the temporary table for id_list
* Insert the user entered IDs
* Create an index on it
* Join and get the records of interest (between idlist.id and mt.table.id)
* Do the processing
Then, when I close the database, the in-memory stuff is all gone. Does
that sound right?
Thanks for your help in getting me started.
Mohit.
9/11/2008 | 12:46 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Considerations with in-memory SQLite3
Ken wrote: > I'll take a stab at this. > > Each connection would have its own private memory database. > > Create an additional temp table id_list , Insert the user id set values into > this table. > > Then > INSERT INTO temptab tt SELECT mt.* FROM maintab mt, id_list > WHERE mt.id = idl.id ; > > Then delete the id_list table or drop it. > It does help, Ken - it's what I was hoping to do and your email clears it for me! Thanks! I'll try this tomorrow and come back if there are more questions. Thanks guys for the help. Cheers, Mohit. 9/11/2008 | 3:59 AM. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Considerations with in-memory SQLite3
I'll take a stab at this. Each connection would have its own private memory database. Create an additional temp table id_list , Insert the user id set values into this table. Then INSERT INTO temptab tt SELECT mt.* FROM maintab mt, id_list WHERE mt.id = idl.id ; Then delete the id_list table or drop it. HTH, Ken --- On Wed, 9/10/08, Mohit Sindhwani <[EMAIL PROTECTED]> wrote: From: Mohit Sindhwani <[EMAIL PROTECTED]> Subject: Re: [sqlite] Considerations with in-memory SQLite3 To: "General Discussion of SQLite Database" Date: Wednesday, September 10, 2008, 2:26 PM Hi Richard Thanks for the quick response! D. Richard Hipp wrote: > On Sep 10, 2008, at 2:09 PM, Mohit Sindhwani wrote: > >> >> I've been using SQLite3 for a while though to be honest, it's been >> more >> of a data store rather than a dynamic database in my applications this >> far. I'm now starting on something where my needs are as such: >> * Store 70,000 records in one table in the database >> * Based on a list from the user, extract up to 3,000 records from the >> table (1 common field) >> * Use the 3,000 records for calculations, etc. >> * Support concurrency of up to 10 such operations >> > > > Your description implies that database is read-only. Is this > correct? If so, then concurrency will not be a problem since SQLite > supports as many simultaneous readers as you like. Only writers need > to be serialized. > > If you create a TEMP table to hold the 3000 selected records then do: > > INSERT INTO temptab SELECT * FROM maintab WHERE ...; > > You can then do your computations on the temporary table without even > interfering with writers on the main table. > The main table is read-only - there will be no writes to the main table. The TEMP tables (one for each concurrent access) would involve writing since I will somehow need to insert the 3000 records from the user into the TEMP table and also the joined results. If I use temp tables, wouldn't the creation of one block the rest from writing at the same time? Finally, on a related but different note, what is the limit for a SQL statement? I'm receiving 3000 IDs from the user and what I really need to do is: INSERT INTO temptab SELECT * FROM maintab WHERE id IN (1, 3, 89, 22, 324, ..); With 3000 records (actually each ID is 6 digits), this would lead to a SQL query that runs into 24KBytes or so! I'm sure I'm missing something. (By the way, this would all be in a Windows application using SQLite embedded into the application). Thanks again, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Considerations with in-memory SQLite3
Hi Richard Thanks for the quick response! D. Richard Hipp wrote: > On Sep 10, 2008, at 2:09 PM, Mohit Sindhwani wrote: > >> >> I've been using SQLite3 for a while though to be honest, it's been >> more >> of a data store rather than a dynamic database in my applications this >> far. I'm now starting on something where my needs are as such: >> * Store 70,000 records in one table in the database >> * Based on a list from the user, extract up to 3,000 records from the >> table (1 common field) >> * Use the 3,000 records for calculations, etc. >> * Support concurrency of up to 10 such operations >> > > > Your description implies that database is read-only. Is this > correct? If so, then concurrency will not be a problem since SQLite > supports as many simultaneous readers as you like. Only writers need > to be serialized. > > If you create a TEMP table to hold the 3000 selected records then do: > > INSERT INTO temptab SELECT * FROM maintab WHERE ...; > > You can then do your computations on the temporary table without even > interfering with writers on the main table. > The main table is read-only - there will be no writes to the main table. The TEMP tables (one for each concurrent access) would involve writing since I will somehow need to insert the 3000 records from the user into the TEMP table and also the joined results. If I use temp tables, wouldn't the creation of one block the rest from writing at the same time? Finally, on a related but different note, what is the limit for a SQL statement? I'm receiving 3000 IDs from the user and what I really need to do is: INSERT INTO temptab SELECT * FROM maintab WHERE id IN (1, 3, 89, 22, 324, ..); With 3000 records (actually each ID is 6 digits), this would lead to a SQL query that runs into 24KBytes or so! I'm sure I'm missing something. (By the way, this would all be in a Windows application using SQLite embedded into the application). Thanks again, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Considerations with in-memory SQLite3
On Sep 10, 2008, at 2:09 PM, Mohit Sindhwani wrote: > Hi Everyone, > > I'm new to the list and would like to start by saying hello! So, > hello! > > I've been using SQLite3 for a while though to be honest, it's been > more > of a data store rather than a dynamic database in my applications this > far. I'm now starting on something where my needs are as such: > * Store 70,000 records in one table in the database > * Based on a list from the user, extract up to 3,000 records from the > table (1 common field) > * Use the 3,000 records for calculations, etc. > * Support concurrency of up to 10 such operations Your description implies that database is read-only. Is this correct? If so, then concurrency will not be a problem since SQLite supports as many simultaneous readers as you like. Only writers need to be serialized. If you create a TEMP table to hold the 3000 selected records then do: INSERT INTO temptab SELECT * FROM maintab WHERE ...; You can then do your computations on the temporary table without even interfering with writers on the main table. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
