The World Beyond the Maps

At my prior place of employment, I largely worked in an ORM (Object-relational mapping) environment. At my current place of employment, I am learning to get really dirty with MSSQL Management Studio Express. I have rediscovered Stored Procedures and moreover am learning of Functions and Views. With regards to functions, there was a Stored Procedure that called a function like so:

...some code...


SELECT


...some code...


isnull(b.thingamabob,'test') as Thingy,


...some code...


FROM


...some code...


OUTER APPLY dbo.somethingerother(a.id) b


...some code...

 
The function itself looks like:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[somethingerother]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))


DROP FUNCTION [dbo].[somethingerother]


GO


CREATE function [dbo].[somethingerother] (@theID int)


RETURNS


@WhateverTable table


(


thingamabob varchar(100)


)


AS


BEGIN


declare @whatever varchar(100)


set @whatever = 'test'


if exists(select * from [OrderExpansion] where orderid = @theID and somethingelse <> 33)


begin


...some code...


end


insert into @WhateverTable (thingamabob)


Values (@whatever)


Return


end


GO

 
It was pointed out to me that if the stored procedure returns 300 rows that it will run 301 queries as the function independently calls a separate query each time it is fired. I am learning a lot about optimization here. This is a better approach:

...some code...


SELECT


...some code...


ISNULL((SELECT TOP 1


FROM dbo.[order] x


WHERE x.orderid = a.id AND x.somethingelse <> 33),'test') as Thingy,


...some code...


FROM


...some code...

There are two approaches to data mapping:

1. automation
2. trust only yourself

My supervisor prefers the later distancing himself from the subselects of ORM in favor of simple joins and other performance optimizations. I am learning.