[Solved] many-to-many query runs slow in windows phone 7 emulator

EverSQL Database Performance Knowledge Base

many-to-many query runs slow in windows phone 7 emulator

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).

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
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`);
The optimized query:
SELECT
        * 
    FROM
        person,
        course,
        registration 
    WHERE
        registration.personId = person.id 
        AND registration.courseId = course.id

Related Articles



* original question posted on StackOverflow here.