The first two articles [1,2] are work jointly authored by researchers at Duke University and IBM Almaden. The papers describe DIADS, a prototype performance diagnostic tool designed to assist in the diagnosis of performance problems when a database server – the authors use Postgres as a test system – utilizes a SAN (Storage Area Network) for its disk resources. The problem with a SAN is that it is a complex, independent system consisting of logical units of disk storage ( pools or volumes ) which typically services multiple application/database servers simultaneously. All too often SAN administration is done independently, forcing DBAs to treat a SAN as a “black box”.

Borisov et al. developed a prototype diagnostic system called DIADS which uses annotated plan graphs to illustrate the use of SAN resources with specific query plan operators. Moreover, DIADS uses configuration dependency analysis and symptom signatures to drill down in the detail of specific annotated plans. DIADS has the ability to compare access plans for the same query, and computes metrics based on the deviation from the mean times for each operator. SAN monitoring data, including physical and logical configuration details, component connectivity, configuration changes over time, and DBA-defined events are included in annotated query plans, enabling DBAs to analyze the actual performance characteristics of specific plan operators together with detailed SAN statistics in a single display. DIADS contains a knowledge base as well, enabling expert-system analysis of plan operator performance degradation through the tracking of correlated and dependent plan operators, correlated operator cardinalities, and the inclusion of a symptoms database to help in the analysis of cause versus effect.

The second work is by Goetz Graefe, Harumi Kuno, and Janet Wiener of HP Labs. In their work, the authors study the problem of determining the level of robustness in a query execution engine – that is, the ability of a server to deliver consistent performance across a variety of unexpected run-time conditions: for example, errors in cardinality estimation or resource contention. The authors argue that query execution robustness is as important as the underlying fundamentals of the query operators themselves – and I couldn’t agree more.

The authors’ approach is to describe the robustness of a plan operator visually using plan robustness maps. These two- or three-dimensional graphs can then be used to reason about how particular execution strategies degrade as the amount of work increases or as system resources become constrained:

Reflecting on the visualization techniques employed here, these diagrams enable rapid verification of expected performance, testing of hypotheses, and insight into the absolute and relative performance of alternative query execution plans. Moreover, even for this very simple query, there is a plethora of query execution plans. Investigating many plans over a parameter space with multiple dimensions is possible only with efficient visualizations.

This work provides an interesting perspective of query execution performance that compliments other work that addresses optimization quality, or dynamic re-optimization of SQL requests on-the-fly: that is, the ability of the system’s optimizer to find the optimal plan for a specific set of system parameters.

[1] Nedyalko Borisov, Shivnath Babu, Sandeep Uttamchandani, Ramani Routray, and Aameek Singh (January 2009). Why did my query slow down? In Proceedings, 4th Biennial Conference on Innovative Data Systems Research (CIDR), Asilomar, California.

[2] Nedyalko Borisov, Shivnath Babu, Sandeep Uttamchandani, Ramani Routray, and Aameek Singh (February 2009). DIADS: Addressing the “My-Problem-or-Yours” Syndrome with Integrated SAN and Database Diagnosis. In Proceedings of the 7th USENIX Conference on File and Storage Technologies (FAST’09), San Francisco, California.

[3] Goetz Graefe, Harumi Kuno, and Janet L. Wiener (January 2009). Visualizing the robustness of query execution. In Proceedings, 4th Biennial Conference on Innovative Data Systems Research (CIDR), Asilomar, California.

With respect to disk drives, several large studies of disk drive reliability [2,3,4,7] have been published in the last few years. In particular, the study done at Google [4] showed a steep increase in failure rates – to between 6 and 10 percent – once a drive passed three years of usage, an interesting point since many disk drive manufacturers offer three-year warranties. Their study also showed a lower correlation between heat and drive failure in later-model drives, something that James Hamilton has written about recently in the push towards using less air conditioning within data centers. Recently at FAST 2009, Alyssa Henry of Amazon [6] spoke in her conference keynote that, at Amazon, the Amazon Simple Storage (EC3) data service sees a hard disk failure rate of 3-5 percent per year across the board, though I am sure, given Google’s survey results, that Amazon’s failure experience is not uniformly distributed across all disk drive manufacturers. Iliadis and Hu [3] believe that the trend towards lower-cost magnetic media results in higher failure rates, a conclusion also reached [7] by Remzi Arpaci-Dusseau and his team at the University of Wisconsin in Madison. To some extent, at least, you do get what you pay for.

