Index

A note on the digital index

A link in an index entry is displayed as the section title in which that entry appears. Because some sections have multiple index markers, it is not unusual for an entry to have several links to the same section. Clicking on any link will take you directly to the place in the text in which the marker appears.

Symbols

@@CPU_BUSY variable (SQL Server), Querying dynamic views

A

AboveThreshold( ) utility function, A Simple Example
ACID property, Multiplying service providers at the application level
aggregates
checksums, Comparing checksums in SQL, Comparing checksums in SQL
denormalization and, Adding columns
simplifying, Simplifying Conditions
simplifying constructs, Using aggregates
array variables, Example 2: A conversion function
arrival rate, Service time and arrival rate–Increasing parallelism, Service time and arrival rate, Increasing parallelism, Increasing parallelism
assessment
analyzing collected material, Analyzing Collected Material–Analyzing Collected Material, Analyzing Collected Material
choosing among approaches, Choosing Among Various Approaches
client-side logging, Dumping statements to a trace file
determining possible gains, Assessing Possible Gains–Assessing Possible Gains, Assessing Possible Gains, Assessing Possible Gains
dumping statements to trace files, Dumping statements to a trace file
example overview, Assessment–A Simple Example, A Simple Example, A Simple Example, SQL Tuning, the Traditional Way
exploiting trace files, Exploiting trace files, Exploiting trace files
in-between logging, Dumping statements to a trace file
querying dynamic views, Querying dynamic views–Dumping statements to a trace file, Dumping statements to a trace file
refactoring example, Refactoring, First Standpoint–Refactoring, Second Standpoint, Refactoring, First Standpoint, Refactoring, Second Standpoint, Refactoring, Second Standpoint, Refactoring, Second Standpoint
server-side logging, Dumping statements to a trace file, Dumping statements to a trace file
traditional SQL tuning, SQL Tuning, the Traditional Way, SQL Tuning, the Traditional Way, SQL Tuning, the Traditional Way, SQL Tuning, the Traditional Way, SQL Tuning, Revisited, SQL Tuning, Revisited
auto-commit mode, Transaction Management, Multiplying service providers at the application level, Multiplying service providers at the application level
Automatic Workload Repository (AWR), Querying dynamic views

B

B-tree indexes, Indexing Review, A Detailed Investigation
batching
client-side processing, Bulk Operations
lists, Passing the list as a single variable, Batching lists
bcp (SQL Server), Brute force comparison
bind variable peeking, Correcting Parsing Issues the Proper Way
bind variables, Correcting Parsing Issues
bitmap indexes, Bitmap indexes
bit_xor( ) aggregate function, Comparing checksums in SQL
blocks (Oracle), Querying dynamic views
bottlenecks, Dealing with multiple queues
brute force comparison, Brute force comparison–SQL comparison, textbook version, SQL comparison, textbook version
BULK INSERT command (SQL Server), Matching Existing Distributions
bulk operations, Bulk Operations–Bulk Operations, Bulk Operations, Bulk Operations, Bulk Operations

C

