Peewee
asked on
Beginners Guide to Performance
Hi there,
i'm interested in looking at the performance of my stored procedure. Whats the best way for mesauring this, and whats the best ways to speed them up ( general tips probably will help me out). Whats a good and bad response time for sybase by the way.
regards Peewee
i'm interested in looking at the performance of my stored procedure. Whats the best way for mesauring this, and whats the best ways to speed them up ( general tips probably will help me out). Whats a good and bad response time for sybase by the way.
regards Peewee
> Whats a good and bad response time for sybase by the way
to do what ?
on how many rows ?
using ASA or ASE ?
what version ?
on what hardware, operating system, disk and memory config ?
etc
that is a pretty open question
to do what ?
on how many rows ?
using ASA or ASE ?
what version ?
on what hardware, operating system, disk and memory config ?
etc
that is a pretty open question
lots of information on working with (and around :) the optimiser at
http://my.sybase.com/detail?id=2602
http://my.sybase.com/detail?id=2602
Peewee
you need to provide some feedback here
you need to provide some feedback here
ASKER
chaps,
apologies for the lack of repsonse here, i seemed to have forgotten about this one for some reason.
Its seems that my problem could be out of date statistics, here's my final question:
how do i check when the last time my statistics for a given table ie test_table where run?
thanks in advance peewee
apologies for the lack of repsonse here, i seemed to have forgotten about this one for some reason.
Its seems that my problem could be out of date statistics, here's my final question:
how do i check when the last time my statistics for a given table ie test_table where run?
thanks in advance peewee
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. look at the execution plan by using SET SHOWPLAN ON before execting the procedure, ensure it is using optimal indexes
2. also measure the timings of the procedure SELECT getdate() before and after the EXEC
3. don't get caught in the trap that cache has on the 2nd and subsequent executions
Fixiing
well this is a lot harder, it is usually the wrong indexes are being used by the optimiser, or the ideal indexes dont exist. You can force indexes using "index hints"
eg. SELECT * FROM yourtable (INDEX yourindex) WHERE ...
but many developers/managers try to avoid index hints. evaluate the entire logic of the procedure, consider removing cursors if you are not executing a child procedure within the loop, try and avoid updating your #temp tables, that is quite slow
we could go on for every on this subject, but we wont
good luck