ADABAS SQL Gateway CONNX with File Maker Pro

, , , , , ,
21

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.

Thanks

MW

22

I took out the LIMIT clause in the trace:

FileMaker Pro A 1e58-2358 ENTER SQLExecDirectW
HSTMT 0x0000000026526150
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 "
SDWORD 193

23

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,

24

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.

25

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.

26

i took out the JOIN and there was no SELECT * COUNT in the trace

SELECT a.intr_intake_type,
a.intr_intake_dt,
a.intr_intake_time,
a.intr_screen_appr_dt,
a.intr_intake_status
FROM ADABAS_10_12_8_33.dbo.MW_int_report_flat as a
where a.intr_intake_type = ‘ANE’
limit 32

27

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.

Mike Waldron

28

Hi Mike,

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.

Thanks

lm

29

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.

MW

30

We have cancelled this project, thanks for all the advice and support.
We are going to a replicated ADABAS.