* 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 Video: Dynamic SQL Pro Tips – from my Mastering Query Tuning class, where I go into even more detailsĭynamic SQL - When, Why, and How to Use It.Dynamic Search Conditions in T-SQL by Erland Sommarskog.The Curse and Blessings of Dynamic SQL by Erland Sommarskog.4 SQL Injection Techniques for Stealing Data by Bert Wagner.You can use any size, small/medium/large – they’ll all work to show the problems.Īfter this session, your next steps for learning are: I’ll be using the Stack Overflow public database. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |