On 6 sep. 2013, at 18:42, "Igor Tandetnik" <i...@tandetnik.org> wrote: > If I recall correctly, query planner's behavior is worst-case quadratic in > the number of tables participating in the query. This includes tables > mentioned directly, and also those pulled in indirectly via views, triggers > or foreign keys.
Ok, maybe that explains it. Do you remember some topics or explanations that cover this more in depth? At least it will be worth for us to do some debugging on the SQLite code to see if we can pin point this behaviour and see if we can work around. > If I may be so bold, I would say that a design that calls for a database with > 10,000 tables doesn't feel right The main reason for this is that the application is used to do research on and examine the quality of processes that take place within organizations. For this research the users can create highly customized research forms with varying number of columns, varying field types and a lot of specific restrictions, which we store in tables that are generated on the fly with the proper indexes (later on the research results are stored in these tables and used for real-time dashboard reporting and downloading reports that are generated with the latest data) Furthermore the data that is examined and imported into the application contains a lot of privately related data. Since we already create these separated tables, we use this as some sort of 'automated' security wall to rule out the risk of users from different organizations reaching or hacking into each others privacy sensitive data in case programming weaknesses or mistakes are made. For this project we went on board with SQLite (which we already used in several other projects) and have built our own 'SQL server' app that takes care of things like: - Asynchronous real-time backups to secondary location (sort of binary logging as found in other DB's, though our implementation is not binary) -Automatically splitting time intensive writes and updates to prevent these locking the app (since SQLite has DB level locking) for users that perform simple tasks that can be performed fast. -This 'stable' server app also rules out startup times of the database in the case of application crashes (since the DB is several gigabytes in size it takes SQLite a few seconds to 'initialize' and thereby hiding application crashes from the users (which access the app through a web interface). -Automatically backing up the DB in compressed and encrypted format. Best regards, Harmen _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
