Forum Discussion
Post ProdigyTransforming an old T-SQL stored procedure with cursors in a performant notebook
Hi,
in an old SQL Server 2022 data solution I've a T-SQL stored procedure containing two cursors, the outer one iterates on some thousands of rows while the inner one iterates on about 10 rows.
I need to evaluate the possibility of transforming this stored procedure in a python notebook, maintaining the loop logics, so to save the effort while the performances are a requirement very important.
I'd like to avoid to transform the cursor logics in set-based operations because this option would require a significant effort and the outcome would be uncertain. Obviously, the T-SQL cursors aren't more performant.
Does anyone have any experiences with a such issue to give me a suggest? Thanks
pmscorca Few suggetions!!
Rewriting the cursor-based T-SQL procedure directly into a Python notebook while preserving the loop logic is unlikely to improve performance. Python introduces additional overhead, especially if database interactions occur inside loops. A more effective approach would be to either partially refactor the cursor logic into set-based SQL operations or adopt a hybrid model where data is processed in bulk in-memory within Python, avoiding iterative database calls.
Note- AI helped me to format this content
2 Replies
- anilgavhane
Super User
pmscorca Few suggetions!!
Rewriting the cursor-based T-SQL procedure directly into a Python notebook while preserving the loop logic is unlikely to improve performance. Python introduces additional overhead, especially if database interactions occur inside loops. A more effective approach would be to either partially refactor the cursor logic into set-based SQL operations or adopt a hybrid model where data is processed in bulk in-memory within Python, avoiding iterative database calls.
Note- AI helped me to format this content
pmscorcaHi,
It seems strange that the best solution could be to rewrite the cursors using set-based queries without taking into account that the logic could have been written from the beginning in a code other than T-SQL by implementing loops.