2 things.... #1 Create indexes on testTable.deTestRecordId, dataXyTable.deTestRecordid, and testTable.testName.
#2 Do you really need the "LIKE" operator? That's going to scan the entire table every time. If you can change that to "=" you'll likely run a lot faster too. This shouild speed up both Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of yuiop [hafer...@gmail.com] Sent: Tuesday, September 18, 2012 12:24 AM To: sqlite_us...@googlegroups.com Subject: EXT :[sqlite] sqliteman vs sqlite3 tcl package I have posted a question in the comp.lang.tcl group here<https://groups.google.com/forum/?fromgroups=#!topic/comp.lang.tcl/SF9pxG7J1RY> and someone suggested that I post here as well. So here it goes... I am currently trying to use the tcl sqlite3 package to perform a database query. The database I am trying to extract data from is pretty large and consists of several tables all linked together by a common column id. I have a SELECT command that works but it takes a very long time (approx 3 seconds). I am comparing this time to the time it takes for the program Sqliteman to run the exact same search (approx 0.3 s). Both searches were performed on the same machine against the same database file. Is Sqliteman configuring the database somehow to perform more optimal database queries? Or is tcl truly that much slower? Other info: At the moment there are 500,000 rows in 2 different tables. Every hour the number of rows increases by ~3200. This will go on for about another week. The time it takes the db eval "SELECT ..." command to run has been getting longer and longer in Tcl. At 24 hours (~76000 rows) the command took about 0.5 seconds to run. Now its up around 3 seconds. Here is one of the queries that I perform. It simply gets all of the x values for a particular test name from a table named dataXyTable. puts [time { dbName eval { SELECT dataXyTable.x FROM testTable, dataXyTable WHERE testTable.deTestRecordId=dataXyTable.deTestRecordId AND testTable.testName LIKE '$testName'" } } The contents of the {} run in sqliteman.exe approx 10 times faster no matter how big the database is. In case its important, the two tables have the following format: CREATE TABLE testTable(\ fileId INTEGER NOT NULL,\ deTestRecordId INTEGER UNIQUE NOT NULL,\ testName VARCHAR(256) NOT NULL,\ dataType VARCHAR(16) NOT NULL,\ dataTable VARCHAR(20) NOT NULL) CREATE TABLE dataXyTable(\ fileId INTEGER NOT NULL,\ deTestRecordId INTEGER NOT NULL,\ x REAL NOT NULL,\ y REAL NOT NULL) Thanks in advance to anyone that can shed some light on this. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
