Re: Retrieving 1, and only 1, row in SQL

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

Alexei makes a good point. Maybe 'where exists' would be better, if it
works as I hope it does, viz., that it stops as soon as it finds a match.

At 02:47 PM 3/15/02 -0600, you wrote:

>QSYS2/SYSCOLUMNS is a logical file (SQL view). Apparently query over this
>view requires internal copy of data.
>You have ALWCPYDTA(*NO). Try changing it to ALWCPYDTA(*YES) or ALWCPYDTA
>(*OPTIMIZE).
>
>Why do you think it's going to copy entire file? It needs to create
>"temporary result" for whatever purpose - it can be quite small.
>
>To analyze SQL performance - run your program in debug mode and look for
>optimizer message. It should give a clue about performance differences
>between the runs.
>
> > Performance plummets on the file check.
> > For example a check on one of our BPCS data libraries returned a count of
> > 81,390 and took about a minute.
>
>your query specifies SELECT COUNT(*). DB will probably scan entire file for
>this query to count all occurences.
>6 million records in a minute - not too bad.
>SELECT COUNT(*) is not the best formulation if you only want to check for
>existence.
>
>     Alexei Pytel
>speaking for myself only
>
>
>
>
>
>                       rob@dekko.com
>                       Sent
> by:                  To:       midrange-l@midrange.com
>                       midrange-l-admin@m        cc:
>                       idrange.com               Subject:  Retrieving 1,
> and only 1, row in SQL
>
>
>                       03/15/2002 01:27
>                       PM
>                       Please respond to
>                       midrange-l
>
>
>
>
>
>
>The file being queried is QSYS2/SYSCOLUMNS.  This file has 6.6million
>records on our development box.  There are no keys on the file and I am not
>going to create an index on an IBM supplied file.
>
>The following subprocedure is used to access the file.  The point of this
>subprocedure is threefold depending on how it is called.  One, determine
>the existence of the library.  Two determine the existence of the file.
>Three determine the existence of the field.  Performance is fast on the
>field check and the file check.  Performance plummets on the file check.
>For example a check on one of our BPCS data libraries returned a count of
>81,390 and took about a minute.
>
>I thought I'd play around with STRSQL.  First I tried
>select dbname
>from qsys2/syscolumns
>where dbname='DATDIVF'
>fetch first 1 row only
>But I got "ALWCPYDTA(*NO) specified but temporary result required for
>SELECT IN".  I sure don't want to wait for it to copy a 6.6 million record
>file.  Would it copy the logical (QSYS2/SYSCOLUMNS, 6,590,211 records,
>ODOBSZ=53,248) or the physical (QSYS/QADBIFLD, 6,590,209 records,
>ODOBSZ=2,212,638,720)?
>
>Any suggestions?
>
>      P checkdb         B
>      D checkdb         PI            10I 0
>      D library                       10A   CONST
>      D File                          10A   CONST OPTIONS(*NOPASS)
>      D field                         10A   CONST OPTIONS(*NOPASS)
>
>      D* Local fields
>      D retField        S             10I 0
>      D sqlstring       s            200a
>      D loFile          s                   like(File)
>      D hiFile          s                   like(File)
>      D loField         s                   like(Field)
>      D hiField         s                   like(Field)
>
>       /free
>        sqlstring = 'Select count(*) from qsys2/syscolumns +
>                     where DBNAME = ? +
>                       and TBNAME between ? and ? +
>                       and NAME   between ? and ?';
>        if %parms>1;
>         loFile=File;
>         hiFile=File;
>        else;
>         loFile=*loval;
>         hiFile=*hival;
>        endif;
>        if %parms>2;
>         loField=Field;
>         hiField=Field;
>        else;
>         loField=*loval;
>         hiField=*hival;
>        endif;
>        retField=*zeros;
>       /end-free
>
>      C/EXEC SQL
>      C+ Prepare stmt from :sqlString
>      C/END-EXEC
>
>
>      C/EXEC SQL
>      C+ declare C1 cursor for stmt
>      C/END-EXEC
>
>      C/EXEC SQL
>      C+ Open C1 using :library, :loFile, :hiFile, :loField, :hiField
>      C/END-EXEC
>
>      C/EXEC SQL
>      C+ Fetch C1 into :retField
>      C/END-EXEC
>
>      C/EXEC SQL
>      C+ Close C1
>      C/END-EXEC
>
>      C                   RETURN    retField
>      P checkdb         E
>
>
>Rob Berendt
>--
>"They that can give up essential liberty to obtain a little temporary
>safety deserve neither liberty nor safety."
>Benjamin Franklin
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.
>
>
>
>
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.