none
CLR stored procedure execution very very slow RRS feed

  • Question

  • Hi, 

    I have tried searching through other posts and unable to resolve my issue. I have been trying to use CLR stored procedure in my project.

    My SQL server database has Linked server definitions to external Oracle database. I used open query command and sqldataadapter in my stored procedure to retrieve the data. After that, I used sqldataadapter.fill(datatable) to store the data in Data tables. Then, I wrote csharp code to loop through the DataTables, perform some calculations and store those values in variables and use INSERT INTO to store the values in my DAtabase table.

    Functionally, it all works great. But, it takes more than 10 minutes!! to execute this stored procedure on Database.  I have read in posts that CLR is slow, but is it this slow? This stored procedure will only insert about 18000 rows. Anybody has any suggestions on why it is so slow, and if anything I overlooked in my above process?

    Thanks

    Saturday, November 24, 2012 12:16 AM

Answers

  • Looking to your implementation I believe you can do the same thing using T-SQL and I strongly urge to use T-SQL as Transact-SQL (T-SQL) is the native programming language and there are a number of significant enhancements to the T-SQL query language in SQL Server 2005/2008 that augment the expressive power of the T-SQL query language, and it should ensure that they are taking full advantage of them before writing CLR.

    To answer your question yes CLR integration is bit slower than TSQL except some exceptional case (Refer this). The reason because of CLR stuff include much complicated compilation process (Refer this).

    As multiple process are involved in your implementation like extraction – Transformation and Load/Update I would request you to first identify which portion of code is taking longer time than optimize it.

    If you think CLR stuff is taking longer time then you can post your code here so that some one can help you to optimize it.  You can look for opening a Context Connection on CLR rather than Regular Sql Connection Refer :Regular vs. Context Connections


    Lingaraj Mishra


    Sunday, November 25, 2012 4:59 AM