How To Make SQL Anywhere Slow

Oh, boy, am I gonna get in trouble with this one... talking about Bad Stuff.

But, sometimes it's good to look at a problem (say, performance) from a different angle. This list actually started as a conversation in the bar, way back in 2001, and I just stumbled on a follow-up email today.

Most of the original points still applied, and with a little cleanup and a few points from the new Capacity Planning whitepaper, here it is... in no particular order...

How To Make SQL Anywhere Slow

1. Turn AutoCommit on. Or forget to turn it off when using ODBC, ADO.NET, JDBC, PHP, etc.

2. Use 1K or 16K or 32K pages.

3. Set ROW_COUNTS to ON so every query gets executed twice.

4. Set ISOLATION_LEVEL to 2 or 3.

5. Set MAX_PLANS_CACHED to 0 so there's no access plan caching.

6. Use SET OPTION instead of SET TEMPORARY OPTION to get all those SYSOPTION changes recorded in the log.

7. Set OPTIMIZATION_GOAL to First-row for complex queries, especially when your application really wants all the rows (which it probably does, all of the time).
   

8. Set PREFETCH to OFF when scrolling through a result set in sequence.

9. Set PREFETCH to ON or Conditional when doing ABSOLUTE FETCHes randomly in the result set.

10. Set QUERY_PLAN_ON_OPEN to ON.

11. Set RECOVERY_TIME to 1 minute to force lots of unnecessary checkpoints.

12. Set a small cache size.

13. Put lots of logic in user-defined functions, like SQL statements using many joins and temporary tables.

14. Call user-defined functions in the WHERE clause.

15. DROP STATISTICS to make the optimizer stupid; it's DROP OPTIMIZER STATISTICS in Version 5 through 7.

16. Code DDL in stored procedures to increase the chances of single-threading.

17. Use base tables as temporary tables to get lots of unnecessary transaction log I/O.

18. In Version 7 and earlier, create indexes where the first 9 bytes are almost universally the same value.

19. Run without a transaction log, thus forcing a checkpoint on every single commit.

20. Put the transaction log mirror on the same physical device as the transaction log, to get twice the I/O without any benefit.

21. Do joins in the application.

22. Move WHERE clause logic into the application.

23. Choose inappropriate indexes.

24. In Version 6 and earlier, don't put indexes on AUTOINCREMENT columns, thus forcing SELECT MAX() after startup.

25. Leave off primary keys so recovery will be slow.

26. Try to emulate the query engine by using intermediate tables step-by-step, in procedures and application code.

27. INSERT rows with missing values, then UPDATE to fill them in, so you get lots and lots of row splits.

28. Don't use wide fetches when fetching large result sets.

29. Don't use wide inserts when inserting lots of data.

30. Use INSERT or INPUT FROM instead of LOAD TABLE when initially populating a database.

31. Use Java in the database.

32. Specify redundant CHECK constraints.

33. PREPARE the same statement over and over again, every single time it's used.

34. Use jConnect instead of the iAnywhere JDBC driver.

35. Use Open Client so you can get TDS.

36. Create convoys on hot rows and pages, say by making thousands of connections use the same rows in the same tables.

37. Send lots of small requests across a high latency network.

38. Use a custom key table instead of DEFAULT AUTOINCREMENT so all your inserts are serialized.

Caveat: Almost none of these points describe default behavior. Generally, you have to work really hard to make SQL Anywhere slow... alas, people have been known to work hard.

Plus: Google says "No results found" for "How To Make Oracle Slow", possibly because Oracle runs slow out of the box (zing! :)