The Cost calculation is changed

Discussion forum about all things SqlBase.
ursamajor
Netherlands
Posts: 51
Joined: 23 Aug 2019, 11:10
Location: Sliedrecht, The Netherlands

The Cost calculation is changed

Post by ursamajor » 29 Oct 2019, 14:29

In SQLBase 11.5 the cost of a certain query is calculated of “715.636” and in SQLBase 12 “173.385.137”. This is in the same database / machine / storage.
Is there an explanation of this phenomena?

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 274
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: The Cost calculation is changed

Post by Steve Leighton » 29 Oct 2019, 21:40

Never seen a cost expressed in this way. Always a straight integer value. e.g.
SQLBase cost.png

Anyway, at the risk of teaching what you already know, SQLBase is a cost based optimizer , so 'Update Statistics on Database' or 'Update statistics on table <table name>' will change the SQL cost dramatically, and so should be done regularly on high volatile tables. I believe UNOAD/LOAD does the same thing, which is what may have occurred when you loaded up SB 12.

p.s. How can it be 'the same database' , if one is v11 and one is v12 - completely different animals, esp. if you are using any of the 'RC' isolation levels.
You do not have the required permissions to view the files attached to this post.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

ursamajor
Netherlands
Posts: 51
Joined: 23 Aug 2019, 11:10
Location: Sliedrecht, The Netherlands

Re: The Cost calculation is changed

Post by ursamajor » 24 Mar 2020, 19:20

I guess I have a little late reply.

The 11.5 database was unloaded and then loaded into sqlbase 12 version.
I added the thousandseparators for visibility purposes.

The isolationlevel also is RL on both databases.
Only that in the Sql.ini readonly parameter is different. 11.5 = 0 and 12=1.

I'll try to repeat the tests and keep your advice in mind and post my results here.

TerryP
Australia
Posts: 263
Joined: 06 Mar 2017, 22:41
Location: Mackay QLD Australia

Re: The Cost calculation is changed

Post by TerryP » 24 Mar 2020, 22:40

Hi All,

When you do an unload/load or use the reorganize command, you (still) have to do an explicit "update statistics on database", or the table and index statistics for page counts are all zero, which significantly affects the optimizer.

I remember raising it with Gupta (or Unify/Centura) back with SQL Base 9.0.1, but it still happens in 11.6.2, 12.0.1, 12.1.2 and 12.2.0. If you don't believe me, use the following script to check it out using the island database:

connect island;
reorganize;
select * from systables where name not like 'SYS%';
select * from systables where name not like 'SYS%';
update statistics on database;
select * from systables where name not like 'SYS%';
select * from systables where name not like 'SYS%';
commit;

Cheers,
Terry Phythian
Reprise Consulting

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests