Take any size of the Stack Overflow database and check out the WebsiteUrl column of the Users table:

Sometimes it’s null, sometimes it’s an empty string, sometimes it’s populated but the URL isn’t valid.

Let’s say that along the way, someone decided to ask ChatGPT to build a function to check for valid website URLs, and then used that code to add a new IsValidUrl column to the Users table (and yes, this is inspired by a real-life client example, hahaha):

CREATE OR ALTER FUNCTION dbo.IsValidUrl (@Url NVARCHAR(MAX))RETURNS BITASBEGIN DECLARE @Result BIT = 0 -- Regex pattern for a valid URL -- This pattern covers: -- - Scheme (http, https, ftp) -- - Optional username:password -- - Domain name or IP address -- - Optional port -- - Optional path -- - Optional query string -- - Optional fragment IF @Url LIKE 'http://%' OR @Url LIKE 'https://%' OR @Url LIKE 'ftp://%' BEGIN IF @Url LIKE '%://[A-Za-z0-9.-]%.%' -- Check for domain/IP after scheme AND @Url NOT LIKE '% %' -- No spaces allowed in URL AND @Url LIKE '%.[A-Za-z]%' -- Ensure there's a period in domain/IP part AND @Url LIKE '%/%' -- Ensure there's at least one slash after the domain AND @Url LIKE '%[A-Za-z0-9/\_-]%' -- Ensure there's at least one valid character in the path BEGIN SET @Result = 1 END END RETURN @ResultENDGOALTER TABLE dbo.Users ADD IsValidUrlAS dbo.IsValidUrl(WebsiteUrl); The user-defined function isn’t accurate, for starters – it’s letting things through that aren’t valid URLs, and stopping things that are actually valid – but let’s set that aside for a second.

What happens when we try to get the top users by reputation? To give SQL Server the best shot, I’m using SQL Server 2022, with the database in 2022 compatibility level, with an index on Reputation:

CREATE INDEX Reputation ON dbo.Users(Reputation);SET STATISTICS TIME, IO ON;GOSELECT TOP 200 *FROM dbo.UsersWHERE IsValidUrl = 1ORDER BY Reputation DESC; The actual query plan is deceivingly simple, despite its terrible performance that takes about a minute to run:

Crouching Tiger, Hidden ScalarWHERE IS YOUR FUNCTION INLINING GOD NOW? I could make movie jokes about this all day. Anyhoo, the plan ignored the Reputation index, did a 2-second table scan, and spent nearly a minute doing the scalar function and the filtering.

To add insult to injury, if you’re going to do 1 minute of CPU work, it sure would help to parallelize that query across multiple cores – but that query can’t get parallelism, as explained in the plan properties:

SpacesNotAvailableEitherYour Query Exercise this week isn’t to fix the accuracy of the function – you can leave it as inaccurate if you like. Your challenge is to have the exact same query run in less than a second. Our goal is to avoid changing application code, and to get a very fast fix in place without blaming the developers. You’re the data professional: be professional.

Put your queries in a Github Gist, and include that link in your comments. Check out the solutions from other folks, and compare and contrast your work. I’ll check back next week with my thoughts. Have fun!

Update: please read the post in its entirety, and follow the instructions. Please don’t just throw ideas in there or half-formed T-SQL. For someone to test your work, they need to see your exact work. C’mon, folks – this isn’t a major project, just a single function. Be fair to people on the other side of the screen.