Showplan Difference
Mar 9, 2001Hi,
I have a view in a database called PS_EMPLMT_SRCH_US and it joins around five tables. I have two databases which are identical structures including data. But when run one query
ie "SELECT * FROM PS_EMPLMT_SRCH_US where EMPLID like '00918%' and OPRCLASS like 'ALLPANLS' ",
response time is totally different even though both databases have same data and same indexes. When I looked at showplan and stats time, both are totally different and order of table fetch is totally different. Is there any idea why this difference? Any help would be appreciated.
Here is the show plan info:
-------------------------------------------------------------------
GOOD Performance Query and plan
-------------------------------------------------------------------
SELECT * FROM PS_EMPLMT_SRCH_US where EMPLID like '00918%' and OPRCLASS like 'ALLPANLS'
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 156 ms, elapsed time = 156 ms.
Table 'PS_SCRTY_TBL_DEPT'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'PSTREENODE'. Scan count 13, logical reads 26, physical reads 0, read-ahead reads 0.
Table 'PS_JOB'. Scan count 29, logical reads 87, physical reads 0, read-ahead reads 0.
Table 'PS_NID_TYPE_TBL'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'PS_PERS_NID'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'PS_PERSONAL_DATA'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
PLAN:
|--Nested Loops(Left Semi Join)
|--Nested Loops(Inner Join)
| |--Nested Loops(Inner Join)
| | |--Nested Loops(Inner Join)
| | | |--Filter(WHERE:([PS_JOB].[EFFSEQ]=[Expr1016]))
| | | | |--Nested Loops(Inner Join)
| | | | |--Filter(WHERE:([PS_JOB].[EFFDT]=[Expr1012]))
| | | | | |--Nested Loops(Inner Join)
| | | | | |--Nested Loops(Inner Join)
| | | | | | |--Index Seek(OBJECT:([HRNEW].[dbo].[PS_PERSONAL_DATA].[PS#PERSONAL_DATA]), SEEK:([PS_PERSONAL_DATA].[EMPLID] >= '00918' AND [PS_PERSONAL_DATA].[EMPLID] < '00919') ORDERED)
| | | | | | |--Index Seek(OBJECT:([HRNEW].[dbo].[PS_JOB].[PS#JOB]), SEEK:([PS_JOB].[EMPLID]=[PS_PERSONAL_DATA].[EMPLID]) ORDERED)
| | | | | |--Table Spool
| | | | | |--Stream Aggregate(DEFINE:([Expr1012]=MAX([PS_JOB].[EFFDT])))
| | | | | |--Top(1)
| | | | | |--Index Seek(OBJECT:([HRNEW].[dbo].[PS_JOB].[PSAJOB]), SEEK:([PS_JOB].[EMPLID]=[PS_JOB].[EMPLID] AND [PS_JOB].[EMPL_RCD#]=[PS_JOB].[EMPL_RCD#] AND [PS_JOB].[EFFDT] <= Convert(Convert(getdate()))) OR
| | | | |--Table Spool
| | | | |--Stream Aggregate(DEFINE:([Expr1016]=MAX([PS_JOB].[EFFSEQ])))
| | | | |--Index Seek(OBJECT:([HRNEW].[dbo].[PS_JOB].[PSAJOB]), SEEK:([PS_JOB].[EMPLID]=[PS_JOB].[EMPLID] AND [PS_JOB].[EMPL_RCD#]=[PS_JOB].[EMPL_RCD#] AND [PS_JOB].[EFFDT]=[PS_JOB].[EFFDT]) ORDERED)
| | | |--Clustered Index Seek(OBJECT:([HRNEW].[dbo].[PS_PERS_NID].[PS_PERS_NID]), SEEK:([PS_PERS_NID].[EMPLID]=[PS_JOB].[EMPLID]) ORDERED)
| | |--Clustered Index Seek(OBJECT:([HRNEW].[dbo].[PS_NID_TYPE_TBL].[PS_NID_TYPE_TBL]), SEEK:([PS_NID_TYPE_TBL].[COUNTRY]=[PS_PERS_NID].[COUNTRY] AND [PS_NID_TYPE_TBL].[NATIONAL_ID_TYPE]=[PS_PERS_NID].[NATIONAL_ID_TYPE]) ORDERED)
| |--Filter(WHERE:(like([PS_SCRTY_TBL_DEPT].[OPRID], 'ALLPANLS')))
| |--Clustered Index Seek(OBJECT:([HRNEW].[dbo].[PS_SCRTY_TBL_DEPT].[PS_SCRTY_TBL_DEPT]), SEEK:([PS_SCRTY_TBL_DEPT].[OPRID] BETWEEN 'ALLPANLS' AND 'ALLPANLS') ORDERED)
|--Row Count Spool
|--Filter(WHERE:([PS_JOB].[EFFDT]>=Convert(Convert(getdate())) OR (([PS_SCRTY_TBL_DEPT].[ACCESS_CD]='Y' AND [PS_JOB].[EFFDT]=[Expr1022]) AND [Expr1031])))
|--Nested Loops(Left Semi Join, WHERE:([PS_JOB].[EFFDT]>=Convert(Convert(getdate()))))
|--Nested Loops(Inner Join, WHERE:([PS_JOB].[EFFDT]>=Convert(Convert(getdate()))))
| |--Clustered Index Seek(OBJECT:([HRNEW].[dbo].[PS_JOB].[PS_JOB]), SEEK:([PS_JOB].[EMPLID]=[PS_JOB].[EMPLID]) ORDERED)
| |--Table Spool
| |--Stream Aggregate(DEFINE:([Expr1022]=MAX([PS_JOB].[EFFDT])))
| |--Index Seek(OBJECT:([HRNEW].[dbo].[PS_JOB].[PSAJOB]), SEEK:([PS_JOB].[EMPLID]=[PS_JOB].[EMPLID] AND [PS_JOB].[EMPL_RCD#]=[PS_JOB].[EMPL_RCD#] AND [PS_JOB].[EFFDT] <= Convert(Convert(getdate()))) ORDERED)
|--Row Count Spool
|--Nested Loops(Left Anti Semi Join, WHERE:([PSTREENODE].[TREE_NODE_NUM]>=[PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM] AND [PSTREENODE].[TREE_NODE_NUM]<=[PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM_END]))
|--Index Seek(OBJECT:([HRNEW].[dbo].[PSTREENODE].[PSCPSTREENODE]), SEEK:([PSTREENODE].[TREE_NODE]=[PS_JOB].[DEPTID] AND [PSTREENODE].[SETID]=[PS_SCRTY_TBL_DEPT].[SETID] AND [PSTREENODE].[TREE_NAME]='DEPT_SECURITY' AND [PSTRE
|--Filter(WHERE:([PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM]<[PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM] OR [PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM]>[PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM]))
|--Index Seek(OBJECT:([HRNEW].[dbo].[PS_SCRTY_TBL_DEPT].[PSASCRTY_TBL_DEPT]), SEEK:([PS_SCRTY_TBL_DEPT].[OPRID]=[PS_SCRTY_TBL_DEPT].[OPRID] AND [PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM] BETWEEN [PS_SCRTY_TBL_DEPT].[TREE_NODE_
-----------------------------------------------------------------------------
BAD Performance Query and plan
-------------------------------------------------------------------------
SELECT * FROM PS_EMPLMT_SRCH_US where EMPLID like '00918%' and OPRCLASS like 'ALLPANLS'
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'PS_JOB'. Scan count 168595, logical reads 515067, physical reads 0, read-ahead reads 0.
Table 'PS_PERSONAL_DATA'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'PS_PERS_NID'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'PS_NID_TYPE_TBL'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'PS_SCRTY_TBL_DEPT'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0.
Table 'PSTREENODE'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 15797 ms, elapsed time = 4500 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
|--Nested Loops(Left Semi Join)
|--Nested Loops(Inner Join, WHERE:([PS_JOB].[SETID_DEPT]=[PS_SCRTY_TBL_DEPT].[SETID]))
| |--Parallelism(Gather Streams)
| | |--Hash Match(Inner Join, HASH:([PS_PERS_NID].[EMPLID])=([PS_JOB].[EMPLID]), RESIDUAL:([PS_JOB].[EMPLID]=[PS_PERS_NID].[EMPLID]))
| | |--Parallelism(Distribute Streams, PARTITION COLUMNS:([PS_PERSONAL_DATA].[EMPLID]))
| | | |--Nested Loops(Inner Join)
| | | |--Nested Loops(Inner Join)
| | | | |--Index Seek(OBJECT:([HRTEST].[dbo].[PS_PERSONAL_DATA].[PS#PERSONAL_DATA]), SEEK:([PS_PERSONAL_DATA].[EMPLID] >= '00918' AND [PS_PERSONAL_DATA].[EMPLID] < '00919') ORDERED)
| | | | |--Clustered Index Seek(OBJECT:([HRTEST].[dbo].[PS_PERS_NID].[PS_PERS_NID]), SEEK:([PS_PERS_NID].[EMPLID]=[PS_PERSONAL_DATA].[EMPLID]) ORDERED)
| | | |--Clustered Index Seek(OBJECT:([HRTEST].[dbo].[PS_NID_TYPE_TBL].[PS_NID_TYPE_TBL]), SEEK:([PS_NID_TYPE_TBL].[COUNTRY]=[PS_PERS_NID].[COUNTRY] AND [PS_NID_TYPE_TBL].[NATIONAL_ID_TYPE]=[PS_PERS_NID].[NATIONAL_ID_TYPE]) ORDERE
| | |--Filter(WHERE:([PS_JOB].[EFFDT]>=Convert(Convert(getdate())) OR ([PS_JOB].[EFFDT]=[Expr1018] AND [PS_JOB].[EFFSEQ]=[Expr1022])))
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([PS_JOB].[EMPLID]))
| | |--Nested Loops(Inner Join, WHERE:([PS_JOB].[EFFDT]>=Convert(Convert(getdate()))))
| | |--Parallelism(Repartition Streams, PARTITION COLUMNS:([PS_JOB].[EMPLID], [PS_JOB].[EFFDT], [PS_JOB].[EMPL_RCD#]), ORDER BY:([PS_JOB].[EFFDT] ASC, [PS_JOB].[EMPL_RCD#] ASC, [PS_JOB].[EMPLID] ASC))
| | | |--Nested Loops(Inner Join, WHERE:([PS_JOB].[EFFDT]>=Convert(Convert(getdate()))))
| | | |--Sort(ORDER BY:([PS_JOB].[EFFDT] ASC, [PS_JOB].[EMPL_RCD#] ASC, [PS_JOB].[EMPLID] ASC))
| | | | |--Clustered Index Scan(OBJECT:([HRTEST].[dbo].[PS_JOB].[PS_JOB]))
| | | |--Table Spool
| | | |--Stream Aggregate(DEFINE:([Expr1018]=MAX([PS_JOB].[EFFDT])))
| | | |--Index Seek(OBJECT:([HRTEST].[dbo].[PS_JOB].[PSAJOB]), SEEK:([PS_JOB].[EMPLID]=[PS_JOB].[EMPLID] AND [PS_JOB].[EMPL_RCD#]=[PS_JOB].[EMPL_RCD#] AND [PS_JOB].[EFFDT] <= Convert(Convert(getdate()))) O
| | |--Table Spool
| | |--Stream Aggregate(DEFINE:([Expr1022]=MAX([PS_JOB].[EFFSEQ])))
| | |--Index Seek(OBJECT:([HRTEST].[dbo].[PS_JOB].[PSAJOB]), SEEK:([PS_JOB].[EMPLID]=[PS_JOB].[EMPLID] AND [PS_JOB].[EMPL_RCD#]=[PS_JOB].[EMPL_RCD#] AND [PS_JOB].[EFFDT]=[PS_JOB].[EFFDT]) ORDERED)
| |--Filter(WHERE:(like([PS_SCRTY_TBL_DEPT].[OPRID], 'ALLPANLS')))
| |--Index Seek(OBJECT:([HRTEST].[dbo].[PS_SCRTY_TBL_DEPT].[PSBSCRTY_TBL_DEPT]), SEEK:([PS_SCRTY_TBL_DEPT].[ACCESS_CD]='Y' AND [PS_SCRTY_TBL_DEPT].[OPRID] BETWEEN 'ALLPANLS' AND 'ALLPANLS') ORDERED)
|--Row Count Spool
|--Nested Loops(Left Anti Semi Join)
|--Index Seek(OBJECT:([HRTEST].[dbo].[PSTREENODE].[PSCPSTREENODE]), SEEK:([PSTREENODE].[TREE_NODE]=[PS_JOB].[DEPTID] AND [PSTREENODE].[SETID]=[PS_SCRTY_TBL_DEPT].[SETID] AND [PSTREENODE].[TREE_NAME]='DEPT_SECURITY' AND [PSTREENODE].[EFFDT]
|--Filter(WHERE:([PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM]<>[PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM]))
|--Index Seek(OBJECT:([HRTEST].[dbo].[PS_SCRTY_TBL_DEPT].[PSASCRTY_TBL_DEPT]), SEEK:([PS_SCRTY_TBL_DEPT].[OPRID]=[PS_SCRTY_TBL_DEPT].[OPRID] AND ([PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM], [PS_SCRTY_TBL_DEPT].[TREE_NODE_NUM_END], [PS_SCRTY_