Solved

Beginners Guide to Performance

Posted on 2003-12-04
6
1,089 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 6 394
sql anywhere query 5 140
Query: Was the data in the Table inserted in ascending order? 11 99
How to get the table names associated with a server 3 66
Read about the ways of improving workplace communication.
This article outlines the process to identify and resolve account lockout in an Active Directory environment.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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