[firebird-support] Re: Slow connection establishment to database
--- In firebird-support@yahoogroups.com, Stefan Heymann wrote: > > Hello everybody, > > I had a strange effect on a customer's system today. We usually deploy > our application with a minimal Firebird client. So the application > folder contains > MyApp.exeand > fbclient.dll and a few other application-related files. > We are using the current incarnation of Firebird 2.5.2.26540, for both > server and fbclient. > > Usually the app folder is shared (read-only) and people start > MyApp.exe from their clients. This is easy to set up and works > perfectly. > \\myserver\myapp\MyApp.exe > > The installation today was on a Windows 2008 64-bit server that serves > as a database server and serves this file share at the same time. When > we started the application from a client, the program came up > immediately, but the connection to the database took very long (10 to > 20 seconds). After that, database communication was fast and normal. > > When I copied the folder to the local client, the connection was > established immediately. Deactivating virus checks on the server did > not help. > > When I share the folder *above* my application folder, the connection > is also established immediately and very fast. > > My suspicion to what is happening here is: > > During connection establishment, fbclient.dll tries to load a file it > usually finds one level above itself (firebird.conf? firebird.msg? > firebird.log?) > > As my program starts from the "root" of a Share, there is no parent > folder, but it takes some time until the OS reports that back (some > sort of timeout when trying to access \\myserver instead of > \\myserver\myshare ???) - so I suspect that is what takes so long. > > When the app folder is a sub-folder of the share, fbclient.dll > immediately finds that empty folder one level up. As it can work > perfectly without firebird.msg (or whatever it tries to read there), > everything goes well from there on. > > Can somebody confirm that this is/was the problem? I can confirm that fbclient.dll looks for firebird.conf at one folder above. I don't know why it takes too long but this is something OS (or your environment) specific. To avoid it i would try to set FIREBIRD environment variable to the fbclient folder. It should be done before fbclient is loaded by application. Hope it helps, Vlad
Re: [firebird-support] How to read the "Memory buffers" size via SQL?
which server you are using Linux or windows let me know then i will tell you perfect soln. From: Fabiano Kureck To: firebird-support@yahoogroups.com Sent: Thursday, 25 April 2013 2:24 AM Subject: [firebird-support] How to read the "Memory buffers" size via SQL? Hi! I must read the "Memory Buffers" parameter from the database using sql. I know that I can use gfix to change and gstat to read. I want to read it via a sql command. How do that? Thanks. [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
RE: [firebird-support] How to read the "Memory buffers" size via SQL?
> I must read the "Memory Buffers" parameter from the database using sql. It is not possible to read from SQL. (It is *very unusual* request) If you use Delphi as your programming language, it you be able to get the value through a call/method available from DB connection components (IBObjects, FIBPlus...). The same function may be available for other client component sets (java client, .Net Provider) but you would need to post to the appropriate support list -- I don't know those details. Sean
[firebird-support] How to read the "Memory buffers" size via SQL?
Hi! I must read the "Memory Buffers" parameter from the database using sql. I know that I can use gfix to change and gstat to read. I want to read it via a sql command. How do that? Thanks. [Non-text portions of this message have been removed]
RE: [firebird-support] SV: Forcing optimizer to use index
Rick, Please do not top-post and trim your replies (don't completely re-quote). Sean > -Original Message- > From: firebird-support@yahoogroups.com [mailto:firebird- > supp...@yahoogroups.com] On Behalf Of Rick Debay > Sent: Wednesday, April 24, 2013 2:17 PM > To: firebird-support@yahoogroups.com > Subject: RE: [firebird-support] SV: Forcing optimizer to use index > > The problem appeared when on a small test box the cache was reduced from > 8192 8KB pages to 4096 pages in order to reduce memory usage. The query is > extremely fast when run a second time, so I assume a large number of pages > have to be moved from disk to cache for each query. > > -Original Message- > From: firebird-support@yahoogroups.com [mailto:firebird- > supp...@yahoogroups.com] On Behalf Of Svein Erling Tysvær > Sent: Tuesday, April 23, 2013 1:32 PM > To: firebird-support@yahoogroups.com > Subject: [firebird-support] SV: Forcing optimizer to use index > > >I have a query that needs to use an index that the optimizer isn't > >using. How can I get it to add the index to the query plan? > >The current plan is > > > >PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX > >(I_PBM_CLAIM_NDC), AC RGM SA INDEX > (PK_ACT_CHC_PHARM_SUBACCT), AC RGM C > >INDEX (PK_CHC), AC RGM PRG INDEX (PK_CRM_PHARM_RPL_GRP), AC > RGM RG > >INDEX > >(PK_CRM_RPL_GRP))) > > > >And it needs to be > > > >PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX > >(I_PBM_CLAIM_NDC, I_PBMCLAIM_DATESBM), AC RGM SA INDEX > >(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM > PRG INDEX > >(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP))) > > > >Without I_PBMCLAIM_DATESBM instead of scanning a few months of data > >it's scanning a few years. > > Have you timed your query with the plans hardcoded to ascertain that it > really is quicker using your preferred plan, Rick? I doubt you'll get much > help > from this list without including the query itself, the index definitions with > a > little bit of additional information about their selectivity and tell us which > Firebird version you're using. You may be able to reduce the query (and > highlight your problem) by eliminating the non-important tuples of your > query (you definitely need AC D and AC CA C, but the rest may not matter for > your problem), but it might also be that someone will spot a better way to > write your query if you include the entire text. > > One thing regarding your desired plan: I think (don't know, it is just a > hunch) > that using several indexes for a table is most useful if it is the first > table in the > plan, so it might be better to have a plan like: > > PLAN SORT (JOIN (AC CA C INDEX(I_PBMCLAIM_DATESBM, another index>), AC D INDEX (), AC RGM SA > INDEX(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC > RGM PRG INDEX(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX > (PK_CRM_RPL_GRP))) > > Set > >
RE: [firebird-support] SV: Forcing optimizer to use index
The problem appeared when on a small test box the cache was reduced from 8192 8KB pages to 4096 pages in order to reduce memory usage. The query is extremely fast when run a second time, so I assume a large number of pages have to be moved from disk to cache for each query. -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær Sent: Tuesday, April 23, 2013 1:32 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] SV: Forcing optimizer to use index >I have a query that needs to use an index that the optimizer isn't >using. How can I get it to add the index to the query plan? >The current plan is > >PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX >(I_PBM_CLAIM_NDC), AC RGM SA INDEX (PK_ACT_CHC_PHARM_SUBACCT), AC RGM C >INDEX (PK_CHC), AC RGM PRG INDEX (PK_CRM_PHARM_RPL_GRP), AC RGM RG >INDEX >(PK_CRM_RPL_GRP))) > >And it needs to be > >PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX >(I_PBM_CLAIM_NDC, I_PBMCLAIM_DATESBM), AC RGM SA INDEX >(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM PRG INDEX >(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP))) > >Without I_PBMCLAIM_DATESBM instead of scanning a few months of data >it's scanning a few years. Have you timed your query with the plans hardcoded to ascertain that it really is quicker using your preferred plan, Rick? I doubt you'll get much help from this list without including the query itself, the index definitions with a little bit of additional information about their selectivity and tell us which Firebird version you're using. You may be able to reduce the query (and highlight your problem) by eliminating the non-important tuples of your query (you definitely need AC D and AC CA C, but the rest may not matter for your problem), but it might also be that someone will spot a better way to write your query if you include the entire text. One thing regarding your desired plan: I think (don't know, it is just a hunch) that using several indexes for a table is most useful if it is the first table in the plan, so it might be better to have a plan like: PLAN SORT (JOIN (AC CA C INDEX(I_PBMCLAIM_DATESBM, ), AC D INDEX (), AC RGM SA INDEX(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM PRG INDEX(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP))) Set ++ 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
Re: [firebird-support] Re: SQL ignores spaces?
On Wed, Apr 24, 2013 at 8:47 AM, uwekeim wrote: > > > > > in other words, before comparison, the shorter string is padded with > > "pad-character" (usually a space) to the length of the longer string. > > > > So, it's not a bug, but a SQL-standards feature. > > > > oops - okay, that's the explanation, although this behavior doesn't make > sense to me... > You're looking a a single case of various numbers of space characters in a column, including none.. Consider this case instead. Suppose through a failure of database design, I have fields in two tables, both fields called first_name. One is declared as "CHAR[15]" and the other as VARCHAR[15]. Someone stores 'Ann' in each field, then tries to join the two tables on first_name. As you know, CHAR fields are blank filled to their full size, so the values are 'Ann' and 'Ann'. If trailing blanks are significant, the two values don't match. I suppose the SQL committee could have said something like "trailing blanks are ignored iff a value includes a non-blank" and satisfied both goals - yours and mine - but I don't really think that's an improvement. Good luck, Ann [Non-text portions of this message have been removed]
Re: [firebird-support] Slow connection establishment to database
Hello, Stefan! Wednesday, April 24, 2013, 6:57:40 PM, you wrote: SH> During connection establishment, fbclient.dll tries to load a file it SH> usually finds one level above itself (firebird.conf? firebird.msg? SH> firebird.log?) use ProcessMonitor to watch where is the delay. Since it's about 10-20 seconds, you will easily see, when it happens and what folder fbclient tries to open. -- Dmitry Kuzmenko, www.ib-aid.com
[firebird-support] Slow connection establishment to database
Hello everybody, I had a strange effect on a customer's system today. We usually deploy our application with a minimal Firebird client. So the application folder contains MyApp.exeand fbclient.dll and a few other application-related files. We are using the current incarnation of Firebird 2.5.2.26540, for both server and fbclient. Usually the app folder is shared (read-only) and people start MyApp.exe from their clients. This is easy to set up and works perfectly. \\myserver\myapp\MyApp.exe The installation today was on a Windows 2008 64-bit server that serves as a database server and serves this file share at the same time. When we started the application from a client, the program came up immediately, but the connection to the database took very long (10 to 20 seconds). After that, database communication was fast and normal. When I copied the folder to the local client, the connection was established immediately. Deactivating virus checks on the server did not help. When I share the folder *above* my application folder, the connection is also established immediately and very fast. My suspicion to what is happening here is: During connection establishment, fbclient.dll tries to load a file it usually finds one level above itself (firebird.conf? firebird.msg? firebird.log?) As my program starts from the "root" of a Share, there is no parent folder, but it takes some time until the OS reports that back (some sort of timeout when trying to access \\myserver instead of \\myserver\myshare ???) - so I suspect that is what takes so long. When the app folder is a sub-folder of the share, fbclient.dll immediately finds that empty folder one level up. As it can work perfectly without firebird.msg (or whatever it tries to read there), everything goes well from there on. Can somebody confirm that this is/was the problem? Best Regards Stefan Heymann
RE: [firebird-support] SV: Forcing optimizer to use index
> Have you timed your query with the plans hardcoded to ascertain that it > really is quicker using your preferred plan, Rick? No, but I'm familiar with the data set and identified this query as running slow (on FB 2.5.2) compared to its speed on a (now decommissioned) FB 1.5.6 box. > query itself, the index definitions with a little bit of additional > information about their selectivity and tell us which Firebird version you're > using SELECT ca.ID FROM V_ALLOC_GPI ca WHERE ca.RPL_GRP = ? AND ca.GPI = ? AND ca.TEE = ? AND ca.UNIT_DOSE_USE = ? AND (ca.UNIT_DOSE_USE = '' OR ca.PKG_SZ = ?) AND ca.IS_BRAND = ? ORDER BY SIGN(ca.DECIMALQTY), ca.DATESBM The view is SELECT FROM V_ALLOCATION ca JOIN V_RPL_GRP_MEMBERS rgm ON ca.ACCOUNTID = rgm.CHC AND ca.SRVPROVID = rgm.PHARMACY JOIN MDDB_DRUG d ON d.PRODUCTID = ca.PRODUCTID WHERE ca.DATESBM >= rgm.OLDEST And I expected the date to come in to play from the WHERE clause in the view. The view V_ALLOCATION contains no joins, and contains the table (aliased as 'c') where I expected two indices to be used. Index I_PBM_CLAIM_NDC (3) Depth: 3, leaf buckets: 2749, nodes: 4415377 Average data length: 0.03, total dup: 4391899, max dup: 23871 Fill distribution: 0 - 19% = 1 20 - 39% = 0 40 - 59% = 0 60 - 79% = 0 80 - 99% = 2748 Index I_PBMCLAIM_DATESBM (1) Depth: 3, leaf buckets: 2723, nodes: 4415377 Average data length: 0.00, total dup: 4411954, max dup: 6433 Fill distribution: 0 - 19% = 0 20 - 39% = 0 40 - 59% = 1 60 - 79% = 0 80 - 99% = 2722 -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær Sent: Tuesday, April 23, 2013 1:32 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] SV: Forcing optimizer to use index >I have a query that needs to use an index that the optimizer isn't >using. How can I get it to add the index to the query plan? >The current plan is > >PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX >(I_PBM_CLAIM_NDC), AC RGM SA INDEX (PK_ACT_CHC_PHARM_SUBACCT), AC RGM C >INDEX (PK_CHC), AC RGM PRG INDEX (PK_CRM_PHARM_RPL_GRP), AC RGM RG >INDEX >(PK_CRM_RPL_GRP))) > >And it needs to be > >PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX >(I_PBM_CLAIM_NDC, I_PBMCLAIM_DATESBM), AC RGM SA INDEX >(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM PRG INDEX >(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP))) > >Without I_PBMCLAIM_DATESBM instead of scanning a few months of data >it's scanning a few years. Have you timed your query with the plans hardcoded to ascertain that it really is quicker using your preferred plan, Rick? I doubt you'll get much help from this list without including the query itself, the index definitions with a little bit of additional information about their selectivity and tell us which Firebird version you're using. You may be able to reduce the query (and highlight your problem) by eliminating the non-important tuples of your query (you definitely need AC D and AC CA C, but the rest may not matter for your problem), but it might also be that someone will spot a better way to write your query if you include the entire text. One thing regarding your desired plan: I think (don't know, it is just a hunch) that using several indexes for a table is most useful if it is the first table in the plan, so it might be better to have a plan like: PLAN SORT (JOIN (AC CA C INDEX(I_PBMCLAIM_DATESBM, ), AC D INDEX (), AC RGM SA INDEX(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM PRG INDEX(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP))) Set ++ 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
[firebird-support] Re: SQL ignores spaces?
(...) --- In firebird-support@yahoogroups.com, Frank wrote: > From the SQL1992 Standard: > > > 3) The comparison of two character strings is determined as fol- > > lows: > > > > a) If the length in characters of X is not equal to the length > > in characters of Y, then the shorter string is effectively > > replaced, for the purposes of comparison, with a copy of > > itself that has been extended to the length of the longer > > string by concatenation on the right of one or more pad char- > > acters, where the pad character is chosen based on CS. If > > CS has the NO PAD attribute, then the pad character is an > > implementation-dependent character different from any char- > > acter in the character set of X and Y that collates less > > than any string under CS. Otherwise, the pad character is a > > . > > in other words, before comparison, the shorter string is padded with > "pad-character" (usually a space) to the length of the longer string. > > So, it's not a bug, but a SQL-standards feature. > > been there too some years ago :-) > > fsg > > -- > "Fascinating creatures, phoenixes, they can carry immensely heavy loads, > their tears have healing powers and they make highly faithful pets." > - J.K. Rowling > oops - okay, that's the explanation, although this behavior doesn't make sense to me... thank you very much :-)
Re: [firebird-support] SQL ignores spaces?
On 04/24/2013 12:44 PM, uwekeim wrote: > Hello, > > i have a table with a field F1 sized VARCHAR(1). > > Some values are '' (field is empty). > > Astonishing following SQLs produce the same result: > > select * from test t where t.f1='' > > or > > select * from test t where t.f1=' ' > > or > > select * from test t where t.f1=' ' > > No matter how many spaces i insert into the sql, the result presents all > datasets where field is empty. > > I testet this with several databases and several Firebird versions: 1.5, 2.1 > and 2.5 (all 32 bit). Result is allways the same. > > In my oppinion this is a bug, isnt it? Only the first SQL shoud return the > datasets, where F1 is empty. Or am i wrong? >From the SQL1992 Standard: > 3) The comparison of two character strings is determined as fol- > lows: > > a) If the length in characters of X is not equal to the length > in characters of Y, then the shorter string is effectively > replaced, for the purposes of comparison, with a copy of > itself that has been extended to the length of the longer > string by concatenation on the right of one or more pad char- > acters, where the pad character is chosen based on CS. If > CS has the NO PAD attribute, then the pad character is an > implementation-dependent character different from any char- > acter in the character set of X and Y that collates less > than any string under CS. Otherwise, the pad character is a > . in other words, before comparison, the shorter string is padded with "pad-character" (usually a space) to the length of the longer string. So, it's not a bug, but a SQL-standards feature. been there too some years ago :-) fsg -- "Fascinating creatures, phoenixes, they can carry immensely heavy loads, their tears have healing powers and they make highly faithful pets." - J.K. Rowling
[firebird-support] Re: SQL ignores spaces?
Hello, the editor has trimmed my message, the 3rd SQL had 10 spaces between ' and '
[firebird-support] SQL ignores spaces?
Hello, i have a table with a field F1 sized VARCHAR(1). Some values are '' (field is empty). Astonishing following SQLs produce the same result: select * from test t where t.f1='' or select * from test t where t.f1=' ' or select * from test t where t.f1=' ' No matter how many spaces i insert into the sql, the result presents all datasets where field is empty. I testet this with several databases and several Firebird versions: 1.5, 2.1 and 2.5 (all 32 bit). Result is allways the same. In my oppinion this is a bug, isnt it? Only the first SQL shoud return the datasets, where F1 is empty. Or am i wrong?
