Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

Showplan Basics, Optimize your SQL

Showplan Basics, Optimize your SQL

As developers, we often focus on code and give less attention to the database than we should. Developers on large development teams, who can usually rely on the database programmers and/or DBA to write and optimize their SQL, tend to shy away from anything database related. On smaller teams the developers are responsible for the schema and DML, but typically don't understand the nuances and complexities of the specific database they're working with. A very important yet undervalued feature in PowerBuilder's database painter, "Explain SQL," takes advantage of the database engine's query analyzer (Showplan report) to show pathing and performance information about a SQL statement. Every developer should know how it works and how to use it. In this article I'll describe the report and show you how to use it to optimize your SQL.

Before you can begin to tune your SQL, you have to identify and understand how the server is processing it - more specifically, how it's being optimized. While this article deals only with Sybase's version of the Showplan report, much of it is the same as Microsoft's rendition. Sybase uses a cost-based optimizer, which means that you don't have to list your tables in the same order as the desired join order. You needn't worry about how to organize your search conditions either - the optimizer will determine the best possible join order and access method for you. Now that may be true in a perfect world, but not the one I live in! In the optimizer's defense, however, many times the blame falls on the shoulders of the DBA or the developer.

For the optimizer to do its job properly, the developer should use valid search arguments (sargs) and limit the number of tables involved in a join. The DBA or architect is responsible for creating a database that's well designed, provides proper indexing and maintains up-to-date distribution statistics. Since all these requirements play important roles in determining how your query will be optimized, I'll have tips throughout this article on what might cause a certain "message" to appear in your Showplan report. The syntax shown in Table 1 assumes it's coded outside the procedure. If coded within the procedure, it stays in effect until the procedure exits.

The basic format of the Showplan output is a step-by-step regurgitation of all your SQL statements, whether they access a table or not. Sybase has improved the output over the years, providing indentation, better numbering of statements and separation of subquery optimization. However, the fact that every statement is "optimized" and displayed in the report still tends to cause a lot of clutter. You can't really tune a print statement, a variable initialization or a declare statement, so just ignore those parts of the report and focus on the statements that access your tables. That's where you'll find your performance problem, assuming there is one. Using the information provided by statistics io and statistics time can also help you narrow down your problem to a particular statement or table in your query, further reducing the area of the Showplan report that you need to be concerned with. Once you've honed in on a particular area of concern, it's just a matter of understanding what to look for and what it all means. The Showplan report (Listing 1) will be examined and broken down throughout this article. I've removed most of the clutter to save space and to focus on the tunable portions. The actual query itself is unimportant. The only thing that matters is what the report is telling us.

Tip: PowerBuilder's Explain Plan nonresizable dialog is so small that the contents are difficult to read. To make viewing easier, use the RMB Menu to copy the contents from the dialog, then paste it into the PowerBuilder text browser (shift+F6).

One of the easiest things to identify right away is the join order selected by the optimizer. The optimizer will determine which table should be accessed first, which one second, and so on, and the report will display the tables in the order chosen. They're not displayed in the same order they were listed in the "from" clause. In the first part of the example that follows, you can see that it has chosen to access prsn as the first or outermost table, and plce as the last or innermost table.

While that's usually the correct order, don't assume that the selected join order is the best one. It could be the key to your query's performance. The optimizer will generally choose the largest table or the one that will require the most I/O as the outermost table, then work its way inward toward the smaller table or the one that requires the least amount of I/O. As a result, the quality of the join order you get is heavily dependent on how accurate the estimate of I/O for each table is.

FROM TABLE
Prsn
:
FROM TABLE
Plce
Tip: Even if the estimates are good, lack of an index that supports the search argument could cause it to choose prsn as the outer table simply because it has no choice but to do a tablescan against it. By making it the outer table, it can scan the table once rather than once for each qualifying row in the plce table. Under the circumstances, the optimizer chose correctly, but if a proper index had been present, it could've chosen a completely different and substantially better join order.

The report will always tell you the statement number and the line number first, which aren't always the same as it ignores comments, blank lines and various keywords when counting the statements. The next thing you'll see is the type of statement that's being optimized, in this case an insert. The update mode will be displayed for all insert, update and delete statements. It can be either direct or deferred, but deferred is more costly due mostly to additional I/O against the transaction log.

Tip: It's not uncommon to see deferred updates, due in large part to the presence of triggers and other limitations of direct updates. Avoiding updates to varchar and null columns can help promote direct updates, but talk to your DBA about other ways to avoid deferred updates.

