Fastest String search

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • beersa
    #1

    Fastest String search

    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 OracleDataReade r
    class to query the database.

    Can anyone suggest me the better approach or design?

    Thanks & Regards,
    BeerSa

    Tags: None
    • Tom Leylan
      #2
      Re: Fastest String search

      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" <sabeer.sabeer@ gmail.comwrote in message
      news:1164944186 .467570.201090@ n67g2000cwd.goo glegroups.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 OracleDataReade r
      class to query the database.
      >
      Can anyone suggest me the better approach or design?
      >
      Thanks & Regards,
      BeerSa
      >

        Comment

        • beersa
          #3
          Re: Fastest String search

          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

            Comment

            • beersa
              #4
              Re: Fastest String search

              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

                Comment

                • Tom Leylan
                  #5
                  Re: Fastest String search

                  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.


                  "beersa" <sabeer.sabeer@ gmail.comwrote in message
                  news:1164949161 .363729.174410@ f1g2000cwa.goog legroups.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
                  >

                    Comment

                    • ShaneO
                      #6
                      Re: Fastest String search

                      beersa wrote:
                      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 OracleDataReade r
                      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.

                        Comment

                        Previous template Next
                        Working...