Solved

Beginners Guide to Performance

Posted on 2003-12-04
6
1,084 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ODBC connection for Raiser's Edge 6 --SqlAnywher 5.0 8 809
SQL Left join on same table 4 301
MS SQL Linked server 3 125
remote logins for proxy table in sybase 5 37
Note: This is the third blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   We’ve been talki…
With the rapid rise in mobile usage, mobile devices are here to stay and have become an integral part of doing business. Here are 9 great apps for your BYOD environment.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

16 Experts available now in Live!

Get 1:1 Help Now