Searching a database...

Question

Post reply

Searching a database...

thehumantrashcan

Newbie

Points: 3
More actions
July 26, 2006 at 6:06 am

#169424

Hi,
This is the first database I have ever created, so please bear with me.
I've created a simple database with 1 column and about 80,000 rows. In each row is a word (basically a dictionary without definitions).
I have written a query which works, and is, as follows (you'll notice that i'm not the most original of people)
SELECT
word
FROM dbo.words
WHERE word= 'hello'
This finds the word hello.
In excel I have a row with 25 letters and then a column with every single combination of letters from 3 to 10 lettered words. (It makes sense to me!)
This comes back with a lot of possibilities (thousands), but is great in the sense that when I change any of the 25 letters the entire column automatically updates.
What I am trying to do is then take all of these possibilities and compare them against the dictionary.
I have written a line in excel which automatically creates a cell a bit like this, for the first couple of thousand possibilities:
WHERE word= 'abc' or word= 'fgm' or word= 'klm' or word= 'pqr' or word= 'uvw' or word= 'bcd' or word= 'ghi' or word= 'lmn' or word= 'qrs' or word= 'vwx'
I then whack this into the query from above and off it goes. The only problem is that the search takes ages, and because of limitations in excel I can't put more than a thousand or so words in the cell.
I am certain there is a faster way of searching through all the possibilities, any help would be much appreciated.
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael Senft Valued Member Points: 57 More actions · Answer 1

Hello,

have you created an indexed for that column ?

thehumantrashcan Newbie Points: 3 More actions · Answer 2

thehumantrashcan

Newbie

Points: 3

July 26, 2006 at 7:45 am

#651621

Yup, I've done that.

Thanks

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

have a look at the query plan, with an index I would have thought maybe 3 i/o per OR statement .. even though it's one column it should work as an index - I assume each value is unique? so make your index clustered. it's such a tiny table, well I assume so - what is the column def. Whole table should be in less than 50 pages.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

thehumantrashcan Newbie Points: 3 More actions · Answer 4

hi colin,

how do I write a query like that?

each value is unique.

the columns are nchar(10)

thanks,

Bill Scott SSCrazy Points: 2338 More actions · Answer 5

it sounds like you are looking for all the words that contain 'Hello' if thats the case change your where clause to look like

WHERE word like '%' + 'hello' + '%'

this will give you a list of all the words that have the string 'hello' in them somewhere.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

aarghhh ! Sorry Bill but very bad idea, a leading % will force a table scan for each sarg .. very expensive.

so i/o counts .. I figure about 1.5k for a table scan.

I think your problem could be the nchar, try changing it to a nvarchar, might work.

Each section of your multi-search statement should do an index seek, check the plan to make sure.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Danster SSCommitted Points: 1632 More actions · Answer 7

I might be barking up the wrong tree, but I think thehumantrashcan needs something like

SELECT word FROM dbo.Words WHERE word IN ('abc','fgm','klm','pqr' etc etc)

Not sure what the speed on that will be but if the word column is indexed, it should be fast-ish. The IN clause isn't limited to any no. of characters except as much as the query window can take.

cheers

Danster