Is ExecuteSQL the best option for this design need?

[argwallace]

Hello.

 

I am trying to determine if using an ExecuteSQL function to return results in a portal will be faster than using a Filter Portal option. Here's the need:

 

I have a Report table that is related another table Called Expenses. The expense file has around 10,000+ records and will grow monthly. My design is to display only some of those expenses based on the criteria included in the different reports from the Report table. For example, I may want to drill down on a certain date range, expense values, category of expense, etc. What is the most efficient way to display these results? Since there are many possible variables, it doesn't seem a straight relationship will be easy to do. So I thought of portal filtering, but this seems to have  performance hit. So then I thought I could use Execute SQL to gather the ids and have the relationship based on that info.

 

Thoughts?

 

Thanks,

 

Stephen

[Wim Decorte]

That would work.  You may also be better off doing this in a list view and using regular finds instead of showing the expenses through a portal.

[Brian C]

SQL is great as LNG as you do not return large record sets or use it in an unstored calc in list view

[argwallace]

Ok, thanks for the feedback everyone. If I don't use SQL and yet have many options that make my relationship more challenging, any options for me. Using list view isn't an option in my scenario.

[Brian C]

This thread might be helpful:

 

http://www.filemakertoday.com/com/showthread.php/29540-which-is-faster-filtering-the-portal-or-defining-the-relationship

[jlamprecht]

A filtered portal with 10k records is going to hurt over the WAN. In addition, Portal Filtering is implicit coding which could cause trouble for future development when either you or someone else comes into the file and tries to figure out what is going on in the portal.

 

Using ExecuteSQL would serve you well here. Gather the IDs using SQL and set a global field to trigger the relationship. Just make sure to stay clear of unstored calculations in your SQL statement as these will cause performance degradation.

 

I hope this helps

[David Jondreau]

Every technique has advantages and disadvantages.

 

If you're using certain comparison operators for ExecuteSQL() you're going to see major performance issues. If you're using >,<, %, or LIKE, forget it.

 

Relationships are usually the fastest in those situations, as long as you're not targeting unstored calculation fields. If you are, then List View and finds are likely the fastest.

 

With that many records, performance will be an issue, especially on the first pass.