QUERY PLAN FOR STATEMENT 2 (at line 12)
STEP 1
The type of query is INSERT.
The update mode is direct.
The first table that'll be accessed is the prsn table. Even if you're performing an insert, update or delete against this table, the report will still indicate "FROM," presumably because it's the source of the data that's being referenced, so don't let that throw you. Something else you'll always see with table accesses is "Nested iteration." It can be ignored since it's how all tables are accessed and it's not tunable.
FROM TABLE
Prsn
Nested iteration
This table will be accessed via a table scan, meaning the entire table must be read even if a few rows qualify only for the search or join criteria. This should raise a red flag when you're reviewing the report. It's not necessarily a bad thing, but it certainly warrants investigation into why it's choosing a tablescan instead of an index.

Until ASE 11.5 was born, you could ignore "Ascending Scan" since all tables and indexes could be read from the start only. Now it's possible to read an index from the end to avoid sorting. Since it's an ascending table scan, it'll read by positioning itself at the beginning of the table as indicated in the output.

Table Scan.
Ascending scan
Positioning at start of table.
Tip: There are many reasons why the optimizer would choose a tablescan versus an index. Here are a few of my favorites.
  • No indexes supporting the search or join criteria: A supporting index will have as its first column one of the search or join columns of the query. If the index also contains other search or join columns as its second through nth column, that tends to be even better. Your DBA can assist you in determining the best order for the columns when defining the index.
  • Table is too small: If the cost of reading the index is greater than scanning the entire table, a tablescan is the correct choice. Generally, a table with fewer than 25 data pages can be read faster with a tablescan, depending on available I/O sizes.
  • More than 20% of the rows in the table qualify: Here again it's cheaper to scan the entire table once than traversing the index.
  • A temp table is being scanned: Many times this is due to a lack of an index. If you do have an index on the temp table, make sure you create the index after the data is loaded and access the temp table in a procedure or batch that's executed after the index has been created. Because Sybase optimizes a stored procedure or batch in its entirety before it's executed, the index statistics aren't created or available until after the optimization is complete. Separating the table loading and index creation from the code that accesses the table allows the called procedure to see the statistics and optimize properly.
  • A valid index is present, but the statistics on its distribution page are missing or out of date: This is something the DBA should maintain regularly, but if you're getting a tablescan and can't figure out why, sometimes the easiest solution is to run "update statistics" against the table or rebuild the index. Either way will update the stats, but rebuilding the index is much more intrusive and takes much longer. Statistics are basically a sampling of the indexed column values stored on a special page called the distribution page. It's sort of your data's demographics for a specific index, and the optimizer uses these demographics to estimate how many rows will be returned based on your search argument.
  • A valid index is available but the distribution of indexed column values is erratic: There may be only 10 possible values for the indexed column in the entire table with one of them occurring on 1,000 rows, another on 300,000 rows, 50,000 rows, and so on. This makes the optimizer's job tough, causing estimates that are way off. This is probably not a good choice of columns to index.
  • An invalid sarg has been provided: For the optimizer to be able to interpret and optimize your query, you must follow certain rules.
    1. Data types must match between columns and search arguments whether those search arguments are variables, constants, procedure parms or expressions. Since it has to get every row to convert the column's data type before it can determine if the row qualifies, it defaults to a tablescan.
    2. Don't use functions or mathematical expressions against indexed columns in your "where" clause. Since it has to get every row and apply the function or expression before it can determine if the row qualifies, it defaults to a tablescan.
    3. Use procedure parameters for your search arguments rather than local variables in stored procedures. The server can't "see" the values of the local variables at optimization time, so it can't compare them to index distribution statistics to get a valid estimate of qualifying rows. Instead of using a variable in the sarg, execute a subprocedure and pass the values as parameters to be used by that procedure's sarg.
When accessing a table, whether it's by tablescan or by index, the report displays the I/O size, the rate it reads pages into memory. The size ranges from 2K to 16K, and with a 2K page size that means the server can read from one to eight pages at a time into memory.

The report also tells where the data is being brought into memory. LRU (least recently used) indicates that the data will be brought into memory at the head of the buffer pool. This is a normal strategy and an attempt to keep the pages in memory for as long as possible for future use. The fetch-and-discard strategy would be indicated by MRU (most recently used), and this indicates the data will be brought into memory near the tail of the buffer pool. The data will remain in memory only for a short time because these buffers are frequently overwritten. It appears a lot with worktables and certain queries where the server is confident the data won't be referenced again.

