473,322 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Tagging a table volatile causes performance penalties

Hi

I'm trying to tune access times on a table which size might vary (from 0
to at least several thousand records).

I've tried marking this table 'volatile' but it caused performance
penalties on some operations:
- table scan from 10 to 200 timerons
- index scan from 15 to 500 timerons

(I took those times from access plans as explained by DB2)

I understand that when a table is marked 'volatile' the optimizer
prefers index scans before table scans, but why on earth does the same
operation takes much more time after the change ?

Or maybe this performance penalty is small and I should not care about
it? I predict that the maximum size of the table might be about 20 000
records.

Thanks in advance

--
Szymon Dembek
May 9 '07 #1
2 2065
Szymon Dembek wrote:
Hi

I'm trying to tune access times on a table which size might vary (from 0
to at least several thousand records).

I've tried marking this table 'volatile' but it caused performance
penalties on some operations:
- table scan from 10 to 200 timerons
- index scan from 15 to 500 timerons

(I took those times from access plans as explained by DB2)

I understand that when a table is marked 'volatile' the optimizer
prefers index scans before table scans, but why on earth does the same
operation takes much more time after the change ?
Table scan's aren't always bad, otherwise the optimizer wouldn't use
them. If you encourage usage of an index you may also have to pay for
either random I/O to get to the columns not in the index or a sort of
the row-id's to at least get those columns without making the hard
drives sing.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 9 '07 #2
On May 9, 7:01 am, Szymon Dembek <demb...@nospam.please.plwrote:
Hi

I'm trying to tune access times on a table which size might vary (from 0
to at least several thousand records).

I've tried marking this table 'volatile' but it caused performance
penalties on some operations:
- table scan from 10 to 200 timerons
- index scan from 15 to 500 timerons

(I took those times from access plans as explained by DB2)

I understand that when a table is marked 'volatile' the optimizer
prefers index scans before table scans, but why on earth does the same
operation takes much more time after the change ?

Or maybe this performance penalty is small and I should not care about
it? I predict that the maximum size of the table might be about 20 000
records.

Thanks in advance

--
Szymon Dembek
The whole point of marking the table volatile is to tell the optimizer
that the current table size (as per statistics) might not be
representative / correct in the near future. The optimizer will assume
significant variation in the size and cost the plan accordingly, hence
the increased cost estimate. Unless you'll be running your query
hundreds of times per second, the difference in the cost you observed
should not be significant.

Regards,
Miro

May 9 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: David Rasmussen | last post by:
What is the difference in meaning and in consequences between struct S { volatile A* a; }; and struct S
15
by: DanGo | last post by:
Hi All, I'm trying to get my head around synchronization. Documentation seems to say that creating a volatile field gives a memorybarrier. Great! But when I do a little performance testing...
1
by: Mike L. Bell | last post by:
Query: update table1 t1 set end_time = ( select end_time from table2 t2 where t2.key1 = t1.key1 and t2.key2 = t1.key2 ) where exists
3
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when...
14
by: Pierre | last post by:
Using the "volatile" keyword, creates a problem if I intend to use any of the interlocked APIs. The compiler generates an error if I use the following line, for example: ...
10
by: Lau Lei Cheong | last post by:
Hello, I really need to use volatile System.Int64 for a .NET v1.1 program in C#. But the compiler complains "a volatile field can not be of type long". How to work around it? Or is there any...
2
by: Jody | last post by:
Hi I've been working on a database which basically incorporates 3 tables to describe say a widget which is either sold or leased. I have the Widget table which stores the information related...
94
by: Samuel R. Neff | last post by:
When is it appropriate to use "volatile" keyword? The docs simply state: " The volatile modifier is usually used for a field that is accessed by multiple threads without using the lock...
3
by: Rakesh Kumar | last post by:
Hi - I am actually trying to get my feet in multi-threaded C++ programming. While I am aware that the C++ standard does not talk about threads (at least, for now - in C++03) - my question is more...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.