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

asked 29 May, 13:00

Gregory%20Durniak's gravatar image

Gregory Durniak
61357
accept rate: 0%

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)?

(29 May, 13:34) Breck Carter
Replies hidden

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

(29 May, 13:51) Gregory Durniak

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
(29 May, 14:19) Breck Carter

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 ?

(29 May, 15:57) Gregory Durniak

Hmmm... bug in code, versus magic :)

Let's ask Meme...

(29 May, 16:37) Breck Carter
Comment Text Removed
1

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 !

(29 May, 22:31) Gregory Durniak
Replies hidden

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 :)

(30 May, 03:58) Breck Carter
showing 2 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×40
×6

question asked: 29 May, 13:00

question was seen: 289 times

last updated: 30 May, 03:58