Thanks for the suggestion of a memory-database, Jean-Christophe. It is 
not something I've used so far with SQLite but I have some preliminary 
questions in the abstract.

The typical scenario with a webservice goes like this (database 
connections are ephemeral, not persistent):

1. User visits URL, passing parameters to the webservice in query-string 
and/or in the form-fields.
2. Webservice:
     1) receives the request
     2) instantiates a database connection
     3) creates a command with SQL statement (in my case, using IN-list)
     4) executes the command
     5) grabs the results
     6) closes the database connection
     7) sends the results to the browser-agent

The question in my mind is whether the following is any more 
performance-efficient than the approach above (note 2a-2e and 5a-5b):
2. Webservice:
     1) receives the request
     2) instantiates a database connection
     2a) creates an in-memory database
     2b) attaches in-memory database
     2c) issues query to disk-database to fetch random hex value to 
ensure temp table is named uniquely
     2d) creates temporary table in the in-memory database
     2e) populates temporary table with values that would otherwise be 
placed in the IN-list
     3) creates a command with SQL statement (now joining disk-tables to 
in-memory table)
     4) executes the command
     5) grabs the results
     5a) drops the temporary table in the IN-memory database
     5b) detaches the memory-database
     6) closes the database connection
     7) sends the results to the browser-agent

    At what point does step #3) in the top IN-list approach become more 
expensive than steps 2a-2e and 5a-5b in the bottom in-memory approach?

Regards
Tim Romano

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to