Views and SQL Server Performance

On a recent consulting job, the performance problems that are easy to get into when using SQL Server Views was again made very clear to me.


A common problem with views that I see is that when developers need to find a column from “somewhere”, they often find it’s available in a view and select it from there, with no thought as to what’s involved in obtaining the data from the view. This gets especially bad if the view is then built on other views.

 

It started with an innocuous little query like:

 

SELECT DISTINCT “SYSNAME” FROM GW_HA_LINELIST

 

Hard to see any problem with that statement?

 

GW_HA_LINELIST is a substantial view that’s built on other views. (field names, etc. modified to hide the owner)

 

The basic code that needed to be executed was:

 

SELECT DISTINCT “SYSNAME” FROM “PIPE_SYMB” AS ps
INNER JOIN J292999PlantManager.dbo.process AS p
ON ps.”SYSNAME” = p.PSERV
INNER JOIN J292999PlantManager.dbo.TAG_REG AS tr
ON p.KEYTAG = tr.KEYTAG
INNER JOIN J292999PlantManager.dbo.plant_run AS pr
ON p.KEYTAG = pr.LINE_ID
WHERE (pr.PSPEC <> ‘NONE’)
AND (pr.PID_STATUS <> ‘DEL’)
AND (pr.AREA = ‘356’)
ORDER BY “SYSNAME”

 

But if you expanded out the views involved, it was actually executing:

 

