Worktable Tricks and Techniques

You can take advantage of how worktables are used and what’s stored in them to get some quick and easy results.

Tip # 1: Don’t use DISTINCT unless you really need it. Some lists are always distinct, including the results of GROUP BY and any output from a single table containing the primary key. These uses of DISTINCT are unnecessary:

--  group  by  output is  always distinct
select  distinct  stor_id,  count(*)
from  sales
group  by  stor_id

--  output  containing  the  primary key is always distinct
select   distinct   stor_id,   stor_name
from   stores
where  state  =  “ME”

Tip # 2: Try to sort by a column rather than a value derived from that column. SQL Server sometimes skips the sort step by using pre-sorted index values. Here’s an example where you request the first 15 characters of a title in alphabetical order:

select   convert(char(15),   title)
from   titles
order  by  convert(char(15), title)

STEP 1
The type of query is SETON
STEP 1
The type of query is INSERT
The update mode is direct
Worktable created for ORDER BY
FROM TABLE
titles
Nested   iteration
Index :  titleind
TO TABLE
Worktable 1
STEP 2
The type of query is SELECT
This step involves sorting
FROM TABLE
Worktable 1
Using GETSORTED Table Scan

SQL Server uses an index to find the titles, but re-sorts the list in a second step using a worktable. To skip the worktable, sort the list by the column itself; SQL Server uses the index to sort the list.

select   convert(char(15),   title)
from   titles
order  by title

STEP 1
The type of query is SELECT
FROM TABLE
titles
Nested   iteration
Index  :  titleind

The second step is eliminated and the worktable is no longer necessary. Not only will this query run faster; the first row will be returned immediately. On large tables, this can be a decisive measurement of performance. If you need to build a worktable, the first row won’t be returned until the entire worktable is built.

Tip # 3: Don’t bother sorting by the grouping values. The data is already sorted in the worktable.

Summary

In this chapter, you’ve looked at some advanced topics related to single table queries. You learned how to use the case operator to generate a crosstab report. That should get you thinking about the many applications for case.

You learned some methods for handling locking in a multiuser environment. The optimistic locking method combined with solid stored procedures provides solid data integrity with minimal overhead and intrusion.

Finally, you looked at the role of worktables and learned to write queries that perform a little better, particularly on large tables.

---

Note:  Be certain to do the exercise at the end of this chapter and to read the solution. It will help you solve similar problems in the future.

---

Q&A

Q Does the case operator cause problems or create overhead?

A I haven’t been able to measure any performance problems related to the case operator. Avoid using it in a WHERE clause; the optimizer won’t be able to use indexes to support queries with case.

Q I want to get locks that span multiple servers. How do multiserver transactions work?

A MS SQL Server 6.5 provides distributed transactions, which are transactions that span servers. Distributed transactions are managed by the Distributed Transaction Coordinator, or DTC, which was shipped first with Windows NT v. 3.51 and is a part of NT 4.0 as well.

Distributed transactions are based on the traditional two-phase commit (2PC) method of ensuring transactional integrity across distributed systems. The DTC informs each server when it’s time to commit a transaction. The server commits once, partway, and then tells the coordinator that the commit is ready to go through. When all of the servers have completed the first phase of commit, the DTC releases the servers to finish the commit process. If any server fails to complete the first commit phase, DTC orders all of the servers to roll back.

Prior to this release of SQL Server, distributed transactions were only possible from a client program written in C with the DB-Library API. The application was responsible for handling all of the coordination of the commit process. Now 2PC is available to any SQL-based or ODBC-based application.

Q Can I examine the contents of a server-generated worktable?

A No. Even though the server uses space in tempdb to store worktables, you can’t find any evidence of them or examine their contents.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  Which method of locking provides the greatest measure of data integrity: pessimistic or optimistic locking?

2.  What are the two key differences between using optimistic locking with timestamps and using HOLDLOCK to manage data integrity?

3.  How would you eliminate the worktable in this query?

select  distinct  substring(title, 1, 10) “title”, title_id, type,
price
from    titles
where   type  in (“business”, “psychology”)
order  by 1

Exercise

There is only one exercise for this chapter, but it’s a tough one. Take the time to read the solution once you’ve worked it out on your own.

The sales table contains columns with order date (ord_date) and sales quantity (qty). Show monthly sales by stor_id for stores 7066, 7067, and 7131, with stor_ids displayed as headings across the top in a crosstab report format. Show the names of the months on the left in order by month, not name. Here’s what you want to display:

From pubs..sales:

Month                             s7066       s7067       s7131
-----------------------------   -----------  ----------  ------
May                               50          0           85
June                              0           80          0
September                         75          10          45

From bigpubs..sales:

Month                              s7066       s7067       s7131
------------------------------   ----------  ----------  -------
January                           1104        0           1704
March                              0           0          1248
April                             0           3000        0
May                                2186        0          85
June                               2724        80         0
August                             1404        0          2088
September                          867         538        45
October                            0           480        0
December                           0           864        0