[sqlite] sqliteman vs sqlite3 tcl package

[yuiop]

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