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
