Is there a way to improve this stored procedure?
It is basically a search for titles. We have a title and subtitle column
in the database. This stored procedure takes over 60 seconds to run, while
other queries to the database take less than a second. I noticed in the
database design, that title and subtitle are designed at nvarchar (255).
Database - SQL Server 2000 on Windows 2000 with 4 Xeons and 2GB Ram (The
database itself it about 350MB, about 50,000 rows)
Servers - Windows 2000 with Website Professional 3.0 and CF5. 2 Xeon 2.8
GHz and 2 GB Ram.
Thanks is advance.
FYI - I am using * in the select because we output all the colunms.
CREATE PROC GeneralDVDMovies_Title
@searchTitle varchar(50),
@searchTitle2 varchar(50),
@searchTitle3 varchar(50)
AS
IF( @searchTitle = @searchTitle2 AND @searchTitle2 = @searchTitle3)
SELECT TOP 550 *
FROM GeneralDVD
WHERE (title like @searchTitle or subtitle like @searchTitle)
AND url IS NOT NULL AND (BackOrder NOT LIKE '%HR%' OR BackOrder IS NULL)
Order by title
ELSE
SELECT TOP 550 *
FROM GeneralDVD
WHERE (title like @searchTitle or title like @searchTitle2 or title like
@searchTitle3 or subtitle like @searchTitle or subtitle like @searchTitle2
or subtitle like @searchTitle3)
AND url IS NOT NULL AND (BackOrder NOT LIKE '%HR%' OR BackOrder IS NULL)
Order by title
GO
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
- Re: Improving stored procedure Jacob
- Re: Improving stored procedure Nick de Voil
- RE: Improving stored procedure Philip Arnold
- RE: Improving stored procedure Jacob
- RE: Improving stored procedure Philip Arnold
- RE: Improving stored procedure Plunkett, Matt
- Re: Improving stored procedure Nick de Voil
- RE: Improving stored procedure Plunkett, Matt