Improve Searches

Question

Post reply

Improve Searches

kaladharreddy15

SSC-Addicted

Points: 476
More actions
March 9, 2010 at 10:03 pm

#141883

If the table customer had millions of rows, what would you do to improve searches based on last name ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GTR SSCrazy Eights Points: 8007 More actions · Answer 1

Create index, Use DTA to check which index will improve performance.

EnjoY!

Jeff Moden SSC Guru Points: 1003927 More actions · Answer 2

I agree... depending on what the search criteria is, an index can help a lot.

A word of caution on DTA... it can and will frequently lead you down the primrose path. Don't just blindly take it's recommendations because it's frequently wrong especially when you have a column with very low cardinality in your query. Although it may give you and index to make your SELECTs lightning fast, that same index can eat the face off your database for INSERTs and UPDATES when the index splits to accomodate new rows. We had such an index on one of our system... it caused 30,000 reads for each insert.

Be very wary of DTA recommendations without full load testing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 3

kaladharreddy15 (3/9/2010)
If the table customer had millions of rows, what would you do to improve searches based on last name ?

It depends on the searches. Full-text index maybe? Add a computed column based on a hash function and index that? As I say, it depends.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mstjean Hall of Fame Points: 3310 More actions · Answer 4

Jeff Moden (3/10/2010)
...A word of caution on DTA...Although it may give you an index to make your SELECTs lightning fast, that same index can eat the face off your database for INSERTs and UPDATES...

Heh... You've GOTTA add that to your sig...

Cursors are useful if you don't know SQL