Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Beginners Guide to Performance

Posted on 2003-12-04
6
Medium Priority
?
1,101 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Read on to get a few ideas on how to promote your next corporate event.
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

718 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