calendar function, Example 1: A calendar function–Example 2: A conversion function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 2: A conversion function, Example 2: A conversion function
cardinality, Available Statistics, A Detailed Investigation, Correcting Parsing Issues the Proper Way
checksum table command (MySQL), Comparing checksums in SQL
checksum( ) function, Comparing checksums in SQL
checksums, Brute force comparison, Comparing checksums in SQL–Limits of Comparison, Comparing checksums in SQL, Comparing checksums in SQL, Comparing checksums in SQL, Comparing checksums in SQL, Comparing checksums in SQL, Limits of Comparison
checksum_agg( ) function, Comparing checksums in SQL
CLASSPATH environment variable, How to Use Roughbench
client-side processing
batching, Bulk Operations
logging, Dumping statements to a trace file
clustered indexes, Clustered indexes, Marshaling Rows, All These Fast Queries
coalesce( ) function, Using coalesce() instead of if … is null, Using coalesce() instead of if … is null
code restructuring
avoiding excesses, Avoiding Excesses
coalesce ( ) function and, Using coalesce() instead of if … is null
fetching everything at once, Fetching all you need at once
getting rid of loops, Getting Rid of Loops–Challenging loops, Analysis of loops, Analysis of loops, Challenging loops
shifting logic, Shifting the logic
columns
adding, Adding columns
computed, Indexes on expressions
splitting, Splitting columns
commit rate, No Obvious Very Wrong Query
commit statement, Reasons behind loops
composite indexes
problems with, A Detailed Investigation–A Detailed Investigation, A Detailed Investigation, A Detailed Investigation, A Detailed Investigation, A Detailed Investigation
computation-only functions, Improving Computation-Only Functions–Improving Computation-Only Functions, Improving Computation-Only Functions, Improving Computation-Only Functions, Improving Computation-Only Functions
computed columns, Indexes on expressions, Improving Computation-Only Functions
concurrent access
contention and, Marshaling Rows
marshaling rows, Marshaling Rows–Splitting Tables, Marshaling Rows, Marshaling Rows, Marshaling Rows, Splitting Tables
shortening critical sections, Shortening critical sections
contention
as bottleneck, Shaking Foundations
concurrency and, Marshaling Rows
database structure and, Refactoring Flows and Databases
parallelism and, Isolating Hot Spots, Dealing with multiple queues–Shaking Foundations, Shaking Foundations
partitioning and, Dealing with multiple queues, Shaking Foundations, Shaking Foundations
primary keys and, Dealing with multiple queues
table counters and, Isolating Hot Spots
control logic flow, Shifting the logic
control structures
exceptions and, Using exceptions–Fetching all you need at once, Using exceptions, Using exceptions, Using exceptions, Using exceptions, Fetching all you need at once
fetching and, Fetching all you need at once
conversion functions, Example 2: A conversion function–Improving Functions Versus Rewriting Statements, Example 2: A conversion function, Example 2: A conversion function, Improving Functions Versus Rewriting Statements, Improving Functions Versus Rewriting Statements
Convert( ) utility function, A Simple Example, Refactoring, First Standpoint
core columns, Identifying the Query Core
correlated subqueries, Subqueries in the where clause, Subqueries in the where clause, Queries of Death
cosmetic columns, Identifying the Query Core
count( ) function, Getting Rid of count(), Getting Rid of count(), Getting Rid of count(), Getting Rid of count(), Getting Rid of count(), Getting Rid of count(), Getting Rid of count(), Avoiding Excesses, All These Fast Queries
create index statement, A Detailed Investigation
create table statement, Comparing checksums in SQL
create view statement, Performance Comparison with and Without a Complex View
critical sections, Shortening critical sections
CURSOR_SHARING parameter (Oracle), Correcting Parsing Issues the Lazy Way

D

data replication, Materializing views
data skewness, Statistics and Data Skewness–Indexing Review, Available Statistics, Extreme values, Indexing Review, Indexing Review
database-changing procedures, User-Defined Functions
databases
parallelism and, Parallelizing Your Program and the DBMS, Parallelizing Your Program and the DBMS, Parallelizing Your Program and the DBMS
refactoring access, How It Works: Refactoring in Practice, Can You Look at the Database?
date type (Oracle), Improving Functions Further
dates
deterministic identification, Improving Functions Further–Improving Functions Further, Improving Functions Further, Improving Functions Further, Improving Functions Further, Improving Functions Further
generating, Generating dates
datetime type, Extreme values, Improving Functions Further
dba_analysis.sql script, Chapter 1
dbcc show statistics command, Available Statistics
dbms_crypto package (Oracle), Comparing checksums in SQL
dbms_sql package (Oracle), Comparing checksums in SQL
dbms_utility package (Oracle), Comparing checksums in SQL
delete statement
functional comparisons and, Comparing Crudely
splitting tables, Splitting Tables
denormalization, Adding columns
dept_ref.sql script, Chapter 4
deterministic functions
cautions using, Improving Functions Further
DTD (document type definition), Generating Random Text
dynamic SQL, Comparing checksums in SQL
dynamic views, Querying dynamic views, Querying dynamic views, Querying dynamic views

E

error handling
SQL mindset, Assuming Success–Assuming Success, Assuming Success, Assuming Success, Assuming Success
ETL (Extract/Transform/Load), Parallelizing Your Program and the DBMS
event 10046 (Oracle), SQL Tuning, the Traditional Way
except set operator (SQL), SQL comparison, textbook version
exception handling
restructuring code, Using coalesce() instead of if … is null–Fetching all you need at once, Using exceptions, Using exceptions, Using exceptions, Fetching all you need at once
executeQuery( ) function, A Simple Example
executeUpdate( ) function, A Simple Example
execution, Assessing Possible Gains
execution plans
optimizer directives and, Execution Plans and Optimizer Directives, Execution Plans and Optimizer Directives
plan stability, Execution Plans and Optimizer Directives
statement caches and, Querying dynamic views
explain command, Performance Comparison with and Without a Complex View
exponential distribution, Using Random Functions
expressions, Indexes on expressions
Extract/Transform/Load (ETL), Parallelizing Your Program and the DBMS

