Optimizing SQLite query to use one query

Question

Problem Description

I'm writing application which is working with database, it select some data from the database and shows it to the user in the list view. There are some criterias which I must keep while selecting items from the database.

In the database I keep some location (latitude and longitude), when selecting items I must get my current position and order items from the closer to me to the farther.
In the database I have TOP companies which must be always first in the list independent on their location (latitude and longitude)

Question

So I need some query which will select all this information in the right ordering at once, because currently I am using 3 queries to do that.

Current solution

Here are the queries which I use to select all information which I need, currently I select all items add them to array and only after that I sort them in the array according to the distance.

SELECT TOP COMPANIES

 select CompanyInfo._id, CompanyInfo.name_en, ContactInfo.telephones, ContactInfo.location, CompanyInfo.websites, ContactInfo.address_en, ContactInfo.location, ContactInfo.position, TopFirmsByCode.Level, ContactInfo.name_en from CompanyInfo, ContactInfo, TopFirmsByCode where CompanyInfo._id=TopFirmsByCode.RegNo AND CompanyInfo._id=ContactInfo._id AND TopFirmsByCode.Code = 452 order by TopFirmsByCode.Level ASC, CompanyInfo.rating_ordering_en ASC`

SELECT THE REST OF COMPANIES ORDERED BY DISTANCE

 select CompanyInfo._id, CompanyInfo.name_en, ContactInfo.telephones,ContactInfo.location,CompanyInfo.websites,ContactInfo.address_en,ContactInfo.location, ContactInfo.position, ContactInfo.name_en from CompanyInfo, ContactInfo where CompanyInfo._id=ContactInfo._id AND CompanyInfo.category_codes LIKE %547% order by ContactInfo.location ASC

What does a profiler show? Is this really where your bottleneck lies? — hd1
– hd1, Commented Sep 9, 2014 at 7:18
@hd1 First time it takes near 7 seconds to show items and then it takes near 2 seconds ..., also some time when the results number is more then 1000 my application crashes :( can you suggest something to fix that ? — Victor Apoyan
– Victor Apoyan, Commented Sep 9, 2014 at 7:26
@hd1 The queries aren't as much of a bottleneck as iterating the result sets (and maybe sorting them) to compose a final result set. Constructing a single query that returns the results you want in the order you want eliminates this entirely. — Karakuri
– Karakuri, Commented Sep 9, 2014 at 7:28
Yes, it does, but there are easier ways to achieve this. I'm trying to get OP to think of a more straightforward solution than making his SQL more complex. — hd1
– hd1, Commented Sep 9, 2014 at 7:29
Run your code through the Android profiler, see where your bottleneck lies and optimise the highest weighted code first. — hd1
– hd1, Commented Sep 9, 2014 at 7:30

Karakuri · Accepted Answer · 2014-09-09 17:50:25Z

1

Give this a try:

select CompanyInfo._id, CompanyInfo.name_en, ContactInfo.telephones, 
    ContactInfo.location, CompanyInfo.websites, ContactInfo.address_en,
    ContactInfo.position, ContactInfo.name_en, TopFirmsByCode.Level,
    (case when TopFirmsByCode.Code = 452 then 1 else 0 end) as isTopFirm
from CompanyInfo
join ContactInfo on (CompanyInfo._id=ContactInfo._id)
join TopFirmsByCode on (CompanyInfo._id=TopFirmsByCode.RegNo)  
where (TopFirmsByCode.Code=452 OR CompanyInfo.category_codes LIKE %547%)
order by isTopFirm DESC, TopFirmsByCode.Level ASC, CompanyInfo.rating_ordering_en ASC

edited Sep 9, 2014 at 17:50

answered Sep 9, 2014 at 7:26

Karakuri

38.6k12 gold badges91 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Victor Apoyan Over a year ago

something is wrong with syntax here (case when TopFirmsByCode.Code = 452 then 1 else 0) as isTopFirm as I am not native with SQL queries can you help me to correct your answer?

Victor Apoyan Over a year ago

Error: near ")": syntax error <select CompanyInfo._id,

Victor Apoyan Over a year ago

Okay I find it you forgot to put END before AS, thanks

Karakuri Over a year ago

sorry, i will fix the post

Collectives™ on Stack Overflow

Optimizing SQLite query to use one query

Problem Description

Question

Current solution

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Problem Description

Question

Current solution

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related