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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
