Stored Procedure to search a view

Question

Apologies for the long post, I have tried to provide you with as much of an test environment as possible.

I have spent several days optimizing this query as much as I can but I am wondering if there is a better approach for achieving the same result.

Environment is SQL Server 2005. This stored procedure code is autogenerated by a Codesmith template.

Table setup

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[StateProvince]    Script Date: 03/07/2012 09:57:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[StateProvince](
[StateProvinceID] [int] IDENTITY(1,1) NOT NULL,
[StateProvinceName] [varchar](50) NOT NULL,
CONSTRAINT [PK_StateProvince] PRIMARY KEY CLUSTERED 
(
[StateProvinceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Gender]    Script Date: 03/07/2012 09:57:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Gender](
[GenderID] [int] IDENTITY(1,1) NOT NULL,
[GenderName] [nchar](10) NOT NULL,
CONSTRAINT [PK_Gender] PRIMARY KEY CLUSTERED 
(
[GenderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Fakename]    Script Date: 03/07/2012 09:57:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Fakename](
[FakeNameID] [int] IDENTITY(1,1) NOT NULL,
[gender] [nvarchar](6) NOT NULL,
[givenname] [nvarchar](20) NOT NULL,
[middleinitial] [nvarchar](1) NOT NULL,
[surname] [nvarchar](23) NOT NULL,
[streetaddress] [nvarchar](100) NOT NULL,
[city] [nvarchar](100) NOT NULL,
[zipcode] [nvarchar](15) NOT NULL,
[country] [nvarchar](2) NOT NULL,
[emailaddress] [nvarchar](100) NOT NULL,
[password] [nvarchar](25) NOT NULL,
[telephonenumber] [nvarchar](25) NOT NULL,
[maidenname] [nvarchar](23) NOT NULL,
[birthday] [datetime] NOT NULL,
[cctype] [nvarchar](10) NOT NULL,
[ccnumber] [nvarchar](16) NOT NULL,
[cvv2] [nvarchar](3) NOT NULL,
[ccexpires] [nvarchar](10) NOT NULL,
[nationalid] [nvarchar](15) NOT NULL,
[upstracking] [nvarchar](24) NOT NULL,
[occupation] [nvarchar](70) NOT NULL,
[company] [nvarchar](70) NOT NULL,
[domain] [nvarchar](70) NOT NULL,
[bloodtype] [nvarchar](3) NOT NULL,
[pounds] [decimal](5, 1) NOT NULL,
[kilograms] [decimal](5, 1) NOT NULL,
[feetinches] [nvarchar](6) NOT NULL,
[centimeters] [smallint] NOT NULL,
[guid] [nvarchar](36) NOT NULL,
[latitude] [numeric](10, 6) NOT NULL,
[longitude] [numeric](10, 6) NOT NULL,
[StateProvinceID] [int] NOT NULL,
[GenderID] [int] NULL,
CONSTRAINT [PK_Fakename] PRIMARY KEY CLUSTERED 
(
[FakeNameID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[vw_Fakename]    Script Date: 03/07/2012 09:57:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--asdf
CREATE VIEW [dbo].[vw_Fakename]
AS
SELECT 
[dbo].Fakename.FakeNameID,
[dbo].Fakename.gender,
[dbo].Fakename.givenname,
[dbo].Fakename.middleinitial,
[dbo].Fakename.surname,
[dbo].Fakename.streetaddress,
[dbo].Fakename.city,
[dbo].Fakename.zipcode,
[dbo].Fakename.country,
[dbo].Fakename.emailaddress,
[dbo].Fakename.password,
[dbo].Fakename.telephonenumber,
[dbo].Fakename.maidenname,
[dbo].Fakename.birthday,
[dbo].Fakename.cctype,
[dbo].Fakename.ccnumber,
[dbo].Fakename.cvv2,
[dbo].Fakename.ccexpires,
[dbo].Fakename.nationalid,
[dbo].Fakename.upstracking,
[dbo].Fakename.occupation,
[dbo].Fakename.company,
[dbo].Fakename.domain,
[dbo].Fakename.bloodtype,
[dbo].Fakename.pounds,
[dbo].Fakename.kilograms,
[dbo].Fakename.feetinches,
[dbo].Fakename.centimeters,
[dbo].Fakename.guid,
[dbo].Fakename.latitude,
[dbo].Fakename.longitude,
[dbo].StateProvince.StateProvinceName,
[dbo].Gender.GenderName
FROM 
[dbo].Fakename
join [dbo].StateProvince ON [dbo].Fakename.StateProvinceID =  [dbo].StateProvince.StateProvinceID
join [dbo].Gender ON [dbo].Fakename.GenderID =  [dbo].Gender.GenderID
GO
/****** Object:  ForeignKey [FK_Fakename_Gender]    Script Date: 03/07/2012 09:57:20 ******/
ALTER TABLE [dbo].[Fakename]  WITH CHECK ADD  CONSTRAINT [FK_Fakename_Gender] FOREIGN KEY([GenderID])
REFERENCES [dbo].[Gender] ([GenderID])
GO
ALTER TABLE [dbo].[Fakename] CHECK CONSTRAINT [FK_Fakename_Gender]
GO
/****** Object:  ForeignKey [FK_Fakename_StateProvince]    Script Date: 03/07/2012 09:57:20 ******/
ALTER TABLE [dbo].[Fakename]  WITH CHECK ADD  CONSTRAINT [FK_Fakename_StateProvince] FOREIGN KEY([StateProvinceID])
REFERENCES [dbo].[StateProvince] ([StateProvinceID])
GO
ALTER TABLE [dbo].[Fakename] CHECK CONSTRAINT [FK_Fakename_StateProvince]
GO

This is a search stored procedure which searches for multiple matches on a fakenames table, orders them and allows paging.

USE [TestDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[vw_Fakename_SelectFilter]
@Filter XML(ViewFilter),
@OrderBy varchar(50),
@OrderByDirection varchar(50),
@iPageSize int                  = 50,
@iPageNumber int                = 0
AS
SET NOCOUNT ON
--DECLARE ALL THE FIELDS WHICH CAN BE SEARCHED ON
DECLARE @FakeNameID INT
DECLARE @gender nvarchar(6)
DECLARE @givenname nvarchar(20)
DECLARE @middleinitial nvarchar(1)
DECLARE @surname nvarchar(23)
DECLARE @streetaddress nvarchar(100)
DECLARE @city nvarchar(100)
DECLARE @zipcode nvarchar(15)
DECLARE @country nvarchar(2)
DECLARE @emailaddress nvarchar(100)
DECLARE @password nvarchar(25)
DECLARE @telephonenumber nvarchar(25)
DECLARE @maidenname nvarchar(23)
DECLARE @cctype nvarchar(10)
DECLARE @ccnumber nvarchar(16)
DECLARE @cvv2 nvarchar(3)
DECLARE @ccexpires nvarchar(10)
DECLARE @nationalid nvarchar(15)
DECLARE @upstracking nvarchar(24)
DECLARE @occupation nvarchar(70)
DECLARE @company nvarchar(70)
DECLARE @domain nvarchar(70)
DECLARE @bloodtype nvarchar(3)
DECLARE @feetinches nvarchar(6)
DECLARE @guid nvarchar(36)
DECLARE @StateProvinceName varchar(50)
--If the @filter doesn't contain anything, then the proc takes like 20 seconds to run
If DATALENGTH(@Filter) <= 0
BEGIN
SET @Filter = ''
END 
SET @iPageNumber = @iPageNumber + 1
-------------------------------------
--PAGE COUNTING
DECLARE @iStartRecord int
DECLARE @iEndRecord int
SET @iStartRecord = (@iPageNumber - 1) * @iPageSize + 1
SET @iEndRecord = @iPageNumber * @iPageSize     
--SET THOSE VARIABLES
SELECT
@FakeNameID = (SELECT D.element.value('@Value','INT') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','NVARCHAR(256)') = 'FakeNameID'),
@gender = (SELECT D.element.value('@Value','nvarchar(6)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(6)') = 'gender'),
@givenname = (SELECT D.element.value('@Value','nvarchar(20)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(20)') = 'givenname'),
@middleinitial = (SELECT D.element.value('@Value','nvarchar(1)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(1)') = 'middleinitial'),
@surname = (SELECT D.element.value('@Value','nvarchar(23)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(23)') = 'surname'),
@streetaddress = (SELECT D.element.value('@Value','nvarchar(100)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(100)') = 'streetaddress'),
@city = (SELECT D.element.value('@Value','nvarchar(100)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(100)') = 'city'),
@zipcode = (SELECT D.element.value('@Value','nvarchar(15)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(15)') = 'zipcode'),
@country = (SELECT D.element.value('@Value','nvarchar(2)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(2)') = 'country'),
@emailaddress = (SELECT D.element.value('@Value','nvarchar(100)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(100)') = 'emailaddress'),
@password = (SELECT D.element.value('@Value','nvarchar(25)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(25)') = 'password'),
@telephonenumber = (SELECT D.element.value('@Value','nvarchar(25)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(25)') = 'telephonenumber'),
@maidenname = (SELECT D.element.value('@Value','nvarchar(23)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(23)') = 'maidenname'),
@cctype = (SELECT D.element.value('@Value','nvarchar(10)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(10)') = 'cctype'),
@ccnumber = (SELECT D.element.value('@Value','nvarchar(16)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(16)') = 'ccnumber'),
@cvv2 = (SELECT D.element.value('@Value','nvarchar(3)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(3)') = 'cvv2'),
@ccexpires = (SELECT D.element.value('@Value','nvarchar(10)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(10)') = 'ccexpires'),
@nationalid = (SELECT D.element.value('@Value','nvarchar(15)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(15)') = 'nationalid'),
@upstracking = (SELECT D.element.value('@Value','nvarchar(24)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(24)') = 'upstracking'),
@occupation = (SELECT D.element.value('@Value','nvarchar(70)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(70)') = 'occupation'),
@company = (SELECT D.element.value('@Value','nvarchar(70)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(70)') = 'company'),
@domain = (SELECT D.element.value('@Value','nvarchar(70)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(70)') = 'domain'),
@bloodtype = (SELECT D.element.value('@Value','nvarchar(3)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(3)') = 'bloodtype'),
@feetinches = (SELECT D.element.value('@Value','nvarchar(6)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(6)') = 'feetinches'),
@guid = (SELECT D.element.value('@Value','nvarchar(36)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','nvarchar(36)') = 'guid'),
@StateProvinceName = (SELECT D.element.value('@Value','varchar(50)') FROM @Filter.nodes('/ArrayOfDataFilter/DataFilter') AS D ( element ) WHERE D.element.value('@Column','varchar(50)') = 'StateProvinceName')
--CREATE THE TEMP TABLE
CREATE TABLE #Temp (
ROWCOUNTID int IDENTITY PRIMARY KEY,
PK  int
)
--MAGIC HERE
INSERT INTO #Temp 
SELECT  t1.FakeNameID as pk
FROM    dbo.vw_Fakename t1
WHERE   
CAST(FakeNameID as varchar(100)) LIKE CASE WHEN IsNull(@FakeNameID,-1)>0 THEN Cast(@FakeNameID as varchar(100)) ELSE '%' END    AND
gender LIKE CASE WHEN Len(IsNull(@gender,''))>0 THEN @gender ELSE '%' END AND
givenname LIKE CASE WHEN Len(IsNull(@givenname,''))>0 THEN @givenname ELSE '%' END AND
middleinitial LIKE CASE WHEN Len(IsNull(@middleinitial,''))>0 THEN @middleinitial ELSE '%' END AND
surname LIKE CASE WHEN Len(IsNull(@surname,''))>0 THEN @surname ELSE '%' END AND
streetaddress LIKE CASE WHEN Len(IsNull(@streetaddress,''))>0 THEN @streetaddress ELSE '%' END AND
city LIKE CASE WHEN Len(IsNull(@city,''))>0 THEN @city ELSE '%' END AND
zipcode LIKE CASE WHEN Len(IsNull(@zipcode,''))>0 THEN @zipcode ELSE '%' END AND
country LIKE CASE WHEN Len(IsNull(@country,''))>0 THEN @country ELSE '%' END AND
emailaddress LIKE CASE WHEN Len(IsNull(@emailaddress,''))>0 THEN @emailaddress ELSE '%' END AND
password LIKE CASE WHEN Len(IsNull(@password,''))>0 THEN @password ELSE '%' END AND
telephonenumber LIKE CASE WHEN Len(IsNull(@telephonenumber,''))>0 THEN @telephonenumber ELSE '%' END AND
maidenname LIKE CASE WHEN Len(IsNull(@maidenname,''))>0 THEN @maidenname ELSE '%' END AND
cctype LIKE CASE WHEN Len(IsNull(@cctype,''))>0 THEN @cctype ELSE '%' END AND
ccnumber LIKE CASE WHEN Len(IsNull(@ccnumber,''))>0 THEN @ccnumber ELSE '%' END AND
cvv2 LIKE CASE WHEN Len(IsNull(@cvv2,''))>0 THEN @cvv2 ELSE '%' END AND
ccexpires LIKE CASE WHEN Len(IsNull(@ccexpires,''))>0 THEN @ccexpires ELSE '%' END AND
nationalid LIKE CASE WHEN Len(IsNull(@nationalid,''))>0 THEN @nationalid ELSE '%' END AND
upstracking LIKE CASE WHEN Len(IsNull(@upstracking,''))>0 THEN @upstracking ELSE '%' END AND
occupation LIKE CASE WHEN Len(IsNull(@occupation,''))>0 THEN @occupation ELSE '%' END AND
company LIKE CASE WHEN Len(IsNull(@company,''))>0 THEN @company ELSE '%' END AND
domain LIKE CASE WHEN Len(IsNull(@domain,''))>0 THEN @domain ELSE '%' END AND
bloodtype LIKE CASE WHEN Len(IsNull(@bloodtype,''))>0 THEN @bloodtype ELSE '%' END AND
feetinches LIKE CASE WHEN Len(IsNull(@feetinches,''))>0 THEN @feetinches ELSE '%' END AND
guid LIKE CASE WHEN Len(IsNull(@guid,''))>0 THEN @guid ELSE '%' END AND
StateProvinceName LIKE CASE WHEN Len(IsNull(@StateProvinceName,''))>0 THEN @StateProvinceName ELSE '%' END
ORDER BY
CASE WHEN @OrderBy = 'FakeNameID' AND @OrderByDirection = 'asc' THEN FakeNameID END,
CASE WHEN @OrderBy = 'FakeNameID' AND @OrderByDirection = 'desc' THEN FakeNameID END DESC,
CASE WHEN @OrderBy = 'gender' AND @OrderByDirection = 'asc' THEN gender END,
CASE WHEN @OrderBy = 'gender' AND @OrderByDirection = 'desc' THEN gender END DESC,
CASE WHEN @OrderBy = 'givenname' AND @OrderByDirection = 'asc' THEN givenname END,
CASE WHEN @OrderBy = 'givenname' AND @OrderByDirection = 'desc' THEN givenname END DESC,
CASE WHEN @OrderBy = 'middleinitial' AND @OrderByDirection = 'asc' THEN middleinitial END,
CASE WHEN @OrderBy = 'middleinitial' AND @OrderByDirection = 'desc' THEN middleinitial END DESC,
CASE WHEN @OrderBy = 'surname' AND @OrderByDirection = 'asc' THEN surname END,
CASE WHEN @OrderBy = 'surname' AND @OrderByDirection = 'desc' THEN surname END DESC,
CASE WHEN @OrderBy = 'streetaddress' AND @OrderByDirection = 'asc' THEN streetaddress END,
CASE WHEN @OrderBy = 'streetaddress' AND @OrderByDirection = 'desc' THEN streetaddress END DESC,
CASE WHEN @OrderBy = 'city' AND @OrderByDirection = 'asc' THEN city END,
CASE WHEN @OrderBy = 'city' AND @OrderByDirection = 'desc' THEN city END DESC,
CASE WHEN @OrderBy = 'zipcode' AND @OrderByDirection = 'asc' THEN zipcode END,
CASE WHEN @OrderBy = 'zipcode' AND @OrderByDirection = 'desc' THEN zipcode END DESC,
CASE WHEN @OrderBy = 'country' AND @OrderByDirection = 'asc' THEN country END,
CASE WHEN @OrderBy = 'country' AND @OrderByDirection = 'desc' THEN country END DESC,
CASE WHEN @OrderBy = 'emailaddress' AND @OrderByDirection = 'asc' THEN emailaddress END,
CASE WHEN @OrderBy = 'emailaddress' AND @OrderByDirection = 'desc' THEN emailaddress END DESC,
CASE WHEN @OrderBy = 'password' AND @OrderByDirection = 'asc' THEN password END,
CASE WHEN @OrderBy = 'password' AND @OrderByDirection = 'desc' THEN password END DESC,
CASE WHEN @OrderBy = 'telephonenumber' AND @OrderByDirection = 'asc' THEN telephonenumber END,
CASE WHEN @OrderBy = 'telephonenumber' AND @OrderByDirection = 'desc' THEN telephonenumber END DESC,
CASE WHEN @OrderBy = 'maidenname' AND @OrderByDirection = 'asc' THEN maidenname END,
CASE WHEN @OrderBy = 'maidenname' AND @OrderByDirection = 'desc' THEN maidenname END DESC,
CASE WHEN @OrderBy = 'birthday' AND @OrderByDirection = 'asc' THEN birthday END,
CASE WHEN @OrderBy = 'birthday' AND @OrderByDirection = 'desc' THEN birthday END DESC,
CASE WHEN @OrderBy = 'cctype' AND @OrderByDirection = 'asc' THEN cctype END,
CASE WHEN @OrderBy = 'cctype' AND @OrderByDirection = 'desc' THEN cctype END DESC,
CASE WHEN @OrderBy = 'ccnumber' AND @OrderByDirection = 'asc' THEN ccnumber END,
CASE WHEN @OrderBy = 'ccnumber' AND @OrderByDirection = 'desc' THEN ccnumber END DESC,
CASE WHEN @OrderBy = 'cvv2' AND @OrderByDirection = 'asc' THEN cvv2 END,
CASE WHEN @OrderBy = 'cvv2' AND @OrderByDirection = 'desc' THEN cvv2 END DESC,
CASE WHEN @OrderBy = 'ccexpires' AND @OrderByDirection = 'asc' THEN ccexpires END,
CASE WHEN @OrderBy = 'ccexpires' AND @OrderByDirection = 'desc' THEN ccexpires END DESC,
CASE WHEN @OrderBy = 'nationalid' AND @OrderByDirection = 'asc' THEN nationalid END,
CASE WHEN @OrderBy = 'nationalid' AND @OrderByDirection = 'desc' THEN nationalid END DESC,
CASE WHEN @OrderBy = 'upstracking' AND @OrderByDirection = 'asc' THEN upstracking END,
CASE WHEN @OrderBy = 'upstracking' AND @OrderByDirection = 'desc' THEN upstracking END DESC,
CASE WHEN @OrderBy = 'occupation' AND @OrderByDirection = 'asc' THEN occupation END,
CASE WHEN @OrderBy = 'occupation' AND @OrderByDirection = 'desc' THEN occupation END DESC,
CASE WHEN @OrderBy = 'company' AND @OrderByDirection = 'asc' THEN company END,
CASE WHEN @OrderBy = 'company' AND @OrderByDirection = 'desc' THEN company END DESC,
CASE WHEN @OrderBy = 'domain' AND @OrderByDirection = 'asc' THEN domain END,
CASE WHEN @OrderBy = 'domain' AND @OrderByDirection = 'desc' THEN domain END DESC,
CASE WHEN @OrderBy = 'bloodtype' AND @OrderByDirection = 'asc' THEN bloodtype END,
CASE WHEN @OrderBy = 'bloodtype' AND @OrderByDirection = 'desc' THEN bloodtype END DESC,
CASE WHEN @OrderBy = 'pounds' AND @OrderByDirection = 'asc' THEN pounds END,
CASE WHEN @OrderBy = 'pounds' AND @OrderByDirection = 'desc' THEN pounds END DESC,
CASE WHEN @OrderBy = 'kilograms' AND @OrderByDirection = 'asc' THEN kilograms END,
CASE WHEN @OrderBy = 'kilograms' AND @OrderByDirection = 'desc' THEN kilograms END DESC,
CASE WHEN @OrderBy = 'feetinches' AND @OrderByDirection = 'asc' THEN feetinches END,
CASE WHEN @OrderBy = 'feetinches' AND @OrderByDirection = 'desc' THEN feetinches END DESC,
CASE WHEN @OrderBy = 'centimeters' AND @OrderByDirection = 'asc' THEN centimeters END,
CASE WHEN @OrderBy = 'centimeters' AND @OrderByDirection = 'desc' THEN centimeters END DESC,
CASE WHEN @OrderBy = 'guid' AND @OrderByDirection = 'asc' THEN guid END,
CASE WHEN @OrderBy = 'guid' AND @OrderByDirection = 'desc' THEN guid END DESC,
CASE WHEN @OrderBy = 'latitude' AND @OrderByDirection = 'asc' THEN latitude END,
CASE WHEN @OrderBy = 'latitude' AND @OrderByDirection = 'desc' THEN latitude END DESC,
CASE WHEN @OrderBy = 'longitude' AND @OrderByDirection = 'asc' THEN longitude END,
CASE WHEN @OrderBy = 'longitude' AND @OrderByDirection = 'desc' THEN longitude END DESC,
CASE WHEN @OrderBy = 'StateProvinceName' AND @OrderByDirection = 'asc' THEN StateProvinceName END,
CASE WHEN @OrderBy = 'StateProvinceName' AND @OrderByDirection = 'desc' THEN StateProvinceName END DESC,
CASE WHEN @OrderBy = 'GenderName' AND @OrderByDirection = 'asc' THEN GenderName END,
CASE WHEN @OrderBy = 'GenderName' AND @OrderByDirection = 'desc' THEN GenderName END DESC
DECLARE @TotalRecords decimal(10,5)
DECLARE @TotalNumberOfPages int
SET @TotalRecords = (SELECT COUNT(*) FROM #Temp)
SET @TotalNumberOfPages = CEILING(@TotalRecords/@iPageSize)
--FINAL SELECT
SELECT  t1.*,
@TotalNumberOfPages as TotalNumberOfPages
FROM    dbo.vw_Fakename t1
JOIN #temp t on t1.FakeNameID = t.pk
WHERE
t.ROWCOUNTID BETWEEN @iStartRecord AND @iEndRecord  
--DROP TEMP TABLE
DROP TABLE #temp

Usage

USE [TestDatabase]
GO
set statistics time on
DECLARE @return_value int
EXEC    @return_value = [dbo].[vw_Fakename_SelectFilter]
@Filter = N'



',
@OrderBy = N'surname',
@OrderByDirection = N'asc',
@iPageSize = 50,
@iPageNumber = 0
GO

I have been using a list from fakenamegenerator on 50,000 random names and having been getting statistics of (blank entries removed):

SQL Server Execution Times:
CPU time = 46 ms,  elapsed time = 37 ms.
SQL Server Execution Times:
CPU time = 47 ms,  elapsed time = 282 ms.
SQL Server Execution Times:
CPU time = 93 ms,  elapsed time = 499 ms.

So the question I have is this: Is there a better/faster way to achieve the same results?

Many thanks! Adam

xSQL Software · Accepted Answer

Adam – the stats are hard to compare as they depend on the hardware configuration and the load on the system at the time of testing.

However, regardless of those execution stats, if you try to get the job done in one generic query as you have done there is no escaping a clustered index scan. Consequently, no matter what the filter looks like the results will be similar – you will see fluctuations in execution tine depending on whether none, some or all of the pages of that table are already in cache and of course depending on what else might be going on at that time but, the amount of work that SQL Server will need to do is the same (compared to scanning the table all the other tasks are insignificant. The sorting of the results may make a difference too but I will leave that out of this discussion for now).

What might make sense to do here is to identify the most common filters and optimize the table/stored procedure for those filters, whereas the rest of the cases will fall on the generic query which requires scanning. For example, let's just say that in 90% of the cases the filter will contain a value for the "givenname" and for the "surname". Let's further assume that in your actual table you know that "surname" is more selective than the "givenname". In that case you create an index on (surname, givenname) and then instead of one generic query you "branch" into two queries:

If the filter contains values for surname and givenname then the query that will be executed will look like: SELECT... FROM... WHERE surname = @surname AND givenname="givenname" AND...

Otherwise you execute the generic query that takes care of everything else.

When your "common scenario" query is executed the optimizer will likely do an index seek which will be significantly faster than the clustered index scan (the more rows you have on that table the larger will be the difference). This way you have taken care of 90% of the cases and you don’t have to worry too much about the other 10%.

Stored Procedure to search a view

Answers (1)

Related Questions