The actual failure rates reported in these studies is vastly different from the reliability metrics offered by disk drive manufacturers. Moreover, disk hardware failure is only part of the story. Previous work by Remzi Arpaci-Dusseau and his research team at Wisconsin found that transient errors with magnetic disk media were commonplace. Here is a quote from the summary of the Linux Storage & Filesystem Workshop, held in San Jose in February 2008:

Ric Wheeler (aside: now with RedHat) introduced the perennial error-handling topic with the comment that bad sector handling had markedly improved over the “total disaster” it was in 2007. He moved on to silent data corruption and noted that the situation here was improving with data checksumming now being built into filesystems (most notably BTRFS and XFS) and emerging support for T10 DIF. The “forced unmount” topic provoked a lengthy discussion, with James Bottomley claiming that, at least from a block point of view, everything should just work (surprise ejection of USB storage was cited as the example). Ric countered that NFS still doesn’t work and others pointed out that even if block I/O works, the filesystem might still not release the inodes. Ted Ts’o closed the debate by drawing attention to a yet to be presented paper at FAST ’08 showing over 1,300 cases where errors were dropped or lost in the block and filesystem layers. (emphasis added)

Reference [5] below studies the lack or mis-reporting of both transient and “hard” filesystem errors across several filesystems. Here is the first paragraph of the paper’s abstract:

The reliability of file systems depends in part on how well they propagate errors. We develop a static analysis technique, EDP, that analyzes how file systems and storage device drivers propagate error codes. Running our EDP analysis on all file systems and 3 major storage device drivers in Linux 2.6, we find that errors are often incorrectly propagated; 1153 calls (13%) drop an error code without handling it.

Write caching or out-of-order writes can cause additional problems. The use of EXT3 on Linux systems, in particular, can result in a corrupt filesystem upon a catastrophic hardware failure due to EXT3’s lack of support for checksumming when writing to the journal – which is supported in EXT4.

Arpaci-Dusseau and his research team at Wisconsin have just recently taken this error analysis to the next level [1]. They purposefully and systematically introduced errors into a MySQL database to determine the server’s ability to recover from the sorts of hard and transient failures known to occur on the filesystems studied previously. Their results, coupled with the sweeping disk failure studies mentioned above, should give all DBAs reason to worry. I would encourage DBAs to review the papers below.

And keep those backups handy.

[1] Sriram Subramanian, Yupu Zhang, Rajiv Vaidyanathan, Haryadi S. Gunawi, Andrea C. Arpaci-Dusseau, Remzi H. Arpaci-Dusseau, and Jeffrey F. Naughton (April 2010). Impact of Disk Corruption on Open-Source DBMS. In Proceedings, 2010 IEEE International Conference on Data Engineering, Long Beach, California. To appear.

[2] Bianca Schroeder and Garth A. Gibson (February 2007). Disk failures in the real world: What does an MTTF of 1,000,000 hours mean to you? In Proceedings, 5th USENIX Conference on File and Storage Technologies, San Jose, California, pp. 1-16.

[3] Ilias Iliadis and Xiao-Yu Hu (June 2008). Reliability Assurance of RAID Storage Systems for a Wide Range of Latent Sector Errors. Proceedings of the International Conference on Networking, Architecture, and Storage, Chongqing, China. IEEE Computer Society, ISBN 978-0-7695-3187-8.

[4] Eduardo Pinheiro, Wolf-Dietrich Weber, and Luiz André Barroso (February 2007). Failure Trends in a Large Disk Drive Population. In Proceedings, 5th USENIX Conference on File and Storage Technologies, San Jose, California, pp. 1-16.

[5] Haryadi S. Gunawi, Cindy Rubio-González, Andrea C. Arpaci-Dusseau, Remzi H. Arpaci-Dusseau, and Ben Liblit (February 2008). EIO: Error Handling is Occasionally Correct. In Proceedings, 6th USENIX Conference on File and Storage Technologies, San Jose, California, pp. 207-222.

[6] Alyssa Henry (February 2009). Cloud Storage FUD (Failure, Uncertainty, and Durability). Keynote address, 7th USENIX Conference on File and Storage Technologies, San Francisco, California.

