December 12, 2001 at 2:35 pm
Hey folks, here is a situation I have visited probably once every 6 months over the last several years. I apologize now for the length of this.
Situation : End user / application needs to be able to do a search that could contain up to 13 search criteria. None of them are mandetory so there could be any number of combinations the user could search on. The developer (me) needs a stored procedure written by the DBA (me) that can accomodate this request in a timely manner. It also needs to be sorted in a number of ways in which the user has control over. The next requirement is not in there now, but in the future the query would need to respond with no more than 500 records... especially if the user doesn't pass any search critiera. I am not worried about this requirement for this example.
I have writtent this type of SP many times over using various techniques. Utilizing ISNULL, COALESCE, Pure Dynamic SQL which builds the SQL statement, with and without views. The fastest that I have found yet for this type of query is the use of Dyanmic SQL to build the SQL statement. I realize SQL server has to compile the SP every time, but the performance difference between compiling every time and then using one of the other techniques is large in my case. One of my goals is to find out if there is a better method or have I used one of the best methods already, but didn't do something correctly to get the performance needed.
Here is an example of what I am using now. This is the fastest responding SP I can get at this point for this type of search. It is kind of long. Also let's assume I have the best implimentation of Indexes and Clustered indexes on the tables.
*** START SP DEFINITION ***
CREATE PROCEDURE GetDocumentScanSearchView
@CompanyNo Integer = 20,
@DateReceivedB DateTime = Null,
@DateReceivedE DateTime = Null,
@DateFiledB DateTime = Null,
@DateFiledE DateTime = Null,
@JobNo VarChar(6) = Null,
@DeptID Integer = Null,
@FolderID Integer = Null,
@SubFolderID Integer = Null,
@Vendor VarChar(50) = Null,
@author VarChar(50) = Null,
@Recipient VarChar(50) = Null,
@Subject VarChar(255) = Null,
@SortOrder Integer = 1,
@SortBy Integer = 2,
@IssueID Integer = Null
/* SortBy : 1 = JobNo, 2 = DateReceived*/
AS
BEGIN
DECLARE @sql nvarchar(2100)
DECLARE @sqlWhere nvarchar(1000)
DECLARE @sqlOrder nvarchar(100)
DECLARE @strDateReceivedB varchar(10)
DECLARE @strDateReceivedE varchar(10)
DECLARE @strDateFiledB varchar(10)
DECLARE @strDateFiledE varchar(10)
DECLARE @strCompanyNo varchar(6)
DECLARE @strDeptID varchar(6)
DECLARE @strFolderID varchar(6)
DECLARE @strSubFolderID varchar(6)
DECLARE @strIssueID varchar(6)
SET @strCompanyNo = CONVERT(VarChar(6), @CompanyNo)
SET @strDateReceivedB = CONVERT(varchar(10),@DateReceivedB,101)
SET @strDateReceivedE = CONVERT(varchar(10),@DateReceivedE,101)
SET @strDateFiledB = CONVERT(varchar(10),@DateFiledB,101)
SET @strDateFiledE = CONVERT(varchar(10),@DateFiledE,101)
SET @strDeptID = CONVERT(varchar(6), @DeptID)
SET @strFolderID = CONVERT(varchar(6), @FolderID)
SET @strSubFolderID = CONVERT(varchar(6), @SubFolderID)
SET @strIssueID = CONVERT(varchar(6), @IssueID)
SET @sql = ''
/* I could let this be SET @sql = @sql + 'SELECT * FROM THE-VIEW-EQUIVALENT-OF-THE-SELECT-STATEMENT-BELOW' */
SET @sql = @sql + 'SELECT DocumentScan.UniqueID, DocumentScan.CompanyNo,
DocumentScan.JobNo, DocumentScanDept.Dept,
DocumentScanType.DocumentType,
DocumentScanFolder.Folder,
DocumentScanSubFolder.SubFolder,
DocumentScan.DateReceived, DocumentScan.ScanDate,
DocumentScan.DocumentDate, DocumentScan.Vendor,
DocumentScan.Author, DocumentScan.Recipient,
DocumentScan.Subject, DocumentScan.DocumentFile,
DocumentScan.IsStored, DocumentScan.StoredDate,
DocumentScan.LastModified, DocumentScan.ModifiedBy, lIssues.Issue
FROM DocumentScan LEFT OUTER JOIN
DocumentScanFolder ON
DocumentScan.FolderID = DocumentScanFolder.UniqueID LEFT OUTER JOIN
DocumentScanSubFolder ON
DocumentScan.SubFolderID = DocumentScanSubFolder.UniqueID LEFT OUTER JOIN
DocumentScanDept ON
DocumentScan.DeptID = DocumentScanDept.UniqueID LEFT OUTER JOIN
DocumentScanType ON
DocumentScan.DocumentTypeID = DocumentScanType.UniqueID LEFT OUTER JOIN
lIssues ON
DocumentScan.IssueID = lIssues.IssueID
'
SET @sqlWhere = ''
SET @sqlWhere = @sqlWhere + isnull('AND DocumentScan.CompanyNo = ' + @strCompanyNo + char(10), '')
SET @sqlWhere = @sqlWhere + 'AND IsStored = 1' + char(10)
SET @sqlWhere = @sqlWhere + isnull(' AND (DateReceived BETWEEN ' + '''' + @strDateReceivedB + '''' +' AND ' + '''' + @strDateReceivedE + '''' + ')' + char(10), '')
SET @sqlWhere = @sqlWhere + isnull(' AND (LastModified BETWEEN ' + '''' + @strDateFiledB + '''' +' AND ' + '''' + @strDateFiledE + '''' + ')' + char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND DocumentScan.JobNo = ' + '''' +@JobNo + '''' + char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND DocumentScanDept.UniqueID = ' + @strDeptID + char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND DocumentScanFolder.UniqueID = ' + @strFolderID + char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND DocumentScanSubFolder.UniqueID = ' + @strSubFolderID + char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND Vendor = ' + '''' + @Vendor + '''' + char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND Author LIKE ' + '''' + '%' + @author + '%' + '''' + char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND Recipient = ' + '''' + @Recipient + '''' + char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND Subject LIKE ' + '''' + '%' + @Subject + '%' + '''' +char(10), '')
SET @sqlWhere = @sqlWhere + isnull('AND DocumentScan.IssueID = ' + '''' + @strIssueID + '''' + char(10), '')
IF (len(@sqlWhere) > 0)
SET @sqlWhere = 'WHERE' + char(10) + right(rtrim(@sqlWhere),(len(@sqlWhere) - 3))
IF @SortOrder = 0
BEGIN
SET @sqlOrder =
CASE @SortBy
WHEN 1 THEN ' ORDER BY DocumentScan.JobNo ASC, DateReceived DESC'
WHEN 2 THEN ' ORDER BY DateReceived ASC, DocumentScan.JobNo'
END
END
IF @SortOrder = 1
BEGIN
SET @sqlOrder =
CASE @SortBy
WHEN 1 THEN ' ORDER BY DocumentScan.JobNo DESC, DateReceived DESC'
WHEN 2 THEN ' ORDER BY DateReceived DESC, DocumentScan.JobNo'
END
END
SET @sql = @sql + @sqlWhere + @sqlOrder
EXEC (@sql)
END
GO
*** END SP DEFINITION ***
Now I have tried using ISNULL or the COALESCE functions instead of building a query string like above, but they cause the tables to do INDEX SEEK, INDEX SCANs, or CLUSTERED INDEX Scans, BUT the execution plan shows it reads in EVERY ROW and then FILTER which tales way to long. My experience has shown me that having to compile the SP every time over using ISNULL or COALESCE when there are more than a couple of WHERE parameters has always been faster. Now of course I may have not been doing something right.
Here is an OLD example of the same SP during early stages of testing. Here I selected from a view which pretty much looked like the SELECT statement above and I used ISNULL in the SELECT statement. I could have used COALESCE, but it yields the same results. And yes I used ISNULL in the above SP, but it was used not as part of the SELECT, but to build a string which ultimately would be the SQL statement. The SP below also doesn't include the ordering. Oh, I also used the IN TSQL statement below, but at one time I just utilized joins like above and it didn't make a difference.
*** START SP DEFINITION ***
CREATE PROCEDURE GetDocumentScanSearchView
@CompanyNo Integer = 20,
@DateReceivedB DateTime,
@DateReceivedE DateTime,
@JobNo VarChar(6) = Null,
@DeptID Integer = Null,
@FolderID Integer = Null,
@SubFolderID Integer = Null,
@Vendor VarChar(50) = Null,
@author VarChar(50) = Null,
@Recipient VarChar(50) = Null,
@Subject VarChar(255) = Null
AS
SELECT *
FROM DocumentScanView
WHERE UniqueID IN
(SELECT UniqueID
FROM DocumentScan
WHERE CompanyNo = @CompanyNo AND IsStored = 1 AND
(DateReceived BETWEEN @DateReceivedB AND @DateReceivedE) AND JobNo
= IsNull(@JobNo, JobNo) AND
DeptID = IsNull(@DeptID,DeptID) AND FolderID = IsNull(@FolderID,
FolderID) AND
SubFolderID = IsNull(@SubFolderID, SubFolderID) AND Vendor =
IsNull(@Vendor,Vendor) AND
Author = IsNull(@Author, Author) AND Recipient = IsNull
(@Recipient, Recipient) AND
Subject LIKE IsNull('%' + @Subject + '%',Subject))
*** END SP DEFINITION ***
I found the above to be very slow and it did a lot of index scans, index seeks or clustered index scans that pulled in EVERY row before filtering. This happens when parts of the WHERE clause equivilated to FieldName = FieldName because the user passed a null parameter. It doesn't matter if you use the ISNULL or COALESCE. Again I know I am using the TSQL IN statement above, but that just happens to be the old copy I could find. I used a join later on and it didn't matter.
Here is one more SP which is somewhat simpler with the corresponding table creation and data inserts... this doesn't even have joins so it is simpler to look at:
*** BEGIN TABLE CREATION ***
CREATE TABLE [dbo].[aTest] (
[TestID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyID] [int] NOT NULL ,
[UserName] [char] (10) NULL ,
[JobNo] [char] (6) NULL ,
[StoredDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[aTest] WITH NOCHECK ADD
CONSTRAINT [PK_aTest] PRIMARY KEY CLUSTERED
(
[TestID]
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [IX_aTest] ON [dbo].[aTest]([CompanyID], [UserName]) ON [PRIMARY]
GO*** END TABLE CREATION ***
*** BEGIN INSERTING OF TEST DATA ***
insert into aTest (CompanyID, UserName, JobNo, StoredDate)
Values(20, 'GregS', 'IT', '05/22/01')
insert into aTest (CompanyID, UserName, JobNo, StoredDate)
Values(20, 'ChipH', NULL, '05/22/01')
insert into aTest (CompanyID, UserName, JobNo, StoredDate)
Values(22, 'JustinV', 'EVW', '05/22/01')
insert into aTest (CompanyID, UserName, JobNo, StoredDate)
Values(30, 'FreddyM', 'BAA', '05/22/01')
insert into aTest (CompanyID, UserName, JobNo, StoredDate)
Values(20, 'KevinC', NULL, '05/22/01')
insert into aTest (CompanyID, UserName, JobNo, StoredDate)
Values(22, 'StacyC', NULL, '05/22/01')
*** END INSERTING OF TEST DATA ***
*** BEGIN SP ***
CREATE PROCEDURE GetTestSearch
@CompanyID Integer = Null,
@UserName varchar(10) = Null,
@JobNo varchar(6) = Null,
@StoredDateB datetime = Null,
@StoredDateE datetime = Null
AS
SELECT *
FROM ATEST
WHERE COALESCE (CompanyID, 99) = COALESCE (@CompanyID, COALESCE
(CompanyID,99))
AND COALESCE(UserName, 'ZZZZZZ') = COALESCE(@UserName, COALESCE
(UserName, 'ZZZZZZ'))
AND COALESCE(JobNo,'ZZZZZZ') = COALESCE(@JobNo, COALESCE
(JobNo,'ZZZZZZ'))
AND COALESCE(StoredDate, GetDate()) BETWEEN COALESCE(@StoredDateB,
'01/01/1901' ) AND COALESCE(@StoredDateE, GetDate())
GO
*** END SP ***
Ok the above is a simpler example, but using the COALESCE causes a CLUSTERED INDEX SCAN which pulls in all records and then it filters. When you look at the filter it is a bunch of IF crap where it tests the arguments.
Sooooooooooo... what is my question?
Well I am looking for any and all input on those who have been doing this longer than I have.
1) First just give me your thoughts and comments on anything you would like about the above.
2) Do you find using ISNULL or COALESCE with multiple parameters of a WHERE statement slow as I do, because it does a INDEX SCAN or INDEX SEEK or CLUSTERED INDEX SCAN that pulls every row/record before filtering.
3) If so is there something that can be changed so it does NOT do this.
4) Are you against using the Dynamic SQL as I have in the first SP? Maybe you just don't allow this kind of search ability.
5) If so can you give a better / FASTER way of doing the same thing?
6) One person has told me to not worry about the ORDER within the SP, but allow the client to order it once it gets the returned result set. Do you agree or disagree?
7) You notice that in building the @sql in the first SP I have it equal to the full textual 'SELECT....' where as I could have a view and said SET @sql = 'SELECT * FROM VIEWEQUIVALENT'. Would you use a View or the actually select statement as I have done? What if you have the same select statement used in a number of SPs? Would you then use a view so when you made a change you wouldn't have to go to every SP that had the full SELECT statement?
PHEW... I hope you followed all of this. I welcome your comments and thoughts. I know of many folks who have the same questions, but I have also seen many different answers as well. What do ya think?
Greg
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply