Why Functions Make Queries Slow
More From This Series
Going Further
For youWork With Your Databases the Way I DoVideo course, $250 until December 1, then $500For your teamAI for Database Teams WorkshopOne day, on-site or remoteOne-on-oneWork With Me for a Day$2,499
Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.
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.