Improving stored procedure
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%' ORBackOrder 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%' ORBackOrder IS NULL) Order by title GO [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Improving stored procedure
BackOrder NOT LIKE '%HR%'could be causing you problems even if that field is indexed, because you're not looking at the beginning of the field. Is there anothere way of doing it? For the title fields also, have you considered full text indexing? Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Improving stored procedure
-Original Message- From: Nick de Voil Sent: Friday, February 27, 2004 9:56 AM To: CF-Talk Subject: Re: Improving stored procedure BackOrder NOT LIKE '%HR%'could be causing you problems even if that field is indexed, because you're not looking at the beginning of the field. Is there anothere way of doing it? Just curious, cause I don't know Sql Server... In oracle, those null checks on URL and BackOrder would also cause problems because they would negate any indexes on those fields.Is that the case in Sql Server? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Improving stored procedure
At 06:55 AM 2/27/2004, you wrote: BackOrder NOT LIKE '%HR%'could be causing you problems even if that field is indexed, because you're not looking at the beginning of the field. Is there anothere way of doing it? I can remove it for the time being.Later create a bit column for HR. For the title fields also, have you considered full text indexing? Considering it now... Nick -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Improving stored procedure
Just curious, cause I don't know Sql Server... In oracle, those null checks on URL and BackOrder would also cause problems because they would negate any indexes on those fields.Is that the case in Sql Server? I don't think they would make the index unusable, but obviously IS NULL and IS NOT NULL aren't going to narrow things down much. Nick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Improving stored procedure
From: Jacob 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). Use Free Text rather than Like - it's TONS faster [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Improving stored procedure
Free Text?Not quite sure what you mean. :D At 07:20 AM 2/27/2004, you wrote: From: Jacob 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). Use Free Text rather than Like - it's TONS faster -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Improving stored procedure
From: Jacob Free Text?Not quite sure what you mean. :D Sorry, meant Full Text, just typing too fast for my own good Full Text Indexing makes a HUGE difference on large tables [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Improving stored procedure
Okay.Even searched google for free text I am already playing with full text search.Going to give it a shot. Thanks Philip and Nick for you help. Jacob At 08:00 AM 2/27/2004, you wrote: From: Jacob Free Text?Not quite sure what you mean. :D Sorry, meant Full Text, just typing too fast for my own good Full Text Indexing makes a HUGE difference on large tables -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Improving stored procedure
Okay.. one last question.. hopefully... How would i do something like this? SELECT* FROM DVD WHERE title like '%music%' or subtitle like '%music%' I tried... SELECT* FROM DVD WHERE contains (title, 'music') or contains (subtitle, 'music') So I want to search title and subtitle for music.If I search only title or only subtitle by itself, it pulls the information quickly.If I use the two together, it take a lng time. Thanks Jacob At 08:00 AM 2/27/2004, you wrote: From: Jacob Free Text?Not quite sure what you mean. :D Sorry, meant Full Text, just typing too fast for my own good Full Text Indexing makes a HUGE difference on large tables -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Improving stored procedure
-Original Message- From: Jacob [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 11:44 AM To: CF-Talk Subject: RE: Improving stored procedure So I want to search title and subtitle for music.If I search only title or only subtitle by itself, it pulls the information quickly.If I use the two together, it take a lng time. Try a union then?Instead of combining the two text searches in an or, try something like: SELECT * FROM DVD WHERE contains(title, 'music') UNION SELECT * FROM DVD WHERE contains(subtitle, 'music'); [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Improving stored procedure
I think I have found the problem.When I select a certain column, it takes a long time.Think that is where I have to start looking. ;-) Thanks.I will try union also. At 08:56 AM 2/27/2004, you wrote: -Original Message- From: Jacob [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 11:44 AM To: CF-Talk Subject: RE: Improving stored procedure So I want to search title and subtitle for music.If I search only title or only subtitle by itself, it pulls the information quickly.If I use the two together, it take a lng time. Try a union then?Instead of combining the two text searches in an or, try something like: SELECT * FROM DVD WHERE contains(title, 'music') UNION SELECT * FROM DVD WHERE contains(subtitle, 'music'); -- [ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
