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



Chuck,

Doh! RUNSQL should have been STRSQL

And 20x diff was never repeated after closing/re-starting i Nav Run SQL Scripts

Thank you!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, July 31, 2013 2:08 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Sql statement vs Sql View performance

On 31 Jul 2013 08:15, Gary Thompson wrote:
I have an SQL statement I use to list current customers requiring an
EDI 856 ASN for delivery.

OK... So we can call this the EDI-SELECT statement; without actually knowing what the query is\does.

I created a view to make this "list" readily available.

So the EDI-SELECT is encapsulated in a VIEW. Per for the following comment, we can infer that encapsulation was via the SQL request to:
CREATE VIEW ASNVIEW as ( _EDI-SELECT_ )

An SQL statement (select * from asnview) against the view runs slower
than the SQL statement the view is based on; requiring about 20x time
using Sys i Nav Run SQL Scripts stats ?

So a generic query SELECT * from the VIEW is 20x slower than the query defined by EDI-SELECT itself. Both apparently were run in the iNav Run SQL feature.? Note that simply running the two requests does not make for a valid test of performance. Good performance testing is quite complicated, but a minimally reasonable test: Try running each three times in a row, and throw out the first timing for each set of three runs, and average the second and third run of each.

(time diff not noticeable in runsql)

Huh? The RUNSQL CL command? And similarly comparing the SELECT * FROM ASNVIEW with the EDI-SELECT? If RUNSQL CL vs Run SQL scripting, then apparently the each SELECT was encapsulated in a CREATE TABLE or INSERT INTO, because the RUNSQL will not allow a SELECT.

Hmmm... Perhaps "time diff" is a reflection of wall-clock timing, and the "20x time" [from "stats"] is some other type of timing that is currently undefined; that "runsql" is the equivalent of "Run SQL Scripts"? Regardless, adjusting the means for the /performance/ test might reveal that the original investigation was flawed due to improperly formulated testing.

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




As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.