On Mon, Sep 28, 2009 at 12:31 PM, Andrew Gatt <[email protected]> wrote: > P Kishor wrote: >> On Mon, Sep 28, 2009 at 12:06 PM, Andrew Gatt <[email protected]> wrote: >> >>> I'm trying to optimise a select statement, but from multiple attached >>> databases. The general statement would be; >>> >>> SELECT * FROM database0.table WHERE id = 1; >>> >>> Where i have a long list of ids to retrieve, which could be from any of >>> the attached databases. With a single database i would prepare the >>> statement and bind to the id value, but from what i understand i can't >>> do this for the database name? Something like; >>> >>> SELECT * FROM :database.table WHERE id = :id; >>> >> >> You can't bind the value of a database and table. >> >> >>> If that can't work is it worth creating some kind of view out of all the >>> attached databases and running a prepared statement on the view? Or >>> should i just leave it as multiple select statements! >>> >> >> How would that help? You would have to create multiple views, one for >> each database, and you would be back to the same problem as before. >> >> > I was under the impression i could create a temp view from all the > attached databases? > > CREATE TEMP VIEW my_view AS SELECT * FROM database0.table UNION ALL > SELECT * FROM database1.table; >
Yes, but I could have misunderstood your problem. I thought you wanted to do the following selects SELECT * FROM database0.table WHERE id = ?; SELECT * FROM database1.table WHERE id = ?; SELECT * FROM database2.table WHERE id = ?; .. SELECT * FROM databasen.table WHERE id = ?; and, in the above, you wanted to replace the value of database? with the name of the database at run time. That is what can't be done with bind values. If you UNION ALL the databases then your query changes from your original query, or what it seemed like to me. Sure, you can create a view with a UNION ALL of all the databases, but then, which id are you going to be binding? On the other hand, if all the databases are really identical in structure, and could just as simply be concatenated end to end into one long database, then that is how it should be in the first place. In other words, if you have database1 ---- id, col1, col2 database2 ---- id, col1, col2 databasen ---- id, col1, col2 Then, you really should have database ---- all the rows from database1 all the rows from database2 .. all the rows from databasen Then you can have a single select statement with a bind value. If you want to keep track of which rows came from which database, you could create an additional column to track that info. > Although this is untested and i'm willing to accept if its wrong. Nah! never trust the advice of a stranger you have never seen (except for Igor). Do your own due diligence. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, WI, United States _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
