1. technology and computing
2. software
3. databases

How to avoid the most
common performance
pitfalls in ASE
…Which standards are useful
and which to throw away...
Steven J. Bologna
Sybase Inc.
Principal Consultant
Overall Performance
• Performance is like grading papers…
• Know your application...
Every system can be improved.
• Personally tuned over 17,500+ systems
Outline
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Connection Parameters (Client to server)
SQL Defaults (Tables/Datatypes)
Input Parameters
Order By
Object Oriented
JDBC
ODBC
Chained
Auto commit (PB, JDBC, Perl)
Isolation Level (JDBC)
Commit Frequency
Timeouts
Blocking timeouts
Network Configurations
Prepared Statements
Surrogate primary key
Cursors
Memory Defaults
Server Configurations
Standards
• Lots of Standards..
•
•
•
•
ANSI, , SQL-86, SQL-89,SQL-92
SQL-99/SQLJ/SQL3, JDBC, ODBC
SQL 2003 - XML, Windowing,
SQL 2006 - XML/SQL, XQuery
• Understand the reasons behind them.
• SQL 2003 -Dropped the bit/bit varying datatype
• SQL J, Rollups,XML - newest features/Most
undefined/incomplete
• Be careful
• Some standards change.
• Some are not complete
Connection Parameters (Client to server)
• Default: Do nothing, use multiple of the same username
• Web based application, Connection Pooling, etc.
• Do you track troublesome processes?
• Same ID used by multiple processes?
• How can you fix?
• Trick: (Can set multiple times!…set commands)
•
•
•
•
Isql -Hmyhostname_steveB
set clientname cl_name
set clienthostname host_name
set clientapplname appl_name
(set only once)
Connection Parameters (Client to server)
• CTLibrary:
• ct_con_props(connptr, CS_SET, CS_APPNAME, app_name,
strlen(app_name), NULL);
• ct_con_props(connptr, CS_SET, CS_HOSTNAME, host_name,
CS_NULLTERM, NULL);
• Login triggers?
• Grab the IP address.
• Set the user name
• declare @var varchar(255)
• set export_options on
declare @var2 varchar(255)
select @var2 = case when ipaddr = '192.168.0.62' then 'User1'
else 'User2'
end
from master..sysprocesses where spid = @@spid
select @var2
SQL Defaults (Tables/ Data types)
• Character fields for everything
• Many languages, New programmers
• Positive: Easy to program
• Negative: Much slower performance, deferred updates, convert
• Extreme:
• create table x1 (
• col1
• col2
• col3
varchar(255)
varchar(255)
varchar(255)
null,
null,
null)
SQL Defaults (Tables/ Data types) - Standards?
Java
java.util Currency
java.lang.string
java.lang.string
java object
byte array
• Best:
•
•
•
•
Hibernate type
currency
string
text
serializable
binary
ASE Type
varchar
varchar
text
varbinary
varbinary
integer or numeric
char vs. varchar
char vs. varchar or text (size may make a difference)
binary vs. varbinary vs. text or image
SQL Defaults (Tables/ Data types)
• Clues to tip you off on Issues:
• Variable length columns for everything!
• Null-able columns on lots of fields/Temporary tables
• Positive: Don’t have to worry about defaults/fields
• Negative: Slower overall performance
• Extreme
• varchar(1) nullable
• Why?
(for Y/N field, or Bit)
• Variable length
• Null
• Really only Y/N. Could even be a bit
• Be consistent on usage for joins!
SQL Defaults (Tables/ Data types)
• Does data-type size matter?
• Varchar(10) vs varchar(255) or char(255) higher?
• Possibly short text (varchar(255) + text/image)
• Size matters for:
•
•
•
•
•
•
•
•
DSS Systems
overall speed (insert)
update speed
Storage Costs
Spindles can currently only run at maximum of 15,000 RPM
Speed has not increased in since since Feb 2000*
Storage vendors Added in NVRAM
IO Rates keep increasing
• *Throughput has increased but not raw speed!
Input Parameters
• Char Fields for all columns
• makes coding slightly easier
• makes the optimizer have trouble
create procedure X (@col1 varchar(10))
as
select @new_col1 = @col1
select *
from table
where col1 = @new_col1
<< DEFAULT optimization
•
Input Parameters
• How to fix this? Now we get exact details.
create procedure X (@col1 varchar(10))
as
execute(“select * “ +
“from table “+
“where col1 = “ + @col1
) -- Exact optimization!
Or Variation
create procedure X (@col1 int)
as
declare @new_col1 varchar(30)
select @new_col1 = convert(varchar(30),@col1)
execute(“select * “ +
“from table “+
“where col1 = “ + @new_col1
NOTE: Show Default Optimization/Skew issues
) -- Exact optimization!
Input Parameters
• Pass by reference vs. by name.
• Positive: Easy to use
• Negative: Slower to parse the more the input/# of
columns
• Some languages Pass by name especially with prepared
statements.
• Exec proc1 @in1 = 1, @in2 = 2, @in3 = “input3”, @in4 =
‘08/03/2008’
• vs.
• exec proc1 1, 2, “input3”, ‘08/03/2008’
• If its going to run 20 minutes…
Order By
• Consider this…
•
•
•
•
•
set rowcount 100
select col1 from table order by col2 desc, col1
vs.
select top 10000 col1, col2 into #tmp1 from table
select top col1 100 from #tmp1 order by col2, col1
• Assume col2 is un-indexed and will return 1 Million rows
• Do you need to go through 1Million rows?
• Can you Client?
• Is the result set highly duplicated? (I.e. smith, Jones etc.)
Object Oriented
• Object Oriented Designs - Possibly the XML/Java Option
in ASE 15.0
•
•
•
•
•
•
Columns tend to be WIDE (varchar(255) or Text)
Indexes tend to be WIDE
Pack/unpack fields all the time.
Usage of XML, SQL
Field Searching.
Currently can put Word index on Wide Columns (Like IQ)
• Strip out key columns
• First name, last name, City, State
• Makes searchable and index-able
• Better to use Materialized/computed columns (ASE 15.0)
Object Oriented
•
•
Example of columns(Long)
•
•
insert into user_object (UserName,Address)
values (‘Smith~John~J’,’43 Green~Westland~Michigan~48098’)
•
•
Search of columns is troublesome
Especially if there are Lots of duplicates or “Allow dup rows” for indexes
•
Example Materialized/computed:
sometimes Developers like to use Identity fields or long columns:
•
•
More on this later
Note: Java addition look this one up!:
• http://www.sybase.com/detail?id=1037457
JDBC
• Tends not to use Stored procedures (not object
oriented…)
• A procedure is an object… (can return an object..)
• Upside: Very dynamic, coding faster
• Downside: Have to Parse, Normalize, Create a Plan for every
statement
• Sometimes development does not use a Type 4 driver
(Sybase Native)
• Early Java Systems used
• JDBC/ODBC
• JDBC/C Code to Server
• caused lots of Datatype conversions.
JDBC
• Sometimes the developers would issue
• 1 SQL Statement for each object so….
100 SQL statements = 100 results of 1 Row
vs.
1 result set of 100 rows
• Common ProblemsNulling of Result sets/SQL Statement
• rs = NULL;
• statement = NULL;
• Downside: Causes memory leak and slower performance
• system.out.printline(“stuff”);
• slows down overall performance.
• Puts lots of stuff in the system console
JDBC
• Scrollable Cursors/Backwards (use Newer driver)
• Never have seen this being used…
• Have you?
• Can just:
select column from table
order by desc
or..
• Select all result sets into a vector then walk through
vector…(backwards and forwards)..
A bit more memory… unless result set 1 Billion rows.
ODBC- ASE/IQ/ASA
• Stay away from:
• “Use Cursors”
Recommend: leave off
• Positive:
Allow fetch of a row then stop.
Setup time of cursor, holding memory, Waiting on client to
• Negative:
finish results, Possible network “glitch, shared page lock longer
• Rules:
• Rule of thumb:
setup usually high, rare to use
50 rows or more
• Set the character set/Sort Order to the server character set
• No need to use anything different unless want to have Multilingual support
• Most applications don’t need. Use exactly the same as server to ensure
identical characters
• Slow down when using “Case Insensitive”
• in Java, use SunIoConverter to convert (not pure java)
ODBC- ASE/IQ/ASA
• Enable dynamic Prepare (Recommend: Leave off!)
•
•
•
•
•
insert into table (col1, col2, col3) values (?, ? ? )
Use only when executing the statement at least 10 times
Used for high speed select, insert, update, delete
Possibly when you would want to create a procedure
Setup time, creates a lightweight procedure
• Fetch Array size (25 to 50 or even 100)
•
•
•
•
More the better
Generally lots of result set get improvements
Negative Might have to wait while filling (Generally rare)
Increase when you move up Packet size = Double benefit
• More on Packet size later...
Chained
• Many languages, Middle-ware, API’s
• Java, JDBC, Perl (Some versions), Spring
• By default DBD::Sybase 1.05 is ON!!!!
• Switched from previous versions
• After connect starts a transaction
• Even for delete, insert, open, fetch, select & update
• Generally also causes “DDL in tran” to be turned on as well
• This is due to “select * into #tmp from table
• or similar statement
• Causes locking, blocking to occur at a much higher rate
Chained - perl
• after connect:
• set chained off
• in upper language (perl,
set autocommit on
• $dbh = DBI->connect('dbi:Sybase:', $user, $pwd,
{syb_chained_txn => 0});
• $h->{AutoCommit} = 0
• you handle:
• $dbh->commit
• Summary: Chained off, AutoCommit On, Can over-ride.
Chained-Java
• Set the procedures to “ANYMODE”
• Allows procedure to be run in chained or unchained mode.
• Better for performance if “unchained”
• Setting this for EACH procedure avoids error message in
java/JDBC
• Sp_procxmode procname, “anymode”
• set chained off
• Connection.setAutoCommit(false)
<-- Chained on
• 25-75% of any system is just select statements
Chained
Interesting solutions(spring):
1) one connection (for unchained) transactions - Mostly
Select
second for chained transactions - (insert,update, delete)
2)extending DataSourceTransactionManager, overriding
doBegin()
autocommit reset = 1
executes a "begin tran" in SqlUpdate statement
3)ConnectionProxy object which overrides setAutoCommit,
commit, rollback operations.
Proxy object keeps AutoCommit to true
4)Others
Isolation Level (JDBC, ANSI spec apps)
• Repeatable reads
• Holds shared lock on all of result set for the duration of select
• inside a transaction this is a performance drag/block
• Needed if
•
•
•
•
going back to first row for some reason
Need 100% accuracy in all results for the life of the transaction
Trying to maintain balance sheet information
You want to stop ALL other users from changing data
• Set this:
jdbc:sybase:Tds:mysrvr:1803/mydb?LITERAL_PARAMS=true&PA
CKETSIZE=16384&REPAT_READ=FALSE
Commit Frequency
• Most applications
• begin tran
• do operation
• commit tran
• Upside:
• Easy to code
• Transactions easy to manage
• Don’t need placeholder or retry logic (I.e. did I do this transaction
already)
• Downside
• Increases the Transaction rate
• Increases the I/o on the disk drives/SAN
Commit Frequency
• Better to do this:
-- create table t1 (col1 int not null)
set nocount on
declare @ins_cnt int
select @ins_cnt = 1
begin tran
while ((@ins_cnt < 100) )
begin
insert into t1(col1) values (1)
if @ins_cnt %10 = 0
begin
commit tran
begin tran
select @ins_cnt = @ins_cnt + 1
select @ins_cnt
end
else
select @ins_cnt = @ins_cnt + 1
end
commit tran
Commit Frequency
• This logic works well for
•
•
•
•
batch jobs
High transaction rate operations
Systems that don’t have lots of blocking already
Tables that are designed well
• This is just the basics… also need
• Retry logic, error handling etc..
• Every table is different so you can play with “@ins_cnt %10 = 0”
Timeouts - Client
• Client Timeouts
• Defaults infinite wait for SQL statement to return
• Pro - No coding changes
• Negative - Clients might wait forever, from long running statement
• Once you disconnect…what happens?
• Does the server react quickly?
• Do you re-issue the same operation Right away?
• To solve reduce the server timeouts to about 1/3 -3/4
client timeouts
• Client timeout at 60 seconds
• Server could timeout at 20-29 seconds…
• enough time for a full rollback...
Timeouts - Client
• Java:
stm.setQueryTimeout(10);
• ct_library:
retcode = ct_config(cntx_ptr, CS_SET, CS_TIMEOUT, (CS_INT
*)&timeout_value, CS_UNUSED, NULL);
• Perl
$dbh = DBI->connect("dbi:Sybase:timeout=240", $user,
$passwd);
• Need to know what is the longest running statement.
• Generally set once at connection time
Server timeouts(locks)
• How long to wait?
• 1-2 minute(s)?
• 5 hours?
• How long is most of the transactions? Is this an OLTP, DSS or
Hybrid (both)
• Server Timeouts
• initial connect - infinite
• Lock timeout
• Process to kill the main root-blocker?
• Can you estimate the time it takes to rollback?
• Long running Transactions
• What to do?
• Redesign?
Server timeouts(locks)
• Sp_configure “lock wait period”, 2147483647
• message:
Msg 12205, Level 17, State 2
Could not acquire a lock within the specified wait period. SERVER
level wait period=10 seconds, spid=17, lock type=shared page,
dbid=4, objid=576002052, pageno=902, rowno=0. Aborting the
transaction.
• Most applications are not designed to test for Lock
timeout.
• Similar to 1205 issue, “deadlock”
• Should test for this in a six sigma application (24X7)
Network Configuration(TCP/IP)
• Common issue (Network Timeouts)
• http://www.sybase.com/detail?id=611
• http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.d
c35823_1500/html/uconfig/X23753.htm
• Order of occurrence
•
•
•
•
•
•
Network hick-up/ User is frustrated/Reboot/Application Recycle
client timeout occurs
<<-- Controllable - mostly infinite
Leaves server socket open
Some time passes
<<--Controllable - 15 min - 2 hours
OS notifies Sybase of issue
Sybase kills the connection
• In the mean time…
• Client reconnects
• issues transaction
• Stops waiting on Open Transaction above….
Network Configuration(TCP/IP)
• What to do?
•
•
•
•
•
•
•
Leave it… NO!
Fix it. Its easy!
Is 15 minutes Reasonable? Probably not...
Transactions should be short.
Batch jobs might be exception
Remember batch jobs and OLTP. (Mixed Workload/Hybrid)
See previous example for the proper code.
Network Timeouts(TCP/IP)
• The issue (see RFC 793):
• Both Sides of Network get different status’s
FIN_WAIT
FIN_WAIT_1
FIN_WAIT_2
TIME_WAIT
CLOSE_WAIT
Sometimes there are lots of timeouts:
netstat -an | grep -i wait
Some TCP/IP implementations different timers for “EACH” Wait
state
Prepared Statements
• Positives
• speed execution
• Parsed/normalized/Optimized plans
• Negatives
•
•
•
•
•
memory usage in Server
Procedure cache
Chunk of memory in use
Not infinite memory
Setup Time
Prepared Statements
• Certain languages automatically create them!
• Not useful if you are throwing away SQL
• Think… How many times to use this…
• Rule of Thumb
• used at least 10-50 times.
• Site:
• prepared 32,000 statements (all slightly different)
Prepared Statements(JDBC, Perl)
• Set DYNAMIC_PREPARE = “false”
• PreparedStatement ps_stmt =
Connection.prepareStatement(sql_stmt);
• props.put(“DYNAMIC_PREPARE”,”false”);
• jdbc:sybase:Tds:mysrvr:1803/mydb?LITERAL_PARAMS=true&
PACKETSIZE=16384&DYNAMIC_PREPARE=FALSE
Surrogate primary key
• What is it?
• Identity or similar, usually with multi-part primary keys
• Assign own keys
• Address: Street, city, state/province/region, zip, zip+4 country
code
• Positives
• Key easy to use to join.
• Makes several columns to one Integer, Numeric Field
• Join speed faster
• Negative:
• usually assigned sequentially/monotonically
• Leads to hot spot(s) on table
Surrogate primary key
• To fix it use one (or more than one) of the following
•
•
•
•
•
•
•
switch to identity (numeric/integer)
isolation level 0, readpast
pad key generation
newid assignment (system assigned, unique across servers)
partitioning of table
ASE Partitions (12.0-12.5.4)
ASE Partitioning Feature (15.0)
Memory Defaults
• Network packet size
• $dbh = DBI->connect(“dbi:Sybase:packetSize=16384”,$user,
$passwd);
• isql -U$user -P$passwd -A16384
• ASE Maximum
• ASA/IQ Maximum
16384
16000
• Server Memory size
• Look how much memory the Machine has
• Determine how much is currently is in use
Server Configurations
• Many server configurations that should be looked at:
• open objects
• open indexes
• open databases
• procedure cache size
• statement cache size
•
•
•
•
•
Maximum network packet size
Additional network memory
DDL in tran
(dboption)
identity in nonunique index (dboption)
auto identity (dboption)
Sources
• http://www.hibernate.org/hib_docs/v3/reference/en/html/t
utorial.html
• http://www.java2s.com/Code/Java/Hibernate/JavaTypeV
SHibernateType.htm
• http://www.seagate.com/www/enus/about/corporate_information/company_milestones/
Questions? Comments?
• Questions?
• Reach me at:
[email protected]
[email protected]
cell 248-797-2802
[email protected]