Use of external dll in T-SQL

Hi All,

I need some functionality that does some statistical analyis on large series of data. I can write some complex queries for this using SQL that will look like APL in a few months from now or i use an external library of functions in a dll that's already pan made and tested.

I've read > http://www.sqlservercentral.com/columnists/dasanka/callingcomfromtsql.asp

And it looks good, now i need to convince some others that reinventing the wheel in SQL ain't very smart businesswise and for maintenance in the future

What are the pitfalls of using a COM[2000] or Assembly[2005] in T-SQL?

Thanks for any comments and points will be spread among participants
Brian
LVL 44
bruintjeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ptjcbCommented:
It does matter if you are running 32bit or 64bit versions of SQL Server. For this I will assume that you are running 32bit.

COM issues -

When SQL Sever 2000 (2005 does this differently) memory is broken into 2 parts - BPool, which is used for internal processes and MemToLeave, which is used for external processes (including COM and assemblies). First, the size for 2000's MemtoLeave is usually only about 384MB. That size can be increased if you use the startup -g parameter.

The BPool size is dynamic, but MemToLeave is static. It is typically used for processes that are over 8K in size along with external process. A runaway process can compete with other process for the limited memory space.

If someone has created a extended process that leaks memory there is nothing in SQL Server to stop it. You have to be careful that all resources are used and released quickly. Memory allocations within MemToLeave are not managed by the database engine.

Assemblies

Assemblies call SQL Server for allocating/deallocating memory. This allows SQL Server to better manage its memory and ensure that the assembly and SQL Server are not competing. SQL Server will also, in theory, reject requests if memory is an issue.

bruintjeAuthor Commented:
thanks for your reply, that was a more memory related which i found very interesting reading through
http://sqljunkies.com/Tutorial/0D4FF40A-695C-4327-A41B-F9F2FE2D58F6.scuk

my main argument is maintenance and the time to production of the solution, writing error free SQL for this will take a week and some debugging, using a dll is one day work and one for debugging

at this point in time the dll is only running after business hours [night] producing tables of statistics so the server will be relatively quiet at that time

are there any other things to look out for?
ptjcbCommented:
>>And it looks good, now i need to convince some others that reinventing the wheel in SQL ain't very smart businesswise and for maintenance in the future

Isn't that what we do every day? Granted change can be hard for some people and I know I have seen some remarkable contortions when people want a language designed for data access in a specific kind of database to perform operating system functions. Of course that means that you have to grant access to users to work outside of the SQL sand box. Giving anyone permission to something like xp_cmdshell just because they want to delete or move a file should give a dba an anxiety attack. That is just trouble and that opens a huge door in security.

>>What are the pitfalls of using a COM[2000] or Assembly[2005] in T-SQL?

Laughing - well I conentrated on the memory part because I have had badly written code bring a server to its knees. I have had clients who were in love with extended procedures and created them to solve every problem. "When you have a hammer, every problem is a nail."

>>my main argument is maintenance and the time to production of the solution, writing error free SQL for this will take a week and some debugging, using a dll is one day work and one for debugging

It is also that there are times that SQL is just not the best language. When whatever you are doing something that requires row-by-row processing, for example, then using an assembly may be a better choice. It is just a tool. There are scenarios where T-sql will always outperform an assembly. There are times when an assembly will be easier to write and outperform a t-sql option.

Time to create the solution, maintenance are all good reasons but the big key will be performance. Management will always choose the solution that runs the fastest if given a choice. If you can prove that your solution will outperform a similar t-sql solution then there is no reason to use t-sql.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bruintjeAuthor Commented:
smells like work and i'm very lazy :-) but this would be worth the trouble and spending my 2 upcoming holidays on.

thanks for your insights! especially about proving your point to the decision makers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.