RE: [firebird-support] NOT EXISTS returns 'no current record for fetch operation'
>In case anyone is confused as to what I'm trying to accomplish, it's an outer >join on line >items where both sides match with no NULL items. >If my approach is poor, then I won't have to worry about question about the >exception. > >SELECT po.ID, pb.ID >FROM > RPL_PO po > JOIN RPL_POBILL pb >ON pb.PO = po.ID >WHERE > NOT EXISTS ( >SELECT 1 >FROM RPL_PO_ITM poi >LEFT JOIN RPL_POBILL_ITM pbi > ON poi.NDC = pbi.NDC AND pbi.INVOICE = pb.ID >WHERE poi.PURCH_ORDER = po.ID > AND pbi.COST IS NULL > ) AND > NOT EXISTS ( >SELECT 1 >FROM RPL_POBILL_ITM pbi >LEFT JOIN RPL_PO_ITM poi > ON pbi.NDC = poi.NDC AND poi.PURCH_ORDER = po.ID >WHERE pbi.INVOICE = pb.ID > AND poi.PRICE IS NULL > ) Well, yes, I'm a bit confused, though I blame it mostly on not knowing more about your system. Instinctively, I'd say your query ought to work, although I've never before seen a NOT EXISTS which links to an outer table in the RIGHT side of a LEFT JOIN and haven't thought all too much about it. I think it is likely that your query could be improved. Am I right in assuming that you want there in ALL cases where there is an RPL_PO_ITM to also exist at least one RPL_POBILL_ITM record and that all matching RPL_POBILL_ITM must have COST defined? Regardless of the answer to the above question, I hope this query does the same as your query and hopefully works (assuming that it is the LEFT JOIN that gives you the error): SELECT po.ID, pb.ID FROM RPL_PO po JOIN RPL_POBILL pb ON pb.PO = po.ID WHERE NOT EXISTS ( SELECT 1 FROM RPL_PO_ITM poi LEFT JOIN RPL_POBILL_ITM pbi ON poi.NDC = pbi.NDC WHERE poi.PURCH_ORDER = po.ID AND pbi.COST IS NULL AND (pbi.INVOICE = pb.ID OR pbi.INVOICE IS NULL) ) AND NOT EXISTS ( SELECT 1 FROM RPL_POBILL_ITM pbi LEFT JOIN RPL_PO_ITM poi ON pbi.NDC = poi.NDC WHERE pbi.INVOICE = pb.ID AND poi.PRICE IS NULL AND (poi.PURCH_ORDER = po.ID OR poi.PURCH_ORDER IS NULL) ) I'm curious whether this works, so please report back. Also, if this is a Firebird error (i.e. that your query returns such an error regardless of how it is executed and that there no stupid thing about this query that we simply fail to see) and that it occurs in new Firebird versions, then it ought to be reported so that it can be fixed. HTH, Set
[firebird-support] Re: Is it possible to know if people are using any FDB (from command line?)
--- In firebird-support@yahoogroups.com, Milan Babuskov wrote: > > venussoftop wrote: > > I was wondering if there was a way to find out if people are using any of > > the FireBird Databases on a given computer. > > Define "using". Do you mean "using via Firebird server" or > "reading/writing the file"? Do you have Firebird server or use embedded? Via FireBird server. > attach to it. (although, if you know all databases, you can connect and > use monitoring tables as well). Can Alias.conf help here? That is one file diligently updated. > > If this can be done from a command file, rather than interactively, that is > > all the more better. > > Which operating system are you using? Windows Sorry Milan I was not clear about the configurations I am using. Thanks and regards Bhavbhuti
[firebird-support] Re: why Blob is so slow ?
--- In firebird-support@yahoogroups.com, Ann Harrison wrote: > > On Thu, Apr 19, 2012 at 11:13 AM, Tupy... nambá wrote: > > > > > But, having many NFE (as many as the transactions), don´t you agree that > > these BLOB´s will be a great source of fragmentation inside the DB ? > > > > Err, no. It's not. I'm not 100% sure what you mean by fragmentation, but > all data, metadata, blobs, internal structure and state are kept on fixed > sized pages in a single file. Yes, if you're running on a disk that's full > and fragmented, that file will be scattered around the disk, but inside, > it's quite tidy. > > > > And, if I´m sure about my thinkings, as Firebird doesn´t have a way to > > defragment inside the DB, you don´t have a way to resolve this. > > > > When pages are released, they're reused. > > > > May be, for having a good solution for such kind of business, one had to > > use a MS SQL Server to periodically defragment the DB. Or another DB name > > that has this funcionality. I searched something like this at Postgres and > > I found a command named VACUUM that does something like this. Think about > > all of this, if you want. If have to have BLOB´s, I think Firebird is not a > > good solution for a great number of them. My thought, you don´t need to > > agree. > > > The PostgreSQL vacuum is similar to Firebird's continuous, on-line garbage > collection, except that it's a separate, off-line command. > > Good luck, > > Ann > > > [Non-text portions of this message have been removed] > Some years ago (at the time of Version 1.5) I observed the same behaviour backing up a database containing a lot of BLOBs. While storing the 'normal' data was reasonably fast, it slowed down when it come to store the BLOBs. Digging some deeper into that problem I found that the (System-File)-IO used uniformly large blocks while storing the non-BLOB tables. But when it came to the BLOB data the datablocks written to disk varied in size. It seemed, dumping a BLOB is a two stage job: first dump the 'normal' data of the table's record, then dump the associated BLOB's data; then continue with the next record. So for dumping a table containing a BLOB the IO seemed to be the problem. To resolve the problem we stored the BLOBs direct on disk and held only a reference in the Database (as already suggested). Actually I don't work with firebird anymore, so I can't verify these observations anymore, but maybe these informations can help you. Roger
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 16:28, Carlos H. Cantu escreveu: > LC> It is a little amazing at time when some things work fast on one machine > and a > LC> lot slower on another, but the sort of problem you are seeing I would > check that > LC> there is not a problem with the hard disc. I've seen that sort of > effect when > LC> the controller is having trouble reading a disk. It WILL read the data > LC> eventually, but keeps winding the heads back to '0' and repositioning > for each > LC> block read. Replacing the hard disk and restoring the data invariably > cleared > LC> the problem. Had it a couple of time now - 'Maxtor' discs have been > stripped > LC> from all my customer machines now! > > My guess is that the time differences are also related to the > configuration of the file system used in his linux server (ie: barrier > and other params). Kouzmenko and me tested in Windows machines. > > Carlos > Firebird Performance in Detail - http://videos.firebirddevelopersday.com > www.firebirdnews.org - www.FireBase.com.br > I am still doing some tests to try to identify the culprit. I tested on another linux machine and the restore is under 3s, but I can't compare because this machine uses SCSI disks on RAID, and mine is a simple (and pretty old) SATA disc. I will test on some real hardware and report back. I had ruled out hardware/file system too fast, thats the reason I posted the original message, the reason I ruled out hardware/file system configuration is because I noted the slowdown on a client site and then tested on my server I noted the same speed problem... But I think that both servers (mine and my customer) have something weird (perhaps filesystem options as pointed out by Carlos). Unfortunatelly I had no remote access to that server. Thanks for all the input and to Carlos and Dmitry for the time to perform the tests. see you !
RE: [firebird-support] NOT EXISTS returns 'no current record for fetch operation'
Rick, > In case anyone is confused Count me as one of them! as to what I'm trying to accomplish, it's an outer > join on line items where both sides match with no NULL items. > If my approach is poor, then I won't have to worry about question about the > exception. > What happens with this query? SELECT po.ID, pb.ID FROM RPL_PO po JOIN RPL_POBILL pb ON pb.PO = po.ID WHERE NOT EXISTS ( SELECT 1 FROM RPL_PO_ITM poi1 LEFT JOIN RPL_POBILL_ITM pbi1 ON poi1.NDC = pbi1.NDC AND pbi1.INVOICE = pb.ID WHERE Poi1.PURCH_ORDER = po.ID AND pbi1.COST IS NULL ) AND NOT EXISTS ( SELECT 1 FROM RPL_POBILL_ITM pbi2 LEFT JOIN RPL_PO_ITM poi2 ON poi2.NDC = pbi2.NDC AND poi2.PURCH_ORDER = po.ID WHERE Pbi2.INVOICE = pb.ID AND poi2.PRICE IS NULL ) And this? SELECT po.ID, pb.ID FROM RPL_PO po JOIN RPL_POBILL pb ON (pb.PO = po.ID) LEFT JOIN RPL_PO_ITM poi1 ON (poi1.PURCH_ORDER = po.ID) LEFT JOIN RPL_POBILL_ITM pbi1 ON ((pbi1.NDC = poi1.NDC) AND (pbi1.INVOICE = pb.ID)) LEFT JOIN RPL_POBILL_ITM pbi2 ON (pbi2.INVOICE = pb.ID) LEFT JOIN RPL_PO_ITM poi2 ON ((poi2.NDC = pbi2.NDC) AND (poi2.PURCH_ORDER = po.ID)) WHERE AND pbi1.COST IS NULL AND poi1.NDC IS NULL AND poi2.NDC IS NULL AND poi2.PRICE IS NULL Sean
Re: [firebird-support] why Blob is so slow ?
LC> It is a little amazing at time when some things work fast on one machine and a LC> lot slower on another, but the sort of problem you are seeing I would check that LC> there is not a problem with the hard disc. I've seen that sort of effect when LC> the controller is having trouble reading a disk. It WILL read the data LC> eventually, but keeps winding the heads back to '0' and repositioning for each LC> block read. Replacing the hard disk and restoring the data invariably cleared LC> the problem. Had it a couple of time now - 'Maxtor' discs have been stripped LC> from all my customer machines now! My guess is that the time differences are also related to the configuration of the file system used in his linux server (ie: barrier and other params). Kouzmenko and me tested in Windows machines. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br
Re: [firebird-support] why Blob is so slow ?
Alexandre Benson Smith wrote: > In this moment I am doing tests with Carlos Cantu and Dmitry Kuzmenko, > and the culprit so far is my machine, on their machine (both !) the > restore took 3s in mine 10 minutes ! > > I am testing on ext3 and ext4 partitions and I will make more tests on > another machine, so I can isolate hardware as a factor. It is a little amazing at time when some things work fast on one machine and a lot slower on another, but the sort of problem you are seeing I would check that there is not a problem with the hard disc. I've seen that sort of effect when the controller is having trouble reading a disk. It WILL read the data eventually, but keeps winding the heads back to '0' and repositioning for each block read. Replacing the hard disk and restoring the data invariably cleared the problem. Had it a couple of time now - 'Maxtor' discs have been stripped from all my customer machines now! -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php
[firebird-support] Re: No index used for join on 'starting with'
19.04.2012 20:30, Rick Debay wrote: > I will, after we migrate to FB 2.5.x. Right now we're still on 1.5.6. This may explain your plan issue. I don't expect you facing it again after migration. Dmitry
Re: [firebird-support] why Blob is so slow ?
On 19-4-2012 18:34, Tupy... nambá wrote: > Still something = doesn´t matter if you have the blob field in a separated > table. Since they are all together in a same DB file, they may cause > defragmentation, no one can ensure where at the DB file they will be written > and probably will be written in the middle of others non-blob columns/fields. > If you have an separated DB for the blob-fields-tables, you will not have > this problem, but then you will have new ACIDity problems. If Firebird had > something like MSSQL Server Linked Servers, than you still could have > integration between the two DB´s, having the best of both (no fragmentation > at one / blob´s at the other). Firebird has distributed transactions, so if you really want to use two databases then you can use a two-phase commit to maintain ACID. Mark -- Mark Rotteveel
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 13:34, Tupy... nambá escreveu: > Still something = doesn´t matter if you have the blob field in a separated > table. Since they are all together in a same DB file, they may cause > defragmentation, no one can ensure where at the DB file they will be written > and probably will be written in the middle of others non-blob columns/fields. > If you have an separated DB for the blob-fields-tables, you will not have > this problem, but then you will have new ACIDity problems. If Firebird had > something like MSSQL Server Linked Servers, than you still could have > integration between the two DB´s, having the best of both (no fragmentation > at one / blob´s at the other). > Having the BLOB on a separated table ensures that the data pages of the main table holds only data and there is no chance that "small" blobs are stored with the record data. Lets suppose I had 1k record, in a 16k page will contain rougly 15 records per page, if the blob are 10k for example, the data page will hold only one record and the blob. So to store 100 records (with the blob data) I will need 100 pages, but the blob data are not often needed, so if I keep then on separate tables I can make FB store 15 records per page, to read 100 records I will need 7 pages, wich is far less than 100. And *when* I need the BLOB I will get it from another table (and another page) see you !
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 13:18, Tupy... nambá escreveu: > MSSQL has two commands of the DBCC that allow to do defragmentation. The > defragmentation is not a garbage collection, but putting all parts of an > object (file or columns, hanging of the level - disc or DB) side by side, in > a way that the reading of data will be almost fast, because all data will be > found almost together. Normally,this is the way to have quick readings of > data. Garbage collection is like removing of erased data. > As I quickly read at some PostGreSQL pages, VACUUM has to be a defragment > command for PostGreSQL. > > Since you know that you can make a defragment at Firebird making an DB > restore, you can make a restore and compare the reading times at the two > situations. If you have a meaningfull increase of readings speed (SELECT´s > and so on) after the restore, this will mean that your problem is of high > fragmentation. > Also, after having made the restore, you can do a new backup and once again, > a second restore, and see if you have time reduce. At the first restore, the > time has to be long, but at the second, no more, because the second backup > will store defragmented data. > If you can, let´s try till now, all I have are only theories. Your > results will be interesting for all of us. > I don't said the Garbage Collection is the same as defragmentation on MSSQL, I said that I don't know about PG, but I *think* VACCUMM is the same as FB Garbage Collection :) and I didn't say that I am sure about it All the tests are done on freshly restore DB, so it's not "fragmented", the slowness is on back-up/restore of a freshly created test database. In this moment I am doing tests with Carlos Cantu and Dmitry Kuzmenko, and the culprit so far is my machine, on their machine (both !) the restore took 3s in mine 10 minutes ! I am testing on ext3 and ext4 partitions and I will make more tests on another machine, so I can isolate hardware as a factor. see you !
Re: [firebird-support] why Blob is so slow ?
On Thu, Apr 19, 2012 at 11:13 AM, Tupy... nambá wrote: > > But, having many NFE (as many as the transactions), don´t you agree that > these BLOB´s will be a great source of fragmentation inside the DB ? > Err, no. It's not. I'm not 100% sure what you mean by fragmentation, but all data, metadata, blobs, internal structure and state are kept on fixed sized pages in a single file. Yes, if you're running on a disk that's full and fragmented, that file will be scattered around the disk, but inside, it's quite tidy. > And, if I´m sure about my thinkings, as Firebird doesn´t have a way to > defragment inside the DB, you don´t have a way to resolve this. > When pages are released, they're reused. > May be, for having a good solution for such kind of business, one had to > use a MS SQL Server to periodically defragment the DB. Or another DB name > that has this funcionality. I searched something like this at Postgres and > I found a command named VACUUM that does something like this. Think about > all of this, if you want. If have to have BLOB´s, I think Firebird is not a > good solution for a great number of them. My thought, you don´t need to > agree. The PostgreSQL vacuum is similar to Firebird's continuous, on-line garbage collection, except that it's a separate, off-line command. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] why Blob is so slow ?
Still something = doesn´t matter if you have the blob field in a separated table. Since they are all together in a same DB file, they may cause defragmentation, no one can ensure where at the DB file they will be written and probably will be written in the middle of others non-blob columns/fields. If you have an separated DB for the blob-fields-tables, you will not have this problem, but then you will have new ACIDity problems. If Firebird had something like MSSQL Server Linked Servers, than you still could have integration between the two DB´s, having the best of both (no fragmentation at one / blob´s at the other). ... I had used MSSQL 6.5 (yes it's a long time ago) so can't comment on the need of defragmentation. I don't know Postgres, but I think the VACUMM is a similar to FB garbage collection. There is a way to defragment FB, make a back-up/restore, but I don't think it's needed, at least I had never had the need for such operation. A big blob will be stored in a bunch of pages that tends to be contiguous at the end of the file (yes, I know unsed page are reused), so I don't think it's the reason. A typical NFE would be around 10KB, depending on the page size it could be stored with the record, or be stored in two blob pages and just the blob id on the record page, anyway I prefer to have a separate table to hold the blobs, because in my case the access to blob's are not so often, so I prefer to have as many records per page as I can, and read a separate table (and therefore page) to read the blob contents when I need it. It's good to read your thougths, I am just arguing about the options :) see you ! ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links [Non-text portions of this message have been removed]
RE: [firebird-support] No index used for join on 'starting with'
I will, after we migrate to FB 2.5.x. Right now we're still on 1.5.6. And to forestall "why haven't you moved yet" posts, I'd love to but we're stuck until mgmt lets us. -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Ann Harrison Sent: Tuesday, April 10, 2012 1:59 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] No index used for join on 'starting with' Rick, It's not going to help in the short run, but I would submit this as a bug. This query uses natural for both tables, when I expected it to use an > index for the join. > > select * > from table1 t1 > join table2 t2 on t2.indexed_char14 starting with t1.indexed_char10 > where t1.unindex_varchar containing 'foo' > > Good luck, Ann [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.
RE: [firebird-support] NOT EXISTS returns 'no current record for fetch operation'
In case anyone is confused as to what I'm trying to accomplish, it's an outer join on line items where both sides match with no NULL items. If my approach is poor, then I won't have to worry about question about the exception. SELECT po.ID, pb.ID FROM RPL_PO po JOIN RPL_POBILL pb ON pb.PO = po.ID WHERE NOT EXISTS ( SELECT 1 FROM RPL_PO_ITM poi LEFT JOIN RPL_POBILL_ITM pbi ON poi.NDC = pbi.NDC AND pbi.INVOICE = pb.ID WHERE poi.PURCH_ORDER = po.ID AND pbi.COST IS NULL ) AND NOT EXISTS ( SELECT 1 FROM RPL_POBILL_ITM pbi LEFT JOIN RPL_PO_ITM poi ON pbi.NDC = poi.NDC AND poi.PURCH_ORDER = po.ID WHERE pbi.INVOICE = pb.ID AND poi.PRICE IS NULL ) Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.
Re: [firebird-support] why Blob is so slow ?
MSSQL has two commands of the DBCC that allow to do defragmentation. The defragmentation is not a garbage collection, but putting all parts of an object (file or columns, hanging of the level - disc or DB) side by side, in a way that the reading of data will be almost fast, because all data will be found almost together. Normally,this is the way to have quick readings of data. Garbage collection is like removing of erased data. As I quickly read at some PostGreSQL pages, VACUUM has to be a defragment command for PostGreSQL. Since you know that you can make a defragment at Firebird making an DB restore, you can make a restore and compare the reading times at the two situations. If you have a meaningfull increase of readings speed (SELECT´s and so on) after the restore, this will mean that your problem is of high fragmentation. Also, after having made the restore, you can do a new backup and once again, a second restore, and see if you have time reduce. At the first restore, the time has to be long, but at the second, no more, because the second backup will store defragmented data. If you can, let´s try till now, all I have are only theories. Your results will be interesting for all of us. --- On Thu, 4/19/12, Alexandre Benson Smith wrote: I had used MSSQL 6.5 (yes it's a long time ago) so can't comment on the need of defragmentation. I don't know Postgres, but I think the VACUMM is a similar to FB garbage collection. There is a way to defragment FB, make a back-up/restore, but I don't think it's needed, at least I had never had the need for such operation. A big blob will be stored in a bunch of pages that tends to be contiguous at the end of the file (yes, I know unsed page are reused), so I don't think it's the reason. A typical NFE would be around 10KB, depending on the page size it could be stored with the record, or be stored in two blob pages and just the blob id on the record page, anyway I prefer to have a separate table to hold the blobs, because in my case the access to blob's are not so often, so I prefer to have as many records per page as I can, and read a separate table (and therefore page) to read the blob contents when I need it. It's good to read your thougths, I am just arguing about the options :) see you ! ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo! Groups Links [Non-text portions of this message have been removed]
[firebird-support] NOT EXISTS returns 'no current record for fetch operation'
This query returns 'no current record for fetch operation' SELECT po.ID, pb.ID FROM RPL_PO po JOIN RPL_POBILL pb ON pb.PO = po.ID WHERE po.ID = ? AND NOT EXISTS ( SELECT 1 FROM RPL_PO_ITM poi LEFT JOIN RPL_POBILL_ITM pbi ON poi.NDC = pbi.NDC AND pbi.INVOICE = pb.ID WHERE poi.PURCH_ORDER = po.ID AND pbi.COST IS NULL ) But these running the queries separately works. SELECT po.ID, pb.ID FROM RPL_PO po JOIN RPL_POBILL pb ON pb.PO = po.ID WHERE po.ID = ? /* plug in values returned above */ SELECT * FROM RPL_PO_ITM poi LEFT JOIN RPL_POBILL_ITM pbi ON poi.NDC = pbi.NDC AND pbi.INVOICE = pb.ID WHERE poi.PURCH_ORDER = po.ID AND pbi.COST IS NULL Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 12:28, Carlos H. Cantu escreveu: > Sorry but the discussion is going "off-topic" for the original > question, that is: why backup/restore of blobs are so much slower > compared to non-blobs data. I'm also curious about this. > > Carlos > Firebird Performance in Detail - http://videos.firebirddevelopersday.com > www.firebirdnews.org - www.FireBase.com.br > I noted this slowness for some time, but never created a test case so it can be measured. I am sending a back-up to Dmitry Kuzmenko (as he asked for) so he could take a look. I really don't know what's happening, but it's strange to me. I think that a profilling of gbak and fb server process during the restore could show where the time is used and shed some light. see you !
Re: [firebird-support] why Blob is so slow ?
Em 19/4/2012 12:13, Tupy... nambá escreveu: > Hi, Alexandre, > For the sample you gave (NFE), I agree with you, because the amount of files > that will be generated will be very great and each file itself is not so big, > probably they will not become a problem. And, in this case, they are part of > a transaction. Probably not, but I´m not sure - one have to make comparisons > to be sure about the best solution. I told in a generic way, specially were > we have contracts, photos, and other no transactional documents. > > But, having many NFE (as many as the transactions), don´t you agree that > these BLOB´s will be a great source of fragmentation inside the DB ? > And, if I´m sure about my thinkings, as Firebird doesn´t have a way to > defragment inside the DB, you don´t have a way to resolve this. > May be, for having a good solution for such kind of business, one had to use > a MS SQL Server to periodically defragment the DB. Or another DB name that > has this funcionality. I searched something like this at Postgres and I found > a command named VACUUM that does something like this. Think about all of > this, if you want. If have to have BLOB´s, I think Firebird is not a good > solution for a great number of them. My thought, you don´t need to agree. > Friendly, best regards,Roberto Camargo. > > I had used MSSQL 6.5 (yes it's a long time ago) so can't comment on the need of defragmentation. I don't know Postgres, but I think the VACUMM is a similar to FB garbage collection. There is a way to defragment FB, make a back-up/restore, but I don't think it's needed, at least I had never had the need for such operation. A big blob will be stored in a bunch of pages that tends to be contiguous at the end of the file (yes, I know unsed page are reused), so I don't think it's the reason. A typical NFE would be around 10KB, depending on the page size it could be stored with the record, or be stored in two blob pages and just the blob id on the record page, anyway I prefer to have a separate table to hold the blobs, because in my case the access to blob's are not so often, so I prefer to have as many records per page as I can, and read a separate table (and therefore page) to read the blob contents when I need it. It's good to read your thougths, I am just arguing about the options :) see you !
Re: [firebird-support] why Blob is so slow ?
Sorry but the discussion is going "off-topic" for the original question, that is: why backup/restore of blobs are so much slower compared to non-blobs data. I'm also curious about this. Carlos Firebird Performance in Detail - http://videos.firebirddevelopersday.com www.firebirdnews.org - www.FireBase.com.br Tn> Hi, Alexandre, Tn> For the sample you gave (NFE), I agree with you, because the Tn> amount of files that will be generated will be very great and each Tn> file itself is not so big, probably they will not become a Tn> problem. And, in this case, they are part of a transaction. Tn> Probably not, but I´m not sure - one have to make comparisons to Tn> be sure about the best solution. I told in a generic way, Tn> specially were we have contracts, photos, and other no transactional documents. Tn> But, having many NFE (as many as the transactions), don´t you Tn> agree that these BLOB´s will be a great source of fragmentation inside the DB ? Tn> And, if I´m sure about my thinkings, as Firebird doesn´t have a Tn> way to defragment inside the DB, you don´t have a way to resolve this. Tn> May be, for having a good solution for such kind of business, one Tn> had to use a MS SQL Server to periodically defragment the DB. Or Tn> another DB name that has this funcionality. I searched something Tn> like this at Postgres and I found a command named VACUUM that does Tn> something like this. Think about all of this, if you want. If have Tn> to have BLOB´s, I think Firebird is not a good solution for a Tn> great number of them. My thought, you don´t need to agree. Tn> Friendly, best regards,Roberto Camargo.
Re: [firebird-support] why Blob is so slow ?
Hi, Alexandre, For the sample you gave (NFE), I agree with you, because the amount of files that will be generated will be very great and each file itself is not so big, probably they will not become a problem. And, in this case, they are part of a transaction. Probably not, but I´m not sure - one have to make comparisons to be sure about the best solution. I told in a generic way, specially were we have contracts, photos, and other no transactional documents. But, having many NFE (as many as the transactions), don´t you agree that these BLOB´s will be a great source of fragmentation inside the DB ? And, if I´m sure about my thinkings, as Firebird doesn´t have a way to defragment inside the DB, you don´t have a way to resolve this. May be, for having a good solution for such kind of business, one had to use a MS SQL Server to periodically defragment the DB. Or another DB name that has this funcionality. I searched something like this at Postgres and I found a command named VACUUM that does something like this. Think about all of this, if you want. If have to have BLOB´s, I think Firebird is not a good solution for a great number of them. My thought, you don´t need to agree. Friendly, best regards,Roberto Camargo. --- On Thu, 4/19/12, Alexandre Benson Smith wrote: From: Alexandre Benson Smith Subject: Re: [firebird-support] why Blob is so slow ? To: firebird-support@yahoogroups.com Date: Thursday, April 19, 2012, 6:42 PM Hi Roberto, Em 19/4/2012 08:52, Tupy... nambá escreveu: > Alexandre, > At my point of view, I prefer avoid using BLOB fields. First of all, because > these kind of field are not indicated for searches of any kind (most of them > are pictures). Second, > because > normally they have very large content, what does the DB increase in a large > amount. I think the most important property of the DB´s is the capability of > searches. But having fields which don´t allow us to do that, disturb the > funcionality of DB´s. > I prefer using to store files outside DB´s, storing inside them the path for > the files. So, you have the speed at all operations (searches and > backup´s/restores) and not a meaningfull increase of the DB´s. > > I´m not sure about the reasons for the backup/restore speed problem, but I > believe that inside the DB happens almost the same as at OS environment = > when adjacent areas are full, then the OS or the DB manager application most > look for distant areas to store parts of the data, causing a data > fragmentation. And to access the complete data, the OS or DB manager must > "remount" them, before delivering to the client. And the DB itself suffers > from the DB file fragmentation at disc level. > At file servers, normally file fragmentation are low (you don´t edit them > directly at the server) and still you can defragment the files. > At SQL server, you find discussions about internal tables and indexes > fragmentation, and you have commands to repair fragmentation. > At Firebird/Interbase, nobody talks about that, but we know it happens and > can became a problem, when the DB is greater in size. BLOB are worst for > causing that, affecting not only the BLOB fields and data itself, but also > fields and data of other data types. And you don´t have (i never see) > commands for DB internal defragment. > Try to do some experiences about that, making comparisons between different > solutions for a same problem. May be imediatelly filled DB will not show > great differences, but DB´s at common filling (day by day), after a great > amount of time, will show meaningfull differences. > Roberto Camargo,Rio de Janeiro / Brazil > In the past I used the approach of store just the filename, and I still use in some cases, but when everything is inside the datase it's easier to be sure that back-up/restore of everything is in place, to move the content around, provide transaction control (all the ACID features) that needs to be re-implemented if I work at filesystem level. Since you are in Brazil I could point a case where the need to store blob's is almost mandatory: The storage of XML files of "Nota Fiscal Eletronica" (eletronic invoice), We need to keep the data for the legal periods specified in our legislation, and to handle thousands (millions ?) of individual files on the filesystem is not the best option in my point of view, it's much easier to be sure that everything is secure inside the database. I disagree with you about the main feature of a RDBMS is search, search is a part of the whole system, but the main feature in my point of view is to store data. :) Of course there is no sense in store something if you cannot search for it, but, you could have a product that stores the data efficiently and not search it so efficiently called a RDBMS, but the other way around is not possible. Quoting Ann Harrison from the top of my head (probably not the exact words) "if you don't need a correct answer, the answer is 13".
Re: [firebird-support] why Blob is so slow ?
Hi Roberto, Em 19/4/2012 08:52, Tupy... nambá escreveu: > Alexandre, > At my point of view, I prefer avoid using BLOB fields. First of all, because > these kind of field are not indicated for searches of any kind (most of them > are pictures). Second, > because > normally they have very large content, what does the DB increase in a large > amount. I think the most important property of the DB´s is the capability of > searches. But having fields which don´t allow us to do that, disturb the > funcionality of DB´s. > I prefer using to store files outside DB´s, storing inside them the path for > the files. So, you have the speed at all operations (searches and > backup´s/restores) and not a meaningfull increase of the DB´s. > > I´m not sure about the reasons for the backup/restore speed problem, but I > believe that inside the DB happens almost the same as at OS environment = > when adjacent areas are full, then the OS or the DB manager application most > look for distant areas to store parts of the data, causing a data > fragmentation. And to access the complete data, the OS or DB manager must > "remount" them, before delivering to the client. And the DB itself suffers > from the DB file fragmentation at disc level. > At file servers, normally file fragmentation are low (you don´t edit them > directly at the server) and still you can defragment the files. > At SQL server, you find discussions about internal tables and indexes > fragmentation, and you have commands to repair fragmentation. > At Firebird/Interbase, nobody talks about that, but we know it happens and > can became a problem, when the DB is greater in size. BLOB are worst for > causing that, affecting not only the BLOB fields and data itself, but also > fields and data of other data types. And you don´t have (i never see) > commands for DB internal defragment. > Try to do some experiences about that, making comparisons between different > solutions for a same problem. May be imediatelly filled DB will not show > great differences, but DB´s at common filling (day by day), after a great > amount of time, will show meaningfull differences. > Roberto Camargo,Rio de Janeiro / Brazil > In the past I used the approach of store just the filename, and I still use in some cases, but when everything is inside the datase it's easier to be sure that back-up/restore of everything is in place, to move the content around, provide transaction control (all the ACID features) that needs to be re-implemented if I work at filesystem level. Since you are in Brazil I could point a case where the need to store blob's is almost mandatory: The storage of XML files of "Nota Fiscal Eletronica" (eletronic invoice), We need to keep the data for the legal periods specified in our legislation, and to handle thousands (millions ?) of individual files on the filesystem is not the best option in my point of view, it's much easier to be sure that everything is secure inside the database. I disagree with you about the main feature of a RDBMS is search, search is a part of the whole system, but the main feature in my point of view is to store data. :) Of course there is no sense in store something if you cannot search for it, but, you could have a product that stores the data efficiently and not search it so efficiently called a RDBMS, but the other way around is not possible. Quoting Ann Harrison from the top of my head (probably not the exact words) "if you don't need a correct answer, the answer is 13". I don't use Blob's that much, but in some cases I think it's a good sollution. Anyway, thanks for sharing your thoughts, I know that store large binary data inside/outside the database is the kind of thing that there is no rule of thumb to choose between one or another, myself use both approachs for distinct use cases. My concerns is that something is "strange" regarding blob manipulation. It's too slow to me. see you ! Alexandre
Re: [firebird-support] why Blob is so slow ?
Alexandre, At my point of view, I prefer avoid using BLOB fields. First of all, because these kind of field are not indicated for searches of any kind (most of them are pictures). Second, because normally they have very large content, what does the DB increase in a large amount. I think the most important property of the DB´s is the capability of searches. But having fields which don´t allow us to do that, disturb the funcionality of DB´s. I prefer using to store files outside DB´s, storing inside them the path for the files. So, you have the speed at all operations (searches and backup´s/restores) and not a meaningfull increase of the DB´s. I´m not sure about the reasons for the backup/restore speed problem, but I believe that inside the DB happens almost the same as at OS environment = when adjacent areas are full, then the OS or the DB manager application most look for distant areas to store parts of the data, causing a data fragmentation. And to access the complete data, the OS or DB manager must "remount" them, before delivering to the client. And the DB itself suffers from the DB file fragmentation at disc level. At file servers, normally file fragmentation are low (you don´t edit them directly at the server) and still you can defragment the files. At SQL server, you find discussions about internal tables and indexes fragmentation, and you have commands to repair fragmentation. At Firebird/Interbase, nobody talks about that, but we know it happens and can became a problem, when the DB is greater in size. BLOB are worst for causing that, affecting not only the BLOB fields and data itself, but also fields and data of other data types. And you don´t have (i never see) commands for DB internal defragment. Try to do some experiences about that, making comparisons between different solutions for a same problem. May be imediatelly filled DB will not show great differences, but DB´s at common filling (day by day), after a great amount of time, will show meaningfull differences. Roberto Camargo,Rio de Janeiro / Brazil --- On Thu, 4/19/12, Alexandre Benson Smith wrote: From: Alexandre Benson Smith Subject: [firebird-support] why Blob is so slow ? To: firebird-support@yahoogroups.com Date: Thursday, April 19, 2012, 2:12 AM For some time I wonder why blob's are so slow during back-up/restore. when I access one blob alone I don't think it's slow, but during the process of back-up/restore I can see that the table that holds the blob took so many time to be processed. Today I created a simple test case that resamble my real scenario: Two Tables CREATE TABLE DOCUMENT ( DOCUMENTID integer NOT NULL, PRODUCTID integer, COMPANYID integer, KIND char(1) NOT NULL COLLATE PT_BR, DESCRIPTION varchar(40) NOT NULL, CONSTRAINT PK_DOCUMENT PRIMARY KEY (DOCUMENTID) ); CREATE TABLE DOCUMENTOBLOB ( DOCUMENTBLOBID integer NOT NULL, DOCUMENTID integer, ITEM integer NOT NULL, BINARYDATA blob sub_type 0 NOT NULL, FILENAME varchar(255) NOT NULL COLLATE PT_BR, CONSTRAINT PK_DOCUMENTBLOB PRIMARY KEY (DOCUMENTBLOBID), CONSTRAINT UNQ_DOCUMENTBLOB UNIQUE (DOCUMENTID, ITEM) ); Table Document has 469 records Table DocumentBlob has 463 records The design were made to support more then a BLOB per document (like many JPG's pages, or a mix of JPG, XLS and PDF) The database has 245MB. I create a simple application to measure the size of the Blobs, the size of the binary data is 236MB. The blobs are not big, one of 37MB, one of 4MB, two of 2MB, twenty eight between 1MB and 2MB and the rest less than a MB. The average size is around 500KB per blob. Here are the timing for a back-up restore: # time /opt/firebird/bin/gbak blob_test.fdb blob_test.fbk -user sysdba -password masterkey -t real 0m6.927s user 0m0.671s sys 0m1.191s # time /opt/firebird/bin/gbak blob_test.fbk blob_teste2.fdb -rep -user sysdba -password masterkey -t real 10m8.894s user 0m0.042s sys 0m0.037s I think it's too slow to process less than 1k records and 250MB of data. Some more info: during the back-up or restore the CPU and i/o is low: Tasks: 93 total, 1 running, 92 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 97.3%id, 2.7%wa, 0.0%hi, 0.0%si, 0.0%st Tasks: 93 total, 1 running, 92 sleeping, 0 stopped, 0 zombie Cpu(s): 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Firebird Version: # /opt/firebird/bin/fbserver -z Firebird TCP/IP server version LI-V2.1.4.18393 Firebird 2.1 gstat -a -r output: Database "blob_teste2.fdb" Database header page information: Flags 0 Checksum 12345 Generation 17 Page size 16384 ODS version 11.1 Oldest transaction 1 Oldest active 2 Oldest snapshot 2 Next transaction 9 Bumped transactio
