Unit QueryTest1 |
This form is a simple attempt to illustrate the wide disparity between the performance of QBE language queries vs. SQL queries, particularly when trying to do a "not-in" query. You need to have the QueryQBE component installed--it should have been included in the distribution file. Along with this VCL there are two paradox 7 tables used for the test. These tables should just be placed in the same directory as the compiled version of this project. The app references a session to create a temp alias that is deleted on FormClose, so you don't need to add a new alias or anything. Two queries, one QBE and one SQL. Two tables. Users.db has 1000 records, UserID | UserName. Trained.db has 200 records, UserID. The two queries are both attempting to answer the same question, what UserID's in Users.db are not present in Trained.db. Pretty simple. Now I'm not an SQL wizard or anything but the QBE syntax is either correct or real close to it: Query Trained.db | UserID | | _join1, count=0 | Users.db | UserID | UserName | | Check _join1! | Check | EndQuery Of course if you construct this query in Paradox or DBD, it won't give you corresponding SQL. I hacked and asked around for the correct syntax and came up with: SELECT U.UserID, U.UserName FROM "Users.db" U WHERE U.UserID NOT IN (SELECT T.UserID FROM "Trained.db" T) If there is a quicker or more correct SQL query, please let me know--I posted questions for several weeks in the clpd newsgroups and never got more than this in terms of an answer. Now, using these two queries, you get the right result sets. There are 800 records in Users.db that don't appear in Trained.db. The difference is that the SQL query takes about 15 times longer on my machine. On my Pentium 90 with 32MB, the QBE query takes anywhere from 0.44 to 0.86 seconds (the time declines if the table has been queried previously). The SQL query takes about 16.00 seconds to do the same and no improvement was noted with repeated queries. This isn't an indictment of anyone. For all I know there might be a SQL statement that will do it in 2/10ths of second. But I do notice a substantial difference in using SQL vs. QBE with Delphi 2.0 for "not-in" queries. With other types of queries the difference is nominal, although the QBE version appears to be more sensitive to previous executions (i.e., the SQL version performs fairly close to it's original speed with repeated executions while the QBE seems to execute more slowly the first time and then improve rapidly with repeated execution). Grant Carpenter grant@tiac.net 4/24/96
Classes |
TForm1 -
Functions |
Types |
Constants |
Variables |
Functions |
Types |
Constants |
Variables |