query performance issue of Sql server 2005 Mobile edition on device

Executing a select query with left outer joins etc takes .53 seconds to execute on sqlce 2.0.

Same query on sql 2005 mobile ed. takes 11 minutes .

on database having same data.

Sample query

SELECT routes.location,routes.equipment_type, routes.contract_type,

routes.maintenance_interval,routes.bank_description,routes.Unit_Des,

routes.Unit_no,max(task_last_completed.date_completed)as date1,min(case when

task_last_completed.due_date is NULL then getdate()-1 else due_date end) as

due_date FROM routes left outer join tasks on tasks.model = routes.model and

tasks.eqtyp = routes.equipment_type inner join task_by_contract_type on

tasks.task_id = task_by_contract_type.task_id and

task_by_contract_type.contract_type = routes.contract_type and

task_by_contract_type.model = routes.model left outer join

task_last_completed on routes.unit_no = task_last_completed.equipment_Id and

tasks.task_Id = task_last_completed.task_Id WHERE routes.location LIKE

'S153825-01%' group by

routes.location,routes.equipment_type,routes.contract_type,routes.maintenanc

e_interval,routes.bank_description,routes.unit_des,routes.unit_no ORDER BY

routes.location, routes.bank_description, routes.Unit_Des

WHY?

Hi Vijay,

Can you answer the following questions to help us understand your scenario better:

What is the configuration of the device (make/OS version/processor type/processor speed/total memory/available memory for use at the time query was run) on which the query was run against SQL Mobile database?|||

Hi Vijay,

Can you answer the following questions to help us understand your scenario better:

What is the configuration of the device (make/OS version/processor type/processor speed/total memory/available memory for use at the time query was run) on which the query was run against SQL Mobile database?|||

Answer as follows:

1- verizon/Windows Mobile 5.0/os5.1.1700(build 14354.0.1.1)/PXA270-416MHz/49.45MB/~23MB

2 - Yes

3 - Yes as existing schema of Sqlce 2.0 is upgraded to sql mobile 5.0 using upgrade.exe

4 - 9.66 MB sql ce 2.0 and 8.09 MB sql mobile

5 - Running the query through query analyser.

6. can we set buffer pool etc. for query analyser on device.

One thing i have identified later in our datbase it hase number of duplicate records but even then sql ce performance is comparative very high.

Even after deleting duplicate records difference and read that sql mobile deals with the outer joins differently as compared to sqlce 2.0 but even then performance is the issue,

Sql ce 2.0 takes 0.53 Minutes

Sql Mobile takes 1.40 Minutes

Thanks

vijay