Hour 17, Improving Database Performance | Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)


Team-Fly

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents

	Appendix C. Answers to Quizzes and Exercises

Hour 17, "Improving Database Performance"

Quiz Answers

1:	Would the use of a unique index on a small table be of any benefit?
A1:	The index may not be of any use for performance issues; but, the unique index would keep referential integrity intact. Referential integrity is discussed in Hour 3, "Managing Database Objects."
2:	What happens when the optimizer chooses not to use an index on a table when a query has been executed?
A2:	A full table scan occurs.
3:	Should the most restrictive clause(s) be placed before the join condition(s) or after the join conditions in the WHERE clause?
A3:	The most restrictive clause(s) should be evaluated before the join condition(s) because join conditions normally return a large number of rows.

Exercise Answers

Q1:

Rewrite the following SQL statements to improve their performance. Use the EMPLOYEE_TBL and the EMPLOYEE_PAY_TBL as described here:

EMPLOYEE_TBL
EMP_ID	VARCHAR(9)	NOT NULL Primary key,
LAST_NAME	VARCHAR(15)	NOT NULL,
FIRST_NAME	VARCHAR(15)	NOT NULL,
MIDDLE_NAME	VARCHAR(15),
ADDRESS	VARCHAR(30)	NOT NULL,
CITY	VARCHAR(15)	NOT NULL,
STATE	varCHAR(2)	NOT NULL,
ZIP	INTEGER(5)	NOT NULL,
PHONE	VARCHAR(10),
PAGER	VARCHAR(10),

 CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)

EMPLOYEE_PAY_TBL
EMP_ID	VARCHAR(9)	NOT NULL primary key,
POSITION	VARCHAR(15)	NOT NULL,
DATE_HIRE	DATETIME,
PAY_RATE	DECIMAL(4,2)	NOT NULL,
DATE_LAST_RAISE	DATETIME,
SALARY	DECIMAL(8,2),
BONUS	DECIMAL(8,2),

 CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID)    REFERENCES EMPLOYEE_TBL (EMP_ID)

 SELECT EMP_ID, LAST_NAME, FIRST_NAME,            PHONE    FROM EMPLOYEE_TBL    WHERE SUBSTRING(PHONE, 1, 3) = '317' OR          SUBSTRING(PHONE, 1, 3) = '812' OR          SUBSTRING(PHONE, 1, 3) = '765';

 SELECT LAST_NAME, FIRST_NAME     FROM EMPLOYEE_TBL    WHERE LAST_NAME LIKE '%ALL%;

 SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME,            EP.SALARY    FROM EMPLOYEE_TBL E,    EMPLOYEE_PAY_TBL EP    WHERE LAST_NAME LIKE 'S%'    AND E.EMP_ID = EP.EMP_ID;

A1:

 SELECT EMP_ID, LAST_NAME, FIRST_NAME,            PHONE    FROM EMPLOYEE_TBL    WHERE SUBSTRING(PHONE, 1, 3) = '317' OR          SUBSTRING(PHONE, 1, 3) = '812' OR          SUBSTRING(PHONE, 1, 3) = '765';

 SELECT EMP_ID, LAST_NAME, FIRST_NAME,            PHONE    FROM EMPLOYEE_TBL    WHERE SUBSTRING(PHONE, 1, 3) IN ('317', '812', '765');

From our experience, it is better to convert multiple OR conditions to an IN list.

 SELECT LAST_NAME, FIRST_NAME     FROM EMPLOYEE_TBL    WHERE LAST_NAME LIKE '%ALL%;

 SELECT LAST_NAME, FIRST_NAME     FROM EMPLOYEE_TBL    WHERE LAST_NAME LIKE 'WAL%;

You cannot take advantage of an index if you do not include the first character in a condition's value.

 SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME,            EP.SALARY    FROM EMPLOYEE_TBL E,    EMPLOYEE_PAY_TBL EP    WHERE LAST_NAME LIKE 'S%'    AND E.EMP_ID = EP.EMP_ID;

 SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME,            EP.SALARY    FROM EMPLOYEE_TBL E,    EMPLOYEE_PAY_TBL EP    WHERE E.EMP_ID = EP.EMP_ID    AND LAST_NAME LIKE 'S%';

List join operations first in the WHERE clause (check with your implementation of SQL on how the optimizer reads conditions in the WHERE clause). Many implementations ' optimizers evaluate data listed last in the WHERE clause first. It is important to filter data before all rows between tables are joined. Also try to evaluate indexed conditions first.


Team-Fly

Top