Sybase Query Optimizer Bug 
Author Message
 Sybase Query Optimizer Bug

On our Sybase SQL Server (version 4.8) on a Sequent, we have found instances
where a query in one database will run in 10 seconds in one database and in 3
minutes and 18 seconds in another database (These databases contain the same
data, indexes, etc.). The ONLY difference in the two databases is that update
statistics has been run on every user table in the poor performing database.

In the showplan results, the query optimizer chooses a different path for both
cases. We have been told by Sybase that there currently is a bug with the query
optimizer, in that it doesn't always choose the correct path based on the
distribution (sysindexes table) for tables with composite clustered indexes.

There apparently is a threshold as to when the optimizer begins acting funny,
as other databases on this server use composite clustered indexes and have seen
no performance problems.

Their recommendation is, that since the bug is not planned to be fixed until
System 10, to avoid usage of composite clustered indexes entirely and also to
avoid using update statistics.  We have multiple applications which already use
composite clustered indexes in all/most of their tables. This would requiree a
significant amount of rework.

Has anyone else seen this problem or have any suggestions?

Bert Quarfordt
Application Technology Division
Conoco Information Systems
Conoco, Inc
Houston, Texas




Wed, 09 Aug 1995 05:18:46 GMT
 Sybase Query Optimizer Bug


Quote:
> In the showplan results, the query optimizer chooses a different path for both
> cases. We have been told by Sybase that there currently is a bug with the query
> optimizer, in that it doesn't always choose the correct path based on the
> distribution (sysindexes table) for tables with composite clustered indexes.

> Has anyone else seen this problem or have any suggestions?

If one of your problems is that tables are being accessed in the wrong
order (the showplan is screwed up) then you can try the following:

        set forceplan on

        select . . ..
                from table_a a, table_b b, table_c c
                where . . .

        set forceplan off

The 'set forceplan on/off' will tell the optimizer to access the tables
in the order that they've been listed in the 'from' clause.  Mind you,
you have to make this determination as to which tables should come first
in the list.

I had a situation here where I let the optimizer determine the showplan.
As my tables grew in size the optimizer continued to use the same showplan,
even though the initial 'smaller' tables had now become the 'larger'
tables (follow me so far? ;-).

I made sure the statistics had been updated for each of the concerned
tables but got nowhere in terms of performance increase soooooooo,
I tried the 'set forceplan on/off' options.  I put the tables in an
order which would allow me to cut down on the number of reads and
scans.

End result, without 'set forceplan on/off' the query took about 40
seconds; with 'set forceplan on/off' the query takes about 2 secs
at the most.  I had one user respond to my tuning with "Wow, it's
like speedy gonzalez!! FAST!" ;-)

Mark



Wed, 09 Aug 1995 06:40:47 GMT
 Sybase Query Optimizer Bug

Quote:

>Their recommendation is, that since the bug is not planned to be fixed until
>System 10, to avoid usage of composite clustered indexes entirely and also to
>avoid using update statistics.

Wow! You mean that in essence they say: don't use Sybase!
--



Thu, 10 Aug 1995 00:44:51 GMT
 Sybase Query Optimizer Bug

   I ran into a similar QO bug.  In this case, I am at the mercy of the
application developers who decided on some complex multi-UNION stuff when
loading data.  The data gets 'awk'ed, then bcp'ed, and finally a grotesque
isql series is run on the data between three databases with multiple UNIONS.
   We have a SUN 690 and an NCR 3550, both with 4.9.1.  The SUN completed
the 'complete load' in three and one half hours, and the NCR took over
SEVENTY!  A set showplan and exec crashed the SUN...all indices were identical.
It seemed that we could force the use of an index, but which one?  
   Ultimately, an EBF was created, which kept us from comparing a showplan from
NCR with a <void> from the SUN.  Now the NCR performs flawlessly.  A list from
the EBF showed over four possible changes that would account for the change
in behavior.  My bet is that the 'costing' algorithm was using a short int
and it would overflow, making the worst choice the best one.
--
+--------------------------------------------------------------------------+
|                   Carlo Milono                                           |

|"Sometimes I think the surest sign that intelligent life exists elsewhere |
| in the universe is that none of it has tried to contact us." B.Watterson |
+--------------------------------------------------------------------------+



Tue, 15 Aug 1995 13:23:14 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. sql 2000 sp2, query optimizer bug

2. Bug in query optimizer

3. Query Optimizer Bug with SQL 7.0

4. query plan optimizer bug

5. query plan optimizer bug

6. Query Optimizer Bug Fixed

7. MS Access to SQL Server with optimizer hints in query or less selective optimizer (more details)

8. SQL Server Optimizer bug?!


 
Powered by phpBB® Forum Software