?
Solved

Use of external dll in T-SQL

Posted on 2006-04-24
4
Medium Priority
?
936 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:bruintje
  • 2
  • 2
4 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 16528586
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.

0
 
LVL 44

Author Comment

by:bruintje
ID: 16529542
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?
0
 
LVL 27

Accepted Solution

by:
ptjcb earned 2000 total points
ID: 16534185
>>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.


0
 
LVL 44

Author Comment

by:bruintje
ID: 16534259
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question