× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Careful with the definition of where the query runs. If the entire query runs on IBM i, then design it for IBM i .

If the query is actually run from an ODBC/JDBC connection (such as from a MS-SQL system) we have seen that the SQL Server will pull the entire data set after performing any joins into the server, and then run the select portion of the statement. So, when the customer was expecting 25 rows to be returned, they actually got well over 1 million rows, that SQL Server then did the selection etc. on and presented the 25 rows as the result set. The selection portion of the statement ran fine but took forever to download the joined million row table. We fixed it by putting a stored procedure on IBM i that SQL Server called, and everything moved very fast and worked as expected afterwards.

That might have been settings on SQL Server or the ODBC/OLE connection to fix that, we did not look that far, but the behavior has been observed many times.


--
Jim Oberholtzer
Agile Technology Architects

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Spencer Elliott
Sent: Monday, April 08, 2019 9:57 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Richard Schoen <Richard.Schoen@xxxxxxxxxxxxxxx>
Subject: Re: Maximum OBDC Query Size

Hi Richard,
The actual issue boils down to speed. Which is faster? A single select with a large result set that is processed by the program or a bunch of selects with a small result set. I chose the single select approach. It just seems to me that it would be faster than 500 selects and 500 open cursor, close cursor ect.


Spencer C Elliott


949-366-5234 X152 (O) l (949) 544-1237 (Skype)

Spencer@xxxxxxxxxxxxxx


www.s4isystems.com

Follow us:

[image: Follow_us][image: Follow_us][image: Follow_us][image: Follow_us][image:
Follow_us]

NOTE: This email message and any attachments are for the sole use of the intended recipient(s) and may contain confidential and/or privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by replying to this email, and destroy all copies of the original message.


On Mon, Apr 8, 2019 at 7:42 AM Richard Schoen via MIDRANGE-L < midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Yeah I realized that once I re-read the question 😊

Looks like what you've discovered should give you enough runway unless
you've had an issue with running your large statement.

One option to consider might be to call an RPG program that contains
the SQL and return a resultset for processing from the RPG app perhaps.

Seems like this might be a good use case for some pre-processing on
the IBMi to a temp table, but I don't know enough about the actual
business case.

Regards,
Richard Schoen
Director of Document Management
e. richard.schoen@xxxxxxxxxxxxxxx
p. 952.486.6802
w. helpsystems.com

------------------------------

message: 3
date: Mon, 8 Apr 2019 07:01:31 -0700
from: Spencer Elliott <spencer@xxxxxxxxxxxxxx>
subject: Re: Maximum OBDC Query Size

Hi Richard,
The question was about how big can a SQL statement be when submitted
through ODBC NOT how big can the result set be. I am working on a
project where I need to submit an extremely large SQL statement (in my
opinion,
10k+) to the IBM i with a very large WHERE (500+ predicates) clause.

Spencer C Elliott


949-366-5234 X152 (O) l (949) 544-1237 (Skype)

Spencer@xxxxxxxxxxxxxx




--
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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.