F

fetching
performance considerations, Bulk Operations
fifo.sql script, Chapter 7 (MySQL)
filtering
activating early, Activating Filters Early–Simplifying Conditions, Simplifying Conditions
core columns and, Identifying the Query Core
views, What Views Are For
first normal form, Splitting columns
fn_trace_gettable( ) function (SQL Server), Exploiting trace files
foreign keys
indexing, Indexing Review
found_rows( ) function, Getting Rid of count(), Getting Rid of count()
fragmentation in data blocks, Splitting Tables
from clause
cleaning up, Cleaning Up the from Clause–Cleaning Up the from Clause, Cleaning Up the from Clause, Cleaning Up the from Clause, Cleaning Up the from Clause
outer joins and, Subqueries in the select list
repeated patterns and, Eliminating Repeated Patterns–Eliminating Repeated Patterns, Eliminating Repeated Patterns
rewriting queries and, Comparing checksums in SQL
subqueries and, Subqueries in the select list
function-based indexes, Indexes on expressions
functions
computation-only, Improving Computation-Only Functions–Improving Computation-Only Functions, Improving Computation-Only Functions
deterministic, Improving Functions Further–Improving Functions Further, Improving Functions Further, Improving Functions Further, Improving Functions Further, Improving Functions Further, Improving Functions Further, Improving Functions Further
improving, Improving Functions Versus Rewriting Statements
user-defined, User-Defined Functions, Using exceptions, Queries of Death
utility, Using exceptions
FxConvert( ) conversion function, Example 2: A conversion function, Example 2: A conversion function, Example 2: A conversion function, Example 2: A conversion function, Example 2: A conversion function, Example 2: A conversion function, Example 2: A conversion function, Example 2: A conversion function

G

Gaussian distribution, Using Random Functions
general_log variable (MySQL), Dumping statements to a trace file
GenerateData.java script, Chapter 1
gen_emp.sql script, Chapter 4
gen_emp_pl.sql script, Chapter 4
get_hash_value( ) function, Comparing checksums in SQL
global counters, Querying dynamic views
GNU Autotools, mklipsum and lipsum
Gnu Statistical Library (GSL), Generating Many Rows
Goldwyn, Assessing Possible Gains
greatest( ) function, Simplifying Conditions
group by clause, Clustered indexes, Queries of Death

H

hardcoded statements
replacing with softcoded, Correcting Parsing Issues–Correcting Parsing Issues the Lazy Way, Correcting Parsing Issues, Correcting Parsing Issues, Correcting Parsing Issues the Lazy Way
SQL engine treatment of, Assessing Possible Gains
hash joins, Merge/Hash Joins
hash( ) function, Comparing checksums in SQL
hashbytes( ) function, Comparing checksums in SQL
HashMap class, Refactoring, First Standpoint
having clause, Performance Comparison with and Without a Complex View

I

in clause, Correcting Parsing Issues the Proper Way
indexes
access considerations, Available Statistics
B-tree, Indexing Review
bitmap, Bitmap indexes
clustered, Clustered indexes
composite, A Detailed Investigation, A Detailed Investigation, A Detailed Investigation, A Detailed Investigation, A Detailed Investigation, Queries of Death
derived from database design, Indexing Review
null values and, Changing the contents
on computed columns, Indexes on expressions
on expressions, Clustered indexes
performance considerations, Available Statistics, Indexing Review
primary key, Marshaling Rows
range scans, SQL Tuning, Revisited, Extreme values, Extreme values
reviewing, A Detailed Investigation
row order and, Marshaling Rows
selectivity and, Available Statistics, Available Statistics
single-column, A Detailed Investigation
tables in schemas, A Quick Look at Schema Indexing–A Quick Look at Schema Indexing, A Quick Look at Schema Indexing, A Quick Look at Schema Indexing, A Quick Look at Schema Indexing
types of, Indexing Review
IndexSelectivity.java script, Chapter 2
information_schema.global_status (MySQL), How to Detect Parsing Issues
information_schema.processlist (MySQL), Querying dynamic views
inner joins, Performance Comparison with and Without a Complex View
InnoDB storage engine, Multiplying service providers at the application level, Multiplying service providers at the application level, Dealing with multiple queues
instr( ) function, Improving Computation-Only Functions
ISO standard, Multiplying service providers at the application level
isolation levels, Multiplying service providers at the application level–Shortening critical sections, Multiplying service providers at the application level, Multiplying service providers at the application level, Multiplying service providers at the application level, Shortening critical sections

