Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

Testing indicates that a hard-working I/O bound stored procedure may not be slowed down much by SET TEMPORARY OPTION PRIORITY = 'Background' in 11.0.1.2276. This matches my previous impression that "background priority doesn't have much visible effect".

SELECT DATEDIFF ( MILLISECOND, '2010-10-29 11:11:17.281', '2010-10-29 11:14:38.187' ) AS "background",
       DATEDIFF ( MILLISECOND, '2010-10-29 11:24:37.953', '2010-10-29 11:26:31.937' ) AS "foreground";
background,foreground
200906,113984

In this particular case, the responsiveness of other processes (even Wordpad) is sometimes adversely affected by this procedure even when running in "background" priority, and I want to give the user the option to slowwwwww it down even more. CPU usage is low, it is disk activity that is crushing the machine.

I am considering executing WAITFOR DELAY after each COMMIT to let the disk catch up to the I/O frenzy (Queries From Hell, plus deletes). The delay amounts would vary with the user-input "speed" setting.

Is that a good idea?

asked 29 Oct '10, 16:02

Breck%20Carter's gravatar image

Breck Carter
32.5k5417271050
accept rate: 20%

My "light-weight" impression of the priority option is that is influences the internal SA scheduler, i.e. it could slow down/fasten the execution of a particular request (task?) compared to other requests (tasks). But it would not have influence on other processes like Wordpad. Just my imagionation (and with V8, the old background_priority works that way).

(29 Oct '10, 20:42) Volker Barth

@Volker: The influence on Wordpad is indirect: By sucking up all the disk resources, other processess that need the disk are sometimes slowed down a lot. At least, that's my explanation. The fact is, sometimes when this stored procedure is running, nothing else works properly; when the stored procedure stops, everything else is fine.

(30 Oct '10, 06:08) Breck Carter
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:

×276

question asked: 29 Oct '10, 16:02

question was seen: 1,078 times

last updated: 29 Oct '10, 16:02