Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Unanswered: dbaccess - unload to VS output-to-file

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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:
    Code:
    $ echo " unload to testoutput select * from catalog;" | time dbaccess yourDB
    $ echo " select * from catalog;" | time dbaccess yourDB
    Check the time of the "REAL" field.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Oct 2007
    Posts
    3
    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

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    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:
    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...

    The output :
    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
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Oct 2007
    Posts
    3
    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.....

  6. #6
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    check the format generated by output , and after that, post ALL information... you are comparing apple with orange here....
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  7. #7
    Join Date
    Mar 2010
    Posts
    5
    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.

  8. #8
    Join Date
    Mar 2010
    Location
    dallas, TX, usa
    Posts
    1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •