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. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment 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.