Post reply

Searching a database...

  • 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

  • July 26, 2006 at 7:36 am

    #651617

    Hello,

    have you created an indexed for that column ?

  • July 26, 2006 at 7:45 am

    #651621

    Yup, I've done that.

    Thanks

  • July 26, 2006 at 10:58 am

    #651713

    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/

  • July 27, 2006 at 12:20 am

    #651793

    hi colin,

    how do I write a query like that?

    each value is unique.

    the columns are nchar(10)

    thanks,

  • July 27, 2006 at 1:03 pm

    #651984

    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.

     

  • July 27, 2006 at 2:48 pm

    #652029

    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/

  • August 3, 2006 at 1:13 am

    #653003

    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

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

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