This is driving me nuts. I have two tables I'm trying to join together on two
text fields.
CREATE TABLE tmp_role ( programId INTEGER,
roleName INTEGER,
position INTEGER,
isNew BOOL,
personId INTEGER,
name STRING);
This table has up to a few dozen records at any one time.
AND
CREATE TABLE person (
personId INTEGER PRIMARY KEY,
name text UNIQUE
);
This table has 10s of thousands of records.
If I do this query:
SELECT person.ROWID FROM ApgDb.tmp_role JOIN PgDb.person ON tmp_role.name =
person.name;
to find the ROWID of each row in person who's name matches that of the name
in tmp_role, it takes about 1 second per match....a really long time.
However, if I instead take each of names in tmp_role and do a seperate
select like this:
SELECT ROWID FROM person WHERE name = "Carell|Steve";
and do it for each name, the search takes only a few ms for few dozen
records in tmp_role.
Now the real problem I'm trying to solve is an UPDATE like this:
UPDATE tmp_role SET personId = (SELECT ROWID FROM person WHERE tmp_role.name
= person.name);
If I break this up into a bunch of different statements to iterate through
the records in tmp_role and then execute a single statement for each name, I
can accomplish this update statement fairly quickly, but as it is authored
above, it's taking about 24 seconds for 24 records or about 1000 times
longer than if I do it the long way :(
--
View this message in context:
http://old.nabble.com/Slow-JOIN-on-two-indexed-text-fields.-Why------tp26601433p26601433.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
