Low performance with wrong statistics

[Former Member]

Statistics that are generated in the table syscolstat are really needed for the database?

I'm having problems because of low performance statistics. I periodically drop statistics and improves performance significantly. Is there any possibility to not generate them?

Thanks, Leonardo.

[Former Member]

I too would like to know if this is possible. I'm seeing the same issue after upgrading from version 9 to 12. If I drop statistics from the PK on a 37 million row table, performance goes through the roof. Response comes back in 2 seconds instead of 60+.

[Former Member]

Leonardo and Grant,

Would you please post the graphical plans with statistics before ( I.e.,the slow plan), and after (i.e., the fast plan) you run 'drop statistics' statement. It would be useful to investigate these particular issues based on your plans.

With many thanks, A. Nica

[Former Member]

Hi Nica,

Sybase 9.0.2 without drop statistics

Sybase 9.0.2 with droped statistics

Test in Sybase 12.0.1

UPDATE:

Select:

select * from tbvenpedido pedido join tbvenitenspedido itens on 
(itens.empresa = pedido.empresa and itens.pedido = pedido.pedido)
order by 1, 2

More details:

  740.091 rows
  Database size: 1,5 gb

[MCMartin]

Strange both plans look identical, are your charts correct?

[Former Member]

Yes @Martin, the graphics are correct. The only difference between the two queries is drop statistics [all my tables] statement.

[Former Member]

These are the results:

Before drop statistics without order by clause

Before drop statistics with order by clause

After drop statistics without order by clause

After drop statistics with order by clause

In my previous tests I was not running the command: call sa_flush_cache() so the result was so diverse. I do not understand why the return of select is so slow.

[chris_keating]

Can you please email or attach the query plan as per the instructions. The screen caps and PDFs do not contain all of the information nor provides an easy mechanism to analyze the issue. To get the plan that we require,

1. Go to Tools | Plan Viewer or Shift-F5
2. Set the Statistics Level drop down to "Detailed and node statistics"
3. Click Get Plan button
4. Click Save As ... button and select a filename that identifies the plan iteration
5. Post or email the .saplan file that is generated from each iteration.

Please ensure that sa_flush_cache() is called for each iteration as noted in Ani's previous post.