June 1, 2009 at 9:43 am
TheSQLGuru (6/1/2009)
Believe it or not Steve I have had not one but TWO hiring managers tell me in the last few months that my rate was significantly LESS than what they expected!
Very good. Glad to hear things are going well, and we'd love to know more about when you pull the "dynamic SQL" tool out.
June 1, 2009 at 9:45 am
The query looks good and I guess was quite popular few years back. However, this is not helpful, then you are writing complex queries or where the data volume is higher as it will reduce performance significantly.
Soumyajit Halder
http://www.itpave.com
June 1, 2009 at 9:49 am
TheSQLGuru (6/1/2009)
The part about doing index seek/bookmark lookup when you should be doing a table scan (or vice-versa) is due to parameter sniffing and plan caching.
I must admit, I've never seen a query of the form that we're discussing (Col1=@Var1 OR @Var1 IS NULL) do an index seek. It's always (in my experience) index scans, clustered index scans or table scans. Can it do a seek seeing that the seek predicate changes as the parameters change?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2009 at 10:17 am
Has anyone ever used dynamically generated temporary stored procs to solve this type of issue?:-D
June 1, 2009 at 10:32 am
I always make use of a COALESCE() instead of the OR in cases where there's an "=" -- (not in this particular case with Age > @Age). I don't really know how this affects performance, but definitely reads better.
SELECT Employee_Name, Gender, Age FROM EmployeeDetails
WHERE Gender = COALESCE(@Gender, Gender)
AND ( @Age IS NULL OR Age > @Age)
Also, can be used in a dynamic UPDATE:
UPDATE EmployeeDetails
SET Gender = COALESCE(@Gender, Gender)
,Age = COALESCE(@Age, Age)
WHERE Employee_Name = @Employee_Name
June 1, 2009 at 12:07 pm
TheSQLGuru (6/1/2009)
1) if you don't have indexes on fields that you are searching for then you have bigger problems than writing bad code.2) If you are searching for non-specific fields (without other more restrictive filters) likewise
And this proves that you missed the point I was trying to make. I specifically said in an on-line transaction processing system, you won't have many columns indexed. Putting too many indexes on an OLTP table will slow INSERT, UPDATE, and DELETE performace. That's a measured factual statement.
This doesn't mean that there aren't any indexes, nor does it mean that the searches are on all non-specific fields. If you have a table with a status column for example, there may only be 4 or 5 statuses, and most of the records may be in the "active" status. If the application lets the user search by status as one of the criteria, having an index on status will not help the query run faster regardless of how the query is written because of the low selectivity. If you force it to use the index in a query, then you could actually be hurting performance.
June 1, 2009 at 12:19 pm
Dave Ballantyne (6/1/2009)
http://www.sommarskog.se/dyn-search-2005.html discusses this and many other similar methods
+1 Erland has covered this topic better than anyone else.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
June 1, 2009 at 12:32 pm
Chris Harshman (6/1/2009)
TheSQLGuru (6/1/2009)
1) if you don't have indexes on fields that you are searching for then you have bigger problems than writing bad code.2) If you are searching for non-specific fields (without other more restrictive filters) likewise
And this proves that you missed the point I was trying to make. I specifically said in an on-line transaction processing system, you won't have many columns indexed. Putting too many indexes on an OLTP table will slow INSERT, UPDATE, and DELETE performace. That's a measured factual statement.
This doesn't mean that there aren't any indexes, nor does it mean that the searches are on all non-specific fields. If you have a table with a status column for example, there may only be 4 or 5 statuses, and most of the records may be in the "active" status. If the application lets the user search by status as one of the criteria, having an index on status will not help the query run faster regardless of how the query is written because of the low selectivity. If you force it to use the index in a query, then you could actually be hurting performance.
I did not miss your point. "too many" is such a subjective value. In my experience it takes a LOT of indexes (10+ at least, usually 20+ or more) to significantly affect perf/concurrency worse than the same perf/concurrency issues caused by not having said index(es). I have not seen overhead be what you seem to be seeing - quite possible given different schemas, data volumes, usage patterns, etc.
Having an index on a status column can be very BENEFICIAL precisely BECAUSE the vast majority of the rows are a single value. That allows non-parameter-sniffed-cached-plan hits on that table that look for the INFREQUENT value(s) (such as what I see most INactive or NOTprocessed) use that index for a seek/lookup and get the rows that need to be worked very efficiently.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2009 at 1:13 pm
Gail makes an excellent point about test VS. production tables. I have a 14.5 million row table and revamping SPROC this way took 6 seconds VS. unclocked for my CASE statements.
Basically, I build a little logic tree:
SELECT @param = CASE
WHEN (@param1 IS NULL) AND (@param2 IS NULL) AND (@param3 IS NULL) THEN '000'
WHEN (@param1 IS NULL) AND (@param2 IS NULL) AND (@param3 IS NOT NULL) THEN '001'
WHEN (@param1 IS NULL) AND (@param2 IS NOT NULL) AND (@param3 IS NULL) THEN '010'
... (remainer of tests)...END
SELECT @WHERE = CASE
WHEN @param = '000' THEN 'No Criteria'
June 1, 2009 at 1:45 pm
Gail makes an excellent point about test VS. production tables. I have a 14.5 million row table and revamping SPROC this way took 6 seconds VS. unclocked for my CASE statements.
Basically, I build a little logic tree:
SELECT @param = CASE
WHEN (@param1 IS NULL) AND (@param2 IS NULL) AND (@param3 IS NULL) THEN '000'
WHEN (@param1 IS NULL) AND (@param2 IS NULL) AND (@param3 IS NOT NULL) THEN '001'
WHEN (@param1 IS NULL) AND (@param2 IS NOT NULL) AND (@param3 IS NULL) THEN '010'
... (remainer of tests)...END
SELECT @WHERE = CASE
WHEN @param = '000' THEN 'No Criteria'
WHEN @param = '001' THEN '(field1='''+ @param1 +''')'
WHEN @param = '010' THEN '(field2='''+ @param2 +''')'
WHEN @param = '011' THEN '(field1='''+ @param1 +''') AND (field2='''+ @param2 +''')'
... END
I then determine if @WHERE is "No Criteria" or has something and wrap the @WHERE with "WHERE( ) ORDER BY field1"
Lastly I put together the final SQL: SELECT @SQL = @SQL + @WHERE (@SQL any valid SQL clause)
EXEC (@SQL)
I realize this use of concatenation is largely frowned upon, but this is not an external (public) database and the SPROC is permissioned by schema and the users have no clue as to the structure of the underlying database (for which they do not have permissions). The advantage, from a programming standpoint is that it is very logical to build the progression of possible parameter variations (done largely with cut/paste) and to make sure you've covered all the variations. As I mentioned at the beginning, the table being searched is 14.5 million rows and for something like a customer ID search it doesn't even clock (less than a second). Additionally, the parameters are typically passed via a LINKED FIELD within a SSRS report, so the users really can't monkey with them at all.
June 1, 2009 at 1:57 pm
Steve Jones - Editor (6/1/2009)
TheSQLGuru (6/1/2009)
Believe it or not Steve I have had not one but TWO hiring managers tell me in the last few months that my rate was significantly LESS than what they expected!Very good. Glad to hear things are going well, and we'd love to know more about when you pull the "dynamic SQL" tool out.
I try to use IF statement conditionals for 'few' permutation situations, such as perhaps 6 different permutations. Past that it gets very cumbersome to handle all the logic.
NOTE: quickie code - may not be syntactically correct!! 🙂
Also, if there are no fields returned from one or more tables AND your where clause doesn't hit those tables either, you may be able to leave them out of the query altogether
Also, as a very minor point, if you know in advance you can code the IFs to be in descending order of hit probability
create proc a (@a int=null, @b-2 int=null)
as
set nocount on
if @a is null and @b-2 is null
begin
select somefields
from table1 t1 inner join table2 t2 on t1.id = t2.id
--NO where clause
end
else if @a is not null and @b-2 is null
begin
select somefields
from table1 t1 inner join table2 t2 on t1.id = t2.id
where field1 = @a
end
else if @a is null and @b-2 is not null
begin
select somefields
from table1 t1 inner join table2 t2 on t1.id = t2.id
where field2 = @b-2
end
else if @a is not null and @b-2 is not null
begin
select somefields
from table1 t1 inner join table2 t2 on t1.id = t2.id
where field2 = @b-2
and field1 = @a
end
Beyond that (or maybe one more level) I will build out dynamic sql that has explicit values, tables, fields, where clause, etc.
Note: I will also use dynamic sql (or possibly option recompile on the IF statements above) if there are parameter-sniffing issues with input values due to non-uniform data distribution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2009 at 5:01 pm
I just want to make a note, that if you choose the "OR statement" route with the null conditionals, be sure to do the following:
1. Make sure you hit the index, before testing @x is null. this will avoid a full table scan as the first step. I do not know the specifics in why, but below is the appropriate syntax...
WHERE T1.x = @x OR @x IS NULL
2. Avoid using functions, such as ISNULL or COALESCE. This will cause a full table scan, because SQL cannot use the index with a fielf wrapped in a function.
3. Use the order of the joins and the order of your indexes in your WHERE clause...
WHERE (T1.x = @x OR @x IS NULL) AND (T1.y = @y OR @y IS NULL) AND (T2.z = @z OR @z IS NULL)
June 1, 2009 at 6:58 pm
use adventureworks
go
drop proc test
go
create proc test (@productid int = null, @quantity int = null)
as
set nocount on
select *
from [Production].[TransactionHistory]
where (@productid is null or @productid = ProductID) --indexed
and (@quantity is null or @quantity = Quantity) --not indexed
go
dbcc freeproccache
go
--show actual execution plan here
set statistics IO on
go
--run these in exactly this order
exec test --table scan (792 reads), all rows
exec test @productid = 784--table scan (792 reads), 746 rows
exec test @productid = 790--table scan(792 reads), 2 rows
exec test @quantity = 2--table scan(792 reads), 9617 rows
exec test @quantity = 123--table scan(792 reads), 2 rows
exec test @productid = 797, @quantity = 13--table scan(792 reads), 1 row
dbcc freeproccache
go
--Gail, here is proof of an index seek for this query style
exec test @productid = 790--index seek, bookmark lookup (163 IO), 2 rows
--not pretty here
exec test @productid = 784--index seek, bookmark lookup (2395 IO), 746 rows
--UGLY
exec test ----index seek, bookmark lookup (340486 IO!!!)
exec test @quantity = 2 ----index seek, bookmark lookup (340486 IO!!!)
exec test @quantity = 123 ----index seek, bookmark lookup (340486 IO!!!)
exec test @productid = 797, @quantity = 13 --index seek, bookmark lookup (1684 IO), 1 row
--disproving the misconception kevin mann proposed that order matters
drop proc test
go
create proc test (@productid int = null, @quantity int = null)
as
set nocount on
select *
from [Production].[TransactionHistory]
where (@quantity is null or @quantity = Quantity) --not indexed
and (@productid is null or @productid = ProductID) --indexed
go
--rerun every execution above in the order and see that everything is exactly the same
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2009 at 7:30 pm
RYan k (6/1/2009)
I always make use of a COALESCE() instead of the OR in cases where there's an "=" -- (not in this particular case with Age > @Age) ...snip
Coalesce only works on non null columns. Else you'll be searching for null values only. Appearance aside, actual records returned will vary depending on the column's null value settings. Anytime "I always use..." invariably means sometimes it won't work cut it.
June 1, 2009 at 10:55 pm
The dificulty is also there when we want to use "like" or "in" oprator in the stored procedure, for these conditions we have to use dynamic querries.
Viewing 15 posts - 31 through 45 (of 83 total)
You must be logged in to reply to this topic. Login to reply