Fastest String search

1 Dec 2006 3:36 AM

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

1 Dec 2006 4:22 AM

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?

Show quoteHide quote

"beersa" <sabeer.sab***@gmail.com> wrote in message
news:1164944186.467570.201090@n67g2000cwd.googlegroups.com...
> 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
>

1 Dec 2006 4:59 AM

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

1 Dec 2006 6:01 AM

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.

Show quoteHide quote

"beersa" <sabeer.sab***@gmail.com> wrote in message
news:1164949161.363729.174410@f1g2000cwa.googlegroups.com...
> 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
>

1 Dec 2006 4:59 AM

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

1 Dec 2006 7:05 AM

ShaneO

beersa wrote:
Show quoteHide quote

> 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.