Sybase Best Practices - Designs

Besides of Sybase Best Practices - Commands, I gonna post another article about designing upon Sybase.

 

Data type design overview

• Data type assignment is appropriate and efficient;
• User defined data type are the same across databases;
• Table locking scheme is appropriate.

#1 Data type assignment

• IO if a big factor in performance
• Use small data types whenever it fits your design
  • Varchar and binary types require more row overhead than fixed-length types
  • Whenever possible, use fixed-length, non-null types for short columns that will be used at index keys
• Numerics slightly faster than strings internally
• Better avoid varchar, binary and other variable length types
• ALWAYS declare not null

#2 User defined data types are the same across databases

• Be sure that related datatypes of the join columns in different tables are compatible. If server has to convert a datatype on one side of a join, it may not use an index for that table.

SQL design overview

• SARGS
• Right data types are declared
• Right indexes created and used
• Right indexes types are created
• Taking note of OR cases

#1 SARGS (Search ARGuments)

• ALWAYS use SARGS
  • Optimizer usable search arguments
  • Enable indexes to be used
• Examples

• site = 'LDN'
  deal_date > '2013-01-01'
  amount > 3000
  amount is null

• Conditions <= or >= is faster than > or <
• What are not SARGS?
  • Predicate with an aritmetic computation

  • Salary * 12 > 30000

  • Subselect predicate using IN, ALL, ANY or EXISTS or NOT EXIST

  • select suppname from suppliers where "v245" in (select partno from parts where parts.suppno = suppliers.suppno)

  • Predicate that self joins, but doesn't use table aliases
  • Predicate that has functions

  • where SQRT(col) > 10

  • Predicate involving not-equals

  • where dept != 10

  • Aggregates AVG, SUM, MIN, MAX, COUNT

#2 Appropriate data types

• These are applicable in both db scripts, triggers, view and stored procedure.

#3 Appropriate indexes created and used

• Indexes are useful to speed up queries
• DO NOT just create an index for every query you create just to fulfill your where clause
• Remember there is cost and drawback for too many indexes
• Indexes are used for
  • WHERE clause
  • JOINS
  • ORDER BY
  • GROUP BY
  • Aggregate
• No need to create index for
  • Very small table that can fit into a cache
  • No direct accesss to a single random row
  • No ordering on result sets
• Need to create index for
  • Used frequently
  • Highly critical query
  • Tables that are read-only or read-mostly can be heavily indexed, as long as your database has enough space available. If there is little update activity and high select activity, you should provide indexes for all of your frequent queries. Be sure to test the performance benefits of index covering. 
• If an index key is unique, define it as unique so the optimizer knows immediately that only one row matches a search argument or a join on the key
• Keep the size of the key as small as possible. Your index trees remain flatter
  • Keep note of composite indexes that have too many columns
  • Keep note of indexed columns that have varible datatypes
• For composite indexes and possible index usage, note the following case:
  • For an index with consists of column ABC, the following order by clauses can use this index
    • A
    • AB
    • ABC
  • The following cannot use the index
    • AC
    • BC

#4 Types of indexes

• There are two types on indexes
  • clustered (table ordered) index 
  • non clustered index
• ONLY ONE clustered index per table
• Clustered indexes
  • Choose indexes based on the kinds of where clauses or joins you perform
    • The primary key, if it is used for where clause and if it randomizes inserts
    • Columns that are accessed by range

    • col1 between 100 and 200 col2 > 62 and <70

       
    • Columns used by order by
    • Columns that are not frequently changed
    • Columns used in joins
  • If there are several possible choices, choose the most commonly needed physical order as the first choice
  • As a second choice, look for range queries. During performance testing, check for "hot spots" due to lock contention
  • DO NOT CREATE CLUSTERED INDEXES ON IDENTITY COLUMN!
  • DO NOT CREATE CLUSTERED INDEXES ON A FREQUENTLY UPDATED COLUMN!
• Non clustered indexes
  • When choosing columns for non-clustered indexes, consider all the uses that were not satisfied by your clustered index choice. In addition, look at columns that can provide performance gains through index covering.
  • Consider using composite indexes to cover critical queriesand to support less frequent queries.

#5 Taking note of OR clauses

• Using OR in where clauses always result in using worktables to compile the results
• Worktables have IO overhead - minimal on small tables, but may cause impact on larger tables
• Result in possible duplicates and require Sybase to internally remove duplicates

Joins design overview

• Make sure that the column data type assignment is the same
• Make sure that the joining are manageable (4 tables)
• Make sure extra information are provided
• When self-joining, making sure aliases are used
• Make sure the inner table and outer table are properly set
• OR clauses and Unions in joins

#1 Make sure that the column data type assignment is the same

• Ensure that to be joined columns have the same datatype
• Beware of the same datatype, but different nullable settings for columns
• Nullable specific points:
  • Datatype char null is stored as varchar
  • Datatype binary null is stored as varbinary
  • Joining char not null with char null involves a conversion!!
• This does not affect numeric and datetime datatypes

#2 Make sure joins are not more than 4 tables

• Sybase is optimized to process at most, join of 4 tables at a time
• If there are more than 4 tables to join, Sybase will not explore certain permutations - possible to use a less-than-optimal query
• If possible, preempt and use a temp table

#3 Make sure extra information are provided

• Any additional information provided to Sybase will encourage joins to use indexes - especially when there are placed in the WHERE clause
• Also include any transitive properties of join
• Example 1
•    

• where table1.name = table2.name
  and table2.name = table3.name
  and table1.name = table3.name <-- added

   
• Example 2

• select name, size  
  from infotab, othertab  
  where infotab.name = othertab.name  
  and infotab.name = "Joe"  
  and othertab.name = "Joe" <- added

#4 When self-joining, make sure aliases are used

• If there is a self-join without a table alias, indexes are not used
• Ensure good habit of placing aliases for all table

#5 Make sure inner and outer tables are set

• If a join between different data types is unavoidable, a workaround can be to force the conversion on the other side of join
• Performance would be improved if the index on huge_table could be used instead

#6 Taking note of OR for joins

• SQL Server cannot optimize join clauses that are linked with OR

• select *    
  from tab1, tab2    
  where tab1.a = tab2.b    
  or tab1.x = tab2.y

   
• If possible, you may use UNION instead - Sybase optimizes each query in UNION separately

• select *  
  from tab1, tab2  
  where tab1.a = tab2.b  
  union all  
  select *  
  from tab1, tab2  
  where tab1.x = tab2.y