Mark Breen
marklbreen at gmail.com
Sun Sep 9 03:14:23 CDT 2012
Hi Francisco, Thank you for your detailed reply. Of course, you are right, most answers on this topic have the caveat "ymmv". I have a few (5/6/7) applications that interface with SAP. Most of the time, I import a few thousand records into SQL server and once they are inside SQL Server, I hang up the phone and come back an hour later. In the case of these EXE's, I never contemplated using the CLR, but based on your reply, it would bring a marginal difference. However we also sometime retrieve one record and in that case, the description you gave of a function would be fantastic. It makes the SAP environment very close to the SQL Server. SAP becomes invisible to the SQL Server user. I have never played with embedding C# in SQL but this is a tempting option. Thanks again. I will be curious to see Mr Colbys results with performance. Mark On 7 September 2012 16:41, Francisco Tapia <fhtapia at gmail.com> wrote: > Hey Mark, > This is just another "ymmv" type answers. You *could* write a solution > in many different ways to get data into your sql system. The inline CLR > function happened to be a function that made the most sense to reduce the > interaction with the database. Over the years I've implemented various > solutions, but I like the CLR solution for these situations where I need > some data invoked as a "temp" table sort of way... Such as when I need a > combination of data from SAP I can get that data right from the database > because in this manner, it serves as the business layer as well. > > it makes it possible to serve data up to a client application for my > internal users, and also present data to our webusers all from the same > source code. If I get it wrong, I get it wrong everywhere :) > > in this example the function returns a table of codes for a given > machineid, > SELECT * FROM dbo.fnGetCodes(machineID) (the system has to go out to a dll > and also goes back to SAP and gets additional data points). > > I think to answer your question, in my experience using the CLR code in > your SQL server can allow you to achieve all of the choices you wrote. > 1) If you write you external application in Visual Studio (c#,vb.net) you > don't really gain any speed, in fact you get the added overhead of > initiation connections to the database. To truly gain significant speeds > you'd want to write your external application in something like pure C, C++ > 2) this is only technically true because your connection links etc, are > wrapped up inside the DLL you write for your Sql CLR. > 3) definitely simplified business logic, where you can centralize your > business rules, now your html5 webapp / android/ iOS application simply > needs to consume a service that does the heavy lifting for you, and the > webservice can be written as lightweight as possible to do as little > processing as needed to passthrough your requests. > 4) given, if you write it once... :) > > > > > -Francisco > -------------------------- > You should follow me on twitter here <http://twitter.com/seecoolguy> > Blogs: SqlThis! <http://bit.ly/sqlthis> | XCodeThis!< > http://bit.ly/xcodethis> > > <http://db.tt/JeXURAx> > > > > > On Thu, Sep 6, 2012 at 12:59 AM, Mark Breen <marklbreen at gmail.com> wrote: > > > Hello Francisco > > > > Is it better to do that than to simple have an external Exe running? > > > > I mean are the benefits > > > > 1) Higher Performance > > 2) More Secure > > 3) Less distributed code infrastructure to manage > > 4) Less code required > > > > If there are other benefits, please let us know > > If you think that some of these are not true, please (if you have time > only > > of course), please let me know eg, 1 and 2 are n/a > > > > Do you have a list of disadvantages > > > > thanks > > > > Mark > > > > > > > > On 5 September 2012 15:48, Francisco Tapia <fhtapia at gmail.com> wrote: > > > > > We use it here to get and submit data to our SAP Webservices > > > > > > > > > -Francisco > > > -------------------------- > > > You should follow me on twitter here <http://twitter.com/seecoolguy> > > > Blogs: SqlThis! <http://bit.ly/sqlthis> | XCodeThis!< > > > http://bit.ly/xcodethis> > > > > > > <http://db.tt/JeXURAx> > > > > > > > > > > > > > > > On Sat, Sep 1, 2012 at 9:57 PM, jwcolby <jwcolby at colbyconsulting.com> > > > wrote: > > > > > > > Just a poll to see if anyone on the list is using the CLR in SQL > > Server. > > > > I do a lot of C# work now and mostly to automate SQL Server. I have > > not > > > > been using the CLR in SQL Server but I am ready to figure it out. > > > > > > > > Anyone? > > > > > > > > -- > > > > John W. Colby > > > > Colby Consulting > > > > > > > > Reality is what refuses to go away > > > > when you do not believe in it > > > > > > > > ______________________________**_________________ > > > > dba-SQLServer mailing list > > > > dba-SQLServer@**databaseadvisors.com < > > dba-SQLServer at databaseadvisors.com > > > > > > > > http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver< > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> > > > > http://www.databaseadvisors.**com <http://www.databaseadvisors.com> > > > > > > > > > > > _______________________________________________ > > > dba-SQLServer mailing list > > > dba-SQLServer at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > > http://www.databaseadvisors.com > > > > > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >