[firebird-support] BLOB
Hi all, I have the following table : CREATE TABLE LOG_TABLE ( ID_LOG_TABLE INTEGER NOT NULL, SESSION_IDINTEGER, LOG_DATA DATE, LOG_TIME TIME, LOG_TEXT VARCHAR(100), LOG_TEXT_COMPLET BLOB SUB_TYPE 1 SEGMENT SIZE 80 ) with some 3000+ records. Why does it takes so much time to backup / restore (gbak) this table ? (because the BLOB field). Do I have any options to speed up the process ? Thank you, Tiberiu
Re: [firebird-support] Simultaneous inserts / selects
On 3.10.2014 ã. 12:00 ÷., brucedickin...@wp.pl [firebird-support] wrote: > Hi, > > do you have any experience in selecting from / inserting to table from > multiple threads? Is it faster in comparision to one thread which > selects / inserts data in queue? > > What is your opinion on this? My gut tells me that it will be always > slower in comparision to single thread. But maybe I am wrong and it is > worth the hassle to use separate threads? > > Assumption here is that each thread pulls of data with different primary > / secondary keys and also inserts with different keys. > > I would like to hear your opinion about this topic. > > Thanks you. I did some experiments with this before and the results show that there is some speedup when doing this on more then one thread but not with to many threads. At some point threads start to be blocked by firebird locks. So you better test for your specific configuration in order to find how many parallel threads is good for you. It all depends by hardware you are going to run this on. Also important is the amount of indexes that your table will have that FB needs to update during insert. The best approach is to put as much as possible inserts into single transaction and with single prepare statement. I hope this will help you in your quest ;-) > > -- Doychin Bondzhev dSoft-Bulgaria Ltd. PowerPro - billing & provisioning solution for Service providers PowerStor - Warehouse & POS http://www.dsoft-bg.com/ Mobile: +359888243116 [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Simultaneous inserts / selects
Doychin Bondzhev doyc...@dsoft-bg.com [firebird-support] skrev: > On 3.10.2014 ã. 12:00 ÷., brucedickin...@wp.pl [firebird-support] wrote: >> do you have any experience in selecting from / inserting to table from >> multiple threads? Is it faster in comparision to one thread which >> selects / inserts data in queue? > I did some experiments with this before and the results show that there > is some speedup when doing this on more then one thread but not with to > many threads. At some point threads start to be blocked by firebird locks. > > So you better test for your specific configuration in order to find how > many parallel threads is good for you. > > It all depends by hardware you are going to run this on. Also important > is the amount of indexes that your table will have that FB needs to > update during insert. > > The best approach is to put as much as possible inserts into single > transaction and with single prepare statement. > Also, although rather unorthodox, if you are going to do large batch inserts, do consider using an external table rather than an insert loop. It is much much faster, even if you code all non-string columns as strings and do conversions in SQL. With this approach, it's pretty easy to create the data to put in the external table file. Regards, Kjell -- -- Kjell Rilbe DataDIA AB E-post: kjell.ri...@datadia.se Telefon: 08-761 06 55 Mobil: 0733-44 24 64
Re: [firebird-support] Simultaneous inserts / selects
I have such experience. If you understand what you want and how to create table that will be very fast all will be ok. But If you never done something like this as the result you may create more slowly multithreading application. For today I have one application that without any problem in the same time writing data to one table from 300 threads(2 Core, 2GB RAM and small part of RAID5). You need RAID10 from HDD or SSD and fast processor, but no one hardware could compensate errors of architecture. When you start a lot of instances of one application - it's like multi-threading, it is slower than one inctance? ) If you want use multiple thread you must to try do it, than analyze errors and do it again, until results became good ) 03.10.2014 13:00, brucedickin...@wp.pl [firebird-support] пишет: Hi, do you have any experience in selecting from / inserting to table from multiple threads? Is it faster in comparision to one thread which selects / inserts data in queue? What is your opinion on this? My gut tells me that it will be always slower in comparision to single thread. But maybe I am wrong and it is worth the hassle to use separate threads? Assumption here is that each thread pulls of data with different primary / secondary keys and also inserts with different keys. I would like to hear your opinion about this topic. Thanks you. -- Regards, Markov Dmitri
[firebird-support] Re: Simultaneous inserts / selects
Thank you guys for your input! I appreciate it! I have some doubts.. isn't TCP/IP a bottleneck here? I mean, I am sending for example 4 times (4 threads) more data to insert via network. @Doychin I do have like 2-3 indexes per table but the speed in single thread was sufficient. Thanks, I hope my quest will give me some nice results:) @Kjell Could you shed some more information about your solution? So you first insert data to external table and then you move all that data at once into the main table? @Dmitri 300 threads? The question is how many of them are hitting the database at the same time? I have one more generall question, do you perform your inserts one by one, I mean: StartTransaction; Insert; Insert; Insert; Commit; or do you generate a batch script? a text file which have in it 1000 records, or 1 or more? Thanks! :)
Re: [firebird-support] Re: Simultaneous inserts / selects
At the same time really about 50-70 threads writing data. Some threads reading data and some others writing. One insert in one transaction(it's feature of my task, I can't do it in another way). Today server without 1GB interface - it's not server. But if you will start application local it's always will be faster. If connection will be via LAN it's always will be slowly. Sometimes I connect to remote server via VPN with ping 200 or higher, so application works very slow but it's work. I never try generate scripts in threads, I use datasets and query like in usualy single thread application. O, I forgot one important rule - "One thread - one connection". So all threads will be isolated and life will be good. 09.10.2014 15:32, brucedickin...@wp.pl [firebird-support] пишет: Thank you guys for your input! I appreciate it! I have some doubts.. isn't TCP/IP a bottleneck here? I mean, I am sending for example 4 times (4 threads) more data to insert via network. @Doychin I do have like 2-3 indexes per table but the speed in single thread was sufficient. Thanks, I hope my quest will give me some nice results:) @Kjell Could you shed some more information about your solution? So you first insert data to external table and then you move all that data at once into the main table? @Dmitri 300 threads? The question is how many of them are hitting the database at the same time? I have one more generall question, do you perform your inserts one by one, I mean: StartTransaction; Insert; Insert; Insert; Commit; or do you generate a batch script? a text file which have in it 1000 records, or 1 or more? Thanks! :) -- Regards, Markov Dmitri
Re: [firebird-support] Re: Simultaneous inserts / selects
On 9.10.2014 ã. 14:32 ÷., brucedickin...@wp.pl [firebird-support] wrote: > Thank you guys for your input! I appreciate it! > > I have some doubts.. isn't TCP/IP a bottleneck here? I mean, I am > sending for example 4 times (4 threads) more data to insert via network. > > @Doychin I do have like 2-3 indexes per table but the speed in single > thread was sufficient. Thanks, I hope my quest will give me some nice > results:) > > @Kjell Could you shed some more information about your solution? So you > first insert data to external table and then you move all that data at > once into the main table? > > @Dmitri 300 threads? The question is how many of them are hitting the > database at the same time? > > I have one more generall question, do you perform your inserts one by > one, I mean: > > StartTransaction; > Insert; > Insert; > Insert; > Commit; > > or do you generate a batch script? a text file which have in it 1000 > records, or 1 or more? > Can't you use insert with parameters? This way you will prepare the statement only once and just execute it many times with different parameters. Doing insert with multiple statements without parameters can hurt performance. Statement must be prepared every time and that takes time and round-trip to the server. What language/library you will use to do this job? regarding the size of batch you can do some testing. performance might depend on the number of threads in this case. You need to find the right balance because in case of failure the whole batch will fail. -- Doychin Bondzhev dSoft-Bulgaria Ltd. PowerPro - billing & provisioning solution for Service providers PowerStor - Warehouse & POS http://www.dsoft-bg.com/ Mobile: +359888243116 [Non-text portions of this message have been removed] ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Simultaneous inserts / selects
I'm joining this late and may have some of the details incorrect. But doesn't your client tool have a batch insert object? If your client is written in either Delphi or .NET, I'm pretty sure it does and that would be the best way to insert bulk records. Dixon On Thu, Oct 9, 2014 at 8:18 AM, Doychin Bondzhev doyc...@dsoft-bg.com [firebird-support] wrote: > On 9.10.2014 ã. 14:32 ÷., brucedickin...@wp.pl [firebird-support] wrote: > > Thank you guys for your input! I appreciate it! > > > > I have some doubts.. isn't TCP/IP a bottleneck here? I mean, I am > > sending for example 4 times (4 threads) more data to insert via network. > > > > @Doychin I do have like 2-3 indexes per table but the speed in single > > thread was sufficient. Thanks, I hope my quest will give me some nice > > results:) > > > > @Kjell Could you shed some more information about your solution? So you > > first insert data to external table and then you move all that data at > > once into the main table? > > > > @Dmitri 300 threads? The question is how many of them are hitting the > > database at the same time? > > > > I have one more generall question, do you perform your inserts one by > > one, I mean: > > > > StartTransaction; > > Insert; > > Insert; > > Insert; > > Commit; > > > > or do you generate a batch script? a text file which have in it 1000 > > records, or 1 or more? > > > > Can't you use insert with parameters? This way you will prepare the > statement only once and just execute it many times with different > parameters. > > Doing insert with multiple statements without parameters can hurt > performance. Statement must be prepared every time and that takes time > and round-trip to the server. > > What language/library you will use to do this job? > > regarding the size of batch you can do some testing. performance might > depend on the number of threads in this case. > > You need to find the right balance because in case of failure the whole > batch will fail. > > -- > Doychin Bondzhev > dSoft-Bulgaria Ltd. > PowerPro - billing & provisioning solution for Service providers > PowerStor - Warehouse & POS > http://www.dsoft-bg.com/ > Mobile: +359888243116 > > > [Non-text portions of this message have been removed] > > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > > -- Dixon Epperson
[firebird-support] Problem altering table on 2.5.2
I ran the following script in FlameRobin. Database is on a virtual machine in the cloud. When I checked results after running the script, also checked them in FlameRobin, all the changes had taken place EXCEPT TBLTESTER.TESTERLOGIN. It was still the old size. The script ran without error. What could cause this? DROP INDEX idx_SubscriberLogin_LoginPwd; COMMIT; DROP INDEX IDX_TESTER_LOGIN; COMMIT; DROP INDEX IDX_TESTER_PWD; COMMIT ALTER TABLE TBLTESTER ALTER TESTERLOGIN TYPE Varchar(80); COMMIT; ALTER TABLE TBLTESTER ALTER TESTERPWD TYPE Varchar(32); COMMIT; ALTER TABLE TBLSUBSCRIBERLOGIN ALTER SUBSCRIBERLOGIN TYPE VARCHAR(80); COMMIT; ALTER TABLE TBLSUBSCRIBERLOGIN ALTER SUBSCRIBERPWD TYPE VARCHAR(32); COMMIT; CREATE ASC INDEX idx_SubscriberLogin_LoginPwd ON tblSubscriberLogin (SubscriberLogin, SubscriberPwd); COMMIT; CREATE ASC INDEX idx_Tester_Pwd ON tblTester (TesterPwd); COMMIT; CREATE UNIQUE ASC INDEX idx_Tester_Login ON tblTester (TesterLogin); COMMIT; Dixon Epperson
Re: [firebird-support] BLOB
On 9-10-2014 10:22, 'Tiberiu Horvath' tiberiu_horv...@yahoo.com [firebird-support] wrote: > Hi all, > I have the following table : > CREATE TABLE LOG_TABLE ( > ID_LOG_TABLE INTEGER NOT NULL, > SESSION_ID INTEGER, > LOG_DATA DATE, > LOG_TIME TIME, > LOG_TEXT VARCHAR(100), > LOG_TEXT_COMPLET BLOB SUB_TYPE 1 SEGMENT SIZE 80 > ) > with some 3000+ records. > Why does it takes so much time to backup / restore (gbak) this table ? > (because the BLOB field). > Do I have any options to speed up the process ? Without more information that is hard to tell. How large are the blobs? How long does your backup take, what else is in this database? I am not sure how gbak reads blobs, but if it honours the defined segment size, or if you write to the blob in very small segments that might be part of the problem. Mark -- Mark Rotteveel ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Simultaneous inserts / selects
On 9-10-2014 14:57, 'E. D. Epperson Jr' dixonepper...@gmail.com [firebird-support] wrote: > I'm joining this late and may have some of the details incorrect. But > doesn't your client tool have a batch insert object? If your client is > written in either Delphi or .NET, I'm pretty sure it does and that would > be the best way to insert bulk records. Firebird doesn't actually support batching, so it is usually supported on a driver level by repeatedly executing the prepared statement with multiple parameter sets just like you would do yourself. There is currently no real performance benefit from using batching functionality over re-using a prepared statement yourself. In theory drivers with their own wire protocol implementation might be able to achieve some increase in performance by sending multiple execute requests before waiting on the responses, but as far as I know currently no driver does this. Mark -- Mark Rotteveel
Re: [firebird-support] Re: Simultaneous inserts / selects
I was referring to SqlBulkCopy which is part of the System.Data.SqlClient in C#. I used that a couple of years back, but granted it was with MsSql. However, I thought I remembered having a similar tool to that back in the Delphi 7 days, I just don't remember if I used it with Firebird or Sql. If you say FirebirdSql can't handle it, it must have been with MsSql. My bad Dixon
RE: [firebird-support] BLOB
Mark, > I am not sure how gbak reads blobs, but if it honours the defined segment > size, IIRC, Ann has mentioned that SEGMENT SIZE is a legacy issue that hasn't really been used in some time. Our application uses BLOB fairly extensively and can report that the GBAK disk write rate increases very significantly when tables with Blobs are being backed-up, orders of magnitude faster than tables with small rows (YMMV), so this suggests that SEGMENT SIZE does not have any effect on GBAK. Sean
Re: [firebird-support] BLOB
LSSBcfs> Our application uses BLOB fairly extensively and can report LSSBcfs> that the GBAK disk write rate increases very significantly LSSBcfs> when tables with Blobs are being backed-up, orders of LSSBcfs> magnitude faster than tables with small rows (YMMV), so this LSSBcfs> suggests that SEGMENT SIZE does not have any effect on GBAK. Interesting, usually my experience is just the opposite: gbak usually slows down in tables with blobs. []s Carlos H. Cantu www.FireBase.com.br - www.firebirdnews.org www.warmboot.com.br - blog.firebase.com.br
[firebird-support] Re: Simultaneous inserts / selects
@Dixon It is never to late to join conversation;) Thanks for your suggestions, I've found some interesting methods in my client's library. @Doychin: I am using Delphi and FIBPlus. I am not sure if we mean the same thing by "batch script". Some guys at my company prefer to generate a long script file: for example instead of making 1 inserts one by one they generate a text which consists all inserts. All values for inserts are converted to string so there are no parameters. It might be faster to send one big script to the server instead of 1 insert commands even if they are prepared. But I am not sure about that, this is something that needs to be checked. I am always sending a prepared statement:) Regards, Martin
