jeudi 21 avril 2016

SQLITE : Optimize ORDER BY Query

All,

I am iOS developer. Currently we have stored 2.5 lacks data in database. And we have implemented search functionality on that. Below is the query that we are using.

select CustomerMaster.CustomerName ,CustomerMaster.CustomerNumber,
    CallActivityList.CallActivityID,CallActivityList.CustomerID,CallActivityList.UserID,
    CallActivityList.ActivityType,CallActivityList.Objective,CallActivityList.Result,
    CallActivityList.Comments,CallActivityList.CreatedDate,CallActivityList.UpdateDate,
    CallActivityList.CallDate,CallActivityList.OrderID,CallActivityList.SalesPerson,
    CallActivityList.GratisProduct,CallActivityList.CallActivityDeviceID,
    CallActivityList.IsExported,CallActivityList.isDeleted,CallActivityList.TerritoryID,
    CallActivityList.TerritoryName,CallActivityList.Hours,UserMaster.UserName,
    (FirstName ||' '||LastName) as UserNameFull,UserMaster.TerritoryID as UserTerritory 
from
    CallActivityList
    inner join CustomerMaster
        ON CustomerMaster.DeviceCustomerID = CallActivityList.CustomerID
    inner Join UserMaster
        On UserMaster.UserID = CallActivityList.UserID 
where
    (CustomerMaster.CustomerName like '%T%'  or
    CustomerMaster.CustomerNumber like '%T%'  or
    CallActivityList.ActivityType like '%T%' or
    CallActivityList.TerritoryName like '%T%' or
    CallActivityList.SalesPerson like '%T%'  ) 
    and  CallActivityList.IsExported!='2' and CallActivityList.isDeleted != '1'
order by
    CustomerMaster.CustomerName
limit 50 offset 0

Without using 'order by' The query is returning result in 0.5 second. But when i am attaching 'order by', Time is increasing to 2 seconds.

I have tried indexing but it is not making any noticeable change. Any one please help. If we are not going through Query then how can we do it fast.

Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire