Eventually there will be more criteria. The WHERE clause should have reduced it some. I will test. There are 500,000 records on the prime file. There could be some date filtering, but the ultimate filter will be date descending.
Let you know.
I took out the LIMIT clause in the trace:
FileMaker Pro A 1e58-2358 ENTER SQLExecDirectW
WCHAR * 0x0000000023F59100 [ 193] "SELECT COUNT (*) FROM ADABAS_10_12_8_33.dbo.MW_int_report_flat as a inner join ADABAS_10_12_8_33.dbo.mW_worker as b\ don intr_curr_worker_id = work_worker_id \ dwhere a.intr_intake_type = ‘ANE’\ d "
Still took a while but less than before.
I am comparing it to the INFONAUT utility which does the same thing in 5 seconds, FMP15 is still taking 30-35 seconds,
Curious when it displayed the table records list, it had 2900 records and it was still reading records in the background. I cancelled it after 15,000 records to look at the trace. This application should find the records it wants within the first 3 pages of records with 16 records on a page. So maybe I could do a date range or maybe status. let you know.
Okay I think it might be the fact that I am ‘importing’ records and writing my own script in FMP. It is applying COUNT by default. When the app is written I am guessing the user will create layouts with rows set to 16 and it will display faster. ??? I can’t find anywhere I can set number of rows per page for the general table display. But at the same time, I reran INFONAUT and it starts showing many records in less than a second. So maybe it is the COUNT problem, just don’t know how to tell FMP not to do the COUNT unless we write an app with layouts, scripts/steps.
i took out the JOIN and there was no SELECT * COUNT in the trace
FROM ADABAS_10_12_8_33.dbo.MW_int_report_flat as a
where a.intr_intake_type = ‘ANE’
It’s my opinion at this time that CONNX/ADABAS SQL Gateway/ODBC is working as it should. This is a FMP15 issue.
Thanks for all the help.
I was going to suggest the CONNX View feature to get around this issue with FMP.
However in my testing it appears the FMP issues a count (*) even with a single table/view.
I’m not sure of your exact use case, but if it is possible to limit the number of rows returned via criteria that may yield the best results.
I will test some things.
Also I am looking at EXECUTE SQL function in FMP. It is suppose to be a direct link to data, it works but I need to find a way to get the @RESULT back.
We have cancelled this project, thanks for all the advice and support.
We are going to a replicated ADABAS.