but that still has a different select cursor for each number - isn't that a major contributor to the overhead?
perhaps something more like this?
begin-select
field1 &f1
field2 &f2
...
...
field250 &250
do write_file
from table2
where number in (select number from table1)
order by table2.number
end-select
-----Original Message-----
From: Arnon Oppenheimer [mailto:arnono@SEMECH.CO.IL]
Sent: Thursday, March 30, 2000 11:57 AM
To: SQR-USERS@list.iex.net
Subject: Re: Performance issue on large select.
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