Pennsylvania
Department of Public Welfare
Office of Information Systems
Execution Plan Example
Version 1.0
March 11, 2004
DPW Business and Technical Standards Document Revised 03/11/04
Table of Contents
Introduction................................................................................................................................ 3
Purpose...................................................................................................................................... 3
Execution Plan Example...........................................................................................................4
Document Change Log................................................................................................................8
Execution Plan Example.doc Page 2 of 8
DPW Business and Technical Standards Document Revised 03/11/04
Execution Plan Example
Introduction
This document shows the user how to develop an execution plan for a SQL Stored Procedure.
Purpose
The purpose of this document is to assist the user in developing an execution plan when
creating or changing a SQL Stored Procedure.
Execution Plan Example.doc Page 3 of 8
DPW Business and Technical Standards Document Revised 03/11/04
Execution Plan Example
/*Please see statsioshowplan.doc for instructions. */
------------------------------------------------------------------------------
----------------------------------------
EXEC USP_SAVE_CLIENT_APPN
111110001,'Lock','Pat',Null,'0',1,1,10000,Null,'Adl1','Adl2','Harrisburg','PA'
,
17111,1000,717,6669999,'06/30/2002','07/17/2002',Null,'12345','1',2,'Approved'
,1,
2,'00021','23432',1,1,1,1,1,'0',1,200,'Regular','06/30/2002',Null,Null,1,Null,
Null,Null,Null
------------------------------------------------------------------------------
-----------------------------------------
Table 'T_ENERGY_YEAR'. Scan count 1, logical reads 1, physical reads 0, read-
ahead reads 0.
Table 'T_PROG'. Scan count 1, logical reads 1, physical reads 0, read-ahead
reads 0.
Table 'T_PROG'. Scan count 1, logical reads 1, physical reads 0, read-ahead
reads 0.
Table 'T_PROG_ACCT_INFO'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.
Table 'T_APPLN_RCV_TYPE'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'T_CDE_ARRNGT_LIVING'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'T_VENDOR_EFT_INFO'. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table 'T_METHOD_PMT'. Scan count 1, logical reads 1, physical reads 0, read-
ahead reads 0.
Table 'T_TYPE_PMT'. Scan count 1, logical reads 1, physical reads 0, read-
ahead reads 0.
Table 'T_STATUS_APPLN'. Scan count 1, logical reads 1, physical reads 0, read-
ahead reads 0.
Table 'T_STATUS_APPLN'. Scan count 1, logical reads 1, physical reads 0, read-
ahead reads 0.
Table 'T_STATUS_APPLN'. Scan count 1, logical reads 1, physical reads 0, read-
ahead reads 0.
Table 'T_STATUS_APPLN'. Scan count 1, logical reads 1, physical reads 0, read-
ahead reads 0.
Table 'T_USER'. Scan count 1, logical reads 2, physical reads 0, read-ahead
reads 0.
Table 'T_APPLN'. Scan count 0, logical reads 0, physical reads 0, read-ahead
reads 0.
Table 'T_LEVEL_FPIG'. Scan count 1, logical reads 1, physical reads 0, read-
ahead reads 0.
Table 'T_PROG'. Scan count 1, logical reads 2, physical reads 0, read-ahead
reads 0.
Execution Plan Example.doc Page 4 of 8
DPW Business and Technical Standards Document Revised 03/11/04
Table 'T_APPLN_PMT'. Scan count 2, logical reads 12, physical reads 0, read-
ahead reads 0.
Table 'T_APPLN'. Scan count 1, logical reads 12, physical reads 0, read-ahead
reads 0.
Table 'T_RSUB_TO_LIHEAP'. Scan count 1, logical reads 26, physical reads 0,
read-ahead reads 26.
Table 'T_APPLN_PMT'. Scan count 2, logical reads 12, physical reads 0, read-
ahead reads 0.
Table 'T_APPLN'. Scan count 1, logical reads 12, physical reads 0, read-ahead
reads 0.
Table 'T_PROG'. Scan count 1, logical reads 2, physical reads 0, read-ahead
reads 0.
------------------------------------------------------------------------------
-----------------------------------------
StmtText
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
----------------------
EXEC USP_SAVE_CLIENT_APPN
111110001,'Lock','Pat',Null,'0',1,1,10000,Null,'Adl1','Adl2','Harrisburg','PA'
,
17111,1000,717,6669999,'06/30/2002','07/17/2002',Null,'12345','1',2,'Approved'
,1,
2,'00021','23432',1,1,1,1,1,'0',1,200,'Regular','06/
/***************************************************************************
* File Name : dbo.USP_SAVE_CLIENT_APPN.prc
* File Desc : Create stored procedure for LIHEAP Redesign Project
* Database : LIHEAP2000
* Language : MS Transact-SQL
*
DECLARE @nbr_energy_year INTEGER,
@idn_client INTEGER,
@idn_prog_cash INTEGER,
@idn_prog_crisis INTEGER,
@idn_client_adr INTEGER,
@cde_status_appln INTEGER,
@cde_rej_status INTEGER,
@cde_apr_status INTEGER,
@cde_pnd_status
SELECT @nbr_energy_year=MAX(NBR_ENERGY_YEAR)
FROM T_ENERGY_YEAR (NOLOCK)
--
-- GET PROGRAM ID's FOR PASSED PROGRAM AND
-- AVAILABLE PROGRAM(CASH AND CRISIS)
--
Execution Plan Example.doc Page 5 of 8
DPW Business and Technical Standards Document Revised 03/11/04
-- CASH
(4 row(s) affected)
StmtText
------------------------------------------------------------------------------
------------------------------------------
|--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1002])))
|--Stream Aggregate(DEFINE:([Expr1002]=MAX([T_ENERGY_YEAR].
[NBR_ENERGY_YEAR])))
|--Top(1)
|--Index Scan(OBJECT:([LIHEAP2000].[dbo].
[T_ENERGY_YEAR].[PK_T_ENERGY_YEAR79]), ORDERED BACKWARD)
(4 row(s) affected)
StmtText
------------------------------------------------------------------------------
---------------
SELECT @idn_prog_cash=IDN_PROG
FROM T_PROG (NOLOCK)
WHERE NAM_PROG='CASH'
-- CRISIS
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------
-----------------
|--Table Scan(OBJECT:([LIHEAP2000].[dbo].[T_PROG]), WHERE:([T_PROG].
[NAM_PROG]='CASH'))
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
---------------------
SELECT @idn_prog_crisis=IDN_PROG
FROM T_PROG (NOLOCK)
WHERE NAM_PROG='CRISIS'
--
-- GET IDN_PROG_ACCT_INFO
--
-- {MTM:02.01:02} - @energy_yr_new is derived based on the @nam_pmt_type
-- (use either @nbr_energy_year or @nbr_energy_yrip)
Execution Plan Example.doc Page 6 of 8
DPW Business and Technical Standards Document Revised 03/11/04
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------
-------------------
|--Table Scan(OBJECT:([LIHEAP2000].[dbo].[T_PROG]), WHERE:([T_PROG].
[NAM_PROG]='CRISIS'))
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------
-------------------------
IF @nam_pmt_type='Regular'
SET @energy_yr_new=@nbr_energy_year
ELSE IF @nam_pmt_type='Exception'
SET @energy_yr_new=@nbr_energy_yrip
ELSE
SELECT @energy_yr_new=NBR_ENERGY_YEAR
FROM T_APPLN
WHERE IDN_APPLN=@idn_appln
(5 row(s) affected)
StmtText
------------------------------------------------------------------------------
------------------------------------------------------------------------------
---
|--Compute Scalar(DEFINE:([Expr1002]=Convert([T_APPLN].
[NBR_ENERGY_YEAR])))
|--Clustered Index Seek(OBJECT:([LIHEAP2000].[dbo].
[T_APPLN].[PK_T_APPLN20]), SEEK:([T_APPLN].[IDN_APPLN]=[@idn_appln]) ORDERED
FORWARD)
(2 row(s) affected)
Execution Plan Example.doc Page 7 of 8
DPW Business and Technical Standards Document Revised 03/11/04
Document Change Log
Change Version CR # Change Description Author and
Date Organization
03/11/04 1.0 Initial creation. Kiley Milakovic
Execution Plan Example.doc Page 8 of 8
