[firebird-support] Request for advice
Is it more advantageous, speed-wise, to split a database into two, i.e. one database for data and another one just for attachments? Regards, Mags
Re: [firebird-support] Request for advice
Hi Mags, my experience says it depends... I don't feel big databases beeing significantly slower than smaller ones, and the overhead of accessing the blobs in the second db is not trivial, but: If you have to access the blobs only very seldom and do regular backup-restore-cycles, it can be a good idea of splitting the database. We did that, for example, with old logging-data, that get's moved out after a couple of months... Regards, Thomas Am 24.04.2014 11:25, schrieb Mags Phangisa: Is it more advantageous, speed-wise, to split a database into two, i.e. one database for data and another one just for attachments? Regards, Mags -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Request for advice
Thanks Thomas. Blobs are seldom accessed. Much appreciated. Mags On Thu, Apr 24, 2014 at 11:32 AM, Thomas Beckmann thomas.beckm...@assfinet.de wrote: Hi Mags, my experience says it depends... I don't feel big databases beeing significantly slower than smaller ones, and the overhead of accessing the blobs in the second db is not trivial, but: If you have to access the blobs only very seldom and do regular backup-restore-cycles, it can be a good idea of splitting the database. We did that, for example, with old logging-data, that get's moved out after a couple of months... Regards, Thomas Am 24.04.2014 11:25, schrieb Mags Phangisa: Is it more advantageous, speed-wise, to split a database into two, i.e. one database for data and another one just for attachments? Regards, Mags -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Request for advice
On 04/24/2014 01:32 PM, Mags Phangisa wrote: Thanks Thomas. Blobs are seldom accessed. As a compromise, I would suggest to place your blobs into a separate table, it will increase the size of your main database, but will also speed up the acccess to your blobs without interfering with everyday database operations. hth fsg -- Fascinating creatures, phoenixes, they can carry immensely heavy loads, their tears have healing powers and they make highly faithful pets. - J.K. Rowling
Re: [firebird-support] Request for advice
On 24-4-2014 20:49, Frank Schlottmann-Gödde wrote: As a compromise, I would suggest to place your blobs into a separate table, it will increase the size of your main database, but will also speed up the acccess to your blobs without interfering with everyday database operations. As most blobs are stored out of band, I would expect that moving them to a separate table this doesn't have a big impact unless you have the habit of always retrieving and loading all fields of a table. Mark -- Mark Rotteveel
[firebird-support] Additional Table Permissions Needed by Update in Stored Procedure
Firebird Version: 2.5.2.26540 OS: Windows 7 I'm having an update permissions problem on a SQL update statement when I use the SQL in a stored procedure with RETURNING/INTO. I have granted update permissions on column1 and column2 to role1 with this command: GRANT REFERENCES, SELECT, UPDATE(column1, column2) ON table1 TO ROLE role1; This is the SQL update in the stored procedure: UPDATE table1 SET column1 = 0, column2 = 'NOW' WHERE user_name = :p_user_name RETURNING column3, column4 INTO :ret_column3, :ret_column4; I get this error: no permission for update/write access to column table1.column3 If I remove RETURNING/INTO from the SQL, then the update is successful. With RETURNING it requires me to grant update permissions on column3 and column4 even though column3 and column4 are not being updated. Should permissions work this way or is there a bug? Thank you, Do THIS before eating carbs #40;every time#41; 1 EASY tip to increase fat-burning, lower blood sugar decrease fat storage http://thirdpartyoffers.netzero.net/TGL3231/53596f76c5f0d6f7671ebst01duc
Re: [firebird-support] Request for advice
Em 24/4/2014 17:08, Mark Rotteveel escreveu: On 24-4-2014 20:49, Frank Schlottmann-Gödde wrote: As a compromise, I would suggest to place your blobs into a separate table, it will increase the size of your main database, but will also speed up the acccess to your blobs without interfering with everyday database operations. As most blobs are stored out of band, I would expect that moving them to a separate table this doesn't have a big impact unless you have the habit of always retrieving and loading all fields of a table. Mark If the blob's are small enough to fit into the data page, it will dramatically reduce the number of records per page, and ever if you don't select it you will read the whole page, where most part of it is the blob content. Moving the blob to a separate table would reduce this. see you !
Re: [firebird-support] Request for advice
As I fear, some people misunderstood Frank's hints, I'd like to clarify: I know no reason to store blobs in data table with other objects data, it should be stored in separate table(s). This does not (significantly) grow database, does not make it significantly slower and does not have any impact on backup/restore: But performs ways better, if blob fields are not main data fields. This optimization has completely different impacts than moving blob data to a different database: Not only that every select has to be evaluated on runtime by execute statement, it has to be fetched from far destination (with no optimization hints), and there are no means to keep referential integrity by database design between the two databases. Thomas Am 25.04.2014 00:29, schrieb Alexandre Benson Smith: Em 24/4/2014 17:08, Mark Rotteveel escreveu: On 24-4-2014 20:49, Frank Schlottmann-Gödde wrote: As a compromise, I would suggest to place your blobs into a separate table, it will increase the size of your main database, but will also speed up the acccess to your blobs without interfering with everyday database operations. As most blobs are stored out of band, I would expect that moving them to a separate table this doesn't have a big impact unless you have the habit of always retrieving and loading all fields of a table. Mark If the blob's are small enough to fit into the data page, it will dramatically reduce the number of records per page, and ever if you don't select it you will read the whole page, where most part of it is the blob content. Moving the blob to a separate table would reduce this. see you ! -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Odp: [firebird-support] Request for advice
Hi, But you also misunderstud advice. Blobs are not stored in table with its other fields. In record exists only blob id and blob is stored on separate pages. Then moving it to separate table do nothing. But i always prefere store big data like document scans outside of database and in main database have only some hint about file name without final location. Only application have knowlage about final location and can load document Regards, Karol Bieniaszewski - Reply message - Od: Thomas Beckmann thomas.beckm...@assfinet.de Do: firebird-support@yahoogroups.com Temat: [firebird-support] Request for advice Data: pt., kwi 25, 2014 00:46 As I fear, some people misunderstood Frank's hints, I'd like to clarify: I know no reason to store blobs in data table with other objects data, it should be stored in separate table(s). This does not (significantly) grow database, does not make it significantly slower and does not have any impact on backup/restore: But performs ways better, if blob fields are not main data fields. This optimization has completely different impacts than moving blob data to a different database: Not only that every select has to be evaluated on runtime by execute statement, it has to be fetched from far destination (with no optimization hints), and there are no means to keep referential integrity by database design between the two databases. Thomas Am 25.04.2014 00:29, schrieb Alexandre Benson Smith: Em 24/4/2014 17:08, Mark Rotteveel escreveu: On 24-4-2014 20:49, Frank Schlottmann-Gödde wrote: As a compromise, I would suggest to place your blobs into a separate table, it will increase the size of your main database, but will also speed up the acccess to your blobs without interfering with everyday database operations. As most blobs are stored out of band, I would expect that moving them to a separate table this doesn't have a big impact unless you have the habit of always retrieving and loading all fields of a table. Mark If the blob's are small enough to fit into the data page, it will dramatically reduce the number of records per page, and ever if you don't select it you will read the whole page, where most part of it is the blob content. Moving the blob to a separate table would reduce this. see you ! -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: Odp: [firebird-support] Request for advice
Good morning Karol, yes, storing big data chunks outside database file is an opinion we regularly use, too. It has the serious disadvantage, that you've to deal with different addressing and right schemes on different operating systems and deployment environments (local/remote servers, UNC-paths etc) and with particular backup mechanisms to keep everything consistent, though. You are right: Blobs are stored in separate pages. But having blob fields inside tables with other fields forces these pages to be allocated when record is created - whether the blob will contain data or not. Furthermore, common (bad) practice of fetching all fields (select * from), sometimes even performed by database access layer of programming language against the developers wish, accesses this data and decreases performance. Thus, having blobs stored in different table(s) usually leads to better performance, because blob access is more evident and people (hopefully) think twice before fetching... Regards, Thomas But you also misunderstud advice. Blobs are not stored in table with its other fields. In record exists only blob id and blob is stored on separate pages. Then moving it to separate table do nothing. But i always prefere store big data like document scans outside of database and in main database have only some hint about file name without final location. Only application have knowlage about final location and can load document Regards, Karol Bieniaszewski - Reply message - Od: Thomas Beckmann thomas.beckm...@assfinet.de Do: firebird-support@yahoogroups.com Temat: [firebird-support] Request for advice Data: pt., kwi 25, 2014 00:46 As I fear, some people misunderstood Frank's hints, I'd like to clarify: I know no reason to store blobs in data table with other objects data, it should be stored in separate table(s). This does not (significantly) grow database, does not make it significantly slower and does not have any impact on backup/restore: But performs ways better, if blob fields are not main data fields. This optimization has completely different impacts than moving blob data to a different database: Not only that every select has to be evaluated on runtime by execute statement, it has to be fetched from far destination (with no optimization hints), and there are no means to keep referential integrity by database design between the two databases. Thomas Am 25.04.2014 00:29, schrieb Alexandre Benson Smith: Em 24/4/2014 17:08, Mark Rotteveel escreveu: On 24-4-2014 20:49, Frank Schlottmann-Gödde wrote: As a compromise, I would suggest to place your blobs into a separate table, it will increase the size of your main database, but will also speed up the acccess to your blobs without interfering with everyday database operations. As most blobs are stored out of band, I would expect that moving them to a separate table this doesn't have a big impact unless you have the habit of always retrieving and loading all fields of a table. Mark If the blob's are small enough to fit into the data page, it will dramatically reduce the number of records per page, and ever if you don't select it you will read the whole page, where most part of it is the blob content. Moving the blob to a separate table would reduce this. see you ! -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
