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