-
1. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
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.
-
2. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
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
-
3. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
Instead of looping through records you can gather their PK's via List () and do just one ExecuteSQL with a IN clause.
-
4. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
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.
-
5. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
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.
-
6. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
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 )
-
7. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
Ok. About how many records in RENTRAK?
-
8. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
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.
-
9. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
What table is the layout based on where you run the script?
-
-
11. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
beverly
Jun 28, 2017 2:39 PM
1 of 1 people found this helpfulI 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
-
12. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
Ok. Last question I think. Is that a true separate table or a TO of RENTRAK?
-
13. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
wimdecorte
Jun 28, 2017 2:43 PM
2 of 2 people found this helpfulThat 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.
-
14. Re: delay in ExecuteSQL when scripted vs. using Data Viewer?
beverly
Jun 28, 2017 2:44 PM
The query looks good even for many many records. Is there a Commit Record in the loop?
Sent from miPhone