Manzoor Ilahi Tamimy wrote:
Here Is The Schema For these Tables.
CREATE TABLE HVH (
Field1 VARCHAR(8), IDC VARCHAR(4),
Field3 VARCHAR(2), Field4 VARCHAR(4),
Field5 VARCHAR(7), Field6 VARCHAR(8),
Field7 VARCHAR(1), Field8 FLOAT);
CREATE TABLE ITM(
IDC VARCHAR(4), ITEMNAME VARCHAR(20),
COLUMN3 VARCHAR(1), COLUMN4 VARCHAR(1),
COLUMN5 VARCHAR(1), COLUMN6 VARCHAR(1),
COLUMN7 VARCHAR(1), COLUMN8 VARCHAR(1),
COLUMN9 VARCHAR(1), COLUMN10 VARCHAR(1),
COLUMN11 VARCHAR(1), COLUMN12 VARCHAR(1),
COLUMN13 VARCHAR(1), COLUMN14 VARCHAR(1),
COLUMN15 VARCHAR(1), COLUMN16 VARCHAR(1));
CREATE INDEX index1 ON ITM (IDC);
//--------------------------------------------------------------------
TEST 1 ( Disk DB )
Table Names itm , HVH
Number of Records : itm  100,000 and HVH  less than 10,000
QUERY:
Select count(*) from itm, HVH where itm .IDC = HVH.IDC
Elapse Time : 54.359 (Sec)
On Mon, 19 Jun 2006 12:38:32 -0400, Derrell.Lipman wrote
The count() function is always slow, I think, because no index can
be used; it must actually count the number of records. I suspect,
though, that your problem is missing indexes. Is there an implicit
or explicit index on itm.IDC? Is there an implicit or explicit
index on HVH.IDC? With query times like you're seeing, I'd assume not.
If you post your schema along with the queries you want to issue,
many people here will likely provide suggestions for improvement.
I think that Derrell has already answered your question. What you are
trying to do is inherently slow. You are doing a cross join of two large
tables and counting the results. Depending upon which indexes you have
defined (i.e. an index on IDC in either HVH or ITM tables), SQLite needs
to scan one table from start to finish, and for each row in that table
it uses an index to locate the matching rows in the other table.
You can look at the output of the "explain query plan" command to see
how your tables and indexes are being scanned.
I suspect that this is a contrived tests case, and that it is not what
you really need to get done. Can you explain what you are trying to do
in more detail? For example, it looks like IDC is, or should be, the
primary key for the ITM table, and that IDC is a foreign key in the HVH
table. Is that true? How many records in the HVH table match each record
in the ITM table; one, a few, or many? What information do you need to
get from these tables (i.e. Is a count of the results what you are
really after)?
If you can explain what you are trying to do in more detail, someone
here can probably help you to generate a more suitable query.
HTH
Dennis Cote
