Unload to CSV Extremely Slow

[former_SQLA_member1694910]

Sql Anywhere 16 using JDBC:

Thru FileMaker Pro, and a JDBC Plugin, I CALL a Stored Procedure with a Cursor, to set values in a Table

I then need to UNLOAD those values to CSV

The UNLOAD takes 10 Seconds when run alone, but after the CALL, it takes 2 Minutes

Why would there be a difference ?

I even tried a SELECT into a Temp Table, then an UNLOAD of the Temp Table. No Better. What am I missing ?

Thanks

Greg

[Breck_Carter]

What is different about the two tests? How big is the CSV file? Is it the same size after both tests (10 seconds and 2 minutes)?

[former_SQLA_member1694910]

The CSV is about 14 Mb ( about 180K very wide records ). The CALL alone is 4 Minutes. The UNLOAD alone is 10 Seconds. However, if I run the UNLOAD query immediately after the CALL, it takes 2 Minutes. The CSV is the same size each time. Very odd

[Breck_Carter]

What kind of hardware are you running on?

Is the cache big enough?

What exactly does the table look like?

What exactly does the UNLOAD look like?

14M and 800 bytes per record should not take even 10 seconds, on a modern consumer-grade desktop.

A test of a million row table unloaded to a 65M CSV file ran in less than two seconds.

CREATE TABLE DBA.inventory ( -- 1,000,000 rows, 30M total = 27M table + 2.1M ext + 356k index, 32 bytes per row
   item_id         /* PK        */ INTEGER NOT NULL DEFAULT autoincrement,
   item_count                      INTEGER NOT NULL,
   item_name                       VARCHAR ( 1024 ) NOT NULL,
   updated_at                      TIMESTAMP NOT NULL DEFAULT timestamp,
   last_modified                   TIMESTAMP NOT NULL DEFAULT timestamp,
   CONSTRAINT ASA78 PRIMARY KEY ( -- 356k
      item_id )
 );

UNLOAD TABLE inventory TO 'c:\\temp\\inventory.csv' FORMAT TEXT;
Execution time: 1.895 seconds

 Directory of C:\\Temp
05/29/2017  02:12 PM        66,566,964 inventory.csv

[former_SQLA_member1694910]

I see it now. It is not the UNLOAD. Any query I run immediately after the Stored Procedure takes 2 Minutes, even "SELECT 1". The Procedure runs three different Cursors, looping thru 186K Records, that set multiple Fields. Perhaps I am missing a COMMIT somewhere, and the Procedure needs 2 Minutes to clean up ?

[Breck_Carter]

Hmmm... bug in code, versus magic 🙂

Let's ask Meme...

[former_SQLA_member1694910]

I think the answer is in my Connection. My JDBC Plugin apparently does not wait for the Call to end, and allows me to send another Query, which then has to wait for the Stored Procedure to finish. Thanks for your quick replies !

[Breck_Carter]

The execution profiler in SQL Anywhere might help you find where the procedure is spending all its time. Once you know which query(ies) are taking a long time, the Graphical Plan With Statistics is your friend (a high-maintenance friend sometimes, but your friend nonetheless 🙂