SQL performance with advantage

1. SQL performance with advantage Peter funkSOFTWARE ENGINEER, ADVANTAGE R&D May 19, 2011

2. Agenda • SQL Engine Architecture • Cursors & Cursor Types • Null Values • Management Via SQL • System Tables • Optimization Considerations • Optimization Tools • Version 10 improvements

3. Architecture • Clients send SQL statement to the server • Processing is performed on the server • SELECT statements pass back cursor • Other SQL statements pass back status • When used with Advantage Local Server, everything is driven by the client

4. What Are Cursors? • Cursors are returned by SELECT statements • Advantage maintains current cursor position on the server • Record at cursor position is returned to client • Client record cache setting may cause more than 1 record to be returned to the client • There are two types of cursors:

5. Dynamic (Live) Cursors • Created when query can be mapped to a single table (with optional filters) • Supports editing (optional) • Uses fewer server resources • As a general rule, live cursors are preferable to static cursors!

6. Static Cursors • Created when a live cursor is not possible • Static cursors are read-only • Use triggers on static views as an alternative • In general, slower than a live cursor • Populated on demand • Use ORDER BY, “GoBottom”, “GoTo”, “RecCount”, and “Index” operations with caution

7. What Makes a Cursor Static • DISTINCT in the SELECT clause • Joins and subqueries • Aggregate functions (MIN, MAX, AVG, etc.) • GROUP BY and HAVING clauses • Certain scalar functions in the WHERE clause • Memo field used in a WHERE clause • Use of LIKE operator • Expressions in the select list • Request Static Cursor

8. How to Use Cursors • Use them like tables • Create temporary indexes • Set filters • Convert to table with AdsCopyTable or AdsConvertTable • AdsGotoRecord may produce unexpected results • Disallowed operations: • Pack, Zap (EmptyTable), LockTable, Reindex

9. NULL Values* • What does NULL mean? • Undefined • Unknown • Does not Apply • The meaning depends on the situation… • Why does this matter? • What is “5-NULL”? • Pay particular attention to column functions. • Aggregate functions ignore NULL values

10. Eliminating NULL Values • Several SQL scalar functions exist: • IFNULL / ISNULL • IFNULL( expr, value ) – returns value if expr is NULL • COALESCE • COALESCE( expr1, …, exprN ) – returns first non-NULL expr • Additional Conditional functions: • “IF” statement: IIF IIF ( <boolean_expression> , <true_expression> , <false_expression> ) Ex: SELECT name, IIF( married = TRUE, ‘Married’, ‘Single’ ) as “Marital Status” FROM employee

11. Management Via SQL • “Canned” Procedures • Over 100 Stored Procedures are available • Data Dictionary connection is required for some • Use: Execute Procedure <ProcName>(<arg-list>); • Some examples… • SELECT * FROM System.SystemProcedures • Help File > sp_<name of procedure> • sp_Reindex(<table name>) • Rebuilds all auto-open indexes associated with the given table.

12. Table Management • sp_CreateIndex(..) – Create an index using an index expression containing an Expression Engine function • sp_PackTable( <table name> ) – Pack the table • Sp_ZapTable( <table name> ) – Removes all data from the table

13. User Management • sp_CreateUser(<Username>, <password>, [<comment>] ) • sp_CreateGroup( <GroupName>, [<comment>] ) • sp_AddUserToGroup(<UserName>,<GroupName> ) • GRANT/REVOKE can be used to manage permissions on users/groups

14. optimization

15. Indexes for Optimization* • Permanent single-key and multi-key indexes used • Multi-key indexes can only be used to (fully) optimize strict equality comparisons combined using the AND operator • Temporary indexes may be created on-the-fly if a permanent index does not exist • Conditional and custom indexes not used • ARC “Show Plan” is the easiest way to see what indexes may be helpful

16. Indexes on Scalar Functions* • Index keys built with expression engine scalar functions may also be used for scalar functions used in the WHERE clause • Indexes can now be built using these scalars • WEEK • QUARTER • DAYOFWEEK, DAYOFYEAR • HOUR, MINUTE, SECOND • DAYNAME • MONTHNAME • These new scalars can also be indexed • ISOWEEK • CHARTOHEX, HEXTOCHAR

17. Using Show Plan Information* • Look for the Red Dot • Create missing indexes • Pay attention to the “Estimated Number of Executions” • More on this later • Now includes more detailed AOF information • AOF segment evaluation ordering • AOF segment key count

18. Parameterized Queries* • Frequently-executed queries can be optimized by turning them into parameterized queries • Parameter data is only sent to server when changed • Query text is not sent for successive executions • Example: Prepared vs. Direct Execution

19. Live Isn’t Always Better* • Static cursors only have to be populated once • If the intent is to iterate through result set multiple times, then a static cursor may give better performance overall • Static cursors may benefit from new caching • Example: Live Versus Static Cursor

20. Small Result Sets / Large Tables* • Traversing a live cursor can be “slow” • Forcing temporary index creation may help • This index is created using only the records that are included in the result set • Order by a field that has no permanent index • The SQL engine can detect this condition, but sometimes this can still be helpful

21. Preventing ambiguity in evaluation* • INNER JOINS are inherently ambiguous • Table ordering in the statement may not affect evaluation • Database engines can choose “incorrectly” • Can we do something to prevent this? • To avoid the ambiguity, use an outer join • Remember, an INNER JOIN is really a subset of a {LEFT|RIGHT} JOIN • An INNER JOIN becomes a LEFT [OUTER] JOIN • Add an additional condition to the WHERE clause • This eliminates the records not included in an INNER JOIN

22. Correlated Sub-queries* • Correlated Sub-Queries cannot be optimized as well as some non-correlated subqueries • Correlated sub-query example: • SELECT * FROM t1 WHERE column1 IN (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2); • Try to re-write statements to remove the outer reference • Even a sub-optimal query without the outer reference can see huge performance gains

23. SQL Monitor* • Displays progress of all active queries on the server • ADSSYS account can cancel any SQL statement executed on a Dictionary connection • Useful for finding out what’s happening during slow server response

24. Logging Query Optimization* • Enable Logging • sp_EnableQueryLogging ( TableName, TruncateExistingData, LogOnlyUnoptimizedQueries, MinimumTimeBeforeLogging, EncryptionPassword ) • Disable Logging • sp_DisableQueryLogging • Log table

25. Features That May Improve Performance • Table Data Caching for static lookup tables • #temp tables now cached in-memory when possible • File handles can be re-used when files must be created • Table Creation is now faster • Affects triggers and stored procedures • SQL Intermediate files are handled more efficiently • AOF evaluation may use multi-segment indexes more often • Binary “DELETED()” indexes may be created • Can affect table reads and AOF Optimization • Improved AOF segment re-ordering (based on enhancements to cost estimate algorithm)

26. SQL Bitwise Operators • The Advantage query engine now supports six bitwise operators: & (AND), | (OR), ^ (XOR), ~ (NOT), >> (SHIFT RIGHT), << (SHIFT LEFT). • Each operator requires the operands to be numeric or binary types (some exceptions apply). • In all cases, the operands will be converted to a 32 bit integer to perform the operation. • The result of the operation will always be an integer type with the size dependent on the size of the operands (BIT, SHORT, or INTEGER).

27. Stored Procedure Results in the FROM Clause • The Advantage query engine now supports using stored procedure results in place of a table or view reference in the FROM clause. For example, get current number of connections and users: • SELECT COUNT(*) AS Connections, COUNT( DISTINCT UserName) AS Users FROM (EXECUTE PROCEDURE sp_mgGetConnectedUsers()) a

28. Summary of optimization • Try to produce live cursors instead of static cursors • Look for missing indexes • Pay attention to “Estimated Number of Executions” • Use Parameterized queries • This can also prevent SQL injection attacks in web apps • Sometimes a static cursor can be preferable • Forcing the creation of a temporary index can be helpful • Use the Query Log to see what queries should be updated • Biggest payoff is in frequently executed or slow queries • Give Version 10 a try!