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.