Jump to content

Is ExecuteSQL the best option for this design need?

This topic is 2603 days old. Please don't post here. Open a new topic instead.

Recommended Posts



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.







Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2603 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Who Viewed the Topic

    1 member has viewed this topic:
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.