Skip to main content

Return to Answer

removed all the maos
Source Link
siggemannen
siggemannen
  • 10.1k
  • 3
  • 12
  • 37

It is because of how SQL CLR scalar function run. When you call a CLR for each password, SQL Server has to go from SQL engine to CLR runtime "every single row". Microsoft actually explain this per-call overhead in their "Performance of CLR Integration Architecture" documentation. But like or small operations it doesn’t really matter, but when you are running 100k iteration per call it will add up. 

And worse news is SQL Server also disable multithreading when a scalar user-defined function is used. This User-defined functions doc explain more about that, they just force non-parallel query plans lmao. So it doesn’t matter how many cores you got, your PBKDF2 only run on one thread, but your .NET app will use all of your threads. Kinda makes sense, In the CLR Integration Overview docs they even warn against using it for CPU-heavy stuff inside SQL since it is not made for it (CLR is supposed to be lightweight). 

I think running PBKDF2 100k times definitely falls in that 'too heavy' category lmao. I suppose the best fix you can do is run your hash stuff outside of SQL, do them in your .NET app then just store the result in DB, since that’s what SQL is best at, a tool meant to interact with database. If you want those inside SQL, you should use a "table-valued function". Those only run once per set of rows instead of once per row, you should read more in the CLR performance docs. Good luck!

It is because of how SQL CLR scalar function run. When you call a CLR for each password, SQL Server has to go from SQL engine to CLR runtime "every single row". Microsoft actually explain this per-call overhead in their "Performance of CLR Integration Architecture" documentation. But like or small operations it doesn’t really matter, but when you are running 100k iteration per call it will add up. And worse news is SQL Server also disable multithreading when a scalar user-defined function is used. This User-defined functions doc explain more about that, they just force non-parallel query plans lmao. So it doesn’t matter how many cores you got, your PBKDF2 only run on one thread, but your .NET app will use all of your threads. Kinda makes sense, In the CLR Integration Overview docs they even warn against using it for CPU-heavy stuff inside SQL since it is not made for it (CLR is supposed to be lightweight). I think running PBKDF2 100k times definitely falls in that 'too heavy' category lmao. I suppose the best fix you can do is run your hash stuff outside of SQL, do them in your .NET app then just store the result in DB, since that’s what SQL is best at, a tool meant to interact with database. If you want those inside SQL, you should use a "table-valued function". Those only run once per set of rows instead of once per row, you should read more in the CLR performance docs. Good luck!

It is because of how SQL CLR scalar function run. When you call a CLR for each password, SQL Server has to go from SQL engine to CLR runtime "every single row". Microsoft actually explain this per-call overhead in their "Performance of CLR Integration Architecture" documentation. But like or small operations it doesn’t really matter, but when you are running 100k iteration per call it will add up. 

And worse news is SQL Server also disable multithreading when a scalar user-defined function is used. This User-defined functions doc explain more about that, they just force non-parallel query plans. So it doesn’t matter how many cores you got, your PBKDF2 only run on one thread, but your .NET app will use all of your threads. Kinda makes sense, In the CLR Integration Overview docs they even warn against using it for CPU-heavy stuff inside SQL since it is not made for it (CLR is supposed to be lightweight). 

I think running PBKDF2 100k times definitely falls in that 'too heavy' category. I suppose the best fix you can do is run your hash stuff outside of SQL, do them in your .NET app then just store the result in DB, since that’s what SQL is best at, a tool meant to interact with database. If you want those inside SQL, you should use a "table-valued function". Those only run once per set of rows instead of once per row, you should read more in the CLR performance docs. Good luck!

Source Link
Mykal Steele
Mykal Steele
  • 51
  • 1

It is because of how SQL CLR scalar function run. When you call a CLR for each password, SQL Server has to go from SQL engine to CLR runtime "every single row". Microsoft actually explain this per-call overhead in their "Performance of CLR Integration Architecture" documentation. But like or small operations it doesn’t really matter, but when you are running 100k iteration per call it will add up. And worse news is SQL Server also disable multithreading when a scalar user-defined function is used. This User-defined functions doc explain more about that, they just force non-parallel query plans lmao. So it doesn’t matter how many cores you got, your PBKDF2 only run on one thread, but your .NET app will use all of your threads. Kinda makes sense, In the CLR Integration Overview docs they even warn against using it for CPU-heavy stuff inside SQL since it is not made for it (CLR is supposed to be lightweight). I think running PBKDF2 100k times definitely falls in that 'too heavy' category lmao. I suppose the best fix you can do is run your hash stuff outside of SQL, do them in your .NET app then just store the result in DB, since that’s what SQL is best at, a tool meant to interact with database. If you want those inside SQL, you should use a "table-valued function". Those only run once per set of rows instead of once per row, you should read more in the CLR performance docs. Good luck!