Re: Debug inconsistent run times for SQL requests from FileMaker clients

Rob,

The term you're thinking of is SQL pass-through...

Given a query
select *
from mytable
where somecolumn = 'VALUE';

Clients have two choices...
1) Pass the entire query to the i
2) Pass just "select * from mytable" and do the filtering on the client

Appears filemarker may be doing #2

Looks like the OP may need to use ExecuteSQL to have the query sent to the
i in it's entirety.
https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help/sql-interact-with-odbc-data-sources.html

Charles



On Fri, Feb 22, 2019 at 10:23 AM Rob Berendt <rob@xxxxxxxxx> wrote:

Charles is probably right.
If I started guessing I would start suggesting things like try what you
can to pass the processing on up to the server versus on the client. For
example, when you do a
Select * from mymultimillionrowtable
from a client it may have to bring down every row for that WHERE check.
But if you can figure out how to get the server to do the heavy lifting it
would really help. Some might suggest a stored procedure, ran on the IBM
i, to return the results. There's also some option in ODBC to pass the
processing on up. Sometimes you have to code it differently. I forget
what the technique is but I have quoted it in the past on this list. No
I'm not gaming you. I really forget and I have other priorities.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Friday, February 22, 2019 12:15 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Debug inconsistent run times for SQL requests from FileMaker
clients

Use ACS to start a DB monitor for the user from each client...

Then dig into the results...

Charles

On Fri, Feb 22, 2019 at 10:07 AM Bobby Adams <bobby_adams@xxxxxxx> wrote:

Hello,

I hope someone can point me in the right direction to help us debug
some SQL run-time inconsistencies.

We have some SQL queries, that access the IBM i DB2 database, that
have wildly different run-times depending on the client. We can run
the queries from the Surveyor product and it runs consistently
time-wise each time. This is true if we run the queries in the IBM
ACS SQL client. If we run that same query from within a product
called FileMaker the run-times will be wildly inconsistent and usually
much much much longer. Although, there are times when it matches the
Surveyor run-times.

The FileMaker product is connecting to the IBM i via the ODBC driver
from IBM ACS.

We have just started developing these queries so it isn't a case of
"it was working fine and now it isn't".

Where should I start looking to figure this out? What are the most
likely culprits?

Any help will be appreciated.

Thanks and regards,
Bobby

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com