aknudsen

ExecuteSQL JOINs and rant

Discussion created by aknudsen on Feb 20, 2018
Latest reply on Feb 22, 2018 by aknudsen

I've posted before who I broke up a large looped Find() into 6 smaller ExecuteSQL queries and brought it down from several hours to just under one. The data I deal with is mainly text fields, and I have a lot of it. Primary table for this db is about 5 mill so that's on the smaller side.

 

Now, having been pleased with the performance I got breaking my queries into smaller ExecuteSQL's and putting them back together I thought ExecuteSQL would prom well this time as well.

 

Now, there are is no elationship between the AlternativeZoneNames and the main table. There is however a different table that holds both the AlternativeZoneName and the IP address which the JOJNS are based on. Both JOINS are to the same table, just different TO. This table is about 50k lines. All the tables are fully indexed on all the fields related to the JOINs and the queries.

 

The result is no more than a few hundred lines, and the equipment I'm running this on can handle a lot. Both my workstation and my server have 64GB RAM and SSD drives so there are no issues there. Client side and server side have about the same result, which is about 5 min or in other words, terrible. Doing the same as a Find() it's almost instantaneous.

 

Looking at the FMS console graph, it seems it's flatlining at around 5-10 kb/s on disk reads.

 

This is the query. Not very advanced.

 

Screen Shot 2018-02-20 at 22.08.18.png

And once processed it looks like this.

 

Screen Shot 2018-02-20 at 22.07.36.png

 

Can I do this with Find()? Sure I can, but  this is so much faster, and with the new UniqueValues() and SortValues() in FM16, I get this done in no time.

 

Screen Shot 2018-02-20 at 22.09.15.png

 

First let me just say that I really like FM and I get my job done way faster than other tools, but something has to give. Apparently these issues has been know for some time, but they are not addressed in the roadmap. Could this be an undocumented fix? Perhaps, but it is really significant that it should be mentioned.

 

Prices are going up and we get, portals for master-detail layouts, FINALLY. Other than that, theres not that much exciting. At least according to the roadmap. Sure there might be a custom function or two not listed.

 

So what would I like to see? A few things that I think most others would like to see:

 

  • New debugger that is threaded and one that you can actually quit.
  • Why is FM only using 25% CPU? If resources are available, use them.
  • Multi-threading, why is it not?

 

I don't want this to be a p**ing contest, but a healthy discussion so please keep it clean.

Outcomes