September 28, 2007 at 3:28 am
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).
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
September 28, 2007 at 3:33 am
RyanRandall (9/28/2007)
However, for the example presented by the article, can someone tell me what's wrong with simply this?...
selectcustomerid, 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).
Exacxtly, though if you throw an 'or @companyName is null' in there the optimiser will ignore any 'parameters' that are not set. eg,
(companyName like @companyName or @companyName is null)
AND
(country like @country or @country is null)
September 28, 2007 at 3:41 am
Ben (9/28/2007)
Exacxtly, though if you throw an 'or @companyName is null' in there the optimiser will ignore any 'parameters' that are not set.
Yes, I know about that (it's in Erland's article), and it is the best method. I left it out here to keep the example aligned with the article and keep the point clear.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
September 28, 2007 at 5:09 am
Sorry but I couldn't see the whole example. The scroll bars on the example did not work.
Steve
September 28, 2007 at 5:17 am
Steve Coleman (9/28/2007)
Sorry but I couldn't see the whole example. The scroll bars on the example did not work.Steve
Steve,
It looks like this...
-- Declare some local variables. Actually, we are creating a pair of variables
-- for each column included in our WHERE clause.
-- The first variable represents the value we are filtering and the second
-- represents the "operator" for the filter.
declare @companyName varchar(255)
declare @companyNameOp varchar(2)
declare @country varchar(255)
declare @countryOp varchar(2)
-- Let's set some sample values now. The values you see here represent the second
-- of the two scenarios described above, i.e. all records for companies located in Germany,
-- excluding companies starting with the letter A
-- Operators are defined here with arbitrary, two-letter values.
-- Of course you could define your own set of operators, with different
-- naming conventions. For our example, here's the meaning of each possible
-- value:
-- ne = not equal
-- eq = equal
-- bg = begins with
-- ed = ends with
-- ct = contains
-- For our example, we are using only varchar fields in our WHERE clause.
-- It is very easy, though, to define operators for other data types as well.
set @companyname = 'A%'
set @companynameOp = 'ne'
set @country = 'Germany'
set @countryOp = 'eq'
-- Ok, now let's form our query.
select
customerid, companyname, country
from
customers
where
case @companyNameOp
when '' then 1 -- Operator not defined, get everything
when 'eq' then -- Operator is "equals"
case when companyname like @companyName then 1 else 0 end
when 'bg' then -- Operator is "begins with"
case when companyname like @companyName +'%' then 1 else 0 end
when 'ed' then -- Operator is "ends with"
case when companyname like '%' + @companyName then 1 else 0 end
when 'ct' then -- Operator is "contains"
case when companyname like '%' + @companyName +'%' then
1 else 0 end
when 'ne' then -- Operator is "not equal"
case when companyname not like @companyName then 1 else 0 end end =1
AND
-- Same approach for the second field
case @countryOp
when '' then 1
when 'eq' then
case when country like @country then 1 else 0 end
when 'bg' then
case when country like @country +'%' then 1 else 0 end
when 'ed' then
case when country like '%' + @country then 1 else 0 end
when 'ct' then
case when country like '%' + @country +'%' then 1 else 0 end
when 'ne' then
case when country not like @country then 1 else 0 end
end =1
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
September 28, 2007 at 6:56 am
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
September 28, 2007 at 8:32 am
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
September 28, 2007 at 9:03 am
RyanRandall (9/28/2007)
I'm with Adam, check out MVP Erland Sommarskog's article on this: http://www.sommarskog.se/dyn-search.htmlHowever, for the example presented by the article, can someone tell me what's wrong with simply this?...
selectcustomerid, 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.
September 28, 2007 at 9:33 am
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.
September 28, 2007 at 10:19 am
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.
September 28, 2007 at 11:32 am
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
October 1, 2007 at 8:06 am
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
October 2, 2007 at 2:11 am
Great Idea!
October 3, 2007 at 9:36 am
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
October 4, 2007 at 8:44 am
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?
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply