Only results after update statistics

Discussions specific to SQLBase 11.6

Only results after update statistics

Postby m.burkhardt » Thu Dec 01, 2016 4:54 am

Hello, we have a fault behavior with our SQLBASE 11.6 and the same on 12.0.1!
Description:
Offline Database: no Client writes into the database!!!
-> check database -> OK!!
-> Execute a complex select-statement (with joins) -> no results!! (That’s Wrong!!)
-> Execute each table separated! Each result set counted 16 Rows
-> Execute the complex select-statement again -> no results!!
-> UPDATE STATISTICS to only one of the involved table!
-> Execute the complex select-statement again -> 16 Rows!
What can I do!


Thanks in advance

Greetings from Germany
Michael
m.burkhardt
 
Posts: 54
Joined: Thu Nov 08, 2007 3:51 am

Re: Only results after update statistics

Postby mvandine » Sun Dec 04, 2016 9:49 pm

Hi Michael,

I would need a testcase. Obviously there is a statistic that is telling the optimizer that there are no rows on a particular table and don't bother joining it. Once the US is done, then the optimizer knows about it and finds the right number of rows.

Might I suggest that you create a stored command once the statistics are correct (which will save time because the optimizer won't have to 'rethink' the best way to do the join) and the same execution path will be used every time you execute it.

Have you tried this same thing with 11.7 or 12.0? You do know that 11.6 is out of Engineering support and no more changes will be made in that version even if a defect is found...

Best regards,
Mike Vandine
Gupta Technologies
User avatar
mvandine
 
Posts: 4120
Joined: Wed Feb 14, 2007 4:15 pm
Location: Perth, Western Australia

Re: Only results after update statistics

Postby Ewald Palmetshofer » Tue Dec 13, 2016 1:08 am

Hi Michael,

I had a similar problem years ago. The problem occured by joining two tables with columns defined char(120). I converted to char(40) and everything was ok.
EDV-Hausleitner GmbH
Bürgerstraße 66
4020 Linz
palmetshofer@edv-hausleitner.at
Ewald Palmetshofer
 
Posts: 225
Joined: Thu Jan 08, 2009 6:15 pm
Location: Linz / Austria


Return to SQLBase 11.6

Who is online

Users browsing this forum: No registered users and 1 guest

cron