Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Beginners Guide to Performance

Posted on 2003-12-04
6
Medium Priority
?
1,109 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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
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 200 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

926 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