Really does depend on the query (sql) you are running.
To investigate start by looking at the explain plan.... http://www.sqlite.org/lang_explain.html http://www.razorsql.com/docs/sqlite_explain_plan.html Classic "slow query" problems are table scans - where the engine has to scan the entire table to get the data you want. This can be improved by adding indexes on the offending columns - at the cost of slower insert and update speeds. Other factors can include whether temp files are being written to ram or disc http://www.sqlite.org/tempfiles.html then after that you're into things like cache sizes. All the above is quite a simplistic overview - there are many more devils and tradeoffs lurking in the details when it comes to performance tuning. Cheers. Owen. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Unabashed Sent: Wednesday, October 21, 2009 2:47 PM To: sqlite-users@sqlite.org Subject: [sqlite] Slow SELECTs in application Hello! I'm using SQLite as DB in my application. My problem consists of two aspects. First , it works great, but on large data SELECTs are very slow (10-20s!). Queries are in one transaction. My table structure is: CREATE TABLE mgWords ( id INTEGER PRIMARY KEY AUTOINCREMENT, id_norm INTEGER, word TEXT, wform TEXT) It seems, that the problem is in sql. How to optimize table to have fast selects? Second part of my trouble is in using unique values. When i'm trying to change structure to CREATE TABLE mgWords ( id INTEGER PRIMARY KEY AUTOINCREMENT, id_norm INTEGER, word TEXT, wform TEXT, UNIQUE (id_norm,word,wform)) and use INSERT INTO mgWords (id_norm,word,wform) VALUES (0,'aword','awform') it clauses error. I'm not good in sql, so I'll be very glad to receive a professional answer. Sorry, please, for my English - this is not my native language. Thank you fo reading it :) -- View this message in context: http://www.nabble.com/Slow-SELECTs-in-application-tp25992880p25992880.ht ml Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
