Databases
StackOverflow2013
Postsdbo
POTrain Timetable Lookup - SQLite too slow, CoreData instead?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrain Timetable Lookup - SQLite too slow, CoreData instead?
    text
    copied!<p><strong>EDIT: I found the problem, which was totally unrelated to the below. It was due to me doing a bitwise comparison that I failed to mention below - I thought it wasn't relevant - I removed this (and found an alternative solution) and my query now executes in &lt; 1 second on device.</strong></p> <p>I'm currently working on my first iPhone application which is coming along nicely. It is an application to lookup train timetables for a certain train network. I have an SQLite database containing about 11,000 rows which hold the data I use to plan a journey.</p> <p>The structure of the table is as follows:</p> <pre><code>from depart to arrive trainid -------------------------------------------------- STNA 06:20 STNB 06:24 TRAINA STNB 06:25 STNC 06:29 TRAINA STNC 06:30 STND 06:35 TRAINA STNA 07:23 STNC 07:30 TRAINB STNC 07:32 STNE 07:40 TRAINB STNE 07:41 STNF 07:50 TRAINB </code></pre> <p>The <code>FROM</code> and <code>TO</code> fields are 3 letter IDS of stations on the route, the <code>DEPART</code> and <code>ARRIVE</code> fields are timestamps and the <code>TRAINID</code> field is an identifier common to all stations on a particular train's journey (ie, all stops on the 06:20 train from STNA to STND share the same trainid).</p> <p>When I want to for example find out which trains will take me from STNA to STND, I do the following query:</p> <pre><code>SELECT t1.from, t1.depart, t2.to, t2.arrive, t1.trainid FROM trains t1, trains t2 WHERE t1.trainid = t2.trainid AND t1.depart &lt; t2.arrive AND t1.from = 'STNA' AND t2.to = 'SNTD'; </code></pre> <p>This does a self join on the trains table (the t1.depart &lt; t2.arrive part is so that it finds trains from STNA->STND instead of STND->STNA).</p> <p>This works well, however it is very slow on the iphone (ok so you might say not so well), it takes about 10 seconds to run (returns about 80 results). I realise this is most probably becuase of the fact an 11,000 row table is being joined onto itself - but I'm at a loss at how I can optimise this. I'm using FMDB for this by the way.</p> <p>I'm thinking I need to go down the CoreData route, but as a newcomer to the iPhone platform I was hoping to avoid that for this project. Do you think CoreData would offer significant performance benefits in this scenario? As someone from a purely SQL background I would appreciate any pointers on how to best to proceed with CoreData - can I do self-joins with CoreData, or should I be modelling my data differently?</p> <p>EDIT: This is the output of an explain on the query im doing:</p> <pre><code>add opcode p1 p2 p3 p4 p5 comme --- ------------ ----- ----- ----- ---------------------------------------- ----- ----- 0 Trace 0 0 0 00 1 String8 0 1 0 GRY 00 2 String8 0 2 0 FST 00 3 Goto 0 47 0 00 4 OpenRead 0 2 0 7 00 5 OpenRead 2 377 0 keyinfo(1,BINARY) 00 6 OpenRead 1 2 0 7 00 7 OpenRead 3 1107 0 keyinfo(1,BINARY) 00 8 IsNull 1 42 0 00 9 Affinity 1 1 0 ab 00 10 SeekGe 2 42 1 1 00 11 IdxGE 2 42 1 1 01 12 IdxRowid 2 3 0 00 13 Seek 0 3 0 00 14 Column 0 6 4 0 00 15 IsNull 4 41 0 00 16 Affinity 4 1 0 db 00 17 SeekGe 3 41 4 1 00 18 IdxGE 3 41 4 1 01 19 IdxRowid 3 3 0 00 20 Seek 1 3 0 00 21 Column 0 5 3 00 22 Column 1 5 5 00 23 Ne 5 40 3 collseq(BINARY) 6a 24 Column 0 3 5 0 00 25 RealAffinity 5 0 0 00 26 Column 1 3 3 0 00 27 RealAffinity 3 0 0 00 28 Ge 3 40 5 collseq(BINARY) 6b 29 Column 1 2 3 00 30 Ne 2 40 3 collseq(BINARY) 69 31 Column 2 0 6 00 32 Column 0 3 7 0 00 33 RealAffinity 7 0 0 00 34 Column 1 2 8 00 35 Column 1 4 9 0 00 36 RealAffinity 9 0 0 00 37 Column 0 5 10 00 38 Column 0 6 11 0 00 39 ResultRow 6 6 0 00 40 Next 3 18 0 00 41 Next 2 11 0 00 42 Close 0 0 0 00 43 Close 2 0 0 00 44 Close 1 0 0 00 45 Close 3 0 0 00 46 Halt 0 0 0 00 47 Transaction 0 0 0 00 48 VerifyCookie 0 8 0 00 49 TableLock 0 2 0 stops 00 50 Goto 0 4 0 00 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload