using SQLite ATTACH

Question

we have two sqlite DB's , we have a requirement to "attach" one to other and perform some joins. we have some questions/concerns as below:

1. say we have attached DB1 with DB2 and performing some SELECT's , can some other thread concurrently UPDATE/INSERT on DB2 or DB1 with a different connection ?
2. is there a separate C API to attach or we need to use "sqlite3_step"
3. how is the performance with ATTACH.

Thanks in Advance

DEE

Answer 1

1. Another thread can concurrently alter either database, but this will mean that at some point the database can be locked for the querying thread. See here about concurrency with SQLite.

2. ATTACH is a one step operation, you can us sqlite3_exec.

3. Performance is a tough thing to predict and will vary greatly with schema, indexing, usage, and data stored (and some other factors too like page size). In some cases, ATTACH can be slower than if all data is in one database. My personnal experience was that separating large datasets was faster for inserts and affected final query output minimally/imperceptibly. Your mileage may vary.