Fastest String search

[beersa]

Hi All,

I have to query the database with the string from text file. Here are
the details:

OS: WinXP Home Pro
DB: Oracle 9.x

The table in DB has 20,000 rows. The text file has 15,000 rows. I wrote
a program and read the string from text file and run SELECT query in DB
to look for the string. The program read line by line. That mean, For
each line the SELECT query is running, i.e. 15,000 times.

I use Streamreader class to read the text file and OracleDataReader
class to query the database.

Can anyone suggest me the better approach or design?

Thanks & Regards,
BeerSa

 

[Tom Leylan]

I don't think there are enough details in the message to know.

Are you doing this one time? Once a day?

Do the contents of the text file change? Are more lines appended to it or
do you get an entirely new file?

Does it contain words or phrases? Are you looking for exact matches or
substring searches?

Can you import the text file into a table and perform a SQL query against
the table?

What do you need out of the process? Another text file, a report, an
updated DB?

And finally, is there a business requirement that limits the amount of time
it can take and/or the format of the DB and text files?

 

[beersa]

Thanks Tom for your prompt response.

I would like to give you more details on this.

Are you doing this one time? Once a day?

Few times only. For some data mapping tasks.

Do the contents of the text file change? Are more lines appended to it or
do you get an entirely new file?

The text file is fixed-size word length and it is entirely new file.

Does it contain words or phrases? Are you looking for exact matches or
substring searches?

It is fixed size words. Looking for exact matches in database. So, the
search string from the text file.

Can you import the text file into a table and perform a SQL query against
the table?

What do you need out of the process? Another text file, a report, an
updated DB?

Output is text file.

And finally, is there a business requirement that limits the amount of time
it can take and/or the format of the DB and text files?

There is no Business requirements .

Hope you can assist me.

Regards,
Sabeer

 

[beersa]

Thanks Tom for your prompt response.

I would like to give you more details on this.

Are you doing this one time? Once a day?

Few times only. For some data mapping tasks.

Do the contents of the text file change? Are more lines appended to it or
do you get an entirely new file?

The text file is fixed-size word length and it is entirely new file.

Does it contain words or phrases? Are you looking for exact matches or
substring searches?

It is fixed size words. Looking for exact matches in database. So, the
search string from the text file.

Can you import the text file into a table and perform a SQL query against
the table?

What do you need out of the process? Another text file, a report, an
updated DB?

Output is text file.

And finally, is there a business requirement that limits the amount of time
it can take and/or the format of the DB and text files?

There is no Business requirements .

Hope you can assist me.

Regards,
BeerSa

 

[Tom Leylan]

If you only need to do it a couple of times and you the DB table is words
I'd tend to import each (if there are a few) of the text files into their
own DB table. At that point you should be able to run a single SQL Select
to match them up.

If you only had to run it once and you had no other way I'd say just process
it the way you have it and if it took a few hours it wouldn't be the end of
the world. On the other hand if you can save 50% of the time (or more) by
importing the file first and that amounts to 30 minutes or more I'd make the
extra effort.

 

[ShaneO]

Hi All,

I have to query the database with the string from text file. Here are
the details:

OS: WinXP Home Pro
DB: Oracle 9.x

The table in DB has 20,000 rows. The text file has 15,000 rows. I wrote
a program and read the string from text file and run SELECT query in DB
to look for the string. The program read line by line. That mean, For
each line the SELECT query is running, i.e. 15,000 times.

I use Streamreader class to read the text file and OracleDataReader
class to query the database.

Can anyone suggest me the better approach or design?

Thanks & Regards,
BeerSa

Beersa, at the risk of starting a barrage of complaints regarding
Portability/Compatibility and cries of "It's just not done that way",
have you ever thought of Rolling Your Own? By this I mean writing your
own Binary File Access method and reading the Oracle database records
without using any database engine.

If you're looking for speed then nothing will beat it!

I've often done this for clients who want VERY, VERY fast access to
information embedded in large databases. Using this type of method will
open the opportunity of reading, and scanning, tens-of-thousands of
records per second and locating the text you require faster than
anything you've probably ever seen before.

Just my thoughts.... You did ask for the "Fastest String Search"!!!

ShaneO

There are 10 kinds of people - Those who understand Binary and those who
don't.