Thread: How do I retreive text very QUICKLY from the database ? . . .
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
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
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