I have a file which contains T-SQL code used to upgrade outdated database to most recent structure. It will check if any "object" in my database is out-of-date and update it.

This file has been maintained for more than a year. Currently, the file size is about 205K bytes with 251 blocks of code (separated by 'GO' command which required by Query Analyzer).

Recently, I wrote a small program which read the stored T-SQL code from file, remove all occurrence of 'GO' command ('GO' is used by query analyzer but not SQL Server) and send them to SQL Server. SQL Server took at least 5 minutes to execute my T-SQL even though the database is already up-to-date. It is same if I run my T-SQL from query analyzer.

One day, my consultants complaint about the upgrade took too long to complete.

Well, fine. I decided to refactor my program. This time, I don't pass everything from file to SQL Server at the same time. Instead, I split the code out using ALINE() function with 'GO' as delimited. Then, send ONE block of code at a time to SQL Server by looping thru the array. Hey, it only took 20-30 seconds to complete!!! It is unbelievable. I was thinking I made some mistakes in my program but found that it is working correctly after some testing.

I suppose, SQL Server tried very hard to parsing/optimize/compile big chunk of T-SQL code cause performance degraded. Compile small piece of code may be more straight forward and not "complicated" SQL Server "brain".

 

Technorati tags: ,