Re: Performance issue on large select.

[Arnon Oppenheimer <arnono@SEMECH.CO.IL>]

The benefit of the subquery is saving tempspace process.
If you do have (good) index on table2.num, you can try this :
(let me know in case it helps and you want to know why....)

 begin-procedure get_fields
move &num to $num edit 999999999 ! prepare dynamic value.
 begin-select
 field1  &f1
 field2  &f2
 ...
 ...
field250 &250
   do write_file
 from table2
 where number = [$num]                       ! dynamic SQL
end-select
end-procedure get_fields


Regards,
Arnon Oppenheimer
 -------------------------------------------------------------------
 SEMECH SOFTWARE MARKETING LTD.
 TEL : (972) - 3 - 5333144
 FAX : (972) - 3 - 5333132
 Email: arnono@semech.co.il
 -------------------------------------------------------------------
----- Original Message -----
From: Steven Calvert <calvert@ULETH.CA>
To: <SQR-USERS@list.iex.net>
Sent: Thursday, March 30, 2000 5:25 PM
Subject: Re: Performance issue on large select.


> My .02:
>
> IMHO a single select should help (depends on the server configuration).
Also, you
> should try creating an index on the "num" fields in both tables if doing
the table
> join (only in table2 if doing the nested select). Finally, try dropping
and
> re-creating the index(es) ... Oracle indexes sometimes become "unbalanced"
(or
> even "invalid"), which decreases performance.
>
> And keep your fingers crossed! <grin>
>
> Steve
>
> Ray Ontko wrote:
>
> > Chad,
> >
> > Is there a reason you can't join the two tables and do
> > one select?
> >
> > begin-select
> > field1   &f1
> > field2   &f2
> > ...
> > field250 &250
> >    do write_file
> > from table1 , table2
> > where table1.num = table2.num
> > end-select
> >
> > Ray
> >
> > [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > > hi all,
> > > any help would be appreciated with the following problem.  Im running
a
> > > large sqr ver 4.3 against Oracle 8 on an Nt machine and its taking 3
hours
> > > to run.  Ill have to run this frequently and could do with better
> > > performance.  Heres what Im doing.
> > > Im selecting 250 fields from a table 6500 times and then writing the
record
> > > out to a file.  Ive tried to select all the fields in one procedure
and have
> > > also tried 250 different begin and end selects within one procedure.
Its
> > > slower the second way.  I know the issue is with the selecting and not
with
> > > writing out as I tested commenting the write procedure.  Anyone got
any
> > > suggestions for performance on such a large select.
> > > Below is the basics of what Im doing.
> > >
> > > Tia and fingers crossed coz its running at the moment.
> > > Chad
> > >
> > > begin-procedure get_num
> > > begin-select
> > > number  &num   ! There are 6500 numbers
> > >  do get_fields
> > > from table1
> > > end-select
> > > end-procedure get_num
> > >
> > > begin-procedure get_fields
> > > begin-select
> > > field1  &f1
> > > field2  &f2
> > > ...
> > > ...
> > > field250 &250
> > >  do write_file
> > > from table2
> > > where number = &num
> > > end-select
> > > end-procedure get_fields
> > >
> > > begin_procedure write_file
> > >  string &f1 &f2 .... &f250 by ',' into $rec
> > >  write 1 from $rec
> > > end-procedure write_file
> >
> > ----------------------------------------------------------------------
> > Ray Ontko   rayo@ontko.com   Phone 1.765.935.4283   Fax 1.765.962.9788
> > Ray Ontko & Co.   Software Consulting Services   http://www.ontko.com/
>
> --
> Steven Calvert
> calvert@uleth.ca
> University of Lethbridge
> (403)329-2071