Re: SQL with no output

Rick Denoire <100.17706_at_germanynet.de> wrote in message news:<6meqrv0cp45ct6rp9rlhgjfm3ld3kdq47p_at_4ax.com>...
> "mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote:
>
>
> >1) nest the statement in a count() statement, i.e.
> >select count(<some non-indexes column)) from (
> >select ...
> >from ...
>
> As you said, skewed.
>
> >2) use the following SQL*Plus commands
> >set termout off
> >spool some_output_file
> ><run commands from a script file>
> >set termout on
> >
> >then do a grep or FIND on the spool file for 'Elapsed'
>
> If I really wanted to do that for myself, I would just put the SQL
> commands in a here script and execute it from the shell, redirecting
> the standard output to /dev/null. BUT: It is not me who needs to run
> this "benchmark". A Windows user is the one who is supposed to run the
> SQL commands (from a DOS command shell, for example). In other words,
> no "grep" and such fancy commands there.
>
> Since this is supposed to be a "benchmark", redrawing the screen while
> the output is being displayed could affect the overall elapsed time in
> an undesired manner (I don't want to measure how fast the computer
> redraws the screen).
>
> Well, if the amount of returned raws in considerable large, even
> writing them into a spool file could adversely influence the real
> execution time on the server side. The only solution is to let sqlplus
> to accept returned rows and send them to "nirvana" (network speed
> could still be an issue).
>
> Thanks
> Rick Denoire

Exactly what are you trying to benchmark?

One way to deal with the overhead when benchmarking is to make it consistent.

 So if you are comparing SQL Server vs ORACLE for example, make sure the command is issued from a client with the same conditions (output to spool file, time measured from commands OUTSIDE the DB tool, network speed, etc.)
 Or if you are comparing different versions of a query, then using the SQL PLUS timing is okay, and letting the output dump to a spool file or screen is fine, since both versions of the query produce the same.

But in the end you seem to be interested in a DOS solution. Try asking in a Microsoft group how to execute a command in a DOS window, minimized (that way the "screen update" issue is bypassed).

As usual, letting us know what the real problem is can help us show you the way out of any corner you may have painted yourself into.

HTH and good luck,
  Ed Received on Fri Nov 21 2003 - 07:05:53 CST