Using I/O Size 16 Kbytes.
With LRU Buffer Replacement Strategy.
Tip: If your report indicates an I/O size of 16K, it's a hint that it's planning on reading a lot of pages and/or reading them sequentially, which is often seen with tablescans and clustered indexes. If it says 2K, most likely it's planning on reading a small amount of data and/or doing random reads. Of course, it could also be that your DBA hasn't configured a large I/O buffer pool.

The next table accessed in the join is plce, and it's being accessed with an index named plce_idx1. As was the case with the tablescan, it's accessed with a nested iteration. However, instead of starting the scan at the beginning of the table as it did with the tablescan, it'll start at the first qualifying page in the leaf level of the index. It'll generally cost about three or four reads just to get to the first qualifying page, but that's insignificant to the total cost in most cases. The columns that had valid sargs provided for in the query and that are part of the index will be displayed in the report.

FROM TABLE
plce
Nested iteration.
Index : plce_idx1
Ascending scan.
Positioning by key.
Keys are:
plce_id
plce_prsn_id
plce_typ

Tip: Compare the search arguments and join columns supplied for this table in your query to the keys listed in the report. If some are missing, they weren't considered valid sargs. Try to correct that problem for quicker access to the qualifying rows as well as better query optimization. Another problem might be that you supplied valid sargs for column1 and column3 of your index, but without column2 in your sarg (or rearranging your column order in the index) it won't be able to use any part of the index past column1 for access.

Inserts, updates and deletes will always have a "TO" table. This is the table that's being acted upon, and in many cases is the same as the "FROM" table, even in the case of many joins.

TO TABLE
prsn_plce
Parallelism, a fairly new feature for Sybase, can assist various types of queries that perform a lot of reads. Basically, this means that a table or tables in your query will be accessed via multiple processes spawned by your process, known as the coordinating process. The spawned worker processes will each read a different part of the table and send the results back to a merge mechanism, which sends the final results to the process that spawned them. At that point the worker processes go away. This mechanism is indicated by several messages in the report.

The first message indicates that at least one table in your statement will be executed in parallel, and in this case it tells you it'll spawn a total of five worker processes to accomplish the task. If multiple tables in a join are going to be accessed via parallelism, the total number of worker processes displayed here would be the product of worker processes required for each table in the join. For example, if one table required five worker processes and another required three, the total worker processes required would be 15, not eight.

The next message indicates that the coordinating process executes "create table". In the case of our example, the actual statement is a "select into", which will generally have two main steps. The first step is to create the table #temp1; the second is to insert qualifying rows. It's the "create table" portion that's executed by a single, coordinating process. In our example there are three steps because it also creates a worktable in between, but that's not always the case; we'll cover worktables later.

The next part of the "select into" is inserting the qualifying rows into the table. The insert itself is actually handled by the merge mechanism and coordinating process, but the select that finds the qualifying rows is being processed in parallel. There will be a total of five worker processes for this step.

Finally we see which table or tables in the statement the worker processes will access. In this case there's no join, so the only table that's accessed is items_xref and all five worker processes access it. The output indicates a hash scan, meaning a hashing algorithm is used to determine which pages should be read by which worker process. See your DBA or documentation about other types of parallel access.

QUERY PLAN FOR STATEMENT 3 (at line 22).
Executed in parallel by coordinating process and 5 worker processes.

STEP 1
The type of query is CREATE TABLE.
Executed by coordinating process.

STEP 2
The type of query is INSERT.
The update mode is direct
Executed in parallel by 5 worker processes.
Worktable1 created for DISTINCT
FROM TABLE
items_xref
:
Executed in parallel with a five-way hash scan.

Tip: Using worker processes can actually cost more I/O even though their purpose is to yield faster results. In other words, five processes reading 500 total pages can run faster than a single process reading 200 pages, because they have to read only 100 pages each and are all reading simultaneously. This means that the optimizer will sometimes choose a query plan that's more costly in I/O because it can take advantage of parallelism to reduce overall response time. This logic is fine during optimization, but when it actually executes the plan, if there aren't enough worker processes configured for the server, it makes a runtime adjustment. The runtime adjustment may reduce the number of worker processes used to access a table or eliminate them all together, but it doesn't change the query plan. If it had planned on doing a tablescan with five worker processes and they weren't available at runtime, it could end up doing a tablescan with a single process. In the example I gave, this means it'll end up having to read 500 pages by a single process instead of choosing another access method that may have cost only 200 pages. Make sure your DBA has configured the number of worker processes adequately to handle the server workload or you may experience these types of problems. Unfortunately, these problems don't show up in the Showplan report but your DBA has ways of monitoring runtime adjustments.

