Solved

Beginners Guide to Performance

Posted on 2003-12-04
6
1,081 Views
Last Modified: 2007-12-19
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

0
Comment
Question by:Peewee
  • 5
6 Comments
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9880782
Analysis
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
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9880791
> 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
0
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9882224
lots of information on working with (and around :) the optimiser at
http://my.sybase.com/detail?id=2602
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:ChrisKing
ID: 9963483
Peewee
you need to provide some feedback here
0
 
LVL 5

Author Comment

by:Peewee
ID: 10542355
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

 
0
 
LVL 6

Accepted Solution

by:
ChrisKing earned 50 total points
ID: 10546598
Peewee

there is no simple way that I am aware of, but you could
- compare the rowcounts from the first two commands
- look at the dates from the third command

regards
ChrisKing

~~~~~~~~~~~~~~~

exec sp_spaceused test_table
go
select o.name,i.name,s.rowcnt
from sysobjects o, sysindexes i, systabstats s
where o.type = 'U' and o.name = 'test_table'
and i.id = o.id and i.name = o.name
and s.id = i.id and s.indid = i.indid
go
select o.name,s.moddate
from sysobjects o, sysstatistics s
where o.type = 'U' and o.name = 'test_table'
and s.id = o.id

SAMPLE OUTPUT
~~~~~~~~~~~
name      rowtotal      reserved      data      index_size      unused
test_table 2685828      503846 KB      303560 KB      176914 KB      23372 KB
                              
name      name      rowcnt                  
test_table      test_table      2684273.0                  
                              
name      moddate                        
test_table      7/03/2004 3:45:43.946 AM                        
test_table      7/03/2004 3:45:43.946 AM                        
test_table      7/03/2004 3:45:43.946 AM                        
test_table      7/03/2004 3:45:06.946 AM                        
test_table      7/03/2004 3:45:06.946 AM                        
test_table      7/03/2004 3:45:06.946 AM                        
test_table      7/03/2004 3:43:15.430 AM                        
test_table      7/03/2004 3:43:15.430 AM                        
test_table      7/03/2004 3:43:15.430 AM                        
test_table      7/03/2004 3:43:04.430 AM                        
test_table      7/03/2004 3:43:04.430 AM                        
test_table      7/03/2004 3:43:04.430 AM                        
test_table      7/03/2004 3:44:19.946 AM                        
test_table      7/03/2004 3:44:19.946 AM                        
test_table      7/03/2004 3:44:19.946 AM                        
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Cannot view sybase SCC in browser using Linux 5 1,760
SyBase SQL Syntax 7 309
detecting a database behind a website 3 357
SYBASE ASE HA Configuration 8 369
What is Backup? Backup software creates one or more copies of the data on your digital devices in case your original data is lost or damaged. Different backup solutions protect different kinds of data and different combinations of devices. For e…
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now