>>Make one big 'working' database by reading all the data from the databases on >>the independent devices and putting it into one big table. Once you have >>sucked out the data you can disconnect from the device. Then do all your >>enquiries on this big table. There's a special convenient form of the INSERT >>command which gets its data directly from a SELECT command:
>>INSERT INTO bigTable SELECT * FROM attachedDB.myTable >>So, in a simplified example, for each pluginnable device do this: >>ATTACH "deviceFile.s3" AS attachedDB >>INSERT INTO allDeviceTable SELECT * FROM attachedDB.myTable >>DETACH attachedDB Then just query allDeviceTable. Thanks Simon for your suggestions. Unfortunately, I cannot detach the devices in my application. The data to be inserted in each database could be live i.e, on every device where a database is created, there could be new live data which needs to be inserted in the database as and when added to the device and made available for a single select. Your suggestions would have worked if it was not live data. Sorry I forgot to mention abt this in my previous mail. Unfortunately, I could not think about any other option than to keep the devices attached all the time.I am trying to find some optimum cache values for each connection. I tried not to use many connections. I just have a single connection open for a device and its database and one attach connection. As of now I am using 500K of default cache for each open connection with 100K for attached connections and 500k default temp-cache and also I have temp-store set to a file. It might hamper my performance, yet I dont have any other option thou. Regards, -Akbar On 26 Mar 2010, at 10:47am, Akbar Syed wrote: >* Unfortunately, my application restricts me to use independent *>* databases than to a single database *>* as each database exists on a different device and contains the info of *>* that device in the database. *>* Multiple devices are allowed to connect to my server app which instead *>* gathers the records from the *>* independent databases by attaching the databases together. * Make one big 'working' database by reading all the data from the databases on the independent devices and putting it into one big table. Once you have sucked out the data you can disconnect from the device. Then do all your enquiries on this big table. There's a special convenient form of the INSERT command which gets its data directly from a SELECT command: INSERT INTO bigTable SELECT * FROM attachedDB.myTable So, in a simplified example, for each pluginnable device do this: ATTACH "deviceFile.s3" AS attachedDB INSERT INTO allDeviceTable SELECT * FROM attachedDB.myTable DETACH attachedDB Then just query allDeviceTable. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
