Does it make sense to use CLR Stored Procedures
-
I'm playing with the idea to build a sample, on how to integrate the Time Period Library for .NET into the SQL Server. So far I have build a demo library/assembly/procedure and does some time period calculations using the AdventureWorks catalog ... and it works :). Because I have little experience using CLR SP, here my questions: - Makes this integration sense to reduce the complexity of T-SQL programming? - What's the performance difference between CLR SP and T-SQL?
Jani Giannoudis
Latest article: Time Period Library for .NET
-
I'm playing with the idea to build a sample, on how to integrate the Time Period Library for .NET into the SQL Server. So far I have build a demo library/assembly/procedure and does some time period calculations using the AdventureWorks catalog ... and it works :). Because I have little experience using CLR SP, here my questions: - Makes this integration sense to reduce the complexity of T-SQL programming? - What's the performance difference between CLR SP and T-SQL?
Jani Giannoudis
Latest article: Time Period Library for .NET
From what I have read, the CLR SP seems to actually return data quicker than T-SQL but that it becomes much harder to maintain your application. Before going this route you need to determine which is more important, speedy data return, or application maintenance.
-
I'm playing with the idea to build a sample, on how to integrate the Time Period Library for .NET into the SQL Server. So far I have build a demo library/assembly/procedure and does some time period calculations using the AdventureWorks catalog ... and it works :). Because I have little experience using CLR SP, here my questions: - Makes this integration sense to reduce the complexity of T-SQL programming? - What's the performance difference between CLR SP and T-SQL?
Jani Giannoudis
Latest article: Time Period Library for .NET
It certainly can, but have you considered user-defined functions?
-
It certainly can, but have you considered user-defined functions?
Implicitly, yes. The question is if it makes sense to provide a library which supports time operations for database tables. Something like:
Overlaps( startDateColumnName, endDateColumnName, testPeriodStartDate, testPeriodEndDate );usage:SELECT * FROM Bookings WHERE Overlaps( 'Start', 'End', '20110101', '20110331' )Another sample may be:GetGaps( tableOrViewName, startDateColumnName, endDateColumnName, searchStartDate, searchEndDate );usage:EXEC GetCaps( 'Bookings', 'Start', 'End', '20110101', '20110331' ) -- return all gapsJani Giannoudis
Latest article: Time Period Library for .NET
-
Implicitly, yes. The question is if it makes sense to provide a library which supports time operations for database tables. Something like:
Overlaps( startDateColumnName, endDateColumnName, testPeriodStartDate, testPeriodEndDate );usage:SELECT * FROM Bookings WHERE Overlaps( 'Start', 'End', '20110101', '20110331' )Another sample may be:GetGaps( tableOrViewName, startDateColumnName, endDateColumnName, searchStartDate, searchEndDate );usage:EXEC GetCaps( 'Bookings', 'Start', 'End', '20110101', '20110331' ) -- return all gapsJani Giannoudis
Latest article: Time Period Library for .NET
If you use them a lot then sure. On my last job I made a database that contained only user-defined functions that I then used in many queries from many databases. User-defined table-valued functions combine the best of views and stored procedures -- they're like views that can take parameters.
-
If you use them a lot then sure. On my last job I made a database that contained only user-defined functions that I then used in many queries from many databases. User-defined table-valued functions combine the best of views and stored procedures -- they're like views that can take parameters.
The idea is to provide a generic library with predefined SPs and UDFs that can be used for complex time period calculations (inside, overlap, gap, intersection...). For example:
EXEC TimePeriodInfo
@table = 'HumanResources.vEmployeeDepartmentHistory',
@startDateField = 'StartDate',
@endDateField = 'EndDate'returns (only fake sample):
periods:
05.01.1998 - 30.06.2000 | 907.00:00
20.01.1998 - 15.08.1999 | 572.00:00
08.02.1999 - 01.10.2001 | 966.00:00
04.03.1999 - 15.12.2003 | 1747.00:00
28.03.2001 - 30.08.2001 | 155.00:00
31.08.2001 - 15.08.2002 | 349.00:00
intersected periods:
20.01.1998 - 15.08.2002 | 1668.00:00
combined periods:
05.01.1998 - 15.12.2003 | 2170.00:00Jani Giannoudis
Latest article: Time Period Library for .NET
-
I'm playing with the idea to build a sample, on how to integrate the Time Period Library for .NET into the SQL Server. So far I have build a demo library/assembly/procedure and does some time period calculations using the AdventureWorks catalog ... and it works :). Because I have little experience using CLR SP, here my questions: - Makes this integration sense to reduce the complexity of T-SQL programming? - What's the performance difference between CLR SP and T-SQL?
Jani Giannoudis
Latest article: Time Period Library for .NET
Hi, CLR procedures are handy and the programmability is much wider than using plain T-SQL. However, depending on the specs it may/may not be wise to use them. Few thoughts: - if you're working with sets (possibly with large amount of rows), building a procedure or function that works row-based may cause intolerable response times. SQL is very good in set based operations (it's designed for that :)). - if you need calculations, complex logic etc then the programmability of .Net will offer much more possibilities over SQL and it may be a better option. Based on the examples you've written, they may be fairly easy to do with SQL using proper conditions etc and in that case the performance would be far better. But as they were only examples I'm not able to say if that's really the situation. A good rule of thumb could be that there's usually no need to replace such functionality with CLR operations that already exists in SQL itself unless there's some other benefits and not too much downsides. :) Best regards, mika
The need to optimize rises from a bad design.My articles[^]