Do you think SET based?
I have come across a lot of developers who are really good at C# programming who end up writing amateur stored procedures contrary to expectations.
When writing a query involving table joins, from that moment on start visualizing how the data in tables start merging together and spread out to form a large living creature. Imagine the data rows hand-holding each other, bridging tables, the null rows born out of left joins, the expected and unexpected duplication, if any! There should be a meaning to this final object that gets created in memory which gives sense to its existence.
When picturing this formation, just spend a couple of minutes to analyse how fast the tables will attract the correct rows to each other. For example, if there is no natural clustered index or man-made indexes on the join condition, the participating rows would frantically run to find their beloved! Technically this would be called “a scan”, and in Bollywood it is a 3-hour movie. Do we want that kind of performance? No! So pick the right columns to join or add the right indexes. Selecting the “right” index demands a story of its own.
We need to think whether this huge object in memory is really worth its size. Just imagine an obese guy competing a lean and healthy chap to the door. Who would get out, first? To see this in SQL Server’s own eyes, take the estimation plan for a slow running query. Taking this cue, break up a SELECT statement if it contains a lot of joins. Think about the size of the individual tables involved too. Create JOINS so that the join itself act as a filter and only the necessary rows from the larger tables participate in the join.
Avoid usage of functions unless it’s existence really makes sense. Imagine the function being called the number of times, the rows in the table are repeated. Scary isn’t it? This could dangerously slow down your procedure’s execution time if the function itself is made up of a few table joins inside it. And such a function may look so innocently deceiving from outside like dbo.WhyWouldYouThinkIBurnYourCPUorIO(int yourSweetheartId)
Once you have a picture of this in your mind, think how the information you initially sought for can be extracted. Is it – a Group by? a Distinct? a Windowing function like Rank Over Partition? OR just a Where clause in the end?
Having said that, the secret to write the best possible query is to understand the individual tables in hand itself. You should KNOW THE DATA so as to CREATE INFORMATION out of it.
Inspire “SET based” thinking to write the most efficient and correct query. Stop thinking one row at a time. Viola! Welcome to SQL Server Engine (Heartbeat)