J

J2SE Development Kit (JDK), How to Build Roughbench
JBoss application server, Dumping statements to a trace file
JDBC
prepared statements, A Simple Example
JDK (J2SE Development Kit), How to Build Roughbench
joins
defined, Nested Loops
inner, Performance Comparison with and Without a Complex View
outer, Performance Comparison with and Without a Complex View
query considerations, Identifying the Query Core, Cleaning Up the from Clause, Cleaning Up the from Clause, Rebuilding the Initial Query

K

Kyte, SQL comparison, better version

L

large objects (LOBs), Splitting Tables
least( ) function, Simplifying Conditions
length( ) function, Improving Computation-Only Functions
lipsum tool, Tools–How to Use mklipsum and lipsum, How to Use mklipsum and lipsum, How to Use mklipsum and lipsum, How to Use mklipsum and lipsum
lists
batching, Batching lists
passing as variables, Passing the list as a single variable–Batching lists, Passing the list as a single variable, Passing the list as a single variable, Batching lists
temporary tables, Using a temporary table
LOAD command (MySQL), Matching Existing Distributions
load data infile statement, Using a temporary table
LOBs (large objects), Splitting Tables
locks
competing for resources and, Competing for Resources
multiple queues and, Dealing with multiple queues
logging
server-side, Dumping statements to a trace file, Dumping statements to a trace file
logical reads
measuring work performance, Querying dynamic views
lookup functions
defined, User-Defined Functions
performance considerations, Improving Lookup Functions
retrieving employee names, Improving Functions Further–Improving Functions Further, Improving Functions Further, Improving Functions Further
loops
analyzing, Reasons behind loops
challenging usage, Challenging loops–Challenging loops, Challenging loops
getting rid of, Getting Rid of Loops–Challenging loops, Challenging loops
nested, Nested Loops
reasons behind, Reasons behind loops–Analysis of loops, Analysis of loops
lorem ipsum, Generating Random Text

M

Markov chains, Generating Random Text
Maslow, Assessing Possible Gains
materialized views, Adding columns
MD5 algorithm, Brute force comparison, Comparing checksums in SQL, Comparing checksums in SQL, Comparing checksums in SQL
mean (mu), Using Random Functions
merge joins, Merge/Hash Joins
min( ) function, Eliminating Repeated Patterns
minus operator (Oracle), SQL comparison, textbook version
mklipsum tool, mklipsum and lipsum–How to Use mklipsum and lipsum, How to Use mklipsum and lipsum, How to Use mklipsum and lipsum, How to Use mklipsum and lipsum
mu (mean), Using Random Functions
multiple queues, Dealing with multiple queues–Parallelizing Your Program and the DBMS, Parallelizing Your Program and the DBMS
MyISAM tables, Multiplying service providers at the application level, Dealing with multiple queues, Dealing with multiple queues, Dealing with multiple queues, Dealing with multiple queues
MySQL
baseline for example, SQL Tuning, the Traditional Way
checksum support, Comparing checksums in SQL–Comparing checksums in SQL, Comparing checksums in SQL, Comparing checksums in SQL, Comparing checksums in SQL
clustered indexes, Clustered indexes
date values, Extreme values
detecting parsing issues, Estimating Performance Loss Due to Parsing
deterministic functions, Improving Functions Further
dynamic views, Querying dynamic views
filtering views, Performance Comparison with and Without a Complex View, Performance Comparison with and Without a Complex View
generating rows, Generating Many Rows–Dealing with Referential Integrity, Dealing with Referential Integrity
InnoDB engine and, Multiplying service providers at the application level
LOB support, Splitting Tables
materialized views, Materializing views
monitoring databases, Can You Look at the Database?
random data generation, Using Random Functions
refactoring views, Refactoring Views
session variables and, Example 2: A conversion function
speed improvement comparison, SQL Tuning, the Traditional Way, SQL Tuning, Revisited, SQL Tuning, Revisited, Refactoring, First Standpoint, Refactoring, Second Standpoint, Comparison and Comments, Comparison and Comments
MySQL Proxy, Dumping statements to a trace file
mysqldump tool, Brute force comparison
mysqlsla tool, Exploiting trace files

N

nested loops, Nested Loops
NextBusinessDay( ) calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 1: A calendar function, Example 2: A conversion function
null values
indexes and, Changing the contents
optimizers and, Available Statistics
outer joins and, Subqueries in the select list
null/not null attribute, Changing the contents
number distribution, Available Statistics, Optimizer Traps, Using Random Functions

O

OLTP (online transaction processing), Transaction Management
optimizer directives, Execution Plans and Optimizer Directives, Execution Plans and Optimizer Directives, Execution Plans and Optimizer Directives, Execution Plans and Optimizer Directives, Execution Plans and Optimizer Directives, Queries of Death
optimizers
basis of searches, Sanity Checks
difficulties encountered, Execution Plans and Optimizer Directives
execution plans and, Execution Plans and Optimizer Directives–Analyzing a Slow Query, Execution Plans and Optimizer Directives, Execution Plans and Optimizer Directives, Analyzing a Slow Query
information considerations, Statistics and Data Skewness–Extreme values, Available Statistics, Available Statistics, Extreme values
performance problems, Statement Refactoring
range of values, Available Statistics
traps, Optimizer Traps–Indexing Review, Indexing Review
values, Available Statistics
optimizer_max_permutations parameter (Oracle), Execution Plans and Optimizer Directives
Oracle
assessing possible gains, Assessing Possible Gains–Assessing Possible Gains, Assessing Possible Gains, Assessing Possible Gains
bitmap indexes, Bitmap indexes
checksum support, Comparing checksums in SQL, Comparing checksums in SQL
detecting parsing issues, How to Detect Parsing Issues
generating rows, Generating Many Rows, Generating Many Rows
identifying code series, Using exceptions
index searches, Available Statistics
materialized views, Materializing views
refactoring views, Refactoring Views
order by clause, Clustered indexes
outer joins
null values and, Subqueries in the select list
views and, Performance Comparison with and Without a Complex View

P

p6spy tracer, Dumping statements to a trace file
pages
data retrieval in, Statistics and Data Skewness
statement references and, Querying dynamic views
parallelism
contention and, Isolating Hot Spots, Dealing with multiple queues
DBMS and, Parallelizing Your Program and the DBMS–Shaking Foundations, Parallelizing Your Program and the DBMS, Parallelizing Your Program and the DBMS, Parallelizing Your Program and the DBMS, Shaking Foundations
increasing, Increasing parallelism
isolating hot spots, Isolating Hot Spots–Parallelizing Your Program and the DBMS, Isolating Hot Spots, Parallelizing Your Program and the DBMS
multiplying service providers, Multiplying service providers at the application level–Shortening critical sections, Multiplying service providers at the application level, Shortening critical sections
synchronization and, Multiplying service providers at the application level
PARAMETERIZATION parameter (SQL Server), Correcting Parsing Issues the Lazy Way
Parameters method (SqlCommand), Correcting Parsing Issues
parsing
correcting issues, Correcting Parsing Issues the Lazy Way
defined, Assessing Possible Gains
performance loss due to, Estimating Performance Loss Due to Parsing–Estimating Performance Loss Due to Parsing, Estimating Performance Loss Due to Parsing, Estimating Performance Loss Due to Parsing, Estimating Performance Loss Due to Parsing, Estimating Performance Loss Due to Parsing
partitioning
contention and, Dealing with multiple queues, Shaking Foundations, Shaking Foundations
denormalization and, Adding columns
hazards of, Shaking Foundations
MyISAM and, Dealing with multiple queues
patterns
counting in strings, Improving Computation-Only Functions–Improving Computation-Only Functions, Improving Computation-Only Functions, Improving Computation-Only Functions, Improving Computation-Only Functions
eliminating repeated, Eliminating Repeated Patterns–Eliminating Repeated Patterns, Eliminating Repeated Patterns, Eliminating Repeated Patterns, Eliminating Repeated Patterns, Eliminating Repeated Patterns, Eliminating Repeated Patterns
performance indexes, Indexing Review
plan stability, Execution Plans and Optimizer Directives
prepared statements
handling lists, Handling Lists in Prepared Statements–Bulk Operations, Passing the list as a single variable, Batching lists, Bulk Operations
JDBC and, A Simple Example
primary key indexes, Marshaling Rows
primary keys
constraints, A Quick Look at Schema Indexing
indexing, Indexing Review, A Detailed Investigation
surrogate keys and, Isolating Hot Spots
privileges, Can You Look at the Database?
procedures, User-Defined Functions
processing
competing for resources, Service time and arrival rate, Service time and arrival rate, Multiplying service providers at the application level, Multiplying service providers at the application level, Multiplying service providers at the application level, Multiplying service providers at the application level, Multiplying service providers at the application level, Multiplying service providers at the application level, Multiplying service providers at the application level, Multiplying service providers at the application level, Isolating Hot Spots
server-side, Dumping statements to a trace file
profiler_analysis.sql script, Chapter 1

