Google Style Optimized Search of a Database

[MarillionFan]

This is an optimization style question.

I have a Dun and Bradstreet database circa 55M records. The request is that users need to look for a Company name. The company name is presently searched for using a wildcard search for example

Where Company Like '%Smiths%'

The problem is, this will do a row by row search and takes sometime.

Do this in Google for example, the return is blurringingly fast.

How can I optimize/write something to return records from a database using a wildcard, but at the same speed as something like a Google Search.

Need an optimization guru here? Atw?

[DimPrawn]

I'm not sure this is a job for a Crystal Reports / MS Access / Shopkeeper type person MF.



What database is the data currently living on? Is it MS SQL Server 2005 (if not, it should be)?

If so, start looking here http://msdn.microsoft.com/en-us/library/ms345119.aspx

Or pay AtW 10 Roubles a day to develop you a SKA.

[PerlOfWisdom]

You need to set up an indexing table with values like:

John Smith
ohn Smith
hn Smith
n Smith
Smith
Smith
mith
ith
th

This allows indexed searches as there is no wildcard at the begining of the string.

[DimPrawn]

Originally Posted by PerlOfWisdom

You need to set up an indexing table with values like:

John Smith
ohn Smith
hn Smith
n Smith
Smith
Smith
mith
ith
th

This allows indexed searches as there is no wildcard at the begining of the string.

Wow, that's a great idea! I wonder how big the database will be if you do that for every word in every column across 55,000,000 rows?

[oracleslave]

Originally Posted by MarillionFan

This is an optimization style question.

I have a Dun and Bradstreet database circa 55M records. The request is that users need to look for a Company name. The company name is presently searched for using a wildcard search for example

Where Company Like '%Smiths%'

What is the front-end used to run the search? Oracle?

[MarillionFan]

Shame, DimPrawn is poo pooing that idea above, shows he only has limited experience

Yes it's an Oracle 10 Database.

There appears to be some Oracle functionality that does seem to create an index of all combinations(as suggested above) based around 'Oracle Text'. From reading the article, it can also be designed to use a fuzzy logic match.

The only problem appears to be if the index is greater than the actual original column, but then again an index using an equal would be quicker than a wildcard anyway

Is this a good method?




http://209.85.173.104/search?q=cache...lnk&cd=1&gl=uk

[DimPrawn]

Originally Posted by MarillionFan

Shame, DimPrawn is poo pooing that idea above, shows he only has limited experience

Yes it's an Oracle 10 Database.

There appears to be some Oracle functionality that does seem to create an index of all combinations(as suggested above) based around 'Oracle Text'. From reading the article, it can also be designed to use a fuzzy logic match.

The only problem appears to be if the index is greater than the actual original column, but then again an index using an equal would be quicker than a wildcard anyway

Is this a good method?




http://209.85.173.104/search?q=cache...lnk&cd=1&gl=uk

No, you carry on mate. Be interesting to see you create your own "google" style index on 55M rows rather than use a highly optimised and sophisticated tool designed for the job such as Full-Text Index on SQL Server.

[oracleslave]

Originally Posted by MarillionFan

Shame, DimPrawn is poo pooing that idea above, shows he only has limited experience

Yes it's an Oracle 10 Database.

There appears to be some Oracle functionality that does seem to create an index of all combinations(as suggested above) based around 'Oracle Text'. From reading the article, it can also be designed to use a fuzzy logic match.

The only problem appears to be if the index is greater than the actual original column, but then again an index using an equal would be quicker than a wildcard anyway

Is this a good method?




http://209.85.173.104/search?q=cache...lnk&cd=1&gl=uk

I have no idea as I have next to zero technical capability. Reason I asked the question regarding the front end is I know there is standard functionality in oracle apps that does the exact searches you mentioned i.e. retrieves customers via wildcard, indexed and fuzzy searches. As there are also API's, pre-built interface to D&B I figured you may be able to look at how oracle have already done it as a starter for 10.

[scooterscot]

Originally Posted by MarillionFan

This is an optimization style question.

I have a Dun and Bradstreet database circa 55M records. The request is that users need to look for a Company name. The company name is presently searched for using a wildcard search for example

Where Company Like '%Smiths%'

The problem is, this will do a row by row search and takes sometime.

Do this in Google for example, the return is blurringingly fast.

How can I optimize/write something to return records from a database using a wildcard, but at the same speed as something like a Google Search.

Need an optimization guru here? Atw?

Optimisation - studied and wrote about this during my studies in operational research. Studied the method employed by google, page ranking, could provide paper to you if interested.

[PerlOfWisdom]

Originally Posted by DimPrawn

Wow, that's a great idea! I wonder how big the database will be if you do that for every word in every column across 55,000,000 rows?

Probably bigger than any database you've seen.