|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
An alternative for Dynamic SQL requireda 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/prodtechnol/sql/70/books/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/default.asp?url=/library/en-us/tsqlref/ts_ea-ez_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/default.asp?url=/library/en-us/optimsql/odp_tun_1_5pde.asp Show quote "Tejas Parikh" <TejasPar***@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:
Show quote >Hey guys I've something like this which is a reauirement. I dont want to use Hi Tejas,>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. 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 (TejasPar***@discussions.microsoft.com) writes:
Show quote > Hey guys I've something like this which is a reauirement. I dont want to Well, as long as you have the % first in the LIKE operations, this is> 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 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, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics