my application is using sqlite for a database. in the database, i have a many-to-many relationship. when i use the sqlite addon/tool for firefox, the sql query joining the tables in the many-to-many runs pretty fast. however, when i run the same query on the emulator, it takes a very long time (5 minutes or more). i haven't even tried it on a real device, thus.
can someone tell me what is going on?
for example, i have 3 table. 1. create table person (id integer, name text); 2. create table course (id integer, name text); 3. create table registration(personId integer, courseId integer);
my sql statements that i have tried are as follows.
select * from person, course, registration where registration.personId = person.id and registration.courseId = course.id
and also as follows.
select * from person inner join registration on person.id=registration.personId inner join course on course.id=registration.courseId
i am using the sqlite client from http://wp7sqlite.codeplex.com. i have 4,800 records in the registration table, 4,000 records in the person table, and 1,000 records in the course table.
is it my queries? is it just the sqlite client? is it the record size? if this problem cannot be fixed on the app, i'm afraid i'll have to push the database remotely (that means my app will have to use the internet).
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `course` ADD INDEX `course_idx_id` (`id`); ALTER TABLE `person` ADD INDEX `person_idx_id` (`id`); ALTER TABLE `registration` ADD INDEX `registration_idx_personid_courseid` (`personId`,`courseId`);
SELECT * FROM person, course, registration WHERE registration.personId = person.id AND registration.courseId = course.id