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

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