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
     
 
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: CMake是一个跨平台的生成代码工具,用于管理项目构建过程,它可以自动化编译过程中的各种任务,如编译、链接和打包等。在使用CMake时,有一些最佳实践需要遵循,以确保项目构建的正确性和高校。CMake Best Practices PDF是一份指南,旨在帮助用户了解如何使用CMake,并通过最佳实践来管理和构建项目。 该指南的主要内容包括使用现代CMake方法编写清晰、可读和可维护的CMake脚本,按功能分割项目并将其组织成库、模块和应用程序,并基于目标架构编写通用CMake文件,以确保跨平台的兼容性。此外,该指南还提供了一些实用技巧和工具,如依赖管理、安装和打包,以帮助开发人员进行更好的构建管理和项目发布。 总的来说,CMake Best Practices PDF是一份非常有价值的指南,旨在帮助开发人员遵循最佳实践来编写和管理CMake项目,以提高项目构建的高校和可维护性。 ### 回答2: 《CMake最佳实践(CMake Best Practices)》是一份技术文档,主要介绍了使用CMake构建C++项目的最佳实践。该文档由Daniel Pfeifer和其它CMake社区成员合作编写,为开发人员提供了一系列关于如何使用CMake构建现代C++应用程序的经验和建议。 该文档详细介绍了CMake的基本工作原理,并提供了使用CMake构建项目的步骤。同时,该文档还着重强调了在编写CMakeLists.txt文件时应该注意的细节,并提供了一些令人信服的理由。 例如,该文档建议编写简洁、模块化的CMakeLists.txt文件,避免使用过多的IF语句和过多的变量。此外,该文档也强调了对目标平台进行适当的测试、生成合适的安装脚本以及使用包管理器等最佳实践。 通过该文档的指导,开发人员可以更好地理解CMake的使用,确保其具有良好的可移植性、可扩展性和可维护性。该文档是CMake社区的重要资源之一,为C++开发人员提供了优秀的参考资料。 ### 回答3: CMake是一款功能强大的构建工具,可用于生成跨平台的软件代码。为了确保项目的顺利构建和维护,需要遵循一些最佳实践。这些实践包括: 1. 确保每个项目都有自己的CMakeLists.txt文件,以便更好地组织项目的结构和依赖关系。同时,应根据需要使用外部项目和库。 2. 编写可移植的CMake代码。这可以帮助确保代码在不同的平台上能够正确地构建和运行。 3. 使用版本控制工具管理代码,并确保将CMake配置文件包含在版本控制系统中。这可以确保在不同的开发环境之间保持一致。 4. 将构建和安装相关的命令分离到不同的CMake文件中,以便更好地管理项目。可以使用类似于“build.sh”或“install.sh”的脚本来组织这些文件。 5. 使用CMake变量来拆分项目配置,以便更好地管理和维护代码。 6. 如果有必要,可以使用预处理器语句来动态配置CMake文件。这可以帮助实现更高级的构建过程。 7. 编写清晰、易于理解的CMake文件。必要时可以添加注释或文档,以便其他开发人员能够理解和维护代码。 总之,CMake是一款非常有用的构建工具,可以帮助简化复杂的项目结构和依赖关系。但是,为了确保项目的顺利构建和维护,需要遵循一些最佳实践,并编写清晰、易于理解的CMake文件。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值