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?