Q

qrysum.sql script, Chapter 4
queries
analyzing, Analyzing a Slow Query–Cleaning Up the from Clause, Cleaning Up the from Clause
merge/hash joins, Rebuilding the Initial Query
query core
activating filtering early, Activating Filters Early, Activating Filters Early
combining set operators, Combining set operators
identifying, Identifying the Query Core–Cleaning Up the from Clause, Identifying the Query Core, Cleaning Up the from Clause
simplifying aggregates, Simplifying aggregates
simplifying conditions, Simplifying Conditions–Using with, Using with
subqueries and, Playing with Subqueries–Activating Filters Early, Subqueries in the select list, Subqueries in the where clause–Activating Filters Early, Subqueries in the where clause, Activating Filters Early, Activating Filters Early
question marks, A Simple Example

R

rand( ) function, Using Random Functions, Matching Existing Distributions
random number generation, Available Statistics, Using Random Functions, Using Random Functions, Using Random Functions, Using Random Functions, Matching Existing Distributions
random text generation, Generating Random Text, Generating Random Text
random variable generation, Generating variables
range scans
for indexes, Available Statistics, Extreme values
rank( ) function, Clustered indexes
read committed isolation level, Multiplying service providers at the application level, Multiplying service providers at the application level
read consistency, Multiplying service providers at the application level
read uncommitted isolation level, Multiplying service providers at the application level
READ_COMMITTED_SNAPSHOT database option, Multiplying service providers at the application level
refactoring
database accesses, Refactoring, First Standpoint, Refactoring, First Standpoint, Refactoring, Second Standpoint, How It Works: Refactoring in Practice, Can You Look at the Database?
query considerations, Queries of Death–No Obvious Very Wrong Query, Queries of Death, All These Fast Queries, No Obvious Very Wrong Query, No Obvious Very Wrong Query
threshold values example, Refactoring, First Standpoint–Refactoring, Second Standpoint, Refactoring, First Standpoint, Refactoring, First Standpoint, Refactoring, First Standpoint, Refactoring, Second Standpoint, Refactoring, Second Standpoint
views, Performance Comparison with and Without a Complex View–Refactoring Views, Refactoring Views, Refactoring Views, Refactoring Views
referential integrity
random data and, Generating Many Rows
reorganizing processing
benefits from, Shaking Foundations
repeatable read isolation level, Multiplying service providers at the application level
Roughbench tool, Roughbench–Output, Generating variables, Output, Output
row versioning, Multiplying service providers at the application level, Multiplying service providers at the application level
rowid (Oracle), Shortening critical sections
row_number( ) function, Clustered indexes

S

