TechEd U.S. -> Boston -> Simulcast & Missile Defence Systems

Hi Folks, got a note this morning to tell me my session for TechEd U.S. will also be simulcast. I’ll post the url when I get it. But also on the note, I saw that local MVP and fellow Readifarian Martin Granell’s session has been also been selected for simulcast. Outstanding!


Martin clearly has picked the best title for any session at TechEd this year: How to get your grandmother building missile defence systems. No idea what he’s going to cover but I’m not going to miss it :-)

CodeCampOz – Return to Wagga – Done and Dusted & Some Interesting Pizza Stats

So, CodeCampOz is over for another year. Again, it was wonderful to see the local developer community in action. Special thanks need to go to:


* Irfan Altas and the gang at Charles Sturt University for their outstanding organisation help


* Chuck Sterling, Andrew Coates, Dave Lemphers and all the Microsoft Australia gang – particularly those in Frank Arrigo’s group – (great to see Deeps and Rose Stamell there too) for their sponsorship and all-round help and encouragement


* Adam Cogan for sponsorship of the speaker trainer


* Mitch Denny for being Mitch


* All the speakers. Apart from Mitch and myself, I think I have the full list in: Dan Green, Bill Chesnut, Charles Sterling, Joseph Cooney, Anthony Borton, Shea Strickland, Philip Argy, Chris Hewitt, Martin Hale, Angus Logan, Darren Neimke, Adam Cogan, Troy Magennis, Nick Randolph, Dave Lemphers, Martin Granell, Nick Weinholt, Paul Glavich, James McCutcheon, Susan Entwisle, Geoff Orr, Rocky Heckman, Deepak Kapoor and Tatham Oddie.


* The non-developer MVPs: Sandi, John (McGhie), Steve, Judy and Shauna who also provided alternate sessions that made attendees realise what they didn’t know about Word or IE.


Special thanks for behind-the-scenes efforts also to:


* Corneliu Tusnea for helping with travel arrangements


* Anthony Chan for helping with signage and stats


* John, Judy, Rose and Steve (you know how you helped)


and anyone else I forgot. Thanks also to all the attendees.


We also received some interesting statistics from Anthony Chan:


* 75 vehicles (including one motor home) were parked on site.


* Most favourite pizza flavour was Pepperoni, followed closely by Hawaiian and BBQ Meatlovers. Less favoured were BBQ Chicken, Cheese and Vegetarian.


* Most favourite drink was Coke (classic) with daylight second and the rest in a close tie (Diet Coke, Coke Zero, Sprite and Water)


* Average was 3 slices per person.


* Only 60% had garlic bread.  A standard 9 inch garlic bread could serve 3 attendees.


Thanks Anthony!


 

Deferred constraint checking?

Another discussion we’ve had on the local mailing lists lately has been around whether or not the ability to defer constraint checking (at least foreign key constraints) until the end of a transaction would be useful in SQL Server.


I see a lot of convoluted update code from things like ADO.NET datasets that would be greatly simplified if you could say:


a) start a transaction


b) send all the updates in any order


c) commit the transaction (and check the RI constraints at this point)


I’d love to hear your thoughts. Oracle has had it for a long time. Would you find it useful? If so, vote for it here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=8830bd75-ec68-4e8f-a4e5-3ab293a979db

Tool to expand SQL Server views?

In a previous post, I mentioned the sort of performance problems I see (when doing consulting work) that relate to the use of VIEWs. A discussion on this on our local mailing lists in the last day or so got me thinking that a tool is needed.


I’d like to see a tool that could be used from within SQL Server Management Studio where you could:


a) highlight some T-SQL text in a query window


b) right-click it and have it expand out the code, including all the underlying views, into another query window. (would obviously also need to be permission-based)


In a perfect world, there would then also be an option to simplify a section of T-SQL code in basic ways eg:


a) remove unnecessary underlying table joins


b) remove duplicated where clause entries


etc.


I’ve put a post on the feedback site. If you’d like to see it, please vote for it at: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=cbd046de-073a-42af-8e3d-481ef66be0c6

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.