0

When you have multiple optional parameters in a stored procedure...

by Jon 30. March 2010 09:47

If you have a number of optional parameters in a stored procedure (that are disabled with boolean logic) that will dramatically change the way the query works depending on the parameter it is best to add (WITH COMPILE) to the stored procedure, to ensure that the best query plan is always used.  The first time a stored procedure is executed, the parameters you have passed will determine the way it is executed.  If you first run the stored procedure against an empty database, used some duff test fields, or you have a report based stored procedure that works in a different way depending on parameters the first call may not be the ideal query plan.  By forcing the query to be recompiled for these optional varied you will massively boost performance.  This feature should be used sparingly, but there are often places where it needs to be essential (Some reporting stored procedures/stored procedures used to search)

ALTER Procedure [dbo].[Report_ExportReworkData]
    @ProductOption as nvarchar(50),
    @SerialNumber as nvarchar(50),
    @WorksOrder as nvarchar(50)

-- Make sure this gets compiled each time to avoid incorrect parmeter sniffing
WITH RECOMPILE

as
select
    ......
where
    (len(@ProductOption) = 0 or WorksOrders.PartNo = @ProductOption) and
    (len(@WorksOrder) = 0 or WorksOrders.WorksOrderNo = @WorksOrder) and
    (len(@SerialNumber) = 0 or  MfgRecords.SerialNumber = @SerialNumber)

Tags: , , , , , , ,

development | SQL

0

It is Saturday evening and I have almost recovered from my Scott Guthrie induced migraine

by Jon 27. March 2010 23:39

Scott Guthrie

I think I have almost recovered and I can finally put some of the stuff I saw and learnt down on screen.  I have had my migraine since around 2pm yesterday, pain killers couldn’t hold it back.  It’s hardly a surprise as it was initiated by Scott Guthrie filling my brain with too much dotnet goodness. Who is this Scott Guthrie bloke anyway and how did all this all happen, and why don't I mind that much? 

Scott is a software developer, he wears a red polo shirt, but more importantly co wrote (yes wrote!), asp.net!  He was a founding member of .net itself and he is official title is ‘Corporate vice president of Microsoft's .NET Developer Platform’.  If you want to hear about dot net he is the bloke to listen to.  Scott was in the UK for two days to give two five hour sessions three of the projects that under his wings:

  • Visual Studio 2010 and asp.net 4
  • Asp.net Model View Controller (MVC)
  • Silverlight and Windows Phone 7 development

I went to the second Guathon which was held in Birmingham City Centre Odeon cinema yesterday.  It was an all day event; Scott did a mammoth five hour talk with only a couple of short breaks for a bite to eat or to grab a bottle of water.

Visual Studio 2010 and Asp.net 4

In the morning session Scott started by going over all the great improvements to visual studio, in brief vs2010 is a big improvement on vs2008, its faster, its easier to see your code, you have more customisation, you can run multiple monitors more effectively and there are lots of refactoring features that make making writing and changing code much quicker.  In summary it is much better, it is a no brainer even if you are going to continue to develop in anything from dotnet 2 and above.  Scott deep dived into vs2010 to show these features, and also demoed lots of improvements to dotnet and asp.net and beyond.

Asp.net Model View Controller (MVC)

This was the second time I saw a session on MVC, but this time I got it. Scott explained asp.net MVC really clearly, its quite a jump from traditional asp.net but I can see the massive improvements it will bring. Although MVC development turns development on its head you can see how code reuse is much more effective and when combined with entity framework 2 it will makes for a very agile development process.

Silverlight and Windows Phone 7 Development

Another fantastic session, it made developing Silverlight look very simple there are big improvements in vs2010 to the development environment to support Silverlight developers and make it less daunting. Windows phone 7 looks like a real iPhone killer, the min specs are a quad core ARM processor combined with a GPU! Windows phone 7 apps are Silverlight so they are quick and smooth and ready to develop. A twitter Silverlight app was developed from scratch and deployed to a phone within 5 minutes live on stage, very impressive.

Odeon Cinima in Birmingham filling up with dotnet developers for the Guathon
Odeon Cinima in Birmingham filling up with dotnet developers for the Guathon

Head Honcho ScottGu from Microsoft about to start one of his presentation at the Guathon
Head Honcho ScottGu from Microsoft about to start one of his presentation at the Guathon

In summary it was a fantastic event, and amazing considering it was free! A big thanks must go to Phil Winstanley for arranging and organising it.

0

Nanos gigantium humeris insidentes

by Jon 8. March 2010 10:53

I have got round to getting a website and blog up and running the day after the Oscars, and although I haven't won any awards I still feel the need to thank everyone that have made it possible.  I am a developer at heart and just 5 or 10 years ago I would have been looking to chop the giant down at the waist and develop my own Blog software or develop a web app and using that instead of using else’s software.  Over the years I have come to the conclusion that what is the point, it is better to short circuit the development cycle, as long as the short circuit is worthy.  Software development is all about the customer or consumer and getting something developed as quickly as possible, which will exceed the customer’s requirements, this is the same for a simple blog.  If you can install a piece of software on a server you have saved yourself many hours which you can then dedicate to getting something else more productive done.  Of course I still need to be a dwarf, and the shoulder needs to be a good base; tweakable to exceed all my needs.  It’s all about getting things done, and that’s what this blog is about.I will be standing on the shoulders of many giants explaining how I get things done. It would be great to get and keep some readers but I don’t mind it only ends up being a simple brain dump of what is in my head, and things I get up to as a dotnet Developer/Manager.

Tags: , , , , , ,

development | General

Powered by BlogEngine.NET 2.0.0.36
Original Design by Laptop Geek, Adapted by onesoft, and finally some tiny tweaks by JonAlb