December 12, 2001 at 5:52 pm
Thats a big query.
I can't get a great idea of your schema, but at first glance I was thinking that using fulltext indexing might help.
If you were to set up a single index with the fields you have specified in your where clause, the user would just have to enter a boolean search clause or you could generate it based on their input. Then in the where clause you could simply write:
where contains(*, "'boolean search clause'")
This would query all of the fields in the index and return any matches. You wouldn't have to dynamically build the query. One drawback is that by querying all columns in the index from within the where clause, you would loose control over searching only specific fields.
I know this might not be the suggestion you are looking for, but its off the cuff and maybe a different approach.
December 12, 2001 at 7:02 pm
I agree with previous comment, start looking at FULL TEXT indexing.
For a start ignore the %SUBJECT% and concentrate on the rest.
Are you running 6.5,7 or 2000?
Record size and no. rows?
December 13, 2001 at 6:29 am
We are running on SQL7 with the latest service pack, but we plan to go to SQL2000 in a couple of months. There are about 23 fields in the record and currently there are only 10780 records, but it will grow by 1000 each month. None of the fields are blobs.
I know my post is rather long... but I hope to get some other input as well. I can't be the only one who has run across this before.
Guys... thanks for the input so far. I will definitely examine full text indexing. I may have some smaller search criteria for other searches where one may not suggest a full text index so hopefully I can get some responses on some of the specific questions at the bottom of the posts.
Thanks... and I am looking forward to other responses.
December 13, 2001 at 7:19 am
Full-text indexing could be used for probably the subject column but be aware that wildcards are not valid at the start of the argument, so a direct replacement of %Subject% would not work. Also, in my experience FTI offers an indexing solution not available with other methods but it is not necessarily faster and FTI would only replace probably one column anyway.
Clearly with so many parameters it is not possible to code for all combinations, so my overall preference for a search sp like this is to use dynamic SQL and sp_executesql. You will lose the colour coding of SQL syntax, and if it goes wrong, you'll get an error message indicating an error at line 1, regardless of where the error originates, but these are usually only syntax errors and are soon cleared up.
As an interesting alternative (if you have the time) you could use lazy evaluation. This keeps the syntax checker and debug info intact. I posted up a script to this site on this recently. Lazy Evaluation means that the normal syntax will be used, and the where clause would still be workable in this case with not too many parameters. It might also be interesting to compare the running times.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 17, 2001 at 7:19 am
Paul,
I can't locate the script you are speaking of. Could you give me a link to it or post it here?
To all of you out there,
There have got to be other folks out there who have run into these types of needs from the developer... what have you done in these situations?
Thanks
December 17, 2001 at 7:34 am
Here is the link:
This will take a little while to write so I'd start with a small version using a couple of variables and check out the query plan - if it looks reasonable, then code up the rest.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 17, 2001 at 12:18 pm
Ok... I have the script.
Could you expand upon this example to where there are 2 search fields and it is possible for one or both to be selected.
I am trying to do what you are suggesting, but I can only get it to search based on one field.
Looking at your example:
select * FROM TblTest
where
CASE
WHEN @Input = -1 THEN 1
when SupplierId = @Input THEN 1
else 0
END = 1
To me this is saying that if the input is -1 then just jump out of the CASE statement to the end. How do I do it if there are 2 inputs @input and @input2. What is the proper syntax so that it will include both input requirements if they are both NOT -1?
How do you build the AND or the OR for the where statement using the CASE as above?
From the example above I can only see how to do this where ONLY 1 criteria is actually used.
Thanks
December 18, 2001 at 2:40 am
This is the sort of thing you could use:
--drop table TblTest
--go
create table TblTest
(
SupplierId int null,
CompanyID int null
)
go
insert into TblTest (SupplierId, CompanyID) values (1,10)
insert into TblTest (SupplierId, CompanyID) values (2,20)
insert into TblTest (SupplierId, CompanyID) values (3,30)
insert into TblTest (SupplierId, CompanyID) values (4,40)
declare @InputSupplier int
set @InputSupplier = 1
declare @InputCompany int
set @InputCompany = -1
select * from TblTest
where
case
when @InputSupplier = -1 and @InputCompany = -1 then 1
when SupplierId = @InputSupplier and @InputCompany = -1then 1
when @InputSupplier = -1 and CompanyID = @InputCompany then 1
when SupplierId = @InputSupplier and CompanyID = @InputCompany then 1
else 0
end = 1
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 18, 2001 at 3:06 am
Additional caveat: although the Lazy Evaluation gives a neat way of not using concatenated strings and dynamic sql, it can only really be used on small tables - I've just discovered that the queryplan involves an initial table scan and then applies the case where clause as a filter. It seems SQL can't determine the indexes to be used, which would clearly not be suitable on large tables. Oh well...
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
Viewing 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply