SQL Server: Performance implementing Dynamic SQL in Stored Procedures with Large Datasets
Can someone help me understand I tried several approaches but none seem to work. Hey everyone, I'm running into an issue that's driving me crazy. I'm working with important performance optimization when executing a stored procedure that constructs dynamic SQL queries based on user input. The stored procedure is intended to filter records from a large dataset (around 3 million rows) based on various parameters. However, I'm noticing that the execution time increases drastically as the dataset grows, especially when using joins with multiple tables. Here's a simplified version of my stored procedure: ```sql CREATE PROCEDURE GetFilteredData @Filter NVARCHAR(50) AS BEGIN DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'SELECT * FROM LargeTable lt JOIN OtherTable ot ON lt.Id = ot.LargeTableId WHERE lt.Name LIKE ''%' + @Filter + '%''' EXEC sp_executesql @SQL END ``` When I run this procedure with a filter, it takes several minutes to return results, and I'm getting timeout errors when the dataset is large. I tried adding indexes on the `Name` column of `LargeTable` and the `LargeTableId` column of `OtherTable`, but it didn't seem to help much. Additionally, I noticed that if I run a similar query directly in SQL without using dynamic SQL, it performs much better: ```sql SELECT * FROM LargeTable lt JOIN OtherTable ot ON lt.Id = ot.LargeTableId WHERE lt.Name LIKE '%searchTerm%' ``` I've also experimented with using a temporary table to store the results of the JOIN first, but that didn't yield important improvements either. I am unsure how to optimize this dynamic SQL approach. Any suggestions or best practices for handling dynamic SQL efficiently in SQL Server? I'm using SQL Server 2019. Thanks! For context: I'm using Sql on Ubuntu. What am I doing wrong? This is part of a larger application I'm building. What's the best practice here? What's the best practice here? The project is a mobile app built with Sql. What's the best practice here?