Write native SQL in Core Data

Question

I need to write a native SQL Query while I'm using Core Data in my project. I really need to do that, since I'm using NSPredicate right now and it's not efficient enough (in just one single case). I just need to write a couple of subqueries and joins to fetch a big number of rows and sort them by a special field. In particular, I need to sort it by the sum of values of their child-entites. Right now I'm fetching everything using NSPredicate and then I'm sorting my result (array) manually, but this just takes too long since there are many thousands of results.

Please correct me if I'm wrong, but I'm pretty sure this can't be a huge challenge, since there's a way of using sqlite in iOS applications.

It would be awesome if someone could guide me into the right direction. Thanks in advance.

EDIT: Let me explain what I'm doing. Here's my Coredata model:

And here's how my result looks on the iPad:

I'm showing a table with one row per customer, where every customer has an amount of sales he made from January to June 2012 (Last) AND 2013 (Curr). Next to the Curr there's the variance between those two values. The same thing for gross margin and coverage ratio.

Every customer is saved in the Kunde table and every Kunde has a couple of PbsRows. PbsRow actually holds the sum of sales amounts per month.

So what I'm doing in order to show these results, is to fetch all the PbsRows between January and June 2013 and then do this:

self.kunden = [NSMutableOrderedSet orderedSetWithArray:[pbsRows valueForKeyPath:@"kunde"]];

Now I have all customers (Kunde) which have records between January and June 2013. Then I'm using a for loop to calculate the sum for each single customer.

The idea is to get the amounts of sales of the current year and compare them to the last year. The bad thing is that there are a lot of customers and the for-loop just takes very long :-(

Answer 1

This is a bit of a hack, but... The SQLite library is capable of opening more than one database file at a given time. It would be quite feasible to open the Core Data DB file (read/only usage) directly with SQLite and open a second file in conjunction with this (reporting/temporary tables). One could then execute direct SQL queries on the data in the Core Data DB and persist them into a second file (if persistence is needed).

I have done this sort of thing a few times. There are features available in the SQLite library (example: full-text search engine) that are not exposed through Core Data.