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

Reply via email to