* Getting multi-parameter results to work right would require a lot of code * Multi-parameter results don't match what the users asked for * It's very susceptible to parameter sniffing The IF branch design has a few drawbacks: They can get optimized for NULL, which can give you really low estimates. Get used, even if some branches rely on parameters that didn't get passed in. Sniffing - the whole plan gets optimized for the first set of parameters that Sometimes - for some branches - but not always. Notice some of the query plans have yellow bang warnings on their sort operatorīecause SQL Server underestimated how many rows those queries would bring back.ĭoes it get better if you clear the cache, and try a different branch first? Plan for all 3 branches when we ran the very first query. In this case, all 3 of the branches were willing to use indexes - too willing -Īnd the estimates weren't right - because SQL Server built the entire execution * Is the optimizer willing to use indexes? When I'm looking at search code performance, I'll often ask: * If we pass in multiple fields, like this, * Will that work? Is there a bug in that logic? */ It's a bad way - it doesn't really work - but let'sĬREATE OR ALTER PROC NVARCHAR(100) = NVARCHAR(100) = INT = NULL AS They want a stored proc that works like this:ĮXEC usp_SearchUsers = 'Brent%', = 'Las Vegas%' ĮXEC usp_SearchUsers = 'Indiana%', = 107548 You should be able to pass in 1, 2, or all 3, and find just the users that We'll be searching by DisplayName, Location, and/or Reputation, so createĬREATE INDEX DisplayName ON dbo.Users(DisplayName) ĬREATE INDEX Location ON dbo.Users(Location) ĬREATE INDEX Reputation ON dbo.Users(Reputation) įolks want a single stored procedure that can search on any of these 3 fields. How big is the users table? Look at how many rows are returned from this query,Īnd on the Messages tab, check the STATISTICS IO results to see how many * Turn on our tuning options and Actual Execution Plans: */ Make sure we don't have extra nonclustered indexes that would interfere with Run these demos one at a time.', 20, 1) WITH LOG RAISERROR(N'Oops! No, don''t just hit F5. This first RAISERROR is just to make sure you don't accidentally hit F5 and * Any supported SQL Server or Azure SQL DB Open source with the Creative Commons License. In one all-demo hour, you'll learn how to write dynamic SQL that's easy to So how do we make those go fast? And how can we get 'em to use indexes? Order, etc, and you have to accept any of 'em. Stored proc with parameters for product category, name, price range, sort For example, you have that "product search" You're comfortable writing T-SQL, and you've built a lot of stored procedures
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |