On Wed, Jan 27, 2010 at 08:28:15AM -0500, Tim Romano scratched on the wall:
> 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
You create the database by attaching it, so these are one step.
> 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
You're making this much too complex.
Without attaching a new ":memory:" or "" database or doing anything
else different from what you're already doing, you can simply give
the command:
CREATE TEMP TABLE in_args (val);
And then insert your values into it. The "TEMP" will make SQLite
automatically create a temp database (that is, an internal equivalent
to "ATTACH <db> AS 'temp'"), but because it is known to be a temp
database, several performance-related configurations are made,
such as setting the database to exclusive locking mode. Temp
databases are also exclusive to the database connection, so there is
no need for unique names or nonsense like that. You couldn't share a
temp database if you wanted to. This kind of thing is an OLD problem
in databases, and it was solved a long time ago.
The temp database will either be an in-memory database, or it can be
a "file-backed" database. Which depends on the value of PRAGMA
temp_store and some compile-time settings. "File-backed" is not
exactly the same as "file-based." IIRC, a file-backed database will
only actually hit disk if it over-flows the cache (which defaults to
500 pages); most of the time even a "file" temp database won't
actually hit disk, so the performance is very good.
> 3) creates a command with SQL statement (now joining disk-tables to
> in-memory table)
No need to re-write the query with a JOIN. The IN expression supports
table names. You can simply say "...IN temp.in_args" (note: no
column name; it must be a one-column table).
> 4) executes the command
> 5) grabs the results
> 5a) drops the temporary table in the IN-memory database
> 5b) detaches the memory-database
If you close the database connection all TEMP stuff is automatically
cleaned up. Again, this is an old problem built very deeply into the
core of any modern RDBMS. They're designed to be used this way.
> 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?
Using an actual temp table, I wouldn't be too concerned about this.
Further, doing it this way avoids the need to build any SQL
statements with string manipulations-- always a very dangerous thing
that can lead to problems. Using a temp table, you should be able to
do everything with static SQL statements and binds.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
