On Tue, Sep 18, 2012 at 8:20 AM, Black, Michael (IS) <michael.bla...@ngc.com > wrote:
> 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. > I don't know how Sqliteman works. But if it configures the LIKE operator to be case preserving, then "testName LIKE $whatever" will use an index. I do know that tclsqlite3.c (which TCL uses) leaves the case sensitivity of LIKE at its default setting (LIKE is not case sensitive) in which case "testName LIKE $whatever" requires a full table scan. That might explain the difference. I suggest either (A) changing "LIKE" to "==" or (B) running "PRAGMA case_sensitive_like=1" will make LIKE work like == in your schema. > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
