October 6, 2011 at 3:05 pm
Hi there, total newbie here! i'm running sql doctor and it's recommending that we rewrite the where clause in this particular SP because it causes a table scan. Is there anyway of rewriting this without using dynamic sql? Or any pointer to the right direction would be appreciated!
Here's the portion of the where clause:
where @emptyrecordset = 0
--and (@startactivityyearmonth is null or a.activity_year_month > @startactivityyearmonth )
and (@field_id is null or b.field_id = @field_id )
and (@pool_id is null or b.pool_id = @pool_id )
Thanks!
October 6, 2011 at 3:33 pm
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
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
October 6, 2011 at 3:44 pm
Thanks Gail! Would dynamic SQL be the only way? Our shop has an anti-dynamic-sql policy in place due to security!
October 6, 2011 at 3:55 pm
On SQL 2005, pretty much yes.
Otherwise, multiple stored procedures for the cases where the various parameters are passed or not, but that means 4 procedures.
On the security side, the solution I show is completely SQL Injection safe (providing you do it the way I showed) and it's possible to use EXECUTE AS to alleviate the risk of granting permission to the base tables.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply