An alternative for Dynamic SQL required

Hey guys I've something like this which is a reauirement. I dont want to use
a dynamic SQL
eg is
create proc proc1
@.ownername varchar(100),
@.IPaddress varchar(15)
as
select a,b,c from Table1 t1
join Table2 t2 on t1.col1=t2.col2
where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
+'%') OR
(t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
(t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
go
Hopefully you can see the situation here.
I have an app where I can select (either or fname or lname) and I can also
select (either or internal IP or external IP). The above proc will work for
either in both cases.
What I want to do in the stored proc is somehow check these conditions. I
dont want to use dynamic SQL. Can you suggest an alternative which will help
in performance when compard to Dynamic SQL? Thank you.One obvious performance issue is that you are performing a LIKE comparison
on a value that is left truncated. For example, if OwnerName is indexed,
then this is will result in an index scan:
where OwnerName LIKE 'John%'
However, the following would result in a non-indexed table scan:
where OwnerLastName LIKE '%Smith'
where OwnerLastName LIKE '%Smith%'
Also, read this document; specifically the paragraphs about "sargable"
comparison arguments.
http://www.microsoft.com/technet/pr...s/inside14.mspx
Actually, dynamic SQL may be the solution to your problem. If you could
construct your SQL on the application side or construct the SQL within the
procedure in a variable and execute using the T-SQL Exec function.
http://msdn.microsoft.com/library/d...br />
05ro.asp
You can use the Show Execution Plan feature of Query Analyzer to determine
if your query is properly utilizing an index.
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:8FD1CC5B-A8F9-4080-A75C-65D85E331CF9@.microsoft.com...
> Hey guys I've something like this which is a reauirement. I dont want to
> use
> a dynamic SQL
> eg is
> create proc proc1
> @.ownername varchar(100),
> @.IPaddress varchar(15)
> as
> select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
> where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
> +'%') OR
> (t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
> (t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
> go
>
> Hopefully you can see the situation here.
> I have an app where I can select (either or fname or lname) and I can also
> select (either or internal IP or external IP). The above proc will work
> for
> either in both cases.
> What I want to do in the stored proc is somehow check these conditions. I
> dont want to use dynamic SQL. Can you suggest an alternative which will
> help
> in performance when compard to Dynamic SQL? Thank you.|||On Fri, 17 Mar 2006 09:05:26 -0800, Tejas Parikh wrote:

>Hey guys I've something like this which is a reauirement. I dont want to us
e
>a dynamic SQL
>eg is
>create proc proc1
>@.ownername varchar(100),
>@.IPaddress varchar(15)
>as
>select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
>where ((t1.OwnerFirstName LIKE '%'+ISNULL(@.ownername,A.OwnerFirstName)
>+'%') OR
> (t1.OwnerLastName LIKE '%'+ ISNULL(@.ownername,A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%'+ISNULL(@.IPaddress,DB.ExternalIP)+'%') OR
> (t1.InternalIP LIKE '%'+ISNULL(@.IPaddress,DB.InternalIP)+'%'))
>go
>
>Hopefully you can see the situation here.
>I have an app where I can select (either or fname or lname) and I can also
>select (either or internal IP or external IP). The above proc will work for
>either in both cases.
>What I want to do in the stored proc is somehow check these conditions. I
>dont want to use dynamic SQL. Can you suggest an alternative which will hel
p
>in performance when compard to Dynamic SQL? Thank you.
Hi Tejas,
Lots of useful information for this type of problem can be found on
Erland's page: http://www.sommarskog.se/dyn-search.html.
Hugo Kornelis, SQL Server MVP|||Tejas Parikh (TejasParikh@.discussions.microsoft.com) writes:
> Hey guys I've something like this which is a reauirement. I dont want to
> use a dynamic SQL
> eg is
> create proc proc1
> @.ownername varchar(100),
> @.IPaddress varchar(15)
> as
> select a,b,c from Table1 t1
> join Table2 t2 on t1.col1=t2.col2
> where ((t1.OwnerFirstName LIKE '%' + ISNULL(@.ownername,
> A.OwnerFirstName) +'%') OR
> (t1.OwnerLastName LIKE '%' + ISNULL(@.ownername,
> A.OwnerLastName) +'%'))
> AND ((t1.ExternalIP LIKE '%' + ISNULL(@.IPaddress,
> DB.ExternalIP) + '%') OR
> (t1.InternalIP LIKE '%' + ISNULL(@.IPaddress,
> DB.InternalIP)+'%'))
> go
Well, as long as you have the % first in the LIKE operations, this is
going to table scan no matter what you do, so there would not be much
point with using dynamic SQL for better performance. Your current code
would work fine.
I would recommend that you leave it to the users to specify any initial %
if they need it. Then there is a at least a ghost of a chance for
indexes to be used. It's difficult to give detailed suggestions though,
as I don't know the tables, and I suspect that your real procedures have
more than these two parameters.
But my article, that Hugo also pointed you to, might give you some
ideas: http://www.sommarskog.se/dyn-search.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx