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" <[email protected]>
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
