Hi,

Has anyone ever seen this behaviour ?

using output-to-file is faster than unload to file in dbaccess !!!


Versions :
IBM Informix Dynamic Server Version 10.00.UC2 -- On-Line -- Up 64 days
SunOS bosslive2 5.9 Generic_118558-35 sun4us sparc FJSV,GPUZC-M

This happens on our live platform, but I have managed to re-create it on
as a test case on stores7demo


Command :
unload to testoutput
select * from catalog;

&

select * from catalog;

Using DBACCESS

Unload to file :


on new screen : Cat testouput|wc –l (2 seconds apart)

0
2001
20053
38932
57084
76047
95316
116680
136428
158893


Output to file


on new screen : Cat testouput|wc –l (2 seconds apart)

0
10905
99145
193921
276070
368944
462710
558209
661197
757483
860570
975145
1084618
1163651


Many thanks
Mashy

I think you are bit confused :
1) The comand "wc" is a Word count : wc - print newline, word, and byte counts for each file
They can't count the time.
Check the "man wc"
2) To measure the time of execution, use the command "time"

To identify the time of execution , try this:
Check the time of the "REAL" field.

thanks for the reply.

though I was taking a count of the rows every 2 seconds in the test unload file not the time.

so my script was basically doing :

----------------------
while true
do
cat testoutput |wc -l
sleep 2
done
-----------------------

Both methods unload/output-to-file gave different counts of rows.... making me think the output-to-file(within dbaccess) is much much faster than 'unload to testoutput'. the sql it self was exactly the same... hope this makes more sense.......

Cheers

Your script is not much accurate to use...
Did you read the file generated by the dbaccess via output?

Depending the amount and size of your columns, each colums is saved by line, so if you have 1000 lines with 10 columns you will have 10.000 of rows in the output file...

looking your outputs, the UNLOAD is faster:
In 9 times (x 2 seconds) they are unload all table (158893 records) : 18 seconds...

The output :
13 times X 2 seconds = 26 seconds...
158893 records

Hi,

thanks for your help on this.

The output(s) I posted was a only a sub set of the reults. The end result will hold many more rows. In the millions I believe...

>> Did you read the file generated by the dbaccess via output?

I think the output on both occasions are in the same format......

------------------------------------------------
>>looking your outputs, the UNLOAD is faster:
>> Quote:
0. 0
1. 2001
2. 20053
3. 38932
4. 57084
5. 76047
6. 95316
7. 116680
8. 136428
9. 158893
In 9 times (x 2 seconds) they are unload all table (158893 records) : 18 seconds...
----------------------------------------------------------------

if we take these results "158893 records" in 9x 2 seconds = 18 seconds. and compare it to the output-to-file method.....


---------------------------------------------------------------
The output-to-file :
Quote:
0. 0
1. 10905
2. 99145
3. 193921
4. 276070
5. 368944
6. 462710
7 558209
8. 661197
9 757483
10 860570
11. 975145
12. 1084618
13. 1163651
13 times X 2 seconds = 26 seconds...
158893 records
----------------------------------------------------------------

0. 0
1. 10905
2. 99145
3. 193921

The same equivelent was less than 3 x 2seconds = 6 Seconds.


hope this helps.....

check the format generated by output , and after that, post ALL information... you are comparing apple with orange here....

1. output to "outfile" in select statement has least overhead.
2. "unload to" statement has to format the output of the select statement with pipe
symbols and is actually running two SQL statements.
3. dbaccess executes SQL statements from the OS, so more overhead than 1 and 2.

That's a very interesting topic.
But this field is still new to me.
It will be grateful if you give me some
more information about it.
Thanks in advance.