scalability, Dealing with multiple queues
scalar subquery, Subqueries in the select list
selectivity, Available Statistics, Available Statistics
serializable isolation level, Multiplying service providers at the application level
server-side processing
logging, Dumping statements to a trace file, Dumping statements to a trace file
SQL statements, Parsing and Bind Variables
Service Profile Identifier (SPID) (SQL Server), Dumping statements to a trace file
service time, Service time and arrival rate–Increasing parallelism, Service time and arrival rate, Increasing parallelism, Increasing parallelism
session variables
MySQL support, Example 2: A conversion function
T-SQL limitations, Example 2: A conversion function
set operators
complex queries and, Unitary Analysis
repeated patterns and, Eliminating Repeated Patterns
setDate( ) function, A Simple Example
setInt( ) function, A Simple Example
setLong( ) function, A Simple Example
SHA1 algorithm, Comparing checksums in SQL
sigma (standard deviation), Using Random Functions
single query approach, Choosing Among Various Approaches, Choosing Among Various Approaches
single-column indexes, A Detailed Investigation–A Detailed Investigation, A Detailed Investigation, A Detailed Investigation, A Detailed Investigation, A Detailed Investigation
slow queries
analyzing, Analyzing a Slow Query–Cleaning Up the from Clause, Cleaning Up the from Clause
slow statements, Statement Refactoring
snapmon.sql script, Can You Look at the Database?, Chapter 1
snapshot too old error, Multiplying service providers at the application level
sp_describe_cursor_columns stored procedure (SQL Server), Comparing checksums in SQL
sp_executesql stored procedure (SQL Server), Comparing checksums in SQL
sp_helpstats stored procedure (SQL Server), Available Statistics
sp_trace_filter stored procedure (SQL Server), Dumping statements to a trace file
SQL injection, Correcting Parsing Issues, Correcting Parsing Issues the Proper Way
SQL mindset
assuming success, Assuming Success–Assuming Success, Assuming Success
writing statements, Task Refactoring–Assuming Success, Using SQL Where SQL Works Better, Assuming Success, Assuming Success
SQL Profiler (SQL Server), Dumping statements to a trace file
SQL Server
clustered indexes, Clustered indexes
detecting parsing issues, How to Detect Parsing Issues, Correcting Parsing Issues
dynamic views, Querying dynamic views
materialized views, Materializing views
parsing issues, Correcting Parsing Issues the Lazy Way
random functions and, Using Random Functions
row versioning, Multiplying service providers at the application level
SQL Profiler, Dumping statements to a trace file
SQL Server Integration Services, Using a temporary table
SQL statements
as telling stories, Statement Refactoring
categories worth tuning, Querying dynamic views
checking data, Brute force comparison–Comparing checksums in SQL, SQL comparison, textbook version, SQL comparison, better version, Comparing checksums in SQL, Comparing checksums in SQL
combining, Combining Statements
comparing checksums, Comparing checksums in SQL–Limits of Comparison, Comparing checksums in SQL, Comparing checksums in SQL, Comparing checksums in SQL, Comparing checksums in SQL, Limits of Comparison, Limits of Comparison
control structures and, Pushing Control Structures into SQL–Getting Rid of count(), Getting Rid of count()
execution plans, Querying dynamic views
functional comparisons and, Comparing Crudely
mindset for writing, The SQL Mindset–Assuming Success, Assuming Success
rewriting, Improving Functions Versus Rewriting Statements
stored procedures and, Querying dynamic views
tuning, Task Refactoring
SQL*Loader utility, Matching Existing Distributions
SQL*Plus utility, Available Statistics
SqlCommand class, Correcting Parsing Issues
SQLite, Exploiting trace files, Generating Many Rows, How to Use mklipsum and lipsum
SqlPipe object, Bulk Operations
sql_log_off session variable, Dumping statements to a trace file
standard deviation (sigma), Using Random Functions
statements
hardcoded, Assessing Possible Gains, Parsing and Bind Variables, Correcting Parsing Issues
prepared, A Simple Example, Handling Lists in Prepared Statements–Bulk Operations, Bulk Operations
recursive, Assessing Possible Gains, All These Fast Queries
slow, Statement Refactoring
softcoded, Parsing and Bind Variables, Correcting Parsing Issues–Correcting Parsing Issues the Lazy Way, Correcting Parsing Issues, Correcting Parsing Issues the Lazy Way
stats.sql script, Chapter 2
Stefanetti, SQL comparison, better version
storage allocation
performance and, Sanity Checks
serialization and, Isolating Hot Spots
stored procedures
checksums and, Comparing checksums in SQL
statement execution and, Querying dynamic views
views and, User Functions and Views
strings
counting patterns, Improving Computation-Only Functions–Improving Computation-Only Functions, Improving Computation-Only Functions, Improving Computation-Only Functions
subpartitioning, Marshaling Rows
subqueries
correlated, Subqueries in the where clause, Subqueries in the where clause, Queries of Death
from clause, Subqueries in the from clause
merging, Subqueries in the select list
select statement and, Subqueries in the select list–Subqueries in the from clause, Subqueries in the select list, Subqueries in the from clause, Subqueries in the from clause
uncorrelated, Subqueries in the where clause, Subqueries in the where clause
where clause, Subqueries in the where clause–Activating Filters Early, Subqueries in the where clause, Subqueries in the where clause, Activating Filters Early
writing, Cleaning Up the from Clause
substr( ) function, Improving Computation-Only Functions
surrogate keys, Isolating Hot Spots, All These Fast Queries
Sybase Open Server, Dumping statements to a trace file
synchronization
database calls and, Parallelizing Your Program and the DBMS
parallelism and, Multiplying service providers at the application level
sys.dm_exec_cached_plans (SQL Server), Querying dynamic views
sys.dm_exec_query_stats (SQL Server), Querying dynamic views
sys.dm_exec_requests (SQL Server), Querying dynamic views
sys.dm_os_performance_counters (SQL Server), Querying dynamic views

