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

my application using sqlite database. in database, have many-to-many relationship. when use sqlite addon/tool firefox, sql query joining tables in many-to-many runs pretty fast. however, when run same query on emulator, takes long time (5 minutes or more). haven't tried on real device, thus.

can tell me going on?

for example, 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 have tried follows.

select *  person, course, registration registration.personid = person.id , registration.courseid = course.id 

and follows.

select * person inner join registration on person.id=registration.personid inner join course on course.id=registration.courseid 

i using sqlite client http://wp7sqlite.codeplex.com. have 4,800 records in registration table, 4,000 records in person table, , 1,000 records in course table.

is queries? sqlite client? record size? if problem cannot fixed on app, i'm afraid i'll have push database remotely (that means app have use internet).

yep, queries. you're not going away can away doing trying on mobile device. have remember aren't running on pc have think differently how approach things (both code , ui). have low memory, slow disk access, slow-ish processor, no virtual memory, etc. you're going have make compromises.

i'm sure ever doing possible on phone without needing offsite server need smart it. example necessary load 4800+ records memory @ once? not, user can't possibly @ @ 4800 @ same time. forgetting database speed showing number of items in listbox going kill app performance wise.

and performance perfect displaying 4800 items user experience? surely allowing user enter search term better , allow filter list more manageable size. implement paging display first 10 records , have user click next next 10?

you might want consider de-normalizing database, have 1 table rather 3. improve performance considerably. yes goes against taught databases in school said: phone = compromises. , remember isn't big oltp mission critical database, phone app - no 1 cares if database in 3rd normal form or not. remember more work give phone (chugging through data building joins) more battery power app consume.

finally if absolutely think must give user list of 4800 records scroll through, should @ kind of data virtualization technique. gives user illusion scrolling through long list, though there few items loaded @ given time.

but short answer is: yes, doing queries problematic, need consider changing them.