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]

Leonardo,

Please post the plan files (or sent them to me directly at [email protected]): in dbisql: 1. call sa_flush_cache() before each execution 2. in the 'Plan Viewer', set the option 'Detailed and node statisitcs' 3. 'Get Plan' 4. save the plan in a file using 'Save As...' 5. repeat 1-4 for each combination {without drop stats, drop stats} X {with order by, without order by}

Thanks Ani