Also, in Step 2 a table called Worktable1 is being created. This isn't a table that's mentioned in the query but is one that the server must create in order to process the Distinct keyword. The loading of the resultset into the worktable is indicated by the "To Table" statement. This table will be created in memory or in tempdb. The results are then sorted and finally returned by scanning the worktable. These resulting rows are then loaded into #temp1, which was the target table of the "select into".

STEP 2
The type of query is INSERT.
:
Worktable1 created for DISTINCT.
FROM TABLE
items_xref
:
TO TABLE
Worktable1.

STEP 3
The type of query is INSERT.
The update mode is direct.
This step involves sorting.
FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
:
TO TABLE
#temp1

Tip: Worktables will also be found with other statements that require sorting, such as Union, Group By and Compute By. This use of a worktable can be costly, so seek alternatives, such as using Union All, when possible. A Union All just returns the first resultset followed by the second without sorting or looking for duplicates. If you're sure there'll be no duplicates, this is a good way of eliminating the worktable step. Make sure you actually need the Distinct. Many times it's a problem with your query, such as an incomplete join or sarg, that leads to your duplicates. Fix the code before using an expensive Distinct.

Part of the search argument for table items_xref was a subquery based on an IN predicate. The older Showplan showed the subquery processing embedded with the rest of the query, but recent versions have broken it out into a separate area for better readability. The output now simply states that it'll "run subquery 1", and the subquery itself will be farther down in the report just prior to the next statement to be optimized. A subquery can fall under another subquery creating nesting levels, which is also indicated in the output. In this case it happens to be a correlated subquery, which is stated in the report as well.

FROM TABLE
items_xref
:
Run subquery 1 (at nesting level 1).
:
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 3.
QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 24).
Correlated Subquery.
Subquery under an IN predicate.
STEP 1
The type of query is SELECT.
:
END OF QUERY PLAN FOR SUBQUERY 1.

Tip: A correlated subquery can be very expensive because it's executed once for every row that qualifies in the outer query. If there are no other sargs besides the subquery, the subquery must be executed once for every row in the outer query's table(s). See your DBA or Performance and Tuning Guide for alternative methods.

If you see "Reformatting" in your Showplan, it's a pretty good indication that you need to add an index. Reformatting is used in joins where a suitable index isn't available on one of the join tables. Rather than having to scan the table multiple times, the optimizer estimates it would take less time to build an index and use it to process the query. A worktable with a clustered index is created and then used in the join. In this particular case it wouldn't buy me anything because it's a temp table, which means it can't have a permanent index. It's really a choice of my coding an index creation or the system doing it for me. In either case the cost of creating the index is the same since it occurs at runtime.

The type of query is INSERT.
The update mode is direct.
Worktable1 created for REFORMATTING.
FROM TABLE
#temp1
:
TO TABLE
Worktable1.

STEP 2
:
FROM TABLE
Worktable1.
Nested iteration.
Using Clustered Index.
Ascending scan.

Tip: If this were a permanent table that it was reformatting, the savings could be substantial if I placed a permanent index on it. This particular query ends up scanning the leaf level of the clustered index since it will be used as the first table in the join, but your report may indicate the use of keys to process it as an inner table. If that's the case, the optimizer is trying to tell you that performance would improve if you supplied an index on those columns ahead of time.

There are many other parts of the Showplan report that I haven't discussed, but the major tunable parts and key indicators have been covered. I hope this will give you a good starting point. Remember, you can't tune your query without knowing how it's being optimized, and the Showplan report is a good place to start. Although Showplan will tell you what the optimizer decided to do, it won't tell you why it decided on a particular join order or access method. I've provided you with some tips to help you make the optimizer behave better. In a future article I'll discuss other tools you can use to determine what the optimizer is seeing and why it made its choices, but until then, start using Showplan and start seeing results.

More Stories By Brian Davignon

Brian Davignon, a senior DBA consultant with Soaring Eagle Consulting, Ltd., in Tampa, Florida, is a certified DBA and performance and tuning specialist (CSPDBA, CSPPTS). He has over 12 years of experience in development and database administration including mainframe and client/server.

He can be reached at: [email protected]

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.