home *** CD-ROM | disk | FTP | other *** search
/ ftp.pasteur.org/FAQ/ / ftp-pasteur-org-FAQ.zip / FAQ / databases / sybase-faq / part8 < prev    next >
Encoding:
Internet Message Format  |  2004-04-21  |  61.6 KB

  1. Path: senator-bedfellow.mit.edu!dreaderd!not-for-mail
  2. Message-ID: <databases/sybase-faq/part8_1082468590@rtfm.mit.edu>
  3. Supersedes: <databases/sybase-faq/part8_1074677126@rtfm.mit.edu>
  4. Expires: 2 Aug 2004 13:43:10 GMT
  5. References: <databases/sybase-faq/part1_1082468590@rtfm.mit.edu>
  6. X-Last-Updated: 2003/03/02
  7. From: dowen@midsomer.org (David Owen)
  8. Newsgroups: comp.databases.sybase,comp.answers,news.answers
  9. Subject: Sybase FAQ: 8/19 - ASE Admin (5 of 7)
  10. Reply-To: dowen@midsomer.org (David Owen)
  11. Followup-To: comp.databases.sybase
  12. Distribution: world
  13. Organization: Midsomer Consultants Inc.
  14. Approved: news-answers-request@MIT.EDU
  15. Keywords: FAQ, DATABASES, SYBASE, ASA, ASE, REP
  16. Originator: faqserv@penguin-lust.MIT.EDU
  17. Date: 20 Apr 2004 13:45:06 GMT
  18. Lines: 1458
  19. NNTP-Posting-Host: penguin-lust.mit.edu
  20. X-Trace: 1082468706 senator-bedfellow.mit.edu 566 18.181.0.29
  21. Xref: senator-bedfellow.mit.edu comp.databases.sybase:106206 comp.answers:56952 news.answers:270292
  22.  
  23. Archive-name: databases/sybase-faq/part8
  24. URL: http://www.isug.com/Sybase_FAQ
  25. Version: 1.7
  26. Maintainer: David Owen
  27. Last-modified: 2003/03/02
  28. Posting-Frequency: posted every 3rd month
  29.    A how-to-find-the-FAQ article is posted on the intervening months.
  30.  
  31.                             Performance and Tuning                             
  32.                                                                                
  33.  
  34.  
  35.     1.5.1   What are the nitty gritty details on Performance and Tuning?
  36.     1.5.2   What is best way to use temp tables in an OLTP environment?
  37.     1.5.3   What's the difference between clustered and non-clustered indexes?
  38.     1.5.4   Optimistic versus pessimistic locking?
  39.     1.5.5   How do I force an index to be used?
  40.     1.5.6   Why place tempdb and log on low numbered devices?
  41.     1.5.7   Have I configured enough memory for ASE?
  42.     1.5.8   Why should I use stored procedures?
  43.     1.5.9   I don't understand showplan's output, please explain.
  44.     1.5.10  Poor man's sp_sysmon.
  45.     1.5.11  View MRU-LRU procedure cache chain.
  46.     1.5.12  Improving Text/Image Type Performance
  47.    
  48. Server Monitoring General Troubleshooting ASE FAQ
  49.  
  50. -------------------------------------------------------------------------------
  51.  
  52. 1.5.1: Sybase ASE Performance and Tuning
  53.  
  54. -------------------------------------------------------------------------------
  55.  
  56. Before going any further, Eric Miner (eric.miner@sybase.com) has made available
  57. two presentations that he made at Techwave 1999.  The first covers the use of 
  58. optdiag.   The second covers features in the way the optimiser works in ASE
  59. 11.9.2 and 12.  These are Powerpoint slides converted to web pages, so they
  60. might be tricky to read with a text based browser!
  61.  
  62. All Components Affect Response Time & Throughput
  63.  
  64. We often think that high performance is defined as a fast data server, but the
  65. picture is not that simple. Performance is determined by all these factors:
  66.  
  67.   * The client application itself:
  68.       + How efficiently is it written?
  69.       + We will return to this later, when we look at application tuning.
  70.   * The client-side library:
  71.       + What facilities does it make available to the application?
  72.       + How easy are they to use?
  73.   * The network:
  74.       + How efficiently is it used by the client/server connection?
  75.   * The DBMS:
  76.       + How effectively can it use the hardware?
  77.       + What facilities does it supply to help build efficient fast
  78.         applications?
  79.   * The size of the database:
  80.       + How long does it take to dump the database?
  81.       + How long to recreate it after a media failure?
  82.  
  83. Unlike some products which aim at performance on paper, Sybase aims at solving
  84. the multi-dimensional problem of delivering high performance for real
  85. applications.
  86.  
  87. OBJECTIVES
  88.  
  89. To gain an overview of important considerations and alternatives for the
  90. design, development, and implementation of high performance systems in the
  91. Sybase client/server environment. The issues we will address are:
  92.  
  93.   * Client Application and API Issues
  94.   * Physical Database Design Issues
  95.   * Networking Issues
  96.   * Operating System Configuration Issues
  97.   * Hardware Configuration Issues
  98.   * ASE Configuration Issues
  99.  
  100.     Client Application and Physical Database Design design decisions will
  101.     account for over 80% of your system's "tuneable" performance so ... plan
  102.     your project resources accordingly !
  103.    
  104. It is highly recommended that every project include individuals who have taken
  105. Sybase Education's Performance and Tuning course. This 5-day course provides
  106. the hands-on experience essential for success.
  107.  
  108. Client Application Issues
  109.  
  110.   * Tuning Transact-SQL Queries
  111.   * Locking and Concurrency
  112.   * ANSI Changes Affecting Concurrency
  113.   * Application Deadlocking
  114.   * Optimizing Cursors in v10
  115.   * Special Issues for Batch Applications
  116.   * Asynchronous Queries
  117.   * Generating Sequential Numbers
  118.   * Other Application Issues
  119.  
  120. Tuning Transact-SQL Queries
  121.  
  122.   * Learn the Strengths and Weaknesses of the Optimizer
  123.   * One of the largest factors determining performance is TSQL! Test not only
  124.     for efficient plans but also semantic correctness.
  125.   * Optimizer will cost every permutation of accesses for queries involving 4
  126.     tables or less. Joins of more than 4 tables are "planned" 4-tables at a
  127.     time (as listed in the FROM clause) so not all permutations are evaluated.
  128.     You can influence the plans for these large joins by the order of tables in
  129.     the FROM clause.
  130.   * Avoid the following, if possible:
  131.       + What are SARGS?
  132.        
  133.         This is short for search arguments. A search argument is essentially a
  134.         constant value such as:
  135.           o "My company name"
  136.           o 3448
  137.        
  138.         but not:
  139.           o 344 + 88
  140.           o like "%what you want%"
  141.       + Mathematical Manipulation of SARGs
  142.        
  143.            
  144.             SELECT name FROM employee WHERE salary * 12 > 100000
  145.            
  146.       + Use of Incompatible Datatypes Between Column and its SARG
  147.        
  148.            
  149.             Float & Int, Char & Varchar, Binary & Varbinary are Incompatible;
  150.            
  151.             Int & Intn (allow nulls) OK
  152.            
  153.       + Use of multiple "OR" Statements - especially on different columns in
  154.         same table. If any portion of the OR clause requires a table scan, it
  155.         will! OR Strategy requires additional cost of creating and sorting a
  156.         work table.
  157.       + Not using the leading portion of the index (unless the query is
  158.         completely covered)
  159.       + Substituting "OR" with "IN (value1, value2, ... valueN) Optimizer
  160.         automatically converts this to an "OR"
  161.       + Use of Non-Equal Expressions (!=) in WHERE Clause.
  162.   * Use Tools to Evaluate and Tune Important/Problem Queries
  163.       + Use the "set showplan on" command to see the plan chosen as "most
  164.         efficient" by optimizer. Run all queries through during development and
  165.         testing to ensure accurate access model and known performance.
  166.         Information comes through the Error Handler of a DB-Library
  167.         application.
  168.       + Use the "dbcc traceon(3604, 302, 310)" command to see each alternative
  169.         plan evaluated by the optimizer. Generally, this is only necessary to
  170.         understand why the optimizer won't give you the plan you want or need
  171.         (or think you need)!
  172.       + Use the "set statistics io on" command to see the number of logical and
  173.         physical i/o's for a query. Scrutinize those queries with high logical
  174.         i/o's.
  175.       + Use the "set statistics time on" command to see the amount of time
  176.         (elapsed, execution, parse and compile) a query takes to run.
  177.       + If the optimizer turns out to be a "pessimizer", use the "set forceplan
  178.         on" command to change join order to be the order of the tables in the
  179.         FROM clause.
  180.       + If the optimizer refuses to select the proper index for a table, you
  181.         can force it by adding the index id in parentheses after the table name
  182.         in the FROM clause.
  183.        
  184.            
  185.             SELECT * FROM orders(2), order_detail(1) WHERE ...
  186.            
  187.             This may cause portability issues should index id's vary/change by
  188.             site !
  189.            
  190. Locking and Concurrency
  191.  
  192.   * The Optimizer Decides on Lock Type and Granularity
  193.   * Decisions on lock type (share, exclusive, or update) and granularity (page
  194.     or table) are made during optimization so make sure your updates and
  195.     deletes don't scan the table !
  196.   * Exclusive Locks are Only Released Upon Commit or Rollback
  197.   * Lock Contention can have a large impact on both throughput and response
  198.     time if not considered both in the application and database design !
  199.   * Keep transactions as small and short as possible to minimize blocking.
  200.     Consider alternatives to "mass" updates and deletes such as a v10.0 cursor
  201.     in a stored procedure which frequently commits.
  202.   * Never include any "user interaction" in the middle of transactions.
  203.   * Shared Locks Generally Released After Page is Read
  204.   * Share locks "roll" through result set for concurrency. Only "HOLDLOCK" or
  205.     "Isolation Level 3" retain share locks until commit or rollback. Remember
  206.     also that HOLDLOCK is for read-consistency. It doesn't block other readers
  207.     !
  208.   * Use optimistic locking techniques such as timestamps and the tsequal()
  209.     function to check for updates to a row since it was read (rather than
  210.     holdlock)
  211.  
  212. ANSI Changes Affecting Concurrency
  213.  
  214.   * Chained Transactions Risk Concurrency if Behavior not Understood
  215.   * Sybase defaults each DML statement to its own transaction if not specified
  216.     ;
  217.   * ANSI automatically begins a transaction with any SELECT, FETCH, OPEN,
  218.     INSERT, UPDATE, or DELETE statement ;
  219.   * If Chained Transaction must be used, extreme care must be taken to ensure
  220.     locks aren't left held by applications unaware they are within a
  221.     transaction! This is especially crucial if running at Level 3 Isolation
  222.   * Lock at the Level of Isolation Required by the Query
  223.   * Read Consistency is NOT a requirement of every query.
  224.   * Choose level 3 only when the business model requires it
  225.   * Running at Level 1 but selectively applying HOLDLOCKs as needed is safest
  226.   * If you must run at Level 3, use the NOHOLDLOCK clause when you can !
  227.   * Beware of (and test) ANSI-compliant third-party applications for
  228.     concurrency
  229.  
  230. Application Deadlocking
  231.  
  232. Prior to ASE 10 cursors, many developers simulated cursors by using two or more
  233. connections (dbproc's) and divided the processing between them. Often, this
  234. meant one connection had a SELECT open while "positioned" UPDATEs and DELETEs
  235. were issued on the other connection. The approach inevitably leads to the
  236. following problem:
  237.  
  238.  1. Connection A holds a share lock on page X (remember "Rows Pending" on SQL
  239.     Server leave a share lock on the "current" page).
  240.  2. Connection B requests an exclusive lock on the same page X and waits...
  241.  3. The APPLICATION waits for connection B to succeed before invoking whatever
  242.     logic will remove the share lock (perhaps dbnextrow). Of course, that never
  243.     happens ...
  244.  
  245. Since Connection A never requests a lock which Connection B holds, this is NOT
  246. a true server-side deadlock. It's really an "application" deadlock !
  247.  
  248. Design Alternatives
  249.  
  250.  1. Buffer additional rows in the client that are "nonupdateable". This forces
  251.     the shared lock onto a page on which the application will not request an
  252.     exclusive lock.
  253.  2. Re-code these modules with CT-Library cursors (aka. server-side cursors).
  254.     These cursors avoid this problem by disassociating command structures from
  255.     connection structures.
  256.  3. Re-code these modules with DB-Library cursors (aka. client-side cursors).
  257.     These cursors avoid this problem through buffering techniques and
  258.     re-issuing of SELECTs. Because of the re-issuing of SELECTs, these cursors
  259.     are not recommended for high transaction sites !
  260.  
  261. Optimizing Cursors with v10.0
  262.  
  263.   * Always Declare Cursor's Intent (i.e. Read Only or Updateable)
  264.   * Allows for greater control over concurrency implications
  265.   * If not specified, ASE will decide for you and usually choose updateable
  266.   * Updateable cursors use UPDATE locks preventing other U or X locks
  267.   * Updateable cursors that include indexed columns in the update list may
  268.     table scan
  269.   * SET Number of Rows for each FETCH
  270.   * Allows for greater Network Optimization over ANSI's 1- row fetch
  271.   * Rows fetched via Open Client cursors are transparently buffered in the
  272.     client:
  273.                     FETCH  ->  Open Client <- N rows
  274.                                    Buffers
  275.   * Keep Cursor Open on a Commit / Rollback
  276.   * ANSI closes cursors with each COMMIT causing either poor throughput (by
  277.     making the server re-materialize the result set) or poor concurrency (by
  278.     holding locks)
  279.   * Open Multiple Cursors on a Single Connection
  280.   * Reduces resource consumption on both client and Server
  281.   * Eliminates risk of a client-side deadlocks with itself
  282.  
  283. Special Issues for Batch Applications
  284.  
  285. ASE was not designed as a batch subsystem! It was designed as an RBDMS for
  286. large multi-user applications. Designers of batch-oriented applications should
  287. consider the following design alternatives to maximize performance :
  288.  
  289. Design Alternatives :
  290.  
  291.   * Minimize Client/Server Interaction Whenever Possible
  292.   * Don't turn ASE into a "file system" by issuing single table / single row
  293.     requests when, in actuality, set logic applies.
  294.   * Maximize TDS packet size for efficient Interprocess Communication (v10
  295.     only)
  296.   * New ASE 10.0 cursors declared and processed entirely within stored
  297.     procedures and triggers offer significant performance gains in batch
  298.     processing.
  299.   * Investigate Opportunities to Parallelize Processing
  300.   * Breaking up single processes into multiple, concurrently executing,
  301.     connections (where possible) will outperform single streamed processes
  302.     everytime.
  303.   * Make Use of TEMPDB for Intermediate Storage of Useful Data
  304.  
  305. Asynchronous Queries
  306.  
  307. Many, if not most, applications and 3rd Party tools are coded to send queries
  308. with the DB-Library call dbsqlexec( ) which is a synchronous call ! It sends a
  309. query and then waits for a response from ASE that the query has completed !
  310.  
  311. Designing your applications for asynchronous queries provides many benefits:
  312.  
  313.  1. A "Cooperative" multi-tasking application design under Windows will allow
  314.     users to run other Windows applications while your long queries are
  315.     processed !
  316.  2. Provides design opportunities to parallize work across multiple ASE
  317.     connections.
  318.  
  319. Implementation Choices:
  320.  
  321.   * System 10 Client Library Applications:
  322.   * True asynchronous behaviour is built into the entire library. Through the
  323.     appropriate use of call-backs, asynchronous behavior is the normal
  324.     processing paradigm.
  325.   * Windows DB-Library Applications (not true async but polling for data):
  326.   * Use dbsqlsend(), dbsqlok(), and dbdataready() in conjunction with some
  327.     additional code in WinMain() to pass control to a background process. Code
  328.     samples which outline two different Windows programming approaches (a
  329.     PeekMessage loop and a Windows Timer approach) are available in the
  330.     Microsoft Software Library on Compuserve (GO MSL). Look for SQLBKGD.ZIP
  331.   * Non-PC DB-Library Applications (not true async but polling for data):
  332.   * Use dbsqlsend(), dbsqlok(), and dbpoll() to utilize non-blocking functions.
  333.  
  334. Generating Sequential Numbers Many applications use unique sequentially
  335. increasing numbers, often as primary keys. While there are good benefits to
  336. this approach, generating these keys can be a serious contention point if not
  337. careful. For a complete discussion of the alternatives, download Malcolm
  338. Colton's White Paper on Sequential Keys from the SQL Server Library of our
  339. OpenLine forum on Compuserve.
  340.  
  341. The two best alternatives are outlined below.
  342.  
  343.  1. "Primary Key" Table Storing Last Key Assigned
  344.       + Minimize contention by either using a seperate "PK" table for each user
  345.         table or padding out each row to a page. Make sure updates are
  346.         "in-place".
  347.       + Don't include the "PK" table's update in the same transaction as the
  348.         INSERT. It will serialize the transactions.
  349.               BEGIN TRAN
  350.         
  351.                         UPDATE pk_table SET nextkey = nextkey + 1
  352.                         [WHERE table_name = @tbl_name]
  353.               COMMIT TRAN
  354.         
  355.               /* Now retrieve the information */
  356.               SELECT nextkey FROM pk_table
  357.               WHERE table_name = @tbl_name]
  358.               
  359.       + "Gap-less" sequences require additional logic to store and retrieve
  360.         rejected values
  361.  2. IDENTITY Columns (v10.0 only)
  362.       + Last key assigned for each table is stored in memory and automatically
  363.         included in all INSERTs (BCP too). This should be the method of choice
  364.         for performance.
  365.       + Choose a large enough numeric or else all inserts will stop once the
  366.         max is hit.
  367.       + Potential rollbacks in long transactions may cause gaps in the sequence
  368.         !
  369.    
  370.     Other Application Issues
  371.    
  372.       + Transaction Logging Can Bottleneck Some High Transaction Environments
  373.       + Committing a Transaction Must Initiate a Physical Write for
  374.         Recoverability
  375.       + Implementing multiple statements as a transaction can assist in these
  376.         environment by minimizing the number of log writes (log is flushed to
  377.         disk on commits).
  378.       + Utilizing the Client Machine's Processing Power Balances Load
  379.       + Client/Server doesn't dictate that everything be done on Server!
  380.       + Consider moving "presentation" related tasks such as string or
  381.         mathematical manipulations, sorting, or, in some cases, even
  382.         aggregating to the client.
  383.       + Populating of "Temporary" Tables Should Use "SELECT INTO" - balance
  384.         this with dynamic creation of temporary tables in an OLTP environment.
  385.         Dynamic creation may cause blocks in your tempdb.
  386.       + "SELECT INTO" operations are not logged and thus are significantly
  387.         faster than there INSERT with a nested SELECT counterparts.
  388.       + Consider Porting Applications to Client Library Over Time
  389.       + True Asynchronous Behavior Throughout Library
  390.       + Array Binding for SELECTs
  391.       + Dynamic SQL
  392.       + Support for ClientLib-initiated callback functions
  393.       + Support for Server-side Cursors
  394.       + Shared Structures with Server Library (Open Server 10)
  395.    
  396.     Physical Database Design Issues
  397.    
  398.       + Normalized -vs- Denormalized Design
  399.       + Index Selection
  400.       + Promote "Updates-in-Place" Design
  401.       + Promote Parallel I/O Opportunities
  402.    
  403.     Normalized -vs- Denormalized
  404.    
  405.       + Always Start with a Completely Normalized Database
  406.       + Denormalization should be an optimization taken as a result of a
  407.         performance problem
  408.       + Benefits of a normalized database include :
  409.          1. Accelerates searching, sorting, and index creation since tables are
  410.             narrower
  411.          2. Allows more clustered indexes and hence more flexibility in tuning
  412.             queries, since there are more tables ;
  413.          3. Accelerates index searching since indexes tend to be narrower and
  414.             perhaps shorter ;
  415.          4. Allows better use of segments to control physical placement of
  416.             tables ;
  417.          5. Fewer indexes per table, helping UPDATE, INSERT, and DELETE
  418.             performance ;
  419.          6. Fewer NULLs and less redundant data, increasing compactness of the
  420.             database ;
  421.          7. Accelerates trigger execution by minimizing the extra integrity
  422.             work of maintaining redundant data.
  423.          8. Joins are Generally Very Fast Provided Proper Indexes are Available
  424.          9. Normal caching and cindextrips parameter (discussed in Server
  425.             section) means each join will do on average only 1-2 physical I/Os.
  426.         10. Cost of a logical I/O (get page from cache) only 1-2 milliseconds.
  427.  3. There Are Some Good Reasons to Denormalize
  428.      1. All queries require access to the "full" set of joined data.
  429.      2. Majority of applications scan entire tables doing joins.
  430.      3. Computational complexity of derived columns require storage for SELECTs
  431.      4. Others ...
  432.    
  433.     Index Selection
  434.    
  435.       + Without a clustered index, all INSERTs and "out-of-place" UPDATEs go to
  436.         the last page. The lock contention in high transaction environments
  437.         would be prohibitive. This is also true for INSERTs to a clustered
  438.         index on a monotonically increasing key.
  439.       + High INSERT environments should always cluster on a key which provides
  440.         the most "randomness" (to minimize lock / device contention) that is
  441.         usable in many queries. Note this is generally not your primary key !
  442.       + Prime candidates for clustered index (in addition to the above) include
  443.         :
  444.           o Columns Accessed by a Range
  445.           o Columns Used with Order By, Group By, or Joins
  446.       + Indexes Help SELECTs and Hurt INSERTs
  447.       + Too many indexes can significantly hurt performance of INSERTs and
  448.         "out-of-place" UPDATEs.
  449.       + Prime candidates for nonclustered indexes include :
  450.           o Columns Used in Queries Requiring Index Coverage
  451.           o Columns Used to Access Less than 20% (rule of thumb) of the Data.
  452.       + Unique indexes should be defined as UNIQUE to help the optimizer
  453.       + Minimize index page splits with Fillfactor (helps concurrency and
  454.         minimizes deadlocks)
  455.       + Keep the Size of the Key as Small as Possible
  456.       + Accelerates index scans and tree traversals
  457.       + Use small datatypes whenever possible . Numerics should also be used
  458.         whenever possible as they compare faster than strings.
  459.    
  460.     Promote "Update-in-Place" Design
  461.    
  462.       + "Update-in-Place" Faster by Orders of Magnitude
  463.       + Performance gain dependent on number of indexes. Recent benchmark (160
  464.         byte rows, 1 clustered index and 2 nonclustered) showed 800%
  465.         difference!
  466.       + Alternative ("Out-of-Place" Update) implemented as a physical DELETE
  467.         followed by a physical INSERT. These tactics result in:
  468.          1. Increased Lock Contention
  469.          2. Increased Chance of Deadlock
  470.          3. Decreased Response Time and Throughput
  471.       + Currently (System 10 and below), Rules for "Update-in-Place" Behavior
  472.         Include :
  473.          1. Columns updated can not be variable length or allow nulls
  474.          2. Columns updated can not be part of an index used to locate the row
  475.             to update
  476.          3. No update trigger on table being updated (because the inserted and
  477.             deleted tables used in triggers get their data from the log)
  478.            
  479.                
  480.                 In v4.9.x and below, only one row may be affected and the
  481.                 optimizer must know this in advance by choosing a UNIQUE index.
  482.                 System 10 eliminated this limitation.
  483.                
  484.     Promote Parallel I/O Opportunities
  485.    
  486.       + For I/O-bound Multi-User Systems, Use A lot of Logical and Physical
  487.         Devices
  488.       + Plan balanced separation of objects across logical and physical
  489.         devices.
  490.       + Increased number of physical devices (including controllers) ensures
  491.         physical bandwidth
  492.       + Increased number of logical Sybase devices ensures minimal contention
  493.         for internal resources. Look at SQL Monitor's Device I/O Hit Rate for
  494.         clues. Also watch out for the 128 device limit per database.
  495.       + Create Database (in v10) starts parallel I/O on up to 6 devices at a
  496.         time concurrently. If taken advantage of, expect an 800% performance
  497.         gain. A 2Gb TPC-B database that took 4.5 hours under 4.9.1 to create
  498.         now takes 26 minutes if created on 6 independent devices !
  499.       + Use Sybase Segments to Ensure Control of Placement
  500.        
  501.            
  502.             This is the only way to guarantee logical seperation of objects on
  503.             devices to reduce contention for internal resources.
  504.            
  505.       + Dedicate a seperate physical device and controller to the transaction
  506.         log in tempdb too.
  507.       + optimize TEMPDB Also if Heavily Accessed
  508.       + increased number of logical Sybase devices ensures minimal contention
  509.         for internal resources.
  510.       + systems requiring increased log throughput today must partition
  511.         database into separate databases
  512.        
  513.         Breaking up one logical database into multiple smaller databases
  514.         increases the number number of transaction logs working in parallel.
  515.        
  516.     Networking Issues
  517.    
  518.       + Choice of Transport Stacks
  519.       + Variable Sized TDS Packets
  520.       + TCP/IP Packet Batching
  521.    
  522.     Choice of Transport Stacks for PCs
  523.    
  524.       + Choose a Stack that Supports "Attention Signals" (aka. "Out of Band
  525.         Data")
  526.       + Provides for the most efficient mechanism to cancel queries.
  527.       + Essential for sites providing ad-hoc query access to large databases.
  528.       + Without "Attention Signal" capabilities (or the urgent flag in the
  529.         connection string), the DB-Library functions DBCANQUERY ( ) and
  530.         DBCANCEL ( ) will cause ASE to send all rows back to the Client
  531.         DB-Library as quickly as possible so as to complete the query. This can
  532.         be very expensive if the result set is large and, from the user's
  533.         perspective, causes the application to appear as though it has hung.
  534.       + With "Attention Signal" capabilities, Net-Library is able to send an
  535.         out-of-sequence packet requesting the ASE to physically throw away any
  536.         remaining results providing for instantaneous response.
  537.       + Currently, the following network vendors and associated protocols
  538.         support the an "Attention Signal" capable implementation:
  539.          1. NetManage NEWT
  540.          2. FTP TCP
  541.          3. Named Pipes (10860) - Do not use urgent parameter with this Netlib
  542.          4. Novell LAN Workplace v4.1 0 Patch required from Novell
  543.          5. Novell SPX - Implemented internally through an "In-Band" packet
  544.          6. Wollongong Pathway
  545.          7. Microsoft TCP - Patch required from Microsoft
  546.    
  547.     Variable-sized TDS Packets
  548.    
  549.     Pre-v4.6 TDS Does Not Optimize Network Performance Current ASE TDS packet
  550.     size limited to 512 bytes while network frame sizes are significantly
  551.     larger (1508 bytes on Ethernet and 4120 bytes on Token Ring).
  552.    
  553.         The specific protocol may have other limitations!
  554.        
  555.     For example:
  556.       + IPX is limited to 576 bytes in a routed network.
  557.       + SPX requires acknowledgement of every packet before it will send
  558.         another. A recent benchmark measured a 300% performance hit over TCP in
  559.         "large" data transfers (small transfers showed no difference).
  560.       + Open Client Apps can "Request" a Larger Packet Shown to have
  561.         significant performance improvement on "large" data transfers such as
  562.         BCP, Text / Image Handling, and Large Result Sets.
  563.           o clients:
  564.               # isql -Usa -Annnnn
  565.               # bcp -Usa -Annnnn
  566.               # ct_con_props (connection, CS_SET, CS_PACKETSIZE, &packetsize,
  567.                 sizeof(packetsize), NULL)
  568.           o An "SA" must Configure each Servers' Defaults Properly
  569.               # sp_configure "default packet size", nnnnn - Sets default packet
  570.                 size per client connection (defaults to 512)
  571.               # sp_configure "maximum packet size", nnnnn - Sets maximum TDS
  572.                 packet size per client connection (defaults to 512)
  573.               # sp_configure "additional netmem", nnnnn - Additional memory for
  574.                 large packets taken from separate pool. This memory does not
  575.                 come from the sp_configure memory setting.
  576.                
  577.                 Optimal value = ((# connections using large packets large
  578.                 packetsize * 3) + an additional 1-2% of the above calculation
  579.                 for overhead)
  580.                
  581.                 Each connection using large packets has 3 network buffers: one
  582.                 to read; one to write; and one overflow.
  583.                   @ Default network memory - Default-sized packets come from
  584.                     this memory pool.
  585.                   @ Additional Network memory - Big packets come this memory
  586.                     pool.
  587.                    
  588.                     If not enough memory is available in this pool, the server
  589.                     will give a smaller packet size, down to the default
  590.                    
  591.     TCP/IP Packet Batching
  592.    
  593.       + TCP Networking Layer Defaults to "Packet Batching"
  594.       + This means that TCP/IP will batch small logical packets into one larger
  595.         physical packet by briefly delaying packets in an effort to fill the
  596.         physical network frames (Ethernet, Token-Ring) with as much data as
  597.         possible.
  598.       + Designed to improve performance in terminal emulation environments
  599.         where there are mostly only keystrokes being sent across the network.
  600.       + Some Environments Benefit from Disabling Packet Batching
  601.       + Applies mainly to socket-based networks (BSD) although we have seen
  602.         some TLI networks such as NCR's benefit.
  603.       + Applications sending very small result sets or statuses from sprocs
  604.         will usually benefit. Benchmark with your own application to be sure.
  605.       + This makes ASE open all connections with the TCP_NODELAY option.
  606.         Packets will be sent regardless of size.
  607.       + To disable packet batching, in pre-Sys 11, start ASE with the 1610
  608.         Trace Flag.
  609.        
  610.            
  611.             $SYBASE/dataserver -T1610 -d /usr/u/sybase/master.dat ...
  612.            
  613.         Your errorlog will indicate the use of this option with the message:
  614.        
  615.             ASE booted with TCP_NODELAY enabled.
  616.            
  617.     Operating System Issues
  618.    
  619.       + Never Let ASE Page Fault
  620.       + It is better to configure ASE with less memory and do more physical
  621.         database I/O than to page fault. OS page faults are synchronous and
  622.         stop the entire dataserver engine until the page fault completes. Since
  623.         database I/O's are asynchronous, other user tasks can continue!
  624.       + Use Process Affinitying in SMP Environments, if Supported
  625.       + Affinitying dataserver engines to specific CPUs minimizes overhead
  626.         associated with moving process information (registers, etc) between
  627.         CPUs. Most implementations will preference other tasks onto other CPUs
  628.         as well allowing even more CPU time for dataserver engines.
  629.       + Watch out for OS's which are not fully symmetric. Affinitying
  630.         dataserver engines onto CPUs that are heavily used by the OS can
  631.         seriously degrade performance. Benchmark with your application to find
  632.         optimal binding.
  633.       + Increase priority of dataserver engines, if supported
  634.       + Give ASE the opportunity to do more work. If ASE has nothing to do, it
  635.         will voluntarily yield the CPU.
  636.       + Watch out for OS's which externalize their async drivers. They need to
  637.         run too!
  638.       + Use of OS Monitors to Verify Resource Usage
  639.       + The OS CPU monitors only "know" that an instruction is being executed.
  640.         With ASE's own threading and scheduling, it can routinely be 90% idle
  641.         when the OS thinks its 90% busy. SQL Monitor shows real CPU usage.
  642.       + Look into high disk I/O wait time or I/O queue lengths. These indicate
  643.         physical saturation points in the I/O subsystem or poor data
  644.         distribution.
  645.       + Disk Utilization above 50% may be subject to queuing effects which
  646.         often manifest themselves as uneven response times.
  647.       + Look into high system call counts which may be symptomatic of problems.
  648.       + Look into high context switch counts which may also be symptomatic of
  649.         problems.
  650.       + Optimize your kernel for ASE (minimal OS file buffering, adequate
  651.         network buffers, appropriate KEEPALIVE values, etc).
  652.       + Use OS Monitors and SQL Monitor to Determine Bottlenecks
  653.       + Most likely "Non-Application" contention points include:
  654.            Resource                    Where to Look
  655.            ---------                   --------------
  656.            CPU Performance             SQL Monitor - CPU and Trends
  657.         
  658.            Physical I/O Subsystem      OS Monitoring tools - iostat, sar...
  659.         
  660.            Transaction Log             SQL Monitor - Device I/O and
  661.                                                      Device Hit Rate
  662.                                                      on Log Device
  663.         
  664.            ASE Network Polling  SQL Monitor - Network and Benchmark
  665.                                                      Baselines
  666.         
  667.            Memory                      SQL Monitor - Data and Cache
  668.                                                      Utilization
  669.                                                 
  670.       + Use of Vendor-support Striping such as LVM and RAID
  671.       + These technologies provide a very simple and effective mechanism of
  672.         load balancing I/O across physical devices and channels.
  673.       + Use them provided they support asynchronous I/O and reliable writes.
  674.       + These approaches do not eliminate the need for Sybase segments to
  675.         ensure minimal contention for internal resources.
  676.       + Non-read-only environments should expect performance degradations when
  677.         using RAID levels other than level 0. These levels all include fault
  678.         tolerance where each write requires additional reads to calculate a
  679.         "parity" as well as the extra write of the parity data.
  680.    
  681.     Hardware Configuration Issues
  682.    
  683.       + Number of CPUs
  684.       + Use information from SQL Monitor to assess ASE's CPU usage.
  685.       + In SMP environments, dedicate at least one CPU for the OS.
  686.       + Advantages and scaling of VSA is application-dependent. VSA was
  687.         architected with large multi-user systems in mind.
  688.       + I/O Subsystem Configuration
  689.       + Look into high Disk I/O Wait Times or I/O Queue Lengths. These may
  690.         indicate physical I/O saturation points or poor data distribution.
  691.       + Disk Utilization above 50% may be subject to queuing effects which
  692.         often manifest themselves as uneven response times.
  693.       + Logical Volume configurations can impact performance of operations such
  694.         as create database, create index, and bcp. To optimize for these
  695.         operations, create Logical Volumes such that they start on different
  696.         channels / disks to ensure I/O is spread across channels.
  697.       + Discuss device and controller throughput with hardware vendors to
  698.         ensure channel throughput high enough to drive all devices at maximum
  699.         rating.
  700.    
  701.     General ASE Tuning
  702.    
  703.       + Changing Values with sp_configure or buildmaster
  704.        
  705.            
  706.             It is imperative that you only use sp_configure to change those
  707.             parameters that it currently maintains because the process of
  708.             reconfiguring actually recalculates a number of other buildmaster
  709.             parameters. Using the Buildmaster utility to change a parameter
  710.             "managed" by sp_configure may result in a mis-configured server and
  711.             cause adverse performance or even worse ...
  712.            
  713.       + Sizing Procedure Cache
  714.           o ASE maintains an MRU-LRU chain of stored procedure query plans. As
  715.             users execute sprocs, ASE looks in cache for a query plan to use.
  716.             However, stored procedure query plans are currently not re-entrant!
  717.             If a query plan is available, it is placed on the MRU and execution
  718.             begins. If no plan is in memory, or if all copies are in use, a new
  719.             copy is read from the sysprocedures table. It is then optimized and
  720.             put on the MRU for execution.
  721.           o Use dbcc memusage to evaluate the size and number of each sproc
  722.             currently in cache. Use SQL Monitor's cache statistics to get your
  723.             average cache hit ratio. Ideally during production, one would hope
  724.             to see a high hit ratio to minimize the procedure reads from disk.
  725.             Use this information in conjuction with your desired hit ratio to
  726.             calculate the amount of memory needed.
  727.       + Memory
  728.           o Tuning memory is more a price/performance issue than anything else
  729.             ! The more memory you have available, the greater than probability
  730.             of minimizing physical I/O. This is an important goal though. Not
  731.             only does physical I/O take significantly longer, but threads doing
  732.             physical I/O must go through the scheduler once the I/O completes.
  733.             This means that work on behalf of the thread may not actually
  734.             continue to execute for quite a while !
  735.           o There are no longer (as of v4.8) any inherent limitations in ASE
  736.             which cause a point of diminishing returns on memory size.
  737.           o Calculate Memory based on the following algorithm :
  738.            
  739.                
  740.                 Total Memory = Dataserver Executable Size (in bytes) +
  741.                 Static Overhead of 1 Mb +
  742.                 User Connections x 40,960 bytes +
  743.                 Open Databases x 644 bytes +
  744.                 Locks x 32 bytes +
  745.                 Devices x 45,056 bytes +
  746.                 Procedure Cache +
  747.                 Data Cache
  748.                
  749.       + Recovery Interval
  750.           o As users change data in ASE, only the transaction log is written to
  751.             disk right away for recoverability. "Dirty" data and index pages
  752.             are kept in cache and written to disk at a later time. This
  753.             provides two major benefits:
  754.              1. Many transactions may change a page yet only one physical write
  755.                 is done
  756.              2. ASE can schedule the physical writes "when appropriate"
  757.           o ASE must eventually write these "dirty" pages to disk.
  758.           o A checkpoint process wakes up periodically and "walks" the cache
  759.             chain looking for dirty pages to write to disk
  760.           o The recovery interval controls how often checkpoint writes dirty
  761.             pages.
  762.       + Tuning Recovery Interval
  763.           o A low value may cause unnecessary physical I/O lowering throughput
  764.             of the system. Automatic recovery is generally much faster during
  765.             boot-up.
  766.           o A high value minimizes unnecessary physical I/O and helps
  767.             throughput of the system. Automatic recovery may take substantial
  768.             time during boot-up.
  769.    
  770.     Audit Performance Tuning for v10.0
  771.    
  772.       + Potentially as Write Intensive as Logging
  773.       + Isolate Audit I/O from other components.
  774.       + Since auditing nearly always involves sequential writes, RAID Level 0
  775.         disk striping or other byte-level striping technology should provide
  776.         the best performance (theoretically).
  777.       + Size Audit Queue Carefully
  778.       + Audit records generated by clients are stored in an in memory audit
  779.         queue until they can be processed.
  780.       + Tune the queue's size with sp_configure "audit queue size", nnnn (in
  781.         rows).
  782.       + Sizing this queue too small will seriously impact performance since all
  783.         user processes who generate audit activity will sleep if the queue
  784.         fills up.
  785.       + Size Audit Database Carefully
  786.       + Each audit row could require up to 416 bytes depending on what is
  787.         audited.
  788.       + Sizing this database too small will seriously impact performance since
  789.         all user processes who generate audit activity will sleep if the
  790.         database fills up.
  791.  
  792. Back to top
  793.  
  794. -------------------------------------------------------------------------------
  795.  
  796. 1.5.2: Temp Tables and OLTP
  797.  
  798. -------------------------------------------------------------------------------
  799.  
  800. (Note from Ed: It appears that with ASE 12, Sybase have solved the problem of
  801. select/into locking the system tables for the duration of the operation. The
  802. operation is now split into two parts, the creation of the table followed byt
  803. the insert. The system tables are only locked for the first part, and so, to
  804. all intents and purposes, the operation acts like a create/insert pair whilst
  805. remaining minimally logged.
  806.  
  807. Our shop would like to inform folks of a potential problem when using temporary
  808. tables in an OLTP environment. Using temporary tables dynamically in a OLTP
  809. production environment may result in blocking (single-threading) as the number
  810. of transactions using the temporary tables increases.
  811.  
  812. Does it affect my application?
  813.  
  814. This warning only applies for SQL that is being invoked frequently in an OLTP
  815. production environment, where the use of "select into..." or "create table #
  816. temp" is common. Application using temp tables may experience blocking problems
  817. as the number of transactions increases.
  818.  
  819. This warning does not apply to SQL that may be in a report or that is not used
  820. frequently. Frequently is defined as several times per second.
  821.  
  822. Why? Why? Why?
  823.  
  824. Our shop was working with an application owner to chase down a problem they
  825. were having during peak periods. The problem they were having was severe
  826. blocking in tempdb.
  827.  
  828. What was witnessed by the DBA group was that as the number of transactions
  829. increased on this particular application, the number of blocks in tempdb also
  830. increased.
  831.  
  832. We ran some independent tests to simulate a heavily loaded server and
  833. discovered that the data pages in contention were in tempdb's syscolumns table.
  834.  
  835. This actually makes sense because during table creation entries are added to
  836. this table, regardless if it's a temporary or permanent table.
  837.  
  838. We ran another simulation where we created the tables before the stored
  839. procedure used it and the blocks went away. We then performed an additional
  840. test to determine what impact creating temporary tables dynamically would have
  841. on the server and discovered that there is a 33% performance gain by creating
  842. the tables once rather than re-creating them.
  843.  
  844. Your mileage may vary.
  845.  
  846. How do I fix this?
  847.  
  848. To make things better, do the 90's thing -- reduce and reuse your temp tables.
  849. During one application connection/session, aim to create the temp tables only
  850. once.
  851.  
  852. Let's look at the lifespan of a temp table. If temp tables are created in a
  853. batch within a connection, then all future batches and stored procs will have
  854. access to such temp tables until they're dropped; this is the reduce and reuse
  855. strategy we recommend. However, if temp tables are created in a stored proc,
  856. then the database will drop the temp tables when the stored proc ends, and this
  857. means repeated and multiple temp table creations; you want to avoid this.
  858.  
  859. Recode your stored procedures so that they assume that the temporary tables
  860. already exist, and then alter your application so that it creates the temporary
  861. tables at start-up -- once and not every time the stored procedure is invoked.
  862.  
  863. That's it! Pretty simple eh?
  864.  
  865. Summary
  866.  
  867. The upshot is that you can realize roughly a 33% performance gain and not
  868. experience the blocking which is difficult to quantify due to the specificity
  869. of each application.
  870.  
  871. Basically, you cannot lose.
  872.  
  873. Solution in pseudo-code
  874.  
  875. If you have an application that creates the same temp table many times within
  876. one connection, here's how to convert it to reduce and reuse temp table
  877. creations. Raymond Lew has supplied a detailed example for trying this.
  878.  
  879. Old
  880.  
  881. open connection
  882.   loop until time to go
  883.     exec procedure vavoom_often
  884.       /* vavoom_often creates and uses #gocart for every call */
  885.       /* eg: select * into #gocart from gocart */
  886.     go
  887.           .
  888.           .
  889.           .
  890.   loop-end
  891. close connection
  892.  
  893. New
  894.  
  895. open connection
  896.   /* Create the temporary table outside of the sproc */
  897.   select * into #gocart from gocart where 1 =2 ;
  898.   go
  899.   loop until time to go
  900.     exec procedure vavoom_often
  901.       /* vavoom_often reuses #gocart which */
  902.       /*   was created before exec of vavoom_often */
  903.       /* - First statement may be a truncate table #gocart */
  904.       /* - Execute with recompile */
  905.       /*   if your table will have more than 10 data pages */
  906.       /*   as the optimizer will assume 10 data pages for temp tables */
  907.     go
  908.           .
  909.           .
  910.           .
  911.   loop-end
  912. close connection
  913.  
  914. Note that it is necessary to call out the code to create the table and it
  915. becomes a pain in the butt because the create-table statement will have to be
  916. replicated in any stored proc and in the initialization part of the application
  917. - this can be a maintenance nuisance. This can be solved by using any macro
  918. package such as m4 or cpp. or by using and adapting the scripts from Raymond
  919. Lew.
  920.  
  921. -------------------------------------------------------------------------------
  922.  
  923. Brian Black posted a stronger notice than this to the SYBASE-L list, and I
  924. would agree, that any use of select/into in a production environments should
  925. looked at very hard.  Even in DSS environments, especially if they share tempdb
  926. with an OLTP environment, should use select/into with care.
  927.  
  928. -------------------------------------------------------------------------------
  929.  
  930. From: Raymond Lew
  931.  
  932. At our company, we try to keep the database and the application loosely coupled
  933. to allow independent changes at the frontend or the backend as long as the
  934. interface stays the same. Embedding temp table definitions in the frontend
  935. would make this more difficult.
  936.  
  937. To get away from having to embed the temp table definitions in the frontend
  938. code, we are storing the temp table definitions in the database. The frontend
  939. programs retrieve the definitions and declare the tables dynamically at the
  940. beginning of each session. This allows for the change of backend procedures
  941. without changes in the frontend when the API does not change.
  942.  
  943. Enclosed below are three scripts. The first is an isql script to create the
  944. tables to hold the definitions. The second is a shell script to set up a sample
  945. procedure named vavoom. The third is shell script to demonstrate the structure
  946. of application code.
  947.  
  948. I would like to thank Charles Forget and Gordon Rees for their assistance on
  949. these scripts.
  950.  
  951. --start of setup------------------------------------------------------
  952. /* Raymond Lew - 1996-02-20 */
  953. /* This isql script will set up the following tables:
  954.    gocart - sample table
  955.    app_temp_defn - where temp table definitions are stored
  956.    app_temp_defn_group - a logical grouping of temp table definitions
  957.                          for an application function
  958. */
  959.  
  960. /******************************/
  961. /* gocart table - sample table*/
  962. /******************************/
  963. drop table gocart
  964. go
  965. create table gocart
  966. (
  967.   cartname    char(10) null
  968.  ,cartcolor   char(30) null
  969. )
  970. go
  971. create unique clustered index  gocart1 on gocart (cartname)
  972. go
  973. insert into gocart values ('go1','blue ')
  974. insert into gocart values ('go2','pink ')
  975. insert into gocart values ('go3','green ')
  976. insert into gocart values ('go4','red ')
  977. go
  978.  
  979.  
  980. /****************************************************************/
  981. /* app_temp_defn - definition of temp tables with their indexes */
  982. /****************************************************************/
  983. drop table app_temp_defn
  984. go
  985. create table app_temp_defn
  986. (
  987.   /* note: temp tables are unique only in first 13 chars */
  988.   objectname  char(20)     not null
  989.  ,seq_no      smallint     not null
  990.  ,defntext    char(255)    not null
  991. )
  992. go
  993. create unique clustered index  app_temp_defn1
  994.   on app_temp_defn (objectname,seq_no)
  995. go
  996. insert into app_temp_defn
  997. values ('#gocart',1,'select * into #gocart')
  998. insert into app_temp_defn
  999. values ('#gocart',2,' from gocart where 1=2 ')
  1000. go
  1001. insert into app_temp_defn
  1002. values ('#gocartindex',1,
  1003.  "create unique index gocartindex on #gocart (cartname) ")
  1004. go
  1005. insert into app_temp_defn
  1006. values ('#gocart1',1, 'select * into #gocart1  from gocart where 1=2')
  1007. go
  1008.  
  1009.  
  1010.  
  1011.  
  1012. /***********************************************************************/
  1013. /* app_temp_defn_group - groupings of temp definitions by applications */
  1014. /***********************************************************************/
  1015. drop table app_temp_defn_group
  1016. go
  1017. create table app_temp_defn_group
  1018. (
  1019.   appname     char(8)  not null
  1020.  ,objectname  char(20) not null
  1021. )
  1022. go
  1023. create unique clustered index  app_temp_defn_group1
  1024.  on app_temp_defn_group (appname,objectname)
  1025. go
  1026. insert into app_temp_defn_group values('abc','#gocart')
  1027. insert into app_temp_defn_group values('abc','#gocartindex')
  1028. go
  1029.  
  1030.  
  1031.  
  1032. /***********************************************************/
  1033. /* get_temp_defn - proc for getting the temp defn by group */
  1034. /***********************************************************/
  1035. drop procedure get_temp_defn
  1036. go
  1037. create procedure get_temp_defn
  1038. (
  1039. @appname               char(8)
  1040. )
  1041. as
  1042.  
  1043. if @appname = ''
  1044.   select defntext
  1045.     from app_temp_defn
  1046.     order by objectname, seq_no
  1047. else
  1048.   select defntext
  1049.     from app_temp_defn a
  1050.        , app_temp_defn_group b
  1051.    where a.objectname = b.objectname
  1052.      and b.appname = @appname
  1053.    order by a.objectname, a.seq_no
  1054.  
  1055. return
  1056. go
  1057.  
  1058. /* let's try some tests */
  1059. exec get_temp_defn ''
  1060. go
  1061. exec get_temp_defn 'abc'
  1062. go
  1063. --end of setup      --------------------------------------------------
  1064.  
  1065.  
  1066.  
  1067.  
  1068.  
  1069.  
  1070. --- start of make.vavoom --------------------------------------------
  1071. #!/bin/sh
  1072. # Raymond Lew - 1996-02-20
  1073. #
  1074. # bourne shell script for creating stored procedures using
  1075. # app_temp_defn table
  1076. #
  1077. # demo procedure vavoom created here
  1078. #
  1079. # note: you have to change the passwords, id and etc. for your site
  1080. # note: you might have to some inline changes to make this work
  1081. #       check out the notes within the body
  1082.  
  1083.  
  1084. # get the table defn's into a text file
  1085. #
  1086. # note: next line :you will need to end the line immediately after eot \
  1087. isql -Ukryten -Pjollyguy -Sstarbug  -w255 << eot \
  1088. | grep -v '\-\-\-\-' | grep -v 'defntext  ' | grep -v ' affected' > tabletext
  1089. exec get_temp_defn ''
  1090. go
  1091. eot
  1092. # note: prev line :you will need to have a newline immediately after eot
  1093.  
  1094. # go mess around in vi
  1095. vi tabletext
  1096.  
  1097. #
  1098. # create the proc vavoom after running the temp defn's into db
  1099. #
  1100. isql -Ukryten -Pjollyguy -Sstarbug  -e << eot |more
  1101. `cat tabletext`
  1102. go
  1103. drop procedure vavoom
  1104. go
  1105. create procedure vavoom
  1106. (
  1107. @color               char(10)
  1108. )
  1109. as
  1110. truncate table #gocart1 /* who knows what lurks in temp tables */
  1111. if @color = ''
  1112.   insert #gocart1 select * from gocart
  1113. else
  1114.   insert #gocart1 select * from gocart where cartcolor=@color
  1115. select @color '@color', * from #gocart1
  1116. return
  1117. go
  1118. exec vavoom ''
  1119. go
  1120. exec vavoom 'blue'
  1121. go
  1122. eot
  1123. # note: prev line :you will need to have a newline immediately after eot
  1124.  
  1125. exit
  1126. # end of unix script
  1127. ---   end of make.vavoom --------------------------------------------
  1128.  
  1129.  
  1130.  
  1131.  
  1132.  
  1133. --- start of defntest.sh -------------------------------------------
  1134. #!/bin/sh
  1135. # Raymond Lew 1996-02-01
  1136. #
  1137. # test script: demonstrate with a bourne shell how an application
  1138. # would use the temp table definitions stored in the database
  1139. #
  1140. # note: you must run setup and make.vavoom first
  1141. #
  1142. # note: you have to change the passwords, id and etc. for your site
  1143. # note: you might have to some inline changes to make this work
  1144. #       check out the notes within the body
  1145.  
  1146. # get the table defn's into a text file
  1147. #
  1148. # note: next line :you will need to end the line immediately after eot \
  1149. isql -Ukryten -Pjollyguy -Sstarbug  -w255 << eot \
  1150. | grep -v '\-\-\-\-' | grep -v 'defntext  ' | grep -v ' affected' > tabletext
  1151. exec get_temp_defn ''
  1152. go
  1153. eot
  1154. # note: prev line :you will need to have a newline immediately after eot
  1155.  
  1156. # go mess around in vi
  1157. vi tabletext
  1158.  
  1159. isql -Ukryten -Pjollyguy -Sstarbug   -e << eot | more
  1160. `cat tabletext`
  1161. go
  1162. exec vavoom ''
  1163. go
  1164. exec vavoom 'blue'
  1165. go
  1166. eot
  1167. # note: prev line :you will need to have a newline immediately after eot
  1168.  
  1169. exit
  1170. # end of unix script
  1171. ---   end of defntest.sh -------------------------------------------
  1172.  
  1173.  
  1174. That's all, folks. Have Fun
  1175.  
  1176. Back to top
  1177.  
  1178. -------------------------------------------------------------------------------
  1179.  
  1180. 1.5.3: Differences between clustered and non-clustered
  1181.  
  1182. -------------------------------------------------------------------------------
  1183.  
  1184. Preface
  1185.  
  1186. I'd like to talk about the difference between a clustered and a non-clustered
  1187. index. The two are very different and it's very important to understand the
  1188. difference between the two to in order to know when and how to use each.
  1189.  
  1190. I've pondered hard to find the best analogy that I could think of and I've come
  1191. up with ... the phone book. Yes, a phone book.
  1192.  
  1193. Imagine that each page in our phone book is equivalent to a Sybase 2K data
  1194. page. Every time we read a page from our phone book it is equivalent to one
  1195. disk I/O.
  1196.  
  1197. Since we are imagining, let's also imagine that our mythical ASE (that runs
  1198. against the phone book) has only enough data cache to buffer 200 phone pages.
  1199. When our data cache gets full we have to flush an old page out so we can read
  1200. in a new one.
  1201.  
  1202. Fasten your seat belts, because here we go...
  1203.  
  1204. Clustered Index
  1205.  
  1206. A phone book lists everyone by last name. We have an A section, we have a B
  1207. section and so forth. Within each section my phone book is clever enough to
  1208. list the starting and ending names for the given page.
  1209.  
  1210. The phone book is clustered by last name.
  1211.  
  1212.     create clustered index on phone_book (last_name)
  1213.    
  1214. It's fast to perform the following queries on the phone book:
  1215.  
  1216.   * Find the address of those whose last name is Cisar.
  1217.   * Find the address of those whose last name is between Even and Fa
  1218.  
  1219. Searches that don't work well:
  1220.  
  1221.   * Find the address of those whose phone number is 440-1300.
  1222.   * Find the address of those whose prefix is 440
  1223.  
  1224. In order to determine the answer to the two above we'd have to search the
  1225. entire phone book. We can call that a table scan.
  1226.  
  1227. Non-Clustered Index
  1228.  
  1229. To help us solve the problem above we can build a non-clustered index.
  1230.  
  1231.     create nonclustered index on phone_book (phone_number)
  1232.    
  1233. Our non-clustered index will be built and maintained by our Mythical ASE as
  1234. follows:
  1235.  
  1236.  1. Create a data structure that will house a phone_number and information
  1237.     where the phone_number exists in the phone book: page number and the row
  1238.     within the page.
  1239.    
  1240.     The phone numbers will be kept in ascending order.
  1241.    
  1242.  2. Scan the entire phone book and add an entry to our data structure above for
  1243.     each phone number found.
  1244.  3. For each phone number found, note along side it the page number that it was
  1245.     located and which row it was in.
  1246.  
  1247. any time we insert, update or delete new numbers, our M-ASE will maintain this
  1248. secondary data structure. It's such a nice Server.
  1249.  
  1250. Now when we ask the question:
  1251.  
  1252.     Find the address of those whose phone number is 440-1300
  1253.    
  1254. we don't look at the phone book directly but go to our new data structure and
  1255. it tells us which page and row within the page the above phone number can be
  1256. found. Neat eh?
  1257.  
  1258. Draw backs? Well, yes. Because we probably still can't answer the question:
  1259.  
  1260.     Find the address of those whose prefix is 440
  1261.    
  1262. This is because of the data structure being used to implement non-clustered
  1263. indexes. The structure is a list of ordered values (phone numbers) which point
  1264. to the actual data in the phone book. This indirectness can lead to trouble
  1265. when a range or a match query is issued.
  1266.  
  1267. The structure may look like this:
  1268.  
  1269. ------------------------------------
  1270. |Phone Number   |  Page Number/Row |
  1271. ====================================
  1272. | 440-0000      |  300/23          |
  1273. | 440-0001      |  973/45          |
  1274. | 440-0002      |   23/2           |
  1275. | ...           |                  |
  1276. | 440-0030      |  973/45          |
  1277. | 440-0031      |  553/23          |
  1278. | ...           |                  |
  1279. ------------------------------------ 
  1280.  
  1281. As one can see, certain phone numbers may map to the same page. This makes
  1282. sense, but we need to consider one of our constraints: our Server only has room
  1283. for 200 phone pages.
  1284.  
  1285. What may happen is that we re-read the same phone page many times. This isn't a
  1286. problem if the phone page is in memory. We have limited memory, however, and we
  1287. may have to flush our memory to make room for other phone pages. So the
  1288. re-reading may actually be a disk I/O.
  1289.  
  1290. The Server needs to decide when it's best to do a table scan versus using the
  1291. non-clustered index to satisfy mini-range type of queries. The way it decides
  1292. this is by applying a heuristic based on the information maintained when an
  1293. update statistics is performed.
  1294.  
  1295. In summary, non-clustered indexes work really well when used for highly
  1296. selective queries and they may work for short, range type of queries.
  1297.  
  1298. Suggested Uses
  1299.  
  1300. Having suffered many table corruption situations (with 150 ASEs who wouldn't? :
  1301. -)), I'd say always have a clustered index. With a clustered index you can fish
  1302. data out around the bad spots on the table thus having minimal data loss.
  1303.  
  1304. When you cluster, build the cluster to satisfy the largest percentage of range
  1305. type queries. Don't put the clustered index on your primary key because
  1306. typically primary keys are increasing linearly. What happens is that you end up
  1307. inserting all new rows at the end of the table thus creating a hot spot on the
  1308. last data page.
  1309.  
  1310. For detail rows, create the clustered index on the commonly accessed foreign
  1311. key. This will aid joins from the master to it.
  1312.  
  1313. Use nonclustered index to aid queries where your selection is very selective.
  1314. For example, primary keys. :-)
  1315.  
  1316. Back to top
  1317.  
  1318. -------------------------------------------------------------------------------
  1319.  
  1320. 1.5.4: Optimistic versus Pessimistic locking?
  1321.  
  1322. -------------------------------------------------------------------------------
  1323.  
  1324. This is the same problem another poster had ... basically locking a record to
  1325. ensure that it hasn't changed underneath ya.
  1326.  
  1327. fcasas@ix.netcom.com has a pretty nifty solution if you are using ct-lib (I'll
  1328. include that below -- hope it's okay Francisco ... :-)) ...
  1329.  
  1330. Basically the problem you are facing is one of being a pessimist or an
  1331. optimist.
  1332.  
  1333. I contend that your business really needs to drive this.
  1334.  
  1335. Most businesses (from my experience) can be optimistic.
  1336.  
  1337. That is, if you are optimistic that the chances that someone is going to change
  1338. something from underneath the end-user is low, then do nothing about it.
  1339.  
  1340. On the other hand, if you are pessimistic that someone may change something
  1341. underneath the end-user, you can solve it at least as follows:
  1342.  
  1343. Solution #1
  1344.  
  1345. Use a timestamp on a header table that would be shared by the common data. This
  1346. timestamp field is a Sybase datatype and has nothing to do with the current
  1347. time. Do not attempt to do any operations on this column other than
  1348. comparisons. What you do is when you grab data to present to the end-user, have
  1349. the client software also grab the timestamp column value. After some thing
  1350. time, if the end-user wishes to update the database, compare the client
  1351. timestamp with what's in the database and it it's changed, then you can take
  1352. appropriate action: again this is dictated by the business.
  1353.  
  1354. Problem #1
  1355.  
  1356. If users are sharing tables but columns are not shared, there's no way to
  1357. detect this using timestamps because it's not sufficiently granular.
  1358.  
  1359. Solution #2 (presented by fcasas)
  1360.  
  1361. ... Also are you coding to ct-lib directly? If so there's something that you
  1362. could have done, or may still be able to do if you are using cursors.
  1363.  
  1364. With ct-lib there's a ct_describe function that lets you see key data. This
  1365. allows you to implement optimistic locking with cursors and not need
  1366. timestamps. Timestamps are nice, but they are changed when any column on a row
  1367. changes, while the ct_describe mechanism detects changes at the columns level
  1368. for a greater degree of granularity of the change. In other words, the
  1369. timestamp granularity is at the row, while ct_describes CS_VERSION_KEY provides
  1370. you with granularity at the column level.
  1371.  
  1372. Unfortunately this is not well documented and you will have to look at the
  1373. training guide and the manuals very closely.
  1374.  
  1375. Further if you are using cursors do not make use of the
  1376.  
  1377.     [for {read only | update [of column_name_list]}]
  1378.    
  1379. of the select statement. Omitting this clause will still get you data that can
  1380. still be updated and still only place a shared lock on the page. If you use the
  1381. read only clause you are acquiring shared locks, but the cursor is not
  1382. updatable. However, if you say
  1383.  
  1384.     update [of ...
  1385.    
  1386. will place updated locks on the page, thus causing contention. So, if you are
  1387. using cursors don't use the above clause. So, could you answer the following
  1388. three questions:
  1389.  
  1390.  1. Are you using optimistic locking?
  1391.  2. Are you coding to ct-lib?
  1392.  3. Are you using cursors?
  1393.  
  1394. Problem #2
  1395.  
  1396. You need to be coding with ct-lib ...
  1397.  
  1398. Solution #3
  1399.  
  1400. Do nothing and be optimistic. We do a lot of that in our shop and it's really
  1401. not that big of a problem.
  1402.  
  1403. Problem #3
  1404.  
  1405. Users may clobber each other's changes ... then they'll come looking for you to
  1406. clobber you! :-)
  1407.  
  1408. Back to top
  1409.  
  1410. -------------------------------------------------------------------------------
  1411.  
  1412. 1.5.5: How do I force an index to be used?
  1413.  
  1414. -------------------------------------------------------------------------------
  1415.  
  1416. System 11
  1417.  
  1418. In System 11, the binding of the internal ordinal value is alleviated so that
  1419. instead of using the ordinal index value, the index name can be used instead:
  1420.  
  1421. select ... from my_table (index my_first_index)
  1422.  
  1423. Sybase 4.x and Sybase System 10
  1424.  
  1425. All indexes have an ordinal value assigned to them. For example, the following
  1426. query will return the ordinal value of all the indexes on my_table:
  1427.  
  1428. select name, indid
  1429.   from sysindexes
  1430. where id = object_id("my_table")
  1431.  
  1432. Assuming that we wanted to force the usuage of index numbered three:
  1433.  
  1434. select ... from my_table(3)
  1435.  
  1436. Note: using a value of zero is equivalent to forcing a table scan.  Whilst this
  1437. sounds like a daft thing to do, sometimes a table scan is a better solution
  1438. than heavy index scanning.
  1439.  
  1440. It is essential that all index hints be well documented.  This is good DBA
  1441. practice.  It is especially true for Sybase System 10 and below.
  1442.  
  1443. One scheme that I have used that works quite well is to implement a table
  1444. similar to sysdepends in the database that contains the index hints.
  1445.  
  1446. create table idxdepends
  1447.     (
  1448.     tblname   varchar(32) not null -- Table being hinted
  1449.    ,depname   varchar(50) not null -- Proc, trigger or app that
  1450.                                    -- contains hint.
  1451.    ,idxname   varchar(32) not null -- Index being hinted at
  1452.  --,hintcount         int     null -- You may want to count the
  1453.                                    -- number of hints per proc.
  1454.     )
  1455.  
  1456. Obviously it is a manual process to keep the table populated, but it can save a
  1457. lot of trouble later on.
  1458.  
  1459. Back to top
  1460.  
  1461. -------------------------------------------------------------------------------
  1462.  
  1463. 1.5.6: Why place tempdb and log on low numbered devices?
  1464.  
  1465. -------------------------------------------------------------------------------
  1466.  
  1467. System 10 and below.
  1468.  
  1469. In System 10 and Sybase 4.X, the I/O scheduler starts at logical device (ldev)
  1470. zero and works up the ldev list looking for outstanding I/O's to process.
  1471. Taking this into consideration, the following device fragments (disk init)
  1472. should be added before any others:
  1473.  
  1474.  1. tempdb
  1475.  2. log
  1476.  
  1477. Back to top
  1478.  
  1479. -------------------------------------------------------------------------------
  1480.  
  1481.