Re: [sqlite] Optimizing performance by moving large texts into a separate table
[Default] On Thu, 8 Nov 2007 00:50:47 +0300, "Igor Sereda" <[EMAIL PROTECTED]> wrote: >Thank you! How about separate DB just for large texts? >Would that be an overkill? I think it would be overkill indeed. I also think it's better to have 'too large' pages for the 'numeric table' than to have too small pages for the 'text table'. >We could use different page sizes for the two DBs. I'm not sure >how well transactions over several DBs are handled though. The problem with using separate databases is that you can't force referential integrity and cascading updates and deletes (using triggers) between the 'numeric table' and the 'text table'. >Best regards, >Igor HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing performance by moving large texts into a separate table
"Igor Sereda" <[EMAIL PROTECTED]> wrote: > Thank you! How about separate DB just for large texts? Would that be an > overkill? We could use different page sizes for the two DBs. I'm not sure > how well transactions over several DBs are handled though. > You could do that, but the benefits are dubious. Transactions across multiple databases are atomic, but quite a bit more disk I/O is involved in making them so. I personally just put BLOBs in a separate table. That seems to be sufficient. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Optimizing performance by moving large texts into a separate table
Thank you! How about separate DB just for large texts? Would that be an overkill? We could use different page sizes for the two DBs. I'm not sure how well transactions over several DBs are handled though. Best regards, Igor -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, November 08, 2007 12:30 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Optimizing performance by moving large texts into a separate table "Igor Sereda" <[EMAIL PROTECTED]> wrote: > We have a database that can possibly grow into millions of rows. Some > = tables have TEXT fields, which may store texts of signigicant > length. = All other data is mostly numeric values. > > We have a thought of moving all large texts into a separate table, and > = replacing text_column with text_id in the rest of the schema. The = > assumption is that db pages are allocated fully to a single table, so > = the numerical part of the database will end up in a few db pages and > so = we'll be able to quickly run queries over them. (We won't have > queries = for texts, only look-ups by text_id.) > > Is our assumption correct? Is that a pattern someone here has = > implemented maybe? How does the size of the whole database affect = > queries to a single table? > This is a good assumption. Keeping large CLOBs and BLOBs in a separate table and referencing them by rowid is what I do. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing performance by moving large texts into a separate table
[EMAIL PROTECTED] wrote: "Igor Sereda" <[EMAIL PROTECTED]> wrote: We have a database that can possibly grow into millions of rows. Some = tables have TEXT fields, which may store texts of signigicant length. = All other data is mostly numeric values. We have a thought of moving all large texts into a separate table, and = replacing text_column with text_id in the rest of the schema. The = assumption is that db pages are allocated fully to a single table, so = the numerical part of the database will end up in a few db pages and so = we'll be able to quickly run queries over them. (We won't have queries = for texts, only look-ups by text_id.) Is our assumption correct? Is that a pattern someone here has = implemented maybe? How does the size of the whole database affect = queries to a single table? This is a good assumption. Keeping large CLOBs and BLOBs in a separate table and referencing them by rowid is what I do. I've had good luck with putting large texts into their own files, and storing the file names in the tables. But that precludes searches within the texts themselves. My applications didn't need to do that. b.g. -- Bill Gatliff [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing performance by moving large texts into a separate table
"Igor Sereda" <[EMAIL PROTECTED]> wrote: > We have a database that can possibly grow into millions of rows. Some = > tables have TEXT fields, which may store texts of signigicant length. = > All other data is mostly numeric values. > > We have a thought of moving all large texts into a separate table, and = > replacing text_column with text_id in the rest of the schema. The = > assumption is that db pages are allocated fully to a single table, so = > the numerical part of the database will end up in a few db pages and so = > we'll be able to quickly run queries over them. (We won't have queries = > for texts, only look-ups by text_id.) > > Is our assumption correct? Is that a pattern someone here has = > implemented maybe? How does the size of the whole database affect = > queries to a single table? > This is a good assumption. Keeping large CLOBs and BLOBs in a separate table and referencing them by rowid is what I do. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