[7] Lakshmi N. Bairavasundaram, Garth R. Goodson, Bianca Schroeder, Andrea C. Arpaci-Dusseau, and Remzi H. Arpaci-Dusseau (February 2008). An Analysis of Data Corruption in the Storage Stack. In Proceedings of the 6th USENIX Symposium on File and Storage Technologies (FAST ’08), San Jose, California, pp. 223–238.

In early October, Shivnath Babu, PC chair for SMDB 2010, along with the Executive Committee of the IEEE Workgroup on Self-managing Database Systems announced an extension to the paper submission deadline from today, October 22, to November 23, 2009 to accommodate conflicting deadlines with the Eurosys and ACM SIGMOD conferences.

Hi Glenn,

Just stumbled across your blog about the RowGenerator (http://iablog.sybase.com/paulley/2009/09/using-rowgenerator/) . I don’t know whether iAnywhere supports the WITH clause but (since the standard does) you could use that one as well (similar to your sa_rowgenerator procedure):

1 2 3 4 5

WITH dummy (counter) AS (SELECT counter FROM TABLE(VALUES (1)) AS x(counter) UNION ALL SELECT counter + 1 FROM dummy WHERE counter < 1000 ) SELECT counter FROM dummy

would return 1000 rows.

I welcomed Jan-Eike’s contribution because, as he quite rightly points out, it is straightforward to generate a set of identifiers recursively using the SQL standard’s common table expression syntax, in this case using the recursive UNION construction.

One can use Jan-Eike’s example almost verbatim in SQL Anywhere. The issues with Jan-Eike’s SQL query are:

• In SQL Anywhere, one must include the RECURSIVE keyword when specifying a recursive query;
• SQL Anywhere servers do not recognize the TABLE keyword; and
• SQL Anywhere already contains a (real) table, DUMMY, that generates a single-row, single-column result set.

So here is a version of Jan-Eike’s example that generates the values between 1 and 10 in SQL Anywhere:

6
7
8
9
10

WITH RECURSIVE foo(counter) AS
  ( SELECT 1 FROM DUMMY
     UNION ALL
   SELECT counter + 1 FROM foo WHERE counter < 10 )
SELECT * FROM foo

that defines the common table expression “foo” (instead of “dummy”) and generates the specified values. The graphical plan for this query is as follows:

Some points to mention:

• Specifying a larger number of values – and hence a deeper level of recursion – may require setting the MAX_RECURSIVE_ITERATIONS connection option to a higher value.
• Jan-Eike’s example generates a sequential set of values, equivalent to what the RowGenerator system table or the sa_rowgenerator() system procedure generates. However, one could modify this query to generate a non-contiguous sequence of any values desired, simply by rewriting the SELECT list expressions in the common table expression.
• Finally, while this recursive version does have utility, the RowGenerator system table may still be a better approach. The advantage of RowGenerator is that it is a (static) base table; hence the query optimizer is much better able to estimate the cardinality of intermediate results when RowGenerator is used within a complex query than when a common table expression is used.

In his post, Jason mentions the availability of a new Type 2 JDBC driver in the SQL Anywhere 11.0.1 release which does not require ODBC to be installed on the machine, significantly simplifying the deployment of JDBC applications on non-Windows platforms with SQL Anywhere and offering a slight performance boost to boot. The option of using this new Type 2 driver (sajdbc.jar) is not reflected in the product documentation, as this driver was made available beginning with Version 11.0.1 builds after 2250.

Jason also mentions that our forthcoming Innsbruck release will offer JDBC 4.0 support, which will eliminate the need to register the driver from within the application.

Unsurprisingly news coverage of this probe has been widespread (see here and here). The current press coverage notwithstanding, I would argue that we are a long, long way from anything of consequence from this “probe”. The United States government attempted to restrict IBM’s business practices during the late 1970’s and early 1980’s with a long, drawn-out anti-trust court case that, eventually, was dropped by the Reagan administration. Withdrawing from the case subsequently prompted the lead government counsel to write a book [1] about the wealth of evidence in the case – at least, in his opinion. I read Delamarter’s book for the first time in early 1987 and even today it’s still a great read.

The players in this case are different, except for the two that matter the most. But I would not count on a different outcome.

[1] Richard T. Delamarter (October 1986). Big Blue: IBM’s Use and Abuse of Power. Dodd Mead; 1st edition. ISBN 978-0396085157.

Both the jConnect and iAnywhere drivers support JDBC 3.0. jConnect is a “pure Java” solution (termed a Type 4 JDBC driver), while the iAnywhere driver is a Type 1 driver because of its reliance on the SQL Anywhere ODBC driver which must be properly installed. It is sometimes argued that a “pure Java” solution is better/faster/more robust; hence, on paper, jConnect should be “better” than the iAnywhere Type 1 driver. However, if one looks more closely, the significant differences between the two solutions are (1) memory management, (2) the use of the TDS wire protocol, and (3) differences in semantics. We look at each of these in turn.

Memory management

With a pure Java solution:

• All objects are managed by the Java virtual machine.
• Java garbage collection cleans things up automatically. The application programmer does not have to worry about objects sticking around indefinitely, memory leaks or objects disappearing while still in use.

Unfortunately, the weakness of pure Java solutions is the same: memory management. The application programmer has little or no real control over the lifespan of an object. Moreover, the programmer has no effective control over garbage collection; garbage collection can kick in at critical times, resulting in random and unreproducible performance problems.

With a hybrid solution such as the iAnywhere JDBC driver, the most important advantage is memory management:

• The programmer retains full control over non-Java objects.
• Garbage collection can be prevented or postponed by non-Java references to Java objects.

However, as with pure Java, the greatest disadvantage of a hybrid solution is – you guessed it – also memory management. In the hybrid case, non-Java objects need to be managed explicitly; program errors lead to memory leaks at best, and memory corruption or GPFs at worst. Moreover, if Java object references are held too long, Java garbage collection won’t kick in.

CMDSEQ versus TDS

jConnect uses Sybase ASE’s native wire protocol, the Tabular Data Stream (TDS) protocol, whereas the iAnywhere JDBC driver uses SQL Anywhere’s native wire protocol which is called Command Sequence (CMDSEQ). There are both semantic and performance differences between the use of the two protocols; each has advantages and disadvantages.

An advantage of TDS is that it supports “fire hose” cursors. That is, with a single TDS language command token one can instruct the server to execute a set of statements, describe all the result sets, and return all the results in one go to the client. In situations where the application desires all of the rows of a result set(s), a fire hose cursor does offer a performance advantage by reducing the amount of round-trip traffic over the wire. However, this comes at a cost: it is the client that is responsible for caching the result set, and the client that must implement cursor scrolling. The TDS client supports a “window” of rows in the result set that the Java application can scroll through – both forwards and backwards. However, should scrolling occur to a range of rows outside this window the entire request is re-issued to the server – necessary since prior rows outside the “window” have been lost. Hence, in this model with scrollable cursors, cursor sensitivity semantics are impossible to guarantee. Moreover, with very large result sets the communication stream can become blocked if the client cannot process the returned rows quickly enough, which can, in turn, block the server.

While fire-hose cursors give an advantage to jConnect connections under the right circumstances, recently-added support for adaptive prefetching in CMDSEQ (see below) mitigates this advantage. Moreover, there are several additional features supported by the iAnywhere JDBC driver that provide advantages over jConnect. These include:

• TDS is limited to TCP/IP, even for local connections, while the iAnywhere JDBC driver can use either TCP/IP or shared memory. This means that when using jConnect applications cannot automatically start and stop local database servers, since this is supported only with shared memory connections.
• Strong encryption support, including RSA, RSA-FIPS, and ECC encryption technologies.
• Complete server-side cursor support. jConnect does not support server-side cursors; it implements a cursor on the client-side by retrieving the entire result set across the network, even if the client will only use a small number of rows from that result set. When using jConnect, application programmers must be careful to write their SQL queries to return the smallest result set necessary, rather than rely on FETCHing only the first few rows, since the entire result set is sent to the client with each SQL request.
• Complete AppInfo support. jConnect truncates AppInfo details.
• Integrated logins on Windows platforms.
• Richer batch SQL statement support – for example, wide (batch) inserts and wide fetches. With SQL Anywhere, jConnect only fully supports wide fetches. jConnect does support wide inserts from the application, which reduces the amount of network traffic required, but on the server TDS wide inserts are simulated, with each row initiating a separate INSERT statement. In contrast, the iAnywhere JDBC driver efficiently supports both wide inserts and wide fetches.

Adaptive prefetching with CMDSEQ

SQL Anywhere version 11 introduced adaptive prefetch as a variant of prefetch behaviour with CMDSEQ connections. Prefetch is designed to reduce communication in a client-server environment by transferring sets of rows to the client in advance of a FETCH request, and is enabled by default. Prefetching can be disabled outright by specifying the DisableMultiRowFetch connection parameter, or by setting the Prefetch connection option to OFF. Prefetch is turned off for cursors declared with sensitive value semantics.

With adaptive prefetching, a SQL Anywhere CMDSEQ client will automatically adjust the number of rows that are prefetched – increasing or decreasing – depending on application behaviour. A hard limit on the maximum number of rows that will be prefetched is 1000. Adaptive prefetching is also controlled by number of rows the application can FETCH in one elapsed second. Adaptive prefetching is enabled for cursors for which all of the following are true:

• ODBC and OLE DB: FORWARD ONLY, READ ONLY (default) cursor types; ESQL: DYNAMIC SCROLL (default), NO SCROLL and INSENSITIVE cursor types; all ADO.Net cursors
• only FETCH NEXT operations are done (no absolute, relative or backwards fetching)
• the application does not change the host variable type between fetches and does not use GET DATA to get column data in chunks (but using one GET DATA to retrieve the value is fine).

jConnect semantics

In addition to the automatic setting of connection options to ASE-equivalent settings upon connecting with jConnect – described in my previous post – there are other semantic differences with jConnect. They include:

• The TDS protocol does not support dates or timestamps prior to January 1, 1753.
• Fixed-length CHAR and BINARY values are automatically padded upon retrieval from blank-padded databases.
• With older versions of jConnect, empty string values – strings of length zero – are returned to the application as a string with a single blank in it. This is because earlier versions of TDS did not distinguish between an empty string and the NULL value.

If a JDBC application wanted to use jConnect but not want Sybase ASE-like behaviour, then the application would have to:

• Revert the connection option settings issued by the sp_tsql_environment() system procedure by setting these options temporarily immediately after connecting.
• Set the connection option RETURN_DATE_TIME_AS_STRING to ON in order to get SQL Anywhere to always return DATE/TIME/TIMESTAMP values as strings. This is to overcome the inability of TDS to handle dates prior to January 1, 1753.
• Set the jConnect option “dynamic prepare” to TRUE to make sure prepared statements are not re-PREPAREd every time they are used.
• Set a cursor name for each statement in order to force jConnect to use TDS cursors instead of fire-hose cursors. Note that with SQL Anywhere, jConnect will still cache result sets on the client regardless of which cursor type is used.
• Set the fetch size explicitly on every statement in order to get jConnect to mimic CMDSEQ prefetch behaviour.
• For older versions of jConnect:
  • Handle ’single-blank strings’ as empty strings.
  • Refrain from using unsigned data types, since unsigned values are not supported with older jConnect releases.

In a subsequent post I’ll outline performance differences between the jConnect and iAnywhere drivers. In our experience with customer applications, most applications benefit from a significant performance boost by switching to the iAnywhere JDBC driver, occasionally up to a factor of two, depending on the nature of the application and the precise sequence of JDBC API calls issued by the application.

My thanks to colleague Karim Khamis for providing me with the background for this article.

The NSERC Business Intelligence Network will create an innovation platform to enhance the collaboration of the top Canadian knowledge and information management researchers and the top Canadian companies in business intelligence, an area that is central to NSERC’s strategic Advanced Communication and Management of Information target area. The NSERC BIN network will raise the visibility and impact of the Canadian information management research community to that of a world leader in business intelligence research. The network’s goal is to provide new solutions for businesses and government organizations to enable them to solve modern business problems and make decisions using integrated, trustworthy, and up-to-date data.

What exactly is Business Intelligence? Here’s the definition of the term from the BIN research proposal:

Business intelligence (BI) is the commercial term for using information within organizations to make informed decisions, and to run operations effectively based on available data. As a research field, it encompasses data and knowledge management, management of digital media, modeling of processes and policies, data quality, data privacy and security, data integration, data exchange, data cleaning, inconsistency management, information retrieval, data mining, analytics, and decision support.

Specific goals of the BIN network are:

• To develop novel technologies for facilitating business decision-making;
• To develop novel technologies for creating and managing integrated, trustworthy
  and up-to date data;

• To strengthen the innovation pipeline of our industry partners;
• To raise the visibility and impact of the Canadian information management
  research community on the practice of business intelligence.

BIN is a strategic NSERC project, which will link fifteen researchers from seven Canadian academic institutions: Dalhousie, Toronto, Waterloo, Ottawa, Carleton, Calgary, and UBC. In addition, BIN has formal industry partners including IBM, Bell Canada, Ingres, Business Objects, and Sybase iAnywhere. At present there are four defined research areas (themes) that are under the BIN umbrella:

• Strategy and Policy Management;
• Capitalizing on Document Assets, which includes (i) feature extraction, (ii) ontology generation and maintenance, (iii) data exploration and summarization, and (iv) access and privacy control;
• Adaptive Data Cleaning; and
• Support for Top-Down (Business-driven) Data Integration.

My team is looking forward to working with some of the BIN researchers to study these aspects of Business Intelligence and, hopefully, incorporate some of these ideas into iAnywhere products, including SQL Anywhere.

My example involved doing some analysis over project tasks that had “creation’ and “completion’ timestamps, akin to

1
2
3
4
5
6
7

CREATE TABLE projects (
  project_id INTEGER NOT NULL PRIMARY KEY,
  short_desc VARCHAR(255), 
  long_desc LONG VARCHAR,
  project_status VARCHAR(20),
  creation_ts TIMESTAMP NOT NULL,
  completion_ts TIMESTAMP )

The actual schema I was querying is much more complex than this, but this simple example serves to illustrate the basic idea. What I wanted was to create a result set that, for every week, contained a count of the number of projects that were in-progress, and the number of projects that were completed in that week. Once the data is factored out week-by-week, then I could perform historical analysis on that intermediate result using some of the builtin OLAP functionality in SQL Anywhere.

The function DATEDIFF( WEEK, completion_ts, creation_ts ) gives the difference in weeks between the two timestamps, so that part is straightforward but for those projects that span a calendar year. Notwithstanding that complication, the more significant problem is that I wanted to generate a row for every week the project was unfinished. I needed to join the projects table with something to generate the additional rows, but what?

SQL Anywhere databases contain a table named RowGenerator precisely for this purpose; it’s a single-column table (row_num) that contains 255 rows with values starting from one. To generate the result set I needed, here’s the query:

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

SELECT p.project_id, p.short_desc, p.creation_ts, p.completion_ts,
       (IF p.completion_ts IS NULL THEN
          ABS(DATEDIFF( WEEK, NOW(), p.creation_ts ))
       ELSE
          ABS(DATEDIFF( WEEK, p.completion_ts, p.creation_ts ))
       ENDIF ) AS weeks_outstanding,
       (IF p.project_status != 'Complete' OR weeks_outstanding = 0 OR weeks_outstanding > week_number THEN 1 ELSE 0 ENDIF) AS incomplete_projects,
       (IF p.completion_ts IS NOT NULL AND (weeks_outstanding = 0 OR weeks_outstanding = week_number) THEN 1 ELSE 0 ENDIF) AS completed_projects,
       (IF weeks_outstanding = 0 THEN
           DATEPART( YEAR, p.creation_ts )
       ELSE
           DATEPART( YEAR, DATEADD( WEEK, RG.week_number, p.creation_ts) )
       ENDIF) AS calendar_year, 
       (IF weeks_outstanding = 0 THEN
           DATEPART( WEEK, p.creation_ts )
       ELSE
           DATEPART( WEEK, DATEADD( WEEK, RG.week_number, p.creation_ts) )
       ENDIF) AS calendar_week
FROM 
    ( SELECT (row_num - 1) AS week_number FROM RowGenerator) AS RG,
     projects p
WHERE 
    weeks_outstanding >= RG.week_number

The query joins the builtin RowGenerator table to the projects table based on the weeks_outstanding value. Hence, for each week a project is incomplete, a row will be generated in the output, including for those projects that are created and completed in the same week (where weeks_outstanding would be zero). Using the DATEPART function with WEEK means that up to 54 weeks in a year are possible, because DATEPART defines a week to begin on a Sunday.

Once I have this result set, I can then embed it in a derived table and, for example, sum the number of open and completed projects by calendar week in a straightforward way.

The correctness of the solution depends on one factor: that there be no projects that take more than 255 weeks to complete, because otherwise there are insufficient rows in the RowGenerator table to generate the required number of rows. Should that be a problem, SQL Anywhere provides another row generator mechanism: the sa_rowgenerator() system procedure. The sa_rowgenerator() procedure takes three parameters: the starting value, the end value, and the step increment (default is 1). Joining sa_rowgenerator() to the projects table is identical to using the RowGenerator base table, since SQL Anywhere supports table functions (procedures in the FROM clause).

• With deferred-maintenance materialized views, the query optimizer may answer queries utilizing one or more materialized views that contain stale data. The “staleness” of any view, and whether or not the view can be used in query answering, is entirely under DBA control. However, deferred-maintenance views permit one to tradeoff data accuracy with the performance gains offered by the materialized view, and the update maintenance cost for that view.
• Conversely, immediately-maintained materialized view are updated within the same transaction as the base-table updates upon which the materialized view is defined. Immediately-maintained views offer a derived, up-to-the-minute copy of the view’s underlying base tables, at the expense of requiring view maintenance with each update operation.

In summary, deferred-maintenace views permit the amortization of maintenance costs for the materialized view. In contrast, immediately-maintained materialized views require each update transaction to incur the overhead of view maintenance, which may result in contention between concurrently-executing transactions.

The deferred-or-immediate materialized view decision is but one of the factors to consider by a database administrator when deciding whether or not to use a materialized view – in the literature this question is known as the view selection problem. However, there are several considerations other than the deferred-or-immediate maintenance tradeoff. Here is a checklist of questions to consider when deciding upon the utility of a materialized view:

• What is the set of queries that can benefit from creation of materialized views?

  
  Answering this question involves an analysis of the query workload for the system, including detailed consideration of both the definition and frequency of individual queries. A good starting point is to begin with frequently executed and expensive queries, particularly those expensive queries with critical response time requirements. SQL Anywhere’s Application Profiling capability, contained in the Sybase Central SQL Anywhere plug-in, is an excellent way to capture your application’s workload and determine the “heavy hitters” contained within it.

  Materialized views that can benefit multiple queries in common represent the most significant gains, because the storage and maintenance costs for the view are constant but the benefits of the materialized view increase with usage. Also, remember that a single query can make use of multiple materialized views. Splitting a complex materialized view into multiple views may permit the optimizer to utilize the materialized view to assist a larger set of queries. If considering a materialized view involving aggregation (GROUP BY), it is often better to materialize basic functions that will permit a wider applicability to multiple queries; for example, AVG() can be obtained from a combination of SUM() and COUNT(*). SQL Anywhere’s query optimizer is intelligent enough to utilize SUM() and COUNT() from a materialized view when the original query contains AVG().

• Does potential improvement in query performance outweigh the storage and maintenance costs of materialized views?

  
  One must tradeoff the potential improvements in query performance with the space requirements for materialized views – and their indexes – and the maintenance costs for the view. Here, one must be aware of the update patterns from application requests; materialized views on heavily-updated base tables may have unacceptable maintenance costs, for two reasons: the cost of the updates to the materialized views themselves, and the increase in lock contention amongst update transactions from concurrent updates to the table (or index) containing the materialized view. This latter problem is difficult to assess without proper capacity planning.

  DBAs often fail to realize that materialized views can be indexed, just like any other base table. Indexes are particularly useful when the application query contains additional joins to tables that are not included in the view; if indexes exist, the optimizer has more physical operator choices – particularly indexed nested-loop join – that can result in significant speed improvements.

• Can the same query be allowed to return different results, if the optimizer chooses to utilize stale data from a materialized view in one case, and chooses to process the underlying (and up-to-date) base tables in another?
• Can stored data for materialized views be allowed to become stale?
• How stale can the data become before it is unacceptable?

  
  These latter questions pertain to the tradeoffs of immediate versus deferred-maintenance materialized views. As described above, deferred-maintenance permits one to amortize view maintenance across multiple update transactions, at the expense of data staleness. Whether or not your application can benefit from deferred-maintenance views is primarily a business question, not a systems one.

In a later post, I’ve present some examples of using materialized views. My thanks to colleague Anil Goel for providing much of the detail in this article.