asked
SQL query optimization
hi experts .My test server query is taking more than 2 minutes to run . DECLARE @Date varchar(10) = '3/16/2017'; DECLARE @Client varchar(50) = 'zip'; SELECT count AS 'SSN Checks' FROM vwEvent WHERE EventType = 'SigningPing' AND EventDate = @Date AND EventParam4 = @Client AND EventParam2 like '%/NameSSN/%' does the query need to change something to get minimum downtime ? in test server it takes 2 minutes i am concern about production .Production is busy and has millions of rows in tables . any good thoughts would help
What's the definition of vwEvent? I'm assuming it's a view, from the name. Without that definition, there's not much we can recommend beyond: 1. Types. Are your date fields (EventDate) really held as varchar(10)? If not, then change the @Date parameter to be of datatype DATE - that'll remove an implicit conversion 2. `EventParam2 LIKE '%/NameSSN/%'` is going to be woefully slow, even if you have got an index on whatever's underlying that in the view, as it will be scanning the index rather than seeking out values.
17 People are following this question.
