Help with avoiding a server meltdown please? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with avoiding a server meltdown please?

Author

Topic

CalmerOceans
Starting Member

1 Post

Posted - 2009-08-07 : 12:12:32

I am reviewing code that a co-worker is claiming "Ready for Production" and from what I see putting this online will shutdown our severs and I’m wondering if someone can help me convince them to refactor the base code before putting this online.

Here is the situation:

We are trying to mimic Google’s Search Suggestions which is an AJAX call functioned on a javascript onkeyup function. So in short this is going to get hammered with lots of traffic.

I have placed code for the current table and the stored procedure and added some sample data that they are intending to use from a web interface and I think this is not as optimized as it should be. I think it should be fairly easy to follow but if you have questions please ask.

Can I get some opinions on how I should proceed please? BTW, I see the clustered index in the wrong place but they insist this is ready to go not to mention the 2 scans on the table from the SP. Suggestions would be very helpful.

The Table


CREATE TABLE [dbo].[en](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Key] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Keyword] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_en] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

The Stored Proc


Create PROCEDURE [dbo].[GetSuggestions] 
	@firstSeach varchar(50) ,
	@secondSearch varchar(50) = ''
AS
BEGIN
	SET NOCOUNT ON;

    IF EXISTS(SELECT 1 FROM dbo.en WHERE [Key] = @firstSeach)
		BEGIN
			SELECT TOP 10 KEYWORD
			FROM dbo.en
			WHERE [Key] = @firstSeach;
		END;
	ELSE
		BEGIN
			SELECT TOP 10 KEYWORD
			FROM dbo.en
			WHERE [Key] = @secondSearch;
		END;	
END

Sample Data Select top 50 *


ID          Key                  Keyword
----------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           A                    amazon
2           A                    american idol
3           A                    aol
5           A                    american airlines
6           A                    addicting games
7           A                    abc
8           A                    autotrader
9           A                    american express
10          A                    apple
261         AA                   aaa
262         AA                   aa.com
263         AA                   aarp
264         AA                   aafes
265         AA                   aarons
266         AA                   aaa discounts
267         AA                   aaron brothers
268         AA                   aaliyah
269         AA                   aaa travel
270         AA                   aaron schock
7019        AAA                  aaa discounts
7020        AAA                  aaa.com
7021        AAA                  aaa travel
7022        AAA                  aaa california
7023        AAA                  aaamath
7024        AAA                  aaa maps
7025        AAA                  aaas
7026        AAA                  aaa cooper
7027        AAA                  aaa texas
7028        AAA                  aaa carolinas
181076      AAAA                 aaa insurance
181077      AAAA                 aaaa battery
181078      AAAA                 aaaa driving school
181079      AAAA                 aaaa storage
181080      AAAA                 aaaa swimming
181081      AAAA                 aaaa.org
181082      AAAA                 aaaai
181083      AAAA                 aaaai 2009
181084      AAAA                 aaaamon.dll
181085      AAAA                 aaaasf
181086      AAAB                 aaa backgrounds
181087      AAAB                 aaa restaurant equipment
181088      AAAB                 aaaba
181089      AAAB                 aaabiz.com
181090      AAAB                 aaable auto insurance
181091      AAAB                 aaabodybuilding.com
181092      AAAB                 aaabor.com
181093      AAAB                 aaabrasives
181094      AAAB                 abor
181095      AAAC                 aaa carolinas
181096      AAAC                 aaace

(50 row(s) affected)

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-08-07 : 12:59:22

Hmm... I don't get the purpose of the @secondSearch parameter. The only thing I can think of would be better placed in the javascript code. If not for that, I'd say slap an index on the [Key] column and use a TOP 10 LIKE query; All things considered that should be fairly quick.

Make sure they're escaping the inputs! This sort of thing can be very vulnerable to a SQL Injection attack.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-07 : 14:51:28

As far as a server meltdown, it's unlikley, you'd be suprised at the workload a good server can handle, but I do not think the methodolgy is not correct in your post.

The way I would continue is the following.

1. Everytime someone searches for a word/term you add it to the database
- Create a table that has the following fields
1.SearchID int identity(1,1)
2.SearchTerm varchar(300)
3.ModifiedSearchTerm varchar(300)
4.NumberOfSearches int)

NOTE: Use the ModifiedSearchterm field to hold the formated value (i.e. if I was searching for "THIS IS MY SEARCH", I would actualy store that phrase in the Search term field, but in the modifiedfield I would store "THISISMYSEARCH".

2. When a search is done check that table for the term. If it exists update the count to +1. If not add the term and mark the count at 1.

3. Everytime someone presses a key then access the database for the top 10 search terms matching the pattern (Remove spaces.


Create proc GetSearchTermsByKeyStrokes
@KeyStrokes varchar(300)
as
Select Top 10 SearchTerm
from
MySearchTable a
where a.ModifiedSearchTerm like @KeyStrokes
order by a.NumberofSearches desc,SearchTearm asc
go

From your front end app you would just call the sp for each key stroke sending the entire keys pressed followed by a % (Properly manipulate the data on the front end b4 passing to the procedure. So if I typed "This is my Search", I would actually be sending

Exec GetSearchTermsByKeyStrokes 'Thisismysearch%'

MAKE SURE YOU PROPERLY INDEX THE TABLE.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources