Thread: How do I retreive text very QUICKLY from the database ? . . .

How do I retreive text very QUICKLY from the database ? . . .

From
"Peter E. Chen"
Date:
Hey All,

I am trying to find a way to retreive character data from a table very
quickly.  My data is a large string (about 250 MB worth of text) of DNA
sequence.  I am currently storing the data as a Postgres "text" datatype.
The character data is in its own table and occupies one row and one column.

I would like to retreive a substring from this very long string.  I have
tried the built-in Postgres string functions to get a substring out from the
table, but it is too slow.  Is there a very fast way to retreive substrings
of character data from the database?

Does changing the datatype help (instead of using the "text" datatype)?

Thank you for any help.

Sincerely,
Peter


Re: How do I retreive text very QUICKLY from the database

From
Neil Conway
Date:
On Thu, 2002-01-24 at 11:42, Peter E. Chen wrote:
> Hey All,
>
> I am trying to find a way to retreive character data from a table very
> quickly.  My data is a large string (about 250 MB worth of text) of DNA
> sequence.  I am currently storing the data as a Postgres "text" datatype.
> The character data is in its own table and occupies one row and one column.

That sounds like a bad candidate for storage in an RDBMS. If you need a
lot of speed, perhaps it would be better to store it on the file system
(reading using mmap() perhaps?), in a DBM-style database, or in shared
memory. If necessary, you could then store the access info for this
storage (e.g. the filename) in the RDBMS.

Alternatively, try using PostgreSQL's large objects support. I don't
know if it would be any faster though...

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


Re: How do I retreive text very QUICKLY from the database

From
John Gray
Date:
On Thu, 2002-01-24 at 16:42, Peter E. Chen wrote:
> Hey All,
>
> I am trying to find a way to retreive character data from a table very
> quickly.  My data is a large string (about 250 MB worth of text) of DNA
> sequence.  I am currently storing the data as a Postgres "text" datatype.
> The character data is in its own table and occupies one row and one column.
>
> I would like to retreive a substring from this very long string.  I have
> tried the built-in Postgres string functions to get a substring out from the
> table, but it is too slow.  Is there a very fast way to retreive substrings
> of character data from the database?
>

I have a patch (hopefully to be in 7.3) which would provide faster
substring access in those cases, so it is possible. It is maintained
against current CVS, and I can provide it to you if you are interested.

Regards

John