Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

delay in ExecuteSQL when scripted vs. using Data Viewer?

Question asked by greaterthandata on Jun 28, 2017
Latest reply on Jun 29, 2017 by greaterthandata

Like • Show 0 Likes0
Comment • 15

Wondering if anyone else has seen this ... using Data Viewer to run an executeSQL query, the results come back in less than a second. But if I copy/paste the same code into a script and run that script ... it takes about 3 minutes to complete.

EXACT SAME executeSQL statement, querying the exact same table with the exact same credentials, exact same layout, exact same found set. doing the two above back to back. very strange ...

am I missing something??

The table we are querying is large ... roughly 1 million records, but as I mentioned ... the query is the same in both situations.

wimdecorte

Jun 28, 2017 2:43 PM

Correct Answer

That kind of difference can happen if your session has an open (uncomitted) record in the table you target with SQL. In that case, FMS will send you all the data first for the whole table so that the client can do the SQL query instead of the server doing it.

See the reply in context

No one else had this question

Outcomes

15 Replies

siplus Jun 28, 2017 1:31 PM
Mark Correct
Correct Answer
Did you try to run the script a second time and see how much it takes ? (My guess: much less).

And I also guess that dataViewer and Scripts do not share a common cache.
Like • Show 0 Likes0
Actions
greaterthandata @ siplus on Jun 28, 2017 1:31 PM
Mark Correct
Correct Answer
yes, unfortunately no different. It actually is a loop going through a found set of records and running the query each time. Basically the goal is for a set field
Like • Show 0 Likes0
Actions
siplus @ greaterthandata on Jun 28, 2017 1:36 PM
Mark Correct
Correct Answer
Instead of looping through records you can gather their PK's via List () and do just one ExecuteSQL with a IN clause.
Like • Show 0 Likes0
Actions
greaterthandata @ siplus on Jun 28, 2017 1:40 PM
Mark Correct
Correct Answer
I appreciate your suggestions, thank you. But in this case, it is a bit more complicated. We are ultimately looping through hundreds of thousands of records each with different query criteria ... so we loop through each one by one.

BUT ... bottom line ... if I manually go from one record to the next with data viewer open, the results are instant. If instead I run a script with a simple "set variable" and nothing else ... the same query takes about 3 minutes. OR ... if I set variable, go to next record, set variable, go to next record .. each iteration takes 3 minutes.
Like • Show 0 Likes0
Actions
bigtom Jun 28, 2017 1:58 PM
Mark Correct
Correct Answer
I have seen something like this before, but most eSQL in scripts is also extremely fast. Not sure how your script is setup but remember the data viewer already has reference to the record you are on and your script might not have that. Also the data viewer cache.

Do you have a sample of the query you are using? Maybe there is a reason.
Like • Show 0 Likes0
Actions
greaterthandata @ bigtom on Jun 28, 2017 2:11 PM
Mark Correct
Correct Answer
you bet ... see below. It's a pretty simple query. FYI - I just created an old school tog in the relationship graph to get the data and that is also more or less instant.

ExecuteSQL (
"select fkPlatform, fkSchedule, fkLibrary from RENTRAK where Date_airdate = ? and TitleNumber = ? and AssetID = ? "
; "" ; "" ;
RENTRAKIMPORTTEMPTABLE::Date_airdate ;
RENTRAKIMPORTTEMPTABLE::TitleNumber ;
RENTRAKIMPORTTEMPTABLE::AssetID )
Like • Show 0 Likes0
Actions
bigtom @ greaterthandata on Jun 28, 2017 2:19 PM
Mark Correct
Correct Answer
Ok. About how many records in RENTRAK?
Like • Show 0 Likes0
Actions
greaterthandata @ bigtom on Jun 28, 2017 2:23 PM
Mark Correct
Correct Answer
a lot ... almost 10 million.

I tried breaking it into two select statements, but in Data Viewer it took about 20 seconds to complete, so I didn't even try it in the script.
Like • Show 0 Likes0
Actions
bigtom @ greaterthandata on Jun 28, 2017 2:33 PM
Mark Correct
Correct Answer
What table is the layout based on where you run the script?
Like • Show 0 Likes0
Actions
greaterthandata @ bigtom on Jun 28, 2017 2:36 PM
Mark Correct
Correct Answer
RENTRAKIMPORTTEMPTABLE
Like • Show 0 Likes0
Actions
beverly @ greaterthandata on Jun 28, 2017 2:39 PM
Mark Correct
Correct Answer
I was going to ask if this was a redraw issue, but going record to record with DV open would suggest that's unlikely.

Have you tried with a layout where there are no fields visible? (Just for ruling out the redraw.) Or PSoS?

Sent from miPhone
1 person found this helpful
Like • Show 0 Likes0
Actions
bigtom @ greaterthandata on Jun 28, 2017 2:40 PM
Mark Correct
Correct Answer
Ok. Last question I think. Is that a true separate table or a TO of RENTRAK?
Like • Show 0 Likes0
Actions
wimdecorte Jun 28, 2017 2:43 PM
Unmark Correct
Correct Answer
That kind of difference can happen if your session has an open (uncomitted) record in the table you target with SQL. In that case, FMS will send you all the data first for the whole table so that the client can do the SQL query instead of the server doing it.
3 people found this helpful
Like • Show 3 Likes3
Actions
beverly @ greaterthandata on Jun 28, 2017 2:44 PM
Mark Correct
Correct Answer
The query looks good even for many many records. Is there a Commit Record in the loop?

Sent from miPhone
Like • Show 0 Likes0
Actions
greaterthandata @ wimdecorte on Jun 29, 2017 7:07 AM
Mark Correct
Correct Answer
Wim provided the correct answer ... but THANK YOU for ALL that contributed.

Just an update to help others in the future having a similar issue ... previously I was creating new records in the target table by using an allow create relationship. So I created the new record (in the foreign table), then populated a few fields, then ran the executeSQL query listed above, then populated a few more fields. This was super slow.

Changing the steps, so that I first ran the executeSQL query, then created the new record and populated the new fields ... it was lightning fast.. What a revelation ... thank you Wim. Huge!

With all this said, I ran a quick test to see which was quicker in this situation, using an executeSQL step OR using an old school filemaker relationship to get the values I needed and FM won in the case, being slightly faster. not a huge difference in time, but over 100's of thousands of records it will make a difference. Obviously this clutters the TOG, but in this case every second in this loop will make a huge difference.

thank you again to all!
Like • Show 1 Like1
Actions

delay in ExecuteSQL when scripted vs. using Data Viewer?

Outcomes

Related Content

Recommended Content