T

T-SQL, Example 2: A conversion function, Example 2: A conversion function
tables
comparing, Comparing Tables and Results–Limits of Comparison, Brute force comparison, SQL comparison, textbook version, Comparing checksums in SQL, Limits of Comparison
contention in, Isolating Hot Spots
schema indexing and, A Quick Look at Schema Indexing–A Quick Look at Schema Indexing, A Quick Look at Schema Indexing, A Quick Look at Schema Indexing
splitting, Splitting Tables
temporary, Temporary tables, Using a temporary table
types of, Cleaning Up the from Clause
unindexed, A Quick Look at Schema Indexing
with multiple indexes, A Quick Look at Schema Indexing
with single indexes, A Quick Look at Schema Indexing
without unique indexes, A Quick Look at Schema Indexing
temporary tables
lists and, Batching lists
testing framework
comparing crudely, Comparing Crudely
comparing tables and results, Comparing Tables and Results–Limits of Comparison, SQL comparison, textbook version, SQL comparison, textbook version, SQL comparison, better version, Limits of Comparison
generating random text, Generating Random Text, Generating Random Text
generating rows, Generating Many Rows, Generating Many Rows
generating test data, Generating Test Data
limits of comparison, Limits of Comparison
matching distributions, Matching Existing Distributions–Generating Many Rows, Generating Many Rows
multiplying rows, Multiplying Rows, Using Random Functions
random functions, Using Random Functions–Matching Existing Distributions, Using Random Functions, Using Random Functions, Matching Existing Distributions
referential integrity, Dealing with Referential Integrity
unit testing, Unit Testing
threshold values
code to generate transactions, A Simple Example, A Simple Example, A Simple Example, A Simple Example, SQL Tuning, the Traditional Way, SQL Tuning, Revisited
tuning comparisons, Choosing Among Various Approaches
tkprof tool (Oracle), Exploiting trace files
tools
lipsum, Tools–How to Use mklipsum and lipsum, How to Use mklipsum and lipsum, How to Use mklipsum and lipsum, How to Use mklipsum and lipsum
mklipsum, mklipsum and lipsum–How to Use mklipsum and lipsum, How to Use mklipsum and lipsum, How to Use mklipsum and lipsum
Roughbench, Roughbench–Output, Output
transactions
checking threshold values, A Simple Example, SQL Tuning, the Traditional Way, Refactoring, First Standpoint
loops and, Challenging loops
tree structures, Indexing Review
trunc( ) function, Improving Functions Further
Twain, Assessing Possible Gains

U

uncorrelated subqueries, Subqueries in the where clause, Subqueries in the where clause, Queries of Death
undo operation, Challenging loops
uniform number distribution, Available Statistics, Optimizer Traps, Using Random Functions
unions
views built as, Performance Comparison with and Without a Complex View
unit testing, Unit Testing
unitary analysis, Unitary Analysis, All These Fast Queries
user-defined functions
categories of, User-Defined Functions
UTF8 encoding, How to Use mklipsum and lipsum
utility functions, Using exceptions

V

v, Finding Out What the Database Is Doing, Finding Out What the Database Is Doing, Querying dynamic views, Querying dynamic views, Parsing and Bind Variables, How to Detect Parsing Issues, How to Detect Parsing Issues
variables
array, Example 2: A conversion function
generating random, Generating variables
session, Example 2: A conversion function
version control software, Multiplying service providers at the application level
views
dynamic, Querying dynamic views–Dumping statements to a trace file, Dumping statements to a trace file
filtering, Performance Comparison with and Without a Complex View, Performance Comparison with and Without a Complex View
materializing, Materializing views
performance considerations, Improving Functions Versus Rewriting Statements–Refactoring Views, What Views Are For, Performance Comparison with and Without a Complex View, Performance Comparison with and Without a Complex View, Performance Comparison with and Without a Complex View, Refactoring Views, Refactoring Views
stored procedures and, User Functions and Views

W

wait times, Assessing Possible Gains
WebLogic application server, Dumping statements to a trace file
WebSphere application server, Dumping statements to a trace file
where clause
views and, Performance Comparison with and Without a Complex View
with clause, Simplifying Conditions

X

XML data, Generating Random Text
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.149.249.174