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 |
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)?
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
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.
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 ?
Hmmm... bug in code, versus magic :)
Let's ask Meme...
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 !
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 :)