SQL vs Code
-
"Can't you use C# in SQL Server now?" Sure you can. There's lots of ways to get around using native SQL (T-QSL for SQL Server or PL/SQL for Oracle) and let the procedural programmer avoid learning/using a set based langauge but they all have the same downside; they are procedural based solutions. So the answer to the second half of your question, "Wouldn't those C# optimizations work with the DB itself?" is 'Probably, but they would still not perform near as well as native SQL.'.
We use CLRs for statistics of warehouse orders and intern transactions. It has in any given day several thousands of rows in items, several hundred to thousands of documents and same number of various locations to track. And we have data collected over ten years. Normally it's few seconds for day or month analysis, but we had to make few yearly and total statistics ('trends') and 'same month over years' comparisons. Nothing beats CLR, but yes, it needs several tweaks on the database to enable it. (We tried CTEs, too slow; same with table variables and temporary tables and cursors)
-
We use CLRs for statistics of warehouse orders and intern transactions. It has in any given day several thousands of rows in items, several hundred to thousands of documents and same number of various locations to track. And we have data collected over ten years. Normally it's few seconds for day or month analysis, but we had to make few yearly and total statistics ('trends') and 'same month over years' comparisons. Nothing beats CLR, but yes, it needs several tweaks on the database to enable it. (We tried CTEs, too slow; same with table variables and temporary tables and cursors)
"(We tried CTEs, too slow; same with table variables and temporary tables and cursors)" Well that was your problem. Cursors are nothing but procedural methodology done within T-SQl so its no ownder you got bad performance. As far as CLR's the only scenario where CLR's will perform better then standard T-SQl that uses a proper set based (that means NO cursors) is where text manipulation is involed at some measurable level. For example if you want to find a key word within a very large amount of text then a CLR will do better. If however you are looking for aggregate values on some set of data then CLR's will not out perform properly written T-SQl code. I work with millions of rows on a regular basis and in a few queries, hundreads of millions of rows so I have some applicable experince with dealing with performance issues and there's no way I'd evcer use a cursor for any of the processes we do and I would only consider a CLR if the process was heavy in text manipulation. CLR's have tehir place and when used properly they're great. The problem is they are often used in the wrong scenario just as cursors are used when they should not be. This is because its much easier to use and understand a cursor in T-SQL when your background is in a procedural based language then to work out a pure set based solution (that means NO cursors). I totally understand why progarmmers opt to use cursors soo often because I've done procedural programming but that doesn't change the fact that a pure set base solution, except when heavy text manipulation is involved, will perfrom better %99 of the time if not higher.
-
"(We tried CTEs, too slow; same with table variables and temporary tables and cursors)" Well that was your problem. Cursors are nothing but procedural methodology done within T-SQl so its no ownder you got bad performance. As far as CLR's the only scenario where CLR's will perform better then standard T-SQl that uses a proper set based (that means NO cursors) is where text manipulation is involed at some measurable level. For example if you want to find a key word within a very large amount of text then a CLR will do better. If however you are looking for aggregate values on some set of data then CLR's will not out perform properly written T-SQl code. I work with millions of rows on a regular basis and in a few queries, hundreads of millions of rows so I have some applicable experince with dealing with performance issues and there's no way I'd evcer use a cursor for any of the processes we do and I would only consider a CLR if the process was heavy in text manipulation. CLR's have tehir place and when used properly they're great. The problem is they are often used in the wrong scenario just as cursors are used when they should not be. This is because its much easier to use and understand a cursor in T-SQL when your background is in a procedural based language then to work out a pure set based solution (that means NO cursors). I totally understand why progarmmers opt to use cursors soo often because I've done procedural programming but that doesn't change the fact that a pure set base solution, except when heavy text manipulation is involved, will perfrom better %99 of the time if not higher.
YSLGuru wrote:
Well that was your problem. Cursors are nothing but procedural methodology done within T-SQl so its no ownder you got bad performance.
Yes, we know, that's why we DO NOT do it unless it needs to be done. And our DBA has over 20 years of XP with relational databases and we take over only when he says he cannot do anything more to optimize query time. THEN we try CLR :) Most complicated thing I did was tariff calculation on goods transport that included dynamic items depending on distance, fuel consumption, client, special privileges etc. Initially done with CTE's it proved slow. Table variables filled independently for each step and combined near the end proved good solution, calculation time going from 20 seconds to .4 seconds