executeSQL really slow

[IT Heroes]

Hi,

I was hoping to build a complex query using the executeSql function. However I am finding even a really simple query is taking a long time to execute.

I have a table "Job Tracking" with approx 36000 records. Executing SELECT COUNT(*) FROM "Job Tracking" takes about 5 seconds. To load the entire table up in a FM table view is instant. If I increase the complexity of the query it takes even longer!. Any reason why it would be so slow? Or have I got something wrong?

Just running on a local filemaker database using filemaker pro 10 advanced

Thanks, Ryan

[andries]

Hi

it might be that your ERD is too heavy... I have had exactly the same problem.

You could try to do the same request with the KeyStone plugin (free open source plugin, however I don't advice to use it in a production environment yet) to see if you have the same results. If the result is slow as well, the problem lies in your file I am afraid.

[IT Heroes]

Thanks. I tried the same query using ODBC and it was just as slow. So it must be the file. A shame because the idea of the SQL was to simplify the ERD quite a bit.

When I open the file, it also opens half a dozen linked files, so I guess that slows it down also?. I wonder if it is worth trying to consolidate everything into 1 file and separate out the UI from the data. I might do some tests, try copying the table to a standalone file and run the queries to see if they are faster.

Thanks!

[IT Heroes]

Ok... well this is interesting... I copied the table to it's own file without any relationships and ran the SQL. It was no faster. So I thought maybe some calculated fields where slowing it down, so I deleted all calculated field. No faster. So I thought maybe there are too many fields, so I culled it down to all but a single key field and it was still no faster!!!. So the limitation seems to be the number of records rather than anything else.

This is just for a simple COUNT(*) query.

But I was hoping to use SQL to quickly go through lots of data, but it seems it isn't going to work. Appending a WHERE clause speeds it up heaps, so for queries that don't need to scan a lot of records it is fine, but it seems that for bulk data crunching it is quite useless.

I am wondering if someone else can verify for me that my findings are correct?

Test Case:

New Database

Create a single table "TEST" with a single numeric field

Insert approx 36000 records

Run a script: ShowDialog[ExecuteSql("SELECT COUNT(*) FROM TEST")]

On my not so fast machine it takes about 10 seconds and maxes the CPU.

Edit: Operations in filemaker are almost instant. If I do a summary field with a SUM in filemaker it updates pretty much instantly. If I do a SELECT SUM(TEST) FROM TEST it takes about the same 10 seconds. So it isn't the computer or anything, it is only slow when going through SQL.

Thanks, Ryan

Edited September 7, 2010 by Guest

[IT Heroes]

Sorry for all the posting, but further investigation suggests that it is the SQL functions that significantly slow it down. It is about 10 times quicker to do "SELECT TEST FROM TEST" than "SELECT COUNT(TEST) FROM TEST". So it ended up about 10 times faster to do "SELECT TEST FROM TEST", store it into an array, then do a array.length to get a count. To get a distinct count (ie. SELECT DISTINCT TEST FROM TEST) it is much much quicker to do SELECT TEST FROM TEST, put it into a Set to eliminate duplicates and then do a Set.Size() to get a count. So it would seem I am better off importing an entire record set into the script and processing it manually... which seems a bit silly?? I'm sure I'll hit memory limitations eventually. Oh well, at least I can process data in Java/Groovy rather than writing an insane filemaker script.

Anyone have any comments on why FM SQL functions are so slow? Don't even try an aggregate function in a subquery :P

[andries]

well

FileMaker already improved a lot the ODBC/JDBC connections, but I have to agree that some improvements are still welcome.

But for me it is already positive they are embracing this technology more and more... As you said before: it really helps in keeping your ERD clean -)

Edited September 7, 2010 by Guest