Using ExecuteSQL: A Cautionary Tale

You know, I was, I was originally thinking in calculated fields.

Jillian was bringing up the, the hidden cal- the hidden objects issue.

But places where execute SQL runs way more often than, than you might have anticipated when you wrote the function.

Um, and I'm sorry, Jillian, you were talking about the impact in that one client and the hidden, uh, object hiding functionality.

Yeah.

I discovered after doing an AI audit that there are...

One of the reasons that the client's system is so slow is hide calcs on layouts, objects on layouts.

So that would be something that if we were going to do some work for them to help with performance, that might be something to kind of cull.

Sorry, is that a s- where it...

Is it on a listing layout where the calc has to, where the hide calculation has to run a whole bunch of times? Um, or-

I didn't explore it too deeply for this particular client, but it seemed to me that anywhere that you're running a SQL to determine whether or not a field should hide doesn't seem like a good idea.

Yeah.

I think, um, execute SQL has to, has to, especially on the client side, execute SQL has to pull all the data over to the client side in order to perform the query.

Um-

Only if there's uncommitted records.

Right.

Sorry.

Only if there's uncommitted records.

Which, so it depends on the database.

So quite often it might have to pull all the data [laughs] um, over to the client side to perform.

So if you've got a...

I guess even if it's a detail layout, if you've got an object being hidden by a, uh, execute SQL calculation and that execute SQL calculation is querying a table that has 100,000 records in it, um, you've obviously got a massive performance hit.

Um, and if you've got 20 users and they're all hitting that, that layout periodically, and it has to perform that calculation all the time, um, that's, that's a crazy, um, a crazy performance hit.

Um, I think I...

To the best of my knowledge, we've never used an execute SQL command in situations like that, or in calculated fields, which is the other thing we were gonna talk about.