Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Conditional Statements in WHERE Clauses Expand / Collapse
Author
Message
Posted Friday, September 28, 2007 6:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 1:47 PM
Points: 467, Visits: 381
I don't see how any query can avoid a table scan given a condition like "all company names that contain the word THE".

I have used this technique to meet some complex requirements for a flexible search proc. I structured the code a little differently by placing the constant first (before the case). From my reading of SARGs I think (don't know) that would help performance some. Also, I always include a default value for the Case.

e.g. where 1 = Case .......
else 1
end
Post #404000
Posted Friday, September 28, 2007 8:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 9:13 AM
Points: 469, Visits: 265
To me its all about knowing your data. This is a great method to use some of the time, but it will cost you in performance in some cases. At our shop we have a very large database (which happens to be on Oracle), but when we are trying to perform an extract to a data mart (SQL Server) we generally do not want to use an index anyway because the volume is so large that the indexes are many times less efficient than a table scan. In this environment the technique described in this article would be good. Thanks
Post #404059
Posted Friday, September 28, 2007 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 4, 2007 12:37 PM
Points: 1, Visits: 5
RyanRandall (9/28/2007)
I'm with Adam, check out MVP Erland Sommarskog's article on this: http://www.sommarskog.se/dyn-search.html

However, for the example presented by the article, can someone tell me what's wrong with simply this?...

select 
customerid, companyname, country
from
customers
where
(companyname LIKE @companyname) AND (country LIKE @country)

This is able to deal with the different operators (NOT LIKE 'A%' can be dealt with by using '[0-9, b-z]%'), and can use indexes if they're there (i.e. avoid a table scan).


That was my first reaction, too. On re-reading the article I see that the point was not simply to write SQL which fits the given example. It was meant to be a generalizable solution for user-defined searches.

My habitual solution when building search forms has simply been to write code that conditionally concatenates SQL fragments which reflect user-selected criteria and operators. So the whole discussion here is interesting. Thanks.
Post #404075
Posted Friday, September 28, 2007 9:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:26 AM
Points: 31,177, Visits: 15,616
I'm not sure you can avoid it for that query, but you can inject intelligence into your application. If you have exact searches, then use a query (or proc) that works for those. If you need less exact searches, then switch to a new proc and take the hit.

Code is fairly cheap and you don't necessarily have to reuse stuff if there's a performance hit.

You have to compare the cost and time of developing (and maintaining) more procs v the hit your users and systems take. If you have any sort of search volume, it's worth doing a little work to optimize different searches.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #404098
Posted Friday, September 28, 2007 10:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 3, 2008 12:28 PM
Points: 109, Visits: 16
The conditional where clause works fine for small sets of data but it is not very efficient. For large data sets it is better to stick to the dynamic sql approach.
Post #404121
Posted Friday, September 28, 2007 11:32 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:06 AM
Points: 6,259, Visits: 2,030
Dynamic query construction should be left to the Client Side. As long as you do that you won't loose flexibility and with the use os sp_execusql some of those could even be reused.

Cheers,



* Noel
Post #404175
Posted Monday, October 1, 2007 8:06 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 21, 2012 3:13 PM
Points: 516, Visits: 1,563
I too had a problem reading the posted script in the original article because I could not see the Vertical Scroll Bar and the right edge of the Horizontal Scroll Bar. I tried maximizing the window and this still didn't help.

Steve



Post #405024
Posted Tuesday, October 2, 2007 2:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 16, 2009 9:09 AM
Points: 7, Visits: 31
Great Idea!
Post #405443
Posted Wednesday, October 3, 2007 9:36 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 1:58 AM
Points: 365, Visits: 947
Doing this type of thing in the order by clause.. now that's the way forward!

If you have 20 lines of sql in an sp and have to order the results in 5 different ways, say by date, firstname, surname, personId, email... that's when you can really cut down on your code in your stored procedure, and avoid 'dynamic queries'.

I don't give any permissions on tables to client applications.. maybe I am harsh, but I like the added layer.
CREATE PROCEDURE usp_getMyData
@orderBy varchar(20)
AS

SELECT personId, birthDate, firstname, surname, email
FROM dbo.persons
ORDER BY
CASE @orderBy
WHEN 'personId' THEN personId
END
DESC,
CASE @orderBY
WHEN 'firstname' THEN firstname
WHEN 'surname' THEN surname
WHEN 'email'THEN email
END
DESC,
CASE
WHEN @orderBy IS NULL THEN birthDate
END
DESC

GO







Post #406274
Posted Thursday, October 4, 2007 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 7,105, Visits: 15,436
I've used the conditional syntax in WHERE clauses, but a conditional ORDER BY? That essentially guarantees not using indexes, and ORDER BY without being able to use indexes tends to be VERY costly. A conditional where clause is a linear growth (N growth), whereas a conditional ORDER by would entail something like hash sorts/bubble sorts (N^2 growth), so the effort grows polynomially.

Are you actually ever seeing it use anything index related using that technique? I'd be really curious if you did - I just don't see how it would.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #406833
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse