Sybase Business Intelligence Solutions - Database Management, Data Warehousing Software, Mobile Enterprise Applications and Messaging
Sybase Brand Color Bar
delete

Search for    in all of Sybase.com
view all search results right arrow
  blank
 
 
 
 
 
 
 
 
 
 

 
 
CLICK TO EXPAND NAVIGATION
CLICK TO EXPAND NAVIGATION
 
 
 
 
Support > Technical Documents > Document Types > Tips > SQL Anywhere INSIDER December 2010  
RSS Feed
 
 
 
techtipslogo2.jpg

December 2010 Edition

Testing Query Performance with FETCHTST

As part of the included "Samples", SQL Anywhere (SA) not only provides the demo database (e.g. demo11) and code examples on how to implement SA functionality, but also helpful command line utilities that can be used for performance testing.

By default they can be found in the following location:

C:\Documents and Settings\All Users\Documents\SQL Anywhere 11\Samples\SQLAnywhere\Performance*

FETCHTST is a utility that can be used to test the performance of a SELECT statement or stored procedure call. Its advantage over other query tools (e.g. Plan Viewer/dbisql) is that it breaks the execution time into the time spent on the database server processing the query vs. the time needed to fetch the rows. It also reports the I/O cost of the query execution on the database server. With this information, a database administrator can diagnose the performance bottleneck, and whether it is an issue within the query optimizer, network, or I/O system.

The FETCHTST utility on Windows is deployed in the following location by default:

C:\Documents and Settings\All Users\Documents\SQL Anywhere 11\Samples\SQLAnywhere\PerformanceFetch

The tool is easy to run from the command-line. It takes an input test SQL script (Which will default to “test.sql”) which has to contain the SELECT statement(s) that will be tested for performance.

Command-line switches to the FETCHTST utility allow the user to specify different output options (e.g. record multiple runs, calculate average, std dev, number of interactions, etc.)

Usage: FETCHTST [options] [<file1> [.. <fileN>]]
Options:

- a [ams] : add to output m:min/max, or s:std.dev, or a:all
- b nnn : fetch nnn records at a time
- c conn_str : database connection string
- ce : continue execution after SQL error
- d describe_type: statement or cursor
- e fname : execute file of SQL before running sql statements
- es cmd_str : execute the command string at the start of each iteration
- ee cmd_str : execute the command string at the end of each iteration
- f file : output rows to 'file' (otherwise rows are not output)
- g : generate category summary (group by SQL text prefix)
- ga : same as -aa -g -gc -u
- gc : print engine cpu usage per statement
- gm nnn : skip the first nnn 'message' statements
- gs nnn : skip the first nnn 'select' queries
- gt : print statement totals only
- h : this help usage information
- i nnn : think time (milliseconds) between statements
- is n : set isolation level at beginning to n
- j nnn : repeat each file nnn times
- k : disable prefetching
- l nnn : stop after nnn records
- m : display summary only
- n : execute queries only
- o outfile : record fetch duration in file
- oa outfile : record all output in file
- oc outfile : record SQL step times to comma delimited file
- oc help : display help on how to load -oc outfile into a table
- p : display plan
- q : quiet mode
- r nnn : output status every nnn rows
- ro : READ ONLY cursors (default FOR UPDATE)
- s nnn : skip by nnn records
- t cursor_type : INSENSITIVE or SCROLL
- u : display timers in microseconds (default milliseconds)
- v : display statement before executing
- w nnn : number of OPEN/CLOSEs
- x nnn : number of DESCRIBEs to execute for each query
- yd nnn : wait nnn milliseconds after engine starts
- ym : start a new engine for each iteration or file
- yn eng_name : name the engine 'eng_name'
- ys start_str : start the engine using the given start string
- z nnn : fetch as strings - max size nnn
<file1> .. <fileN> : name of file(s) containing sql statement(s)
- file(s) may contain multiple queries and other SQL, separated by a line containing only "go"
- default file is "test.sql" if none specified.
e.g.
c:\>fetchtst c "eng=demo11;userid=dba;pwd=sql;" test.sql

 

Output Examples

a) An "I/O-bound" Example

Note in the output below that there is very little time spent in the PREPARE/DESCRIBE/OPEN steps. In other words, the cost of parsing, optimizing and choosing the best query plan was minimal on the database srever. However, the cost of reading the first rows of the query (5.444 s) was high. This cost was mostly attributed to a large number of disk reads (2942 I/Os):

PREPARE: 0.021 seconds
DESCRIBE: 0.590 seconds
OPEN: 0.247 seconds
FETCH first row: 5.444 seconds, 2942 I/Os, 540 per second
FETCH remaining rows: 0.189 seconds
CLOSE: 0.002 seconds
DROP: 0.000 seconds
Total: 6.492 seconds, 2942 I/Os, 453 per second
Fetch rate: 10316 rows in 5.632 seconds, 1831.630 per second

In this instance, increasing the database server cache, or having a faster disk may speed up the query execution time.

b) A "Network-bound" Example

Note in the output below that the time spent in the PREPARE/DESCRIBE/OPEN steps is once again negligible, and the majority of the execution cost is associated with fetching the remaining rows from the network. This is an example of executing the query and “pulling” all of the rows across the network, where almost all of the cost is associated with the overhead of the network transfer.

PREPARE: 0.001 seconds
DESCRIBE: 0.001 seconds
OPEN: 0.001 seconds
FETCH first row: 0.001 seconds, 0 I/Os, 0 per second
FETCH remaining rows: 331.099 seconds
CLOSE: 0.000 seconds
DROP: 0.000 seconds
Total: 331.103 seconds, 0 I/Os, 0 per second
Fetch rate: 710572 rows in 331.100 seconds, 2146.097 per second

In this case, changing network prefetch settings, implementing a faster network or moving the client closer to the database server may reduce this execution time.

c) An "Optimizer-bound" Example

Note in the output below that there is a proportionally large duration of time in the DESCRIBE step: 72.165 seconds. During the DESCRIBE phase, the query optimizer needs to read in all of the referenced schema information about all the tables and all its referenced objects, foreign keys indexes etc. that are involved in the query. This particular example was run on a slow machine on a highly fragmented database, with over 150 referenced objects.

PREPARE: 0.000 seconds
DESCRIBE: 72.165 seconds
OPEN: 1.356 seconds
FETCH first row: 53.514 seconds, 5415 I/Os, 101 per second
FETCH remaining rows: 0.003 seconds
CLOSE: 0.000 seconds
DROP: 0.000 seconds
Total: 127.038 seconds, 5415 I/Os, 42 per second
Fetch rate: 49 rows in 53.517 seconds, 0.916 per second

After rebuilding the database (to reduce internal fragmentation), the execution time for the same query was significantly reduced:

PREPARE: 0.000 seconds
DESCRIBE: 10.823 seconds
OPEN: 0.564 seconds
FETCH first row: 16.819 seconds, 5977 I/Os, 355 per second
FETCH remaining rows: 0.003 seconds
CLOSE: 0.000 seconds
DROP: 0.000 seconds
Total: 28.209 seconds, 5977 I/Os, 211 per second
Fetch rate: 49 rows in 16.821 seconds, 2.913 per second

Summary

FETCHTST is a simple tool to use to assist with performance testing. It provides a lot of valuable information which may help to diagnose query performance bottlenecks. It can easily be added to a batch script and become a part of an application’s automated test suite.


 

DOCUMENT ATTRIBUTES
Last Revised: Dec 01, 2010
Product: SQL Anywhere
Technical Topics: SQL Anywhere
  
Business or Technical: Technical
Content Id: 1089228
Infotype: Tips
 
 
 

 
© Copyright 2014, Sybase Inc. - v 7.6 Home / Contact Us / Help / Jobs / Legal / Privacy / Code of Ethics