SELECT DISTINCT “SYSNAME” FROM “PIPE_SYMB” WHERE “SYS_NAME” IN (SELECT DISTINCT “SYSTEM”
FROM (

SELECT DISTINCT
TOP 100 PERCENT J292999PlantManager.dbo.TAG_REG.TAG_NO + ‘-‘ + J292999PlantManager.dbo.plant_run.PAREA + ‘-‘ + J292999PlantManager.dbo.process.PSZ_NOM
+ ‘-‘ + J292999PlantManager.dbo.plant_run.PSPEC + ‘-‘ + J292999PlantManager.dbo.plant_run.INSCODE AS LineNumber,
J292999PlantManager.dbo.TAG_REG.TAG_NO AS LineID, J292999PlantManager.dbo.plant_run.PAREA AS AREA,
SUBSTRING(J292999PlantManager.dbo.process.PNUM, 5, 1) AS TrainNumber, CASE
WHEN (LEFT(PSUFFIX,1) = ‘Q’) AND (LEN(PSUFFIX) > 1)
THEN PNUM + PSUFFIX
ELSE PNUM
END AS [LINENO], J292999PlantManager.dbo.process.PSERV AS SYSTEM,
J292999PlantManager.dbo.plant_run.PSZ AS NOMDIAMETER, J292999PlantManager.dbo.plant_run.PSPEC AS Spec,
J292999PlantManager.dbo.plant_run.INSCODE AS InsulationType, J292999PlantManager.dbo.plant_run.RUN_TRC AS HTRACED,
J292999PlantManager.dbo.process.CONST_TYPE AS ConstType, RTRIM(J292999PlantManager.dbo.process.[FROM]) AS LineFrom,
RTRIM(J292999PlantManager.dbo.process.[TO]) AS LineTo, J292999PlantManager.dbo.plant_run.INSTHKNS AS InsulationThickness,
J292999PlantManager.dbo.process.ENG_STATUS AS EngStatus, J292999PlantManager.dbo.process.DES_STATUS AS DesignStatus,
J292999PlantManager.dbo.process.WORK_PACK AS ConstructionWorkPack, J292999PlantManager.dbo.process.WBS,
J292999PlantManager.dbo.process.MOD_NO AS ModuleNo, J292999PlantManager.dbo.process.STREAM_ID AS StreamNumber,
J292999PlantManager.dbo.plant_run.PWHT, J292999PlantManager.dbo.plant_run.NDE,
J292999PlantManager.dbo.plant_run.PAINT_SYS AS PaintSystem, J292999PlantManager.dbo.plant_run.PID_NO AS PIDNumber1,
J292999PlantManager.dbo.doc_reg.DOC_NAME AS PIDNumber, J292999PlantManager.dbo.plant_run.PID_REV AS PidRevision,
J292999PlantManager.dbo.plant_run.PFD_NO AS PFDNumber, J292999PlantManager.dbo.plant_run.FLUID_GROUP AS FluidGroup,
J292999PlantManager.dbo.plant_run.ISO_NO AS IsometricNumber, J292999PlantManager.dbo.plant_run.STRESSANALYSIS AS StressAnalysisType,
J292999PlantManager.dbo.plant_run.RUN_DESIGN_TEMP AS DesignTemperature,
J292999PlantManager.dbo.plant_run.RUN_DESIGN_PRES AS DesignPressure, J292999PlantManager.dbo.plant_run.TEST_MEDIA AS TestMedia,
J292999PlantManager.dbo.plant_run.DESIGNFLOW, J292999PlantManager.dbo.plant_run.COLOR_CODE AS CoulourCode,
J292999PlantManager.dbo.plant_run.INTERN_CLEAN AS InternalClean, J292999PlantManager.dbo.plant_run.TEST_PRESS AS TestPressure,
J292999PlantManager.dbo.process.COMMENTS, J292999PlantManager.dbo.process.HOLD_DESC AS HoldDescription,
J292999PlantManager.dbo.process.HOLD_DATE AS HoldDate, J292999PlantManager.dbo.process.STRESS_REF AS StressReference,
J292999PlantManager.dbo.plant_run.POPP AS OperatingPressure, J292999PlantManager.dbo.plant_run.POPT AS OperatingTemperature,
J292999PlantManager.dbo.plant_run.SG AS SpecificGravity, J292999PlantManager.dbo.plant_run.PROJECT1,
J292999PlantManager.dbo.plant_run.PROJECT2, J292999PlantManager.dbo.plant_run.PROJECT5,
J292999PlantManager.dbo.plant_run.RUN_DESIGN_CODE AS DesignCode, J292999PlantManager.dbo.plant_run.COLOR_CODE AS ColourCode,
J292999PlantManager.dbo.plant_run.MAT_OF_CONST AS CONST_MAT
FROM J292999PlantManager.dbo.process INNER JOIN
J292999PlantManager.dbo.TAG_REG ON
J292999PlantManager.dbo.process.KEYTAG = J292999PlantManager.dbo.TAG_REG.KEYTAG LEFT OUTER JOIN
J292999PlantManager.dbo.plant_run ON J292999PlantManager.dbo.process.KEYTAG = J292999PlantManager.dbo.plant_run.LINE_ID LEFT OUTER JOIN
J292999PlantManager.dbo.doc_link ON J292999PlantManager.dbo.doc_link.KEYTAG = J292999PlantManager.dbo.plant_run.KEYTAG LEFT OUTER JOIN
J292999PlantManager.dbo.doc_reg ON J292999PlantManager.dbo.doc_link.DOC_ID = J292999PlantManager.dbo.doc_reg.DOC_ID
WHERE (J292999PlantManager.dbo.plant_run.LINE_ID IS NOT NULL) AND (J292999PlantManager.dbo.plant_run.PSPEC <> ‘NONE’) AND
(J292999PlantManager.dbo.plant_run.PID_STATUS <> ‘DEL’)
ORDER BY J292999PlantManager.dbo.TAG_REG.TAG_NO,
J292999PlantManager.dbo.TAG_REG.TAG_NO + ‘-‘ + J292999PlantManager.dbo.plant_run.PAREA + ‘-‘ + J292999PlantManager.dbo.process.PSZ_NOM + ‘-‘ + J292999PlantManager.dbo.plant_run.PSPEC
+ ‘-‘ + J292999PlantManager.dbo.plant_run.INSCODE, J292999PlantManager.dbo.doc_reg.DOC_NAME DESC
) AS GW_HA_LINELIST WHERE “AREA” = ‘356’) ORDER BY 1


This query alone was responsible for millions of database reads per execution, instead of perhaps hundreds. Views can be a wonderful tool when used appropriately but in the real world, you have to consider what’s involved in them producing the data for you. In this case, a stored procedure that did the work required made a stunning difference to the system performance and was able to make take full advantage of query plan reuse as well.

2 thoughts on “Views and SQL Server Performance”

  1. I see the same thing a lot with managed code performance issues. Because most managed APIs are so easy to call (esp. compared to the Windows SDK cousins), there is no sense in the code of the amount of work that goes on under the hood to execute the higher-level functionality.

    A very hard problem to address in terms of developer eductation, as you sort of have to know how a method call is implemented, which is the opposite (in terms of productivity) to what you want developers to have to do when using a high-level API.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>