Regarding Temp Table Logic

[mprabu_p]

Hi All,

I have 1500,000 lacs records in my Permanant table. Now I want to copy some records (which are satisfied 2 conditions) to one temp table. How I copy the Records.

Now I use the following condition,

for each xx_table where xx_table_field1 = "A"
and xx_table_filed2 = "B":

create temp_table.
assign fileld 1
field 2
.
.
etc.

end.

Note:
Field1 is available in index , field2 is not available in index.

The above condition takes nearly 90 minitues to create the temp table. How I fine tuned this logic?

Please Help....

Prabu.M

 

[tamhas]

There are several possible issues here.

The first thing I would do is the FOR EACH with nothing inside. If that is taking a large part of the total time, then your issue is query performance and has nothing to do with the temp-table.

You could try switching to QUERY or REPEAT and adding a FIELDS specification to reduce the volume of what is returned, especially if this is client/server.

You don't show both sides of the assignment in your sample code, but I suppose it must be there or you wouldn't be getting anything.

Do you know how many records are in the subset? If it is a lot, you might be running out of memory for the temp-table and slopping over to disk. If so, giving the session more memory for the temp-table would help.

 

[TomBascom]

As Thomas says, first do the FOR EACH by itself and see how long that takes. Count the records selected with a simple "i = i + 1."

My guess is that that time is too long. To fix that you will need probably to add an index that has both fields as leading components. If you know something about the selectivity of the fields then you should try to make the most selective field the first one in the index. You might also just be having general performance issues -- proper setting of -B and -spin are the two easiest tweaks that might help.

If the record count above is more than a few thousand then you might be exceeding the temp-table buffer space and incurring disk io. You can check that by turning on the -t (lower-case "t") client parameter. If the DBI* file is growing then increase -Bt to use more RAM to buffer temp-tables.

(FIELDS works fine with FOR EACH so you don't need to switch to a QUERY but if this is a client-server system then, as Thomas says, it might be very helpful to add a field list.)

 

[jawad]

I agree with Thomas and Tom, when you have controlled the temp-table buffer space
Try this one:


for each xx_table where xx_table_field1 = "A": /* so you use the index on field1 */

if xx_table_filed2 = "B" Then
repeat:
create temp_table.
assign fileld 1
field 2
etc.
leave.
end. /* repeat */

end.

I'm sure it wil be faster....