Why Functions Make Queries Slow
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
WOW. My takeaway from this is that table variables are slow and temp tables are the way to go if you need to. I switched out a query that was running insanely slow and had table variables to temp tables. I am embarrassed to say that the previous run time was exceeding 5 hours, and it wasn’t a whole lot of data. It was previously 2 hours, but my boss had me expand the scope and it just took forever. I wasn’t aware my stupid table variables were the cause; it is doing quite a lot of case statements and mathematical equations, but nothing that’s 5 hours of craziness.
I switched my table definitions to temp tables instead and holy bananas, 18 seconds! It’s so insane. I can’t believe I was doing that so bad for so long. I thought the table variables were making things cleaner. Most scripts were a tolerable few minutes. Off to get rid of table variables in all of my scripts.
Better late than never! Glad you got something useful out of the video.