RE: [firebird-support] Gbaked and restored database larger than the original?
Take a look here: http://www.firebirdfaq.org/faq361/ I'd add that restore also has a -use_all_space option. If you don't specify this, then pages are filled to approximately 80% (80% is preferable over 100% unless it is a read-only database). See http://www.firebirdsql.org/manual/gbak-cmdline.html or http://www.firebirdsql.org/manual/gfix-pagespace.html for more information. Set -Original Message- From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 8. juli 2015 11:48 To: firebird-support@yahoogroups.com Subject: [firebird-support] Gbaked and restored database larger than the original? Hi, Just for my cursiosity and for a better understanding of Firebird: what can be a possible reason for a gbaked and restored (to a different file) database being _larger_ than the original one? I've got a db of size 104521728 bytes. I do gbak -B -T database.fdb database.gbk, then gbak -C database.gbk newdatabase.fdb. The size of newdatabase.fdb is 104751104 (~ 200kB more). I'm the only one messing with these databases, the platform is Linux x64, Firebird 2.5.2, all operations performed on the same machine, in the same file system. Before the backup-restore, a quite large chunk of metadata was added/altered in the original database (procedures, fields), which is the only clue I can think of at the moment (adding a default value to a field which had none?). It's quite obvious why a restored database might be smaller than the original one (no garbage), but the other way around is a bit puzzling. Both databases seem to work fine, so I'm in no trouble, but my ignorance in this matter bothers me nonetheless ;) I'd be grateful for any plausible explanations. thanks in advance and best regards Tomasz Posted by: Tomasz Tyrakowski t.tyrakow...@sol-system.pl ++ 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
SV: [firebird-support] general question: calculated fileds vs performance
Hello! Generally speaking, regarding performance, is expensive having calculated fields? Not complex ones, I mean this type of thing: ... COMPUTED BY (cast(qty * price * tax / 100 as money 2)) So far I don't notice any difference but I'd like to hear opinions about whether is a good idea using them. Probably having a real field, maintained with a trigger is better? I don't like having redundant data in my database, but I'm note sure what is the better option: waste a bit of space or (probably) decrease performance?? ? I've heard (don't remember who said it, and since I don't use calculated fields much myself, I cannot tell how reliable this information is) that calculated fields ought only to refer to the current record, i.e. not a different table or different record in the same table. So, as long as qty, price and tax all are part of the same record as your COMPUTED BY value, this ought to be fine. If they are stored in different lookup tables, then a view or a trigger maintained field may be preferrable. Hopefully, others will verify this or tell that I'm wrong. Set
RE: [firebird-support] Get ID of record with minsort
I have a table with one ID-Column and one Sort-Column. I need the ID from the record with the lowest sort-number. Sortnumbers are not unique (can be doubled). Can I do this with one SQL-Statement? If you want one row returned: Select ID_column From Table Order by Sort_Column Rows 1 If you want all rows returned: Select ID_column From Table t1 Where not exists(select * from Table t2 where t1.Sort t2.Sort) Alternatively, you could use With tmp(Sort) as (select min(Sort) from table) Select t.ID_Column From tmp Join table t on tmp.Sort = t.Sort Set
Re: [firebird-support] Soc Sec No comparison using Firebird
Greetings All, I would like to pass into a stored procedure two social security numbers for comparison and have the result tell me if one character has changed, or if two characters were switched or if it does not compare at all. Has anyone done anything like this they could share? Or is it even possible? Never done anything similar before, but strictly speaking, the following would answer your question: execute block (ss1 varchar(20) = :ss1, ss2 varchar(20) = :ss2) returns (comparison varchar(50)) as declare variable maxlen integer; declare variable curpos integer; declare variable ss1a char(1); declare variable ss1b char(1); declare variable ss2a char(1); declare variable ss2b char(1); begin Comparison = ''; maxlen = character_length(ss1); if (maxlen character_length(ss2)) then maxlen = character_length(ss2); if (ss1 = ss2) then comparison = 'Equal'; if (comparison = '' and (ss1 starting ss2 or ss2 starting ss1)) then comparison = 'Substring'; if (comparison = '') then begin curpos = 1; while (curpos = maxlen) do begin ss1a = substring(ss1 from curpos for 1); ss1b = substring(ss1 from curpos+1 for 1); ss2a = substring(ss2 from curpos for 1); ss2b = substring(ss2 from curpos+1 for 1); if (ss1a is distinct from ss2a) then begin if (comparison '') then comparison = 'Differ more than one character'; if (comparison='') then begin if (ss1a = ss2b and ss1b = ss2a) then begin comparison = 'swapped'; curpos = curpos+1; end if (comparison='') then comparison = 'One character differs'; end end curpos = curpos+1; end end suspend; end I say strictly speaking, because whereas this would catch the difference between 'Hello' and 'Helol' or 'Hello' and 'Hel1o', it wouldn't catch additional or missing characters like 'Hello' vs 'Helo'. Should be fixable, but I'll leave that task for you (I'd expect you to need curpos1 and curpos2). Having said all this, it wouldn't surprise me if there existed a UDF that did something similar to my execute block, I simply don't know since I've never needed one. HTH, Set
Re: [firebird-support] Firebird migration to increase perfomances
Yes I just checked my original query will return 6 rows without the distinct
OK, then lets add another CTE so that the calculations aren't multiplied:
with tmp1(surname, name, trxdate, source, job_number, creditnr, sourceid,
serviceadvisOR) as
(select distinct trim(c.name||' '||c.surname), trim(ca.name), j.jdate,
j.source, i.job_number, j.source2, j.sourceid, s.name||' '||s.surname
from journal j
join invoices i on j.source = i.inv_nr
left join customer c on j.cid= c.cid
left join cashcustomer ca on j.source = ca.invnr
and i.job_number = ca.jobnr
left join staff s on j.serviceadvisOR = s.staff_id
where j.source not starting 'STOCK UNIT'
and j.jdate between '2015/06/01' AND '2015/06/22'
and j.source not starting 'DEPOSIT'
and j.cid 0
and j.module 2
and ((j.source2 not starting 'S/D'
and j.source2 not starting 'P/D')
or j.source2 is null)
and j.description not starting 'SPLIT INV'
and j.jtype in (1, 4)),
tmp2(surname, name, trxdate, source, job_number, creditnr, sourceid,
serviceadvisOR, credVAT, VAT,invoicetotalINC, credinc, creditnote,
invoiceINCRedo, invoiceVATRedo, cost) as
(select t.surname, t.name, t.jdate, t.source, t.job_number, t.creditnr,
t.sourceid, t.serviceadvisOR
sum(iif(j.accnr = '5995300' and j.module in (66, 77) and t.source2 =
j.source2 and j.jtype = 1, coalesce(j.creditamount, 0) -
coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '5995300' and j.jtype = 1, coalesce(j.creditamount,
0) - coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '3993100' and j.jtype = 1, j.debitamount, 0)),
sum(iif(j.accnr = '1501500' and j.jtype = 1, coalesce(j.debitamount,
0)-coalesce(j.creditamount, 0), 0)),
sum(iif(j.accnr = '1501500' and j.jtype = 4, coalesce(j.creditamount,
0)-coalesce(j.debitamount, 0), 0)),
sum(iif(j.accnr = '3993100' and j.jtype = 1 and j.module = 77,
j.creditamount, 0)),
sum(iif(j.accnr = '5995300' and j.jtype = 1 and j.module = 77,
coalesce(j.debitamount, 0)-coalesce(j.creditamount, 0), 0)),
sum(iif(j.accnr starting '6' and j.jtype = 1, coalesce(j.debitamount,
0)-coalesce(j.creditamount, 0), 0))
from tmp1 t
join journal j on t.source = j.source
and t.trxdate = j.jdate
and j.jtype in (1, 4)
and (j.accnr in ('1501500', '3993100', '5995300')
or j.accnr starting '6')
group by 1, 2, 3, 4, 5, 6, 7, 8)
select surname, name, trxdate, source, job_number, creditnr, sourceid,
serviceadvisOR, credVAT, VAT, invoicetotalINC, invoicetotalINC-VAT+credvat as
invoicetotal,
credinc+creditnote-invoiceINCRedo+invoiceVATRedo credits,
invoicetotalINC-VAT+credinc+creditnote-invoiceINCRedo as nett, cost,
invoicetotalINC-VAT+credinc+creditnote-cost-invoiceINCRedo as gp,
invoicetotalINC-VAT+credinc+creditnote-cost-invoiceINCRedo/NULLIF(invoicetotalINC-VAT+credinc+creditnote,
0)*100 as gppersent
from tmp
where invoiceINC-invoicevat+credvat 0
order by 1,3,4
Does this get you the right result?
By the way, I'm positively surprised if the performance improved from almost 16
seconds to 0.16 seconds, I'd hoped for up to a 10-fold improvement, not
100-fold. I'm sure you didn't mean 0.16 milliseconds, Firebird is simply not
that quick.
HTH,
Set
RE: [firebird-support] Firebird migration to increase perfomances
Hi Set Wow your query runs like a bat out of hell J 0.16 Milliseconds !! and it easier to maintain as you say. The only issue is that it somehow multiplies the totals by 6 on all results as compared mine? i.e. mine will return an invoice total of say 18500.00 and yours will return 111000.00 This is across all the results any idea why? Would you get six times as many rows in your result set with your original query if you didn't have the DISTINCT, Stef? Or are any of the JOINs returning more than one row - e.g. are there six invoices for each entry in journal? This should be fairly simple to fix, but more information about the tables and how unique each JOIN are would be required. Set
RE: [firebird-support] Firebird migration to increase perfomances
At first glance, your plan seems OK. However, it would be better if we also could see the index definitions as well as get some information about index selectivity. And, this list removes attachments, so please post the text of the query as part of the message. Set Stef van der Merwe wrote: I am not a SQL master but do know the basics. I am have an issue with sub select queries and setting conditions. Attached is the query I have a problem with, see my comments on the last condition. P.S see below is my plan I am sure the query could be done better for faster performance any advise would be greatly appreciated !! Plan PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (STAFF INDEX (STAFF_IDX1)) PLAN (CUSTOMER INDEX (CUSTOMER_IDX1)) PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN SORT (JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX (INVOICES_IDX5))) Adapted Plan PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (STAFF INDEX (STAFF_IDX1)) PLAN (CUSTOMER INDEX (CUSTOMER_IDX1)) PLAN (CA INDEX (CASHCUSTOMER_IDX2, CASHCUSTOMER_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX16)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN (JOURNAL INDEX (JOURNAL_IDX6, JOURNAL_IDX1)) PLAN SORT (JOIN (J INDEX (JOURNAL_IDX1, JOURNAL_IDX2, JOURNAL_IDX2), I INDEX (INVOICES_IDX5))) Prepare time = 16ms Execute time = 15s 828ms Avg fetch time = 465.53 ms Current memory = 74 375 188 Max memory = 74 391 660 Memory buffers = 8 192 Reads from disk to cache = 253 Writes from cache to disk = 0 Fetches from cache = 730 569 [Non-text portions of this message have been removed]
RE: [firebird-support] Firebird migration to increase perfomances
Hi all, some years ago I developed a web application based on Interbase, Win98, PHP, Apache. During those yeas has been lightly updated with lase release of software, now the we application (10 users) use the follow: Microsoft Server 2012 (64bit) Apache 2.0 PHP 5.4 Firebird SQL 1.5 (3 databases, 250 Mbyte the bigger one) The application works very well, but I'm asking to the mailing list if it possible to have more performance considering the database side without make a big software refactoring. Which release do you suggest? What will be the percentage of performances get? There can be lots of reasons for a database application being slow. If the database have huge tables, and the application is written thinking in terms of tables as opposed to datasets, then things will be slow regardless of Firebird version. If it is one particular query that is slow, you may be lucky with the optimizer working better on 2.5, but the correct way to fix things would still be to change the query. You may also experience the opposite when upgrading, i.e. that a query that works great on Fb 1.5, are slower on 2.5. And there are lots of other things that can make a database application slow. Firebird 2.5 is generally better than Firebird 1.5, so a general advice would be to upgrade. However, there’s no “performance factor” that was set to 5 in Fb 1.5 and 8 in Fb 2.5 and you’ve told us too little about your situation for us to give you any good advice. Some things will be quicker, some may be slower. ‘It works very well, how can I improve with minimum changes?’ is an almost impossible question to answer without more information regardless of whether you’re talking about databases, playing soccer or cooking. Set
Re: [firebird-support] Firebird migration to increase perfomances
There can be lots of reasons for a database application being slow. If the database have huge tables, and the application is written thinking in terms of tables as opposed to datasets, then things will be slow Svein. An interesting comment, to me anyway, regarding tables vs datasets. I'm no SQL guru, I just use Firebird as the backend of an app I'm writing, but would you mind elaborating on what you've said. Just some buzzwords/key words I should use on a google search would be greatly appreciated. Sure, Andrew, this is nothing complicated at all. Once upon a time I used Paradox, a desktop database that I consider quite OK for small tables and single users. However, tables with one hundred thousand rows or so made things slow down considerably. Using Firebird the Paradox way, i.e. loading all records and all fields every time is a bad idea except for tiny lookup tables. Firebird is good at finding the fields of the records you're interested in - including joining tables or doing some minor calculations upon the fields. Using Firebird simply as a place to store tables and doing all selection in code in your app rather than using a WHERE clause will make things slower - you may compare it to me prefering to take what I want to eat out of the fridge rather than take everything out of the fridge, find what I want to eat and put the rest back into the fridge. Similarly, I prefer to name the fields I need, rather than use SELECT * (there are exceptions, of course). Generally speaking, a result set should only contain the data that you require for whatever you're doing, and the smaller the result set is, the better. Using Firebird the client/server way with appropriate indexing, it doesn't normally matter all too much how big tables and databases are, although I must admit that I'm only used to working with tables with a few million rows, and have no experience with really big databases. Set
RE: [firebird-support] Firebird 2.52 gbak fails to do a restore - error trigger (3)
Unlike Vlad, Helen and Thomas, I know next to nothing about problems like yours (I'm good at SELECTs, which is way different from corrupted backups). Hence, listen more to them than to me. gbak:restoring privilege for user SYSDBA gbak: ERROR:action cancelled by trigger (3) to preserve data integrity gbak: ERROR:table/procedure has non-SQL security class defined gbak:Exiting before completion due to errors I read this as attempting to grant SYSDBA something that is impossible. E.g. it wouldn't surprise me if InterBase 6 allowed you to grant EXECUTE to a table (EXECUTE only makes sense to grant to a procedure), and that Firebird barks since is stricter regarding syntax than IB 6 was. Now, I don't know how to restore a database without grants specified to the table (others may know), but I'd recommend you to try restoring using the -o switch (one table at a time), possibly also using the -i (set indexes inactive) and -n (check constraints disabled) switches. But as I said, this is rambling and guesswork, as opposed to Vlad's, Helen's and Thomas' knowhow. Set PS! Firebird 2.5 is a considerably better database than InterBase 6.0 was (although 2.5.1 had a flaw that almost required backup/restore when upgrading to 2.5.2 or higher)
[firebird-support] GROUP BY optional with constants?
I did a mistake with a query I wrote (Fb 2.5.3 or 2.5.4), but to my surprise Firebird did not complain and gave me the desired result. What I found was that whilst Firebird complains about missing GROUP BY if writing something like SELECT 'This does not work', RDB$RELATION_ID, list(RDB$CHARACTER_SET_NAME) FROM RDB$DATABASE It does not complain if constants are used: SELECT 'This works', list(RDB$CHARACTER_SET_NAME) FROM RDB$DATABASE or even SELECT 'This works', RDB$RELATION_ID, list(RDB$CHARACTER_SET_NAME) FROM RDB$DATABASE GROUP BY 2 Now, I do not object to not having to write GROUP BY - I'm just very surprised that it seems to be optional with constants. Is there any particular reason, or is this a 'bug to be appreciated rather than fixed'? Set
RE: [firebird-support] Re: Inssuficient rights for operation....
Hello. I used Firebird 1.5.6 on server on WIN7, a database with a role with full rights, and a user defined in ROLE, USER. I have a problem. In the network with a application, on different client station, login with the USER, all functions work OK. From server or another WIN7 station, with USER login, a have one error, with insufficient sql rights for operation, no permission to read/select acces to TABLE. All different functions work OK. ROLE with GRANTS in all tables. The solution from win7 is to give rights on USER for table CONFIG_BALANTA, the rights from the ROLE give this error WHY THE DIFFERENT functionality? Same user, same rights on database. You may well know all I'm writing here, just thought I should point it out if it happened to be a simple misunderstanding. It does not matter from where you log in. What does matter, is username (obviously) and that you connect using the correct role. Let's say I'm a member of two separate roles: DB_READ and DB_WRITE which have the obvious rights to a table that I have no access to myself. If I connect to the database specifying DB_WRITE as the role, I can do whatever I want, if I use DB_READ when connecting, then I can read, but not write data and if I do not specify any role when connecting, then I cannot do anything (not even read) unless rights have been granted me as a user rather than any role I'm a member of. HTH, Set
RE: [firebird-support] How to CAST float to integer with error?
Hello,
is it possible by using CAST or in any other way to get database error when
casting such number to integer?
SELECT CAST('13.245' AS INTEGER) FROM RDB$DATABASE
This gives 13 but instead I would like to get an error because precision is
lost.
However, I would like to NOT get an error when doing this:
SELECT CAST('13.000' AS INTEGER) FROM RDB$DATABASE
Becasue here precision is not lost.
Is it possible to obtain such behaviour in easy way or do I have to use
regular expressions?
Not quite what you're asking for, Bruce, but
SELECT CAST(13.245 AS INTEGER) FROM RDB$DATABASE
WHERE CAST(13.245 AS INTEGER) * 1000 = cast(13.245 * 1000 as Integer)
does return any rows, whereas this statement does:
SELECT CAST(13.000 AS INTEGER) FROM RDB$DATABASE
WHERE CAST(13.000 AS INTEGER) * 1000 = cast(13.000 * 1000 as Integer)
HTH,
Set
RE: [firebird-support] Parametrized queries and execute statement - dynamic parameter list
3. when i change proc to use execute statement with parameters SET TERM ^ ; CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER) AS DECLARE VARIABLE VAR_SQL VARCHAR(1000); BEGIN VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID'; EXECUTE STATEMENT (VAR_SQL) (ID := :ID) INTO :SUM_AAA; SUSPEND; END^ SET TERM ; ^ and run query SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC_PARAM(T.FIELD_ID) P) FROM TABLEX T than select is prepared only once - ok :) I'm impressed if Firebird understands that VAR_SQL doesn't change between each iteration so that preparing once is possible! 4. but how to change this sample code to use paramaters and do not prepare statement on every record? SET TERM ^ ; CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER) AS DECLARE VARIABLE VAR_SQL VARCHAR(1000); DECLARE VARIABLE VAR_BBB INTEGER; DECLARE VARIABLE VAR_NR INTEGER; BEGIN VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID '; VAR_NR = 1; FOR SELECT BBB FROM TABLEZ WHERE ID=:ID INTO :VAR_BBB DO BEGIN /* DO SOME CALCULATION ON VAR_BBB */ VAR_SQL = VAR_SQL || ' OR ID=:ID' || VAR_NR; How to add here parameter to list of parameters for below execute statement? END EXECUTE STATEMENT (VAR_SQL) (ID := :ID, ID1, ID2, ID3 ...) - How to specify parameters dynamically? INTO :SUM_AAA; SUSPEND; END^ SET TERM ; ^ Is this somehow possible? Or should i add this to the Firebird bug tracker as new feature request? This is important from performance point of view. Here you change the SQL for each iteration, hence I cannot see how it would be possible to prepare only once. What you could try, is to use a temporary table, change your statement to: SELECT SUM(Y.AAA) FROM TABLEY Y JOIN TMP_TABLE T ON Y.ID = T.ID and in your loop do INSERT INTO TMP_TABLE(ID) VALUES(:VAR_NR) HTH, Set
Re: [firebird-support] Why index is not used in this query?
SELECT * FROM TABLE_2 T2 INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID After executing this query I am getting such plan: PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2)) Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1? And why I am getting error when i try to enforce it with: PLAN JOIN (T1 INDEX (PK_TABLE_1), T2 INDEX (FK_TABLE_2)) Admittedly, my way of plan thinking is far from the way things are implemented, but still - it normally helps me understand why the optimizer behaves as it does. A simple query like yours I typically consider from the first table to the last in the plan. Hence, I'd start with T1. SELECT * FROM TABLE_1 T1 Here, there's no limiting JOIN, WHERE or anything similar that can be used to limit the number of rows to return (T2 isn't involved yet, hence any clause referring to that tuple cannot be considered). So, the quickest and only sensible option (at least unless you want to slow things down) is to go NATURAL on T1. Then, we add T2 INNER JOIN TABLE_2 T2 ON T2.TABLE_1_ID = T1.ID Here, you already have T1 available, and using an index for T2.TABLE_1_ID will help reducing the potential number of records to look up considerably. Is there no way to utilize the index for T1.ID? Sure there is, you can start with T2 rather than T1. However, then you would start with SELECT * FROM TABLE_2 T2 which cannot use an index. Adding INNER JOIN TABLE_1 T1 ON T2.TABLE_1_ID = T1.ID would, however, benefit from using the index for T1.ID. Is there really no way to use an index for both fields? No, not unless you add a WHERE (or ON) clause that limits the result set through including one or more constants, e.g. AND T1.ID between 2 and 4 Having said that, it is only on rather big tables that it is bad to see NATURAL on the first tuple in the plan (well, in more complex queries - e.g. involving subselects - it may not be the first tuple). And I think you would normally use a proper WHERE clause on big tables, although it is of course thinkable to e.g. want to have a query that should find all persons being a citizen of both London and Paris (millions of persons in both tables, but only a very small subset being part of both). Such rare queries are normally slowish, sometimes even after optimization. HTH, Set
Re: [firebird-support] Re: Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
Except that grouping by OPS_ZIP would not accomplish the same thing. Multiple zip codes map to the same REGION_ID and the zip codes might not be contiguous. Guess I'm going to have to rethink this. What about with tmp(PB_LOAD_TYPE, APPT_DATE, REGION_ID, PICKUP, DELIVERY) as (SELECT PB_LOAD_TYPE, CAST(COALESCE(OPS_ONBOARD_TIME, OPS_DELIVERED_TIME, OPS_APPT_LOW, 'TODAY') AS DATE), COALESCE((SELECT FIRST 1 CTRR_REGION_ID FROM CT_REGION_RANGES WHERE OPS_ZIP BETWEEN CTRR_LOW_ZIP AND CTRR_HIGH_ZIP ORDER BY CTRR_REGION_ID), 'N/A'), IIF(OPS_TYPE = 'P', 1, 0), IIF(OPS_TYPE = 'P', 0, 1) FROM OPS_STOP_REC LEFT JOIN OPS_HEADER ON PB_ID = OPS_ORDER_ID WHERE PB_TYPE = 'O' AND PB_DT_ENT = :PB_DT_ENT) SELECT PB_LOAD_TYPE, APPT_DATE, REGION_ID, sum(PICKUP) as PICKUPS, sum(DELIVERY) as DELIVERIES FROM tmp GROUP BY PB_LOAD_TYPE, APPT_DATE, REGION_ID ORDER BY APPT_DATE, PB_LOAD_TYPE, REGION_ID HTH, Set
Re: [firebird-support] Is it possible to do this with window function?
Hi guys. Hi Bruce! I am having such data: ID DATE_TIMETYPE 101.01.2015, 00:00:00.000A 201.01.2015, 00:01:00.000A 301.01.2015, 00:02:00.000A 401.01.2015, 00:10:00.000B 501.01.2015, 00:15:00.000B 601.01.2015, 00:20:00.000A 701.01.2015, 00:25:00.000A Is it possible to write query which will result: TYPE DATE_MIN DATE_MAX A01.01.2015, 00:00:00. 000 01.01.2015, 00:02:00.000 B01.01.2015, 00:10:00.000 01.01.2015, 00:15:00.000 A01.01.2015, 00:20:00.000 01.01.2015, 00:25:00.000 As you can see I would like to have three groups and not two like it would have been with usage of standard GROUP BY. I know I can write a selectable stored procedure which will result me such data but is it possible to do with new window functions and partitioning? Thanks. Well, that's a rather unusual enquiry, but your particular question can be solved by rather standard SQL (probably even worked on InterBase before Firebird was born). SELECT A.TYPE, A.DATE_TIME as DATE_MIN, B.DATE_TIME as DATE_MAX FROM MyTable A JOIN MyTable B ON A.TYPE = B.TYPE AND B.ID = A.ID LEFT JOIN MyTable ANot ON A.ID-1 = ANot.ID AND A.TYPE = ANot.TYPE LEFT JOIN MyTable NotBetween ON NotBetween.ID BETWEEN A.ID AND B.ID AND A.TYPE IS DISTINCT FROM NotBetween.TYPE LEFT JOIN MyTable BNot ON B.ID+1 = BNot.ID AND B.TYPE = BNot.TYPE WHERE ANot.ID IS NULL AND NotBetween.ID IS NULL AND BNot.ID IS NULL If there can be holes in the sequence - e.g. if the last record had ID 8 rather than 7 and you wanted it grouped with ID 6 even though there were no ID 7, things would still be feasible, but somewhat more complex. Sorry for not using Windows functions - I'm still on Fb 2.5 and don't have them under my hood yet. Set
SV: [firebird-support] SQL Case query takes 4 minutes 30 seconds to execute !!
[I've removed irrelevant details from your original question] I sincerely hope I am in the right place. We have a query which takes 4 minutes 30 seconds to execute this is simply too long. Please help ! Definitely, Stef, most performance problems can be solved on this list, and I agree that 4 and a half minute sounds long. CREATE TABLE JOURNAL ( JDATE DATE, SOURCE2 VARCHAR(40), ACCNR INTEGER, SUPPID INTEGER, JTYPE INTEGER); CREATE INDEX JOURNAL_IDX1 ON JOURNAL (JDATE); CREATE INDEX JOURNAL_IDX13 ON JOURNAL (SUPPID); CREATE INDEX JOURNAL_IDX16 ON JOURNAL (SOURCE2); CREATE INDEX JOURNAL_IDX2 ON JOURNAL (JTYPE); CREATE INDEX JOURNAL_IDX3 ON JOURNAL (ACCNR); There are only 350,000 (350 Thousand)records in the table Below is my actual sql query select distinct sum((case (extract (year from jdate )||''||extract ( month from jdate )) when 20154 then (coalesce(invoicetotal,0)-coalesce(paid,0)) else 0 end)) as Current_days, sum((coalesce(invoicetotal,0)-coalesce(paid,0)))as totaldue from (select (jdate)as jdate, (Select COALESCE(sum(debitamount),0 ) from journal where accnr = '5995100' and suppid=j.suppid and source2 = j.source2 and jdate = j.jdate)as paid, (Select COALESCE(sum(creditamount),0 ) from journal where accnr = '5995100' and suppid=j.suppid and source2 = j.source2 and jdate = j.jdate)as invoicetotal from journal j where j.jdate = '2015/04/28' and j.accnr = 5995100 and j.SUPPID = '1' and (j.jtype = 2 or j.jtype = 99)) having sum((invoicetotal-paid))0 order by 2 asc Please let me know if I should provide more information. The generated plan could have given more hints as to what is wrong, but I've tried to rewrite your query anyway. So, could you see if the following query gets the same result and whether or not it is any quicker? with DateSource(jdate, source2) as (select distinct jdate, source2 from journal where jdate = '2015/04/28' and accnr = 5995100 and SUPPID = 1 and jtype in (2, 99)), tmp(jdate, paid, invoicetotal) as (select d.jdate, sum(debitamount), sum(creditamount) from journal j join DateSource d on j.jdate = d.jdate and j.source2 = d.source2 where j.accnr = 5995100 and j.SUPPID = 1 group by 1) select sum(iif(jdate between '2015/04/01' and '2015/04/30', invoicetotal - paid, 0) current_days, sum(invoicetotal - paid) totaldue from tmp having sum(invoicetotal - paid) 0 order by 2 It would be interesting to learn whether or not this query is any quicker, and if it isn't, the plans of both your original query and my modified version. Please also check whether the modified query gets the correct result, there may be subleties that I missed. HTH, Set
RE: [firebird-support] SQLAnywhere to Firebird possibility
Hi, I have been a major contributor to Firebird since 2003, also my previous company BASX, which I was the Head developer of, was a gold sponsor of Firebird. I have recently moved to another company that uses SQLAnywhere. They are interested in moving to Firebird. But Firebird does not have some of the key features of SQL Anywhere, they depend on a lot. like XMLForest, XMLElement, etc, full XML compatibility is available in both SQLAnywhere and Oracle. like local temporary tables/ Hash Tables. like enabled select column aliases to be reused in same SQL. eg. SELECT (x/y) AS MYDIV ,MYDIV * Z AS MYDIVMULTIPLIED FROM RDB$DATABASE. Is it possible. If I put an option on the table, that the company I work for sponsors Firebird. and then Firebird could add this type of stuff? Your particular example I would typically write (using Fb 2.5, but I think it has been available since 2.0 or 2.1): With tmp(MYDIV) as (SELECT (x/y) from RDB$DATABASE) SELECT MYDIV, MYDIV * Z AS MYDIVMULTIPLIED FROM tmp The answer to your more important question about sponsorship, I would guess depends on the individual feature and how it fits with the general ideas of the Firebird future. Certainly sponsors can influence priorities in development. The developers sometimes lurk on this list, but I think firebird-devel could be a more proper place to discuss sponsorship since there are more of them there and it could be easier to see how the desires of your company fits the development. Set
RE: [firebird-support] Need Speed Optimization for SQL
I have below SQL and DDL of respective columns used in this SQL.
Below sql takes 6-7 seconds to give the result.
Would it be possible to bring the output time to 1 seconds, coz there are some
other operartion I need to perform based on this sql output withing short
period of t ime.
SQL:
SELECT
Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
FROM
CRM_DOCUMENT_ITEMS
INNER JOIN CRM_DOCUMENT_HEADER ON CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER =
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
WHERE
CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and CRM_DOCUMENT_ITEMS.FK_JOB_ITEM in
(select PK_JOB_ITEMS from CRM_JOB_ITEMS where FK_JOB =
'{8BDDED49-8509-48C1-A169-B7E68A74C230}')
GROUP BY
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
Hopefully, Rob or Virgos solutions worked, if not try this:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
HTH,
Set
RE: [firebird-support] Need Speed Optimization for SQL
Hi SET,
Nice to see you here, but I am getting below error after executing your sql, i
think group by clause is needed, would you please help?
Yes, sorry I forgot group by:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
group by cdi.FK_JOB_ITEM
HTH,
Set
RE: [firebird-support] Need Speed Optimization for SQL
Hi SET,
I tried as below way by adding Group By clause:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
group by cdi.FK_JOB_ITEM
And sql is taking 1.893 seconds
Any other way to make this more faster ? Please If possible
If you have indexes (or if they are keys) for these three fields:
CRM_JOB_ITEMS.FK_JOB
CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
then it will most likely be difficult to optimize this query any further
(sometimes having trigger generated summary tables can help, but that does
complicate things quite a bit and I've never seen it done when several tables
are involved, so I'd only consider this if this particular query was executed
frequently and was one of the most important queries in your system).
Note that while I may be considered a Firebird SELECT expert, I am a novice
regarding other parts of Firebird (like configuration settings), so maybe
someone else can help you speed up your query a bit more.
Set
[firebird-support] Re: Need Speed Optimization for SQL
CRM_DOCUMENT_HEADER Table - PK_DOCUMENT_HEADER is primary key and having seperate index on this column as well What do you mean by this, Vishal? I think it was fixed a while ago, but duplicate indexes used to confuse the optimizer, so PK_DOCUMENT_HEADER ought not to be both the primary key and have a separate single-field index (unless the PK or index is DESCENDING or consists of more than one field) - at best it makes INSERTs marginally slower due to having to maintain two identical indexes. Set
Re: [firebird-support] SQL puzzle: Order based on Prerequisite
In a table called Steps, I have the following fields: ID, B4Me, Dsc, -ID field contains a unique ID for each record -B4Me contains the ID of some other record in the table that MUST appear in a result set, BEFORE this record. B4Me may be null. This is called the B4Me order. -Records will be entered in random order, so Natural order cannot be relied upon as a substitute for B4Me I need an SQL statement that will return all records with a user supplied search word (such as oil, or glycol or micron) in the Dsc field, which could return a variable number of records, but what ever the number of records returned, the *order* must be such that each record is preceded by the record who's ID matches the B4Me value of a given record.nbs p; Important Notes: - The SQL may return one or more records with a blank B4Me. In which case, Natural order can be used for those records, and they are all considered First or rather, Before everything else. Once all the records with a blank B4Me are listed, the remaining must be in the B4Me order. - It is possible, though unlikely, that following the B4Me order will result in a circular reference. (Two or more records that reference each other, or the last record in a large result set, references the first (or any other in the result set) record in the B4Me field). In these cases, the SQL must either list all records until a circular reference is detected, then don't list the offending record, or display an error message, but it must not hang FB and get an out of memory error. Thanks in advance for any help you can provide. The possibility of a circular reference, makes this a difficult task. SQL is great for working with sets of data, iteration and recursion are more difficult, but doable with CTEs or stored procedures, but I cannot think of how to exit a circular reference gracefully within one statement. Hence, I'd recommend using a global temporary table: CREATE GLOBAL TEMPORARY TABLE MyTmp (ID INTEGER, OrderNo INTEGER) ON COMMIT DELETE ROWS; --doesn't really matter whether you commit or delete rows in this particular case... and then EXECUTE BLOCK or a STORED PROCEDURE; EXECUTE BLOCK(MySearch varchar(50) = :MySearch) Returns(OutID Integer) as declare variable ID integer; declare variable ID2 integer; declare variable OrderNo integer; begin delete from MyTmp; --start afresh every time you execute this block OrderNo = 1000; for select ID, B4Me from Steps where Dsc containing :MySearch into :ID, :ID2 do begin while (:ID is not null) do begin if exists(select * from MyTmp where ID = :ID) then begin --This is how to escape from a circular reference id = null; end else begin INSERT INTO MyTmp(ID, OrderNo) VALUES(:ID, :OrderNo); OrderNo = OrderNo - 1; ID = ID2; if (ID is not null) then begin SELECT B4Me from Steps where ID = :ID into :ID2; end end end end for select ID from MyTmp order by OrderNo into :OutID do suspend; end I've interpreted Before everything else to mean before any of its own children, not before any children regardless of heritage - or, to put it simple - you would appear before all your children, but not necessarily before my children. Hopefully, this can serve as a template for how you can solve your situation, despite being more than one statement. Set
RE: [firebird-support] Recursive CTE question
Can the question be rephrased as you being interested in which sets are identical, Elias? If I understand things correctly, I would assume double negation to be the simple answer you're looking for: with tmp(id) as (select distinct id from CRITICALPARAMVALS) select t.id, t2.id from tmp t join tmp t2 on t.id t2.id where not exists(select * from CRITICALPARAMVALS a where t.id = a.id and not exists(select * from CRITICALPARAMVALS b where t2.id = b.id and a.PARAM=b.param and (exists (select isequal from TEST_FLOAT_EQ(a.val, b.val, 1e-5) where ISEQUAL=1 Sorry, the above select returns equal sets, but also sets where B is a superset of A. You need to add and not exists(select * from CRITICALPARAMVALS a where t2.id = a.id and not exists(select * from CRITICALPARAMVALS b where t.id = b.id and a.PARAM=b.param and (exists (select * from TEST_FLOAT_EQ(a.val, b.val, 1e-5) where ISEQUAL=1 for it to only find identical sets. Needless to say, such a query can be a bit slow with huge tables. Moreover, I don't know how TEST_FLOAT_EQ is implemented or why you use this rather than a.val BETWEEN b.val - 0.1 and b.val + 0.1 HTH, Set
RE: [firebird-support] V1.56 query killing my V2.54 app
Ok, used the +0 and worked. On v1.56 I was used with setting up a high granularity data column (col04Int - part of the primary key) with a True/false (0/1) type of column (ColDetSmIntFlag) to boost the selectivity of the index IXColDetSmIntFlag. I kept the index with that configuration for a just in case. (The stat of the index is 0,01407...) Set, don't get me wrong, I am very gratefull for your help and for Firebird, but saying that a Natural on a big table seems better than an index doesn't compute for me, and I've been using Firebird since Interbase and Oracle since v6 (as DBA BTW). At a least case scenario it should use the PK when there is a declared join using the PK. For me, the new optimizer is wierd and highly illogical. Glad to hear that +0 worked. We used Fb 1.5 for a long time (switched from 1.5 to 2.5 a year or two ago) and I’m happy to say that my impression generally is that the optimizer in 2.5 is better than it was in 1.5 and that the need for tricks like +0 is less than it used to be. Though there will of course be differences, and it is not a surprise that you notice some drawbacks rather than benefits when upgrading a particular database (I assume you have optimized poorly performing queries with Fb 1.5 long ago, rather than been waiting for the upgrade). If you compare new, unoptimized queries between 1.5 and 2.5, odds are that you would conclude that 2.5 are better. I guess the reason for the optimizer preferring the NATURAL plan is a combination of msttbl having about half the number of records of dettbl and the index on ColDetSmIntFlag having lousy selectivity (the optimizer doesn’t have histograms yet (I am uncertain whether histograms are part of Firebird 3 or if it will be a later release), and doesn’t know that 1 is a less frequent value than 0). Set
Re: [firebird-support] V1.56 query killing my V2.54 app
Hi all, I'm trying to upgrade an app to v2.54 from v1.56 but there are some queries that aren't planning the way they should. I have this query: Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45 from dettbl b Inner Join msttbl a on (a.col01Int = i.col01Int and a.col02SmInt = i.col02SmInt and a.col03SmInt = i.col03SmInt) where b.ColDetSmIntFlag = 1 Order by a.col04VarCh45 on v1.56 I get this plan: PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl and on v2.54 I get PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl that is killing my app. The tables in question have 450K+ and 800K+ rows. ALTER TABLE msttbl ADD CONSTRAINT PK_msttbl PRIMARY KEY (col01Int, col02Int, col03Int); CREATE INDEX IXmsttbl_col04VarCh45 ON msttbl (col04VarCh45); ALTER TABLE dettbl ADD CONSTRAINT PK_dettbl PRIMARY KEY col01Int, col02Int, col03Int, col04Int); ALTER TABLE dettbl ADD CONSTRAINT FK_dettbl FOREIGN KEY (col01Int, col02Int, col03Int) REFERENCES msttbl (col01Int, col02Int, col03Int); CREATE INDEX IXColDetSmIntFlag ON dettbl (ColDetSmIntFlag, col04Int); I've been changing the query and trying to change indexes to no success. Is there a way to force FB 2.54 to chose the right indexes? Hi Andrew! Forcing a correct plan is not always benefitial or possible, but it is normally quite easy to prevent a bad plan from being used. I'd recommend you to try: Select Distinct a.col01Int, a.col02SmInt, a.col03SmInt, a.col04VarCh45 from dettbl b Inner Join msttbl a on (a.col01Int = b.col01Int+0 and a.col02SmInt = b.col02SmInt and a.col03SmInt = b.col03SmInt) where b.ColDetSmIntFlag = 1 Order by a.col04VarCh45 The additional +0 should prevent FK_dettbl from being used, but there's no guarantee that another poor plan cannot be chosen, so this kind of modification sometimes needs a couple of attempts before you reach a good result. HTH, Set
Re: [firebird-support] Recursive CTE question
Hi everyone! This might not be a straightforward Firebird question, but I'm hoping there's a feature I'm unaware of that can help me beyond plain-vanilla SQL. I have two tables. The first is a list of names of critical parameters, and the second relates certain object IDs, critical parameter names, and critical parameter values: [removed CRITICALPARAMS definition since I don't think it is of importance to the question] CREATE TABLE CRITICALPARAMVALS ( ID INTEGER NOT NULL, PARAM Varchar(32) NOT NULL, VAL Float NOT NULL, CONSTRAINT PK_CRITICALPARAMVALS_1 PRIMARY KEY (DATAPOINTHASH,PARAM) ); insert into CRITICALPARAMVALS values (1, 'a', 0.0); insert into CRITICALPARAMVALS values (1, 'b', 0.0); insert into CRITICALPARAMVALS values (1, 'c', 2.0); insert into CRITICALPARAMVALS values (1, 'foo', 99.0); insert into CRITICALPARAMVALS values (2, 'a', 0.0); insert into CRITICALPARAMVALS values (2, 'b', 0.0); insert into CRITICALPARAMVALS values (2, 'c', 2.0); insert into CRITICALPARAMVALS values (2, 'foo', 99.0); insert into CRITICALPARAMVALS values (3, 'a', 0.0); insert into CRITICALPARAMVALS values (3, 'b', 0.0); insert into CRITICALPARAMVALS values (3, 'c', 1.0); insert into CRITICALPARAMVALS values (3, 'foo', 98.0); insert into CRITICALPARAMVALS values (4, 'a', 0.0); insert into CRITICALPARAMVALS values (4, 'b', 0.0); insert into CRITICALPARAMVALS values (4, 'c', 1.0); insert into CRITICALPARAMVALS values (4, 'foo', 98.0); insert into CRITICALPARAMVALS values (5, 'a', 0.0); insert into CRITICALPARAMVALS values (5, 'b', 0.0); insert into CRITICALPARAMVALS values (5, 'c', 2.0); insert into CRITICALPARAMVALS values (5, 'foo', 98.0); The problem is to partition the critical parameter space, grouping together all object IDs that have the same parameter values. We can think of using a seed object ID, and asking what other IDs belong to the same partition as the seed object. In our example, objects 1 and 2 form a partition, 3 and 4 form another, and 5 forms a third. All five objects are equal in the critical parameters a and b, but differ in parameters c and foo. Is there any way to solve this using plain-vanilla SQL? How about a recursive CTE? Can the question be rephrased as you being interested in which sets are identical, Elias? If I understand things correctly, I would assume double negation to be the simple answer you're looking for: with tmp(id) as (select distinct id from CRITICALPARAMVALS) select t.id, t2.id from tmp t join tmp t2 on t.id t2.id where not exists(select * from CRITICALPARAMVALS a where t.id = a.id and not exists(select * from CRITICALPARAMVALS b where t2.id = b.id and a.PARAM=b.param and (exists (select isequal from TEST_FLOAT_EQ(a.val, b.val, 1e-5) where ISEQUAL=1 At least, I guess (haven't tested anything, just know that double negation has been an answer to set equality before) this query would return two rows like this: 1 2 3 4 HTH, Set
Re: [firebird-support] V1.56 query killing my V2.54 app
Actually, the problem isn't with FK_dettbl, but with the A Natural. In a Master detail relationship with 450K+ rows in the master and 800K+ rows in the detail, that natural is a killer. The problem is that Firebird thinks that PLAN SORT (SORT (JOIN (A NATURAL, B INDEX (FK_dettbl is a better choice than PLAN SORT (SORT (JOIN (B INDEX (IXColDetSmIntFlag),A INDEX (PK_msttbl The optimizer doesn't think NATURAL is better than using an index, it thinks that the selectivity of FK_dettbl is so much better than IXColDetSmIntFlag and PK_msttbl combined that it outweights the cost of going NATURAL on A. Normally, it isn't all too bad to go natural on the first table of a plan, but b.ColDetSmIntFlag = 1 may be more selective than the optimizer thinks and then preventing a particular plan from being used can help speed up queries like yours. The +0 should prevent FK_dettbl from being used, and I'm pretty sure the optimizer will not try to go natural on both tables. Hence the optimizer should suggest another plan - hopefully the one you want. HTH, Set
RE: [firebird-support] Cast as Numeric without parenthesis
I would expect numbers in the range -2147483648 to +2147483647 to be accepted
(the size of an integer), and then there’s a separate field for storing whether
things are shifted to the left or right. I’ve no experience or theoretical
knowledge confirming this, but your examples would fit such a description.
Hence, I would guess NUMERIC to default to NUMERIC(9, 0).
I've confirmed that
SELECT CAST('2147483647' as numeric) FROM RDB$DATABASE
SELECT CAST('2147483647' as numeric(9,0) FROM RDB$DATABASE
both are accepted, whereas
SELECT CAST('2147483648' as numeric) FROM RDB$DATABASE
SELECT CAST('2147483648' as numeric(9,0) FROM RDB$DATABASE
are rejected.
HTH,
Set
RE: [firebird-support] Cast as Numeric without parenthesis
then why here is an error?
SELECT CAST('123456789.23' as numeric) FROM RDB$DATABASE
Logically speaking I don't understand why
SELECT CAST('123456789.23' as numeric(9,0)) FROM RDB$DATABASE
is failing when
SELECT CAST('1.23' as numeric(1,0)) FROM RDB$DATABASE
succeeds.
Practically, I guess it means that Firebird first converts from a (var)char to
an intermediate integer field before trying to round to nearest whole number,
but I don't know why.
Normally, I don't store numbers in strings, and
SELECT CAST(123456789.23 as integer) FROM RDB$DATABASE
succeeds.
Here, I find that
SELECT CAST(123456789.12345678 as decimal) FROM RDB$DATABASE
succeeds, whereas
SELECT CAST(123456789.123456789 as decimal) FROM RDB$DATABASE
fails. I don't know why.
Sorry for not being able to shed any more light,
Set
RE: [firebird-support] Extract week of the year (within year)
is possible to get week of the year within year?
I see that built in functions only support ISO-8601 (or i do not know some
parameter)
Actually, Karol, “week within year” is not the week, Karol. 1 January could be
week 53 and 31 December could be week 1. If you want to consider 1 January
always as part of week 1 (even if that day could be the only day of week 1),
I’d suggest you try something like:
select (extract(yearday from cast('1.1.2014' as date))-extract(weekday from
cast('1.1.2014' as date)))/7+1
from rdb$database
If you want the first seven days of a week to always be week 1 (and changing
which day a week start with for each year), you could drop the second EXTRACT.
HTH,
Set
RE: [firebird-support] How To Generate Auto Increment Number in SQL Itself ?
HI Louis, It's pretty cool, but i need only in SQL, that too same number for all records for same employee and next incremental number for all records of the another employee. SET any help offered ? The first thing that comes to mind, is EXECUTE BLOCK (which is basically a STORED PROCEDURE built dynamically and not stored anywhere and that can be used anywhere a SELECT can be used, at least for recent Fb versions) combined with a temporary table, i.e. EXECUTE BLOCK RETURNS (output params) AS DECLARE VARIABLE RowNo INTEGER; DECLARE VARIABLE EmpCode Empcode type; BEGIN RowNo = 1; FOR SELECT DISTINCT EmpCode FROM your entire where clause INTO :EmpCode DO BEGIN INSERT INTO MyTempTable(RowNo, EmpCode) VALUES (:RowNo, :EmpCode); RowNo = RowNo + 1; END FOR SELECT Whatever you want FROM MyTempTable MTT JOIN all your other tables WHERE your entire where clause INTO output params DO SUSPEND; END It might also be possible to actually create a stored procedure similar to: CREATE PROCEDURE (MyID whatever RETURNS (RowNo Integer) AS BEGIN RowNo = SELECT RowNo FROM MyTempTable WHERE MyID = :MyID; IF (RowNo IS NULL) THEN BEGIN RowNo = SELECT GetContext(SetContext(GetContext) --Don't have time now to find out exactly how INSERT INTO MyTempTable(RowNo, MyID) VALUES(:RowNo, :MyID); END END Then you'd only use a LEFT JOIN to this stored procedure in your queries (this could be reusable for different queries, not tied to your particular query). HTH, Set
RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010
For iLoop as Integer=0 to grid.Rows.Count-1'Grid Has 100,000 Rows
'-Sometimes Hang Here Also-
rdr = Get_Reader(USP_CHECK_AND_GET_ITEM_EXIST('
Mid(Trim(dicFields_Name.Item(SP_ITEM_CODES)), 1, 20) ','
Mid(Trim(dicFields_Name.Item(ITEM_PART_CODES)), 1, 20) ','
Mid(Trim(dicFields_Name.Item(ITEM_NAMES)), 1, 50) ','
Mid(Trim(dicFields_Name.Item(CUSTOM_PART_CODE)), 1, 20) ','
Mid(Trim(dicFields_Name.Item(CUSTOM_PART_NAME)), 1, 50) ','
Mid(Trim(dicFields_Name.Item(SALE_UNIT)), 1, 10) ','
Mid(Trim(dicFields_Name.Item(SALE_SUB_UNIT)), 1, 10) ','
Mid(Trim(dicFields_Name.Item(ITEM_LOCAL_TAX)), 1, 20) ','
Mid(Trim(dicFields_Name.Item(ITEM_GROUP)), 1, 50) ','
Mid(Trim(dicFields_Name.Item(ITEM_CATEGORY_ID)), 1, 50) '), fbTrans, ,
myImportConnection)
...
If Is_Update=True Then
If
Update_Item_Master(nItem_IDS,Item_Name,Rate,bla,bla,bla,myImportConnection)=True
Then
...
Public Function
Update_Item_Master(Var1,Var2,Var3,Bla,Bla,Bla,myImportConnection)
Try
'Insert Statement
PrePare_Column_Parameter_And_Values
Using fCommand As FbCommand = myImportConnection.CreateCommand
fCommand.Cancel()
fCommand.CommandText = Insert Into M_ITEM(
strColumns.ToString.TrimEnd(,) ) Values(
strValues.ToString.TrimEnd(,) )
fCommand.Connection = myImportConnection
fCommand.CommandType = CommandType.Text
fCommand.Transaction = fbTrans
myResult = fCommand.BeginExecuteNonQuery(Nothing, Nothing)
'While Not myResult.IsCompleted
'Me.Text = lblInvoice_Caption.Text [Processing Row No.
Row_No.ToString() - Adding In Item Master]
'End While
'iError = fCommand.EndExecuteNonQuery(myResult)
iError = fCommand.ExecuteNonQuery() '---Hang In THis
Line
fCommand.Parameters.Clear()
End Using
Return True
Catch ex as Exception
return False
Finally
End Try
End Function
Thanks, though not quite the type of code I'd expected (I'd expected more SQL
and less VB). I notice a couple of things:
a) You try to insert when Is_Update is true (not false)
b) You create a completely new statement for each row
Now, I don't know whether or not it is deliberate to INSERT inside the
Update_Item_Master function or not. What I do know, is that creating a new
statement 10 times is considerably slower (though 10 hours would mean only
three record per second and that would surprise me) than writing one statement
with parameters, prepare it once and execute it 10 times with different
parameter values.
I do not know VB and cannot give much other hints from this code, I guess the
problem could either be on the Firebird side (you could show us more
Firebird-related code, e.g. the source of SP_ITEM_CODES if that is a Firebird
stored procedure, or one of the troublesome INSERT queries that you end up
creating (i.e. strColumns and strValues expanded) or on the VB side (this list
cannot help with VB issues).
Set
RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010
update never fails but it fails in insert query. loop run in a single connection with a new datarows in same query. I think it is about time to show us some code (not all). Could it be that another transaction not visible to your current transaction has inserted a row that creates a lock conflict with your insert and that your code repeatedly tries (and fails) to insert? Or that it is a real deadlock, where one transaction first successfully updates record A, then fails on record B, whereas another transaction has successfully updated record B and fails on record A? Typically, there would be no problems on INSERT, whereas UPDATE could create problems. Getting the problem on INSERT, I'd say indicates some kind of lock conflict which means that you have some UNIQUE field or constraint or don't use generators to populate your primary key. Set
RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010
please, somone guide me where i am wrong This support list is for the Firebird database. For the .NET providers, you need to subscribe to the firebird-net-provider list: List-Subscribe: https://lists.sourceforge.net/lists/listinfo/firebird-net-provider, At first glance it sounds like a lock conflict with a wait transaction, which is not specific to .net. To me this list would seem to be better unless and until it requires .net provider specific information. If it is a lock conflict, then the process would hang on one out of 100 000 records. Using wait transaction(s) for batch processing of 100 000 records may not be a good idea, in most cases it would be better to return back some error in a log and continue processing those records that succeed. Though we don't yet know whether or not it is a lock conflict. I'd recommend Sukhen to change from WAIT to NO WAIT (if this is an issue) and if this doesn't help, to give us further information: Does the update process successfully process some record before taking extremely long on one record? Does the update process sometimes succeed within minutes or an hour or two, but then suddenly take over 10 hours? Are you constructing a new query for every insert/update and use 10 transactions or even connections, or do you have one UPDATE OR INSERT statement using parameters all running inside the same transaction (or even declaring an external table for the import)? Set
RE: [firebird-support] Hang On ExecuteNonQuery Using Firebird2.5 with visual studio 2010
Sukhen wrote: update never fails but it fails in insert query. loop run in a single connection with a new datarows in same query. I answered: I think it is about time to show us some code (not all). Could it be that another transaction not visible to your current transaction has inserted a row that creates a lock conflict with your insert and that your code repeatedly tries (and fails) to insert? Or that it is a real deadlock, where one transaction first successfully updates record A, then fails on record B, whereas another transaction has successfully updated record B and fails on record A? Typically, there would be no problems on INSERT, whereas UPDATE could create problems. Getting the problem on INSERT, I'd say indicates some kind of lock conflict which means that you have some UNIQUE field or constraint or don't use generators to populate your primary key. Sukhen answered: is it could be any buffersize/memmroy issue. please, suggest if it is how to check it in firebird please, somebody help me out I didn't answer because I know nothing about buffersize/memory issues with Firebird (other than knowing that buffersize/memory very rarely causes problems with Firebird itself, don't know about the components you use in VB to connect to Firebird). Normally, INSERTs would never fail. When they fail, that indicate some duplicate in a unique index or constraint or primary key (though that ought to give an error rather than be time consuming). Or it could possibly be an infinite loop. INSERTs can be slow, depending on complexity. Show us some code, I'm particularly interested in the INSERT that fails and the table definitions for the tables that the INSERT refers to. Set
RE: [firebird-support] Multiple WHERE clauses
You may consider
WITH TMP(My72, cFrom, cTo, dValueFrom, dValueTo) as
(SELECT CAST(:My72 as integer), US1.dValue, US2. dValue FROM RDB$DATABASE
LEFT JOIN dSetValue('Current Year', 'From') US1 ON (1=1)
LEFT JOIN dSetValue('Current Year', 'To') US2 ON (1=1))
SELECT whatever
FROM TMP t
CROSS JOIN tDevelopment d
WHERE d.iID = t.My72 OR
(t.MyParam is null AND CAST(d.tDT AS DATE) BETWEEN t.dValueFrom,
t.dValueTo)
I’ve assumed the dSetValue procedure returns 0 or 1 record. I did this because
the way you wrote things, the procedure would be executed twice for every row
in tDevelopment, something not necessary unless result of the procedure depends
on values in tDevelopment (which it doesn’t in your case, since ‘Current Year’,
‘From’ and ‘To’ are constants).
HTH,
Set
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 23. februar 2015 06:25
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Multiple WHERE clauses
Hi all
I have a SELECT statement which needs to have a WHERE clause adapt if a
variable has a value or not:
WHERE CASE WHEN 72 0 THEN tDevelopment.iID = 72
ELSE (CAST(tDevelopment.tDT AS DATE) BETWEEN (SELECT US1.dValue
FROM dSetValue('Current Year', 'From') US1)
AND (SELECT US2.dValue
FROM dSetValue('Current Year', 'To') US2)) END
The value 72 is variable. What I need to do is when this value is a non zero I
want to get only one record which matches the variable value and iID
ELSE I want it to adapt to get a range of records as per stored dates
Can we have such a WHERE clause? (The one above does not work for me) What if
there are more such varied conditio ns to apply?
Please advise
Thanks and regards
Bhavbhuti
[Non-text portions of this message have been removed]
RE: [firebird-support] Charset in ISQL: How to show special characters
my impression was the other way arround ;-) Then I'm either misreading your e-mail, or you've switched the examples around? When I change the charset in isql to any charset, that is definitly not used in the database I always get the same output from isql. I was expecting that the presentation of the same data (= varchar with special characters) would change in isql if I switch charset. What application is show ö and what isn't? And is that valid? Two further questions: Was the field that you imported into defined with ISO8859_1 at the time of import or is this something that has changed later? And how did you import (I don't know if this second question matter, just wondered if you did something very unusual). Set
RE: [firebird-support] Know nothing - please help
I have a query like this: select artikli.a_sif, v1.v_txt, v2.v_txt, coalesce(sum(magkol.k_total),0) total, coalesce(sum(magkol.k_nabcen * magkol.k_total),0) vrednost, coalesce(sum(magkol.k_nabcen * magkol.k_total),0)/ coalesce(sum(magkol.k_total),1) nc, coalesce(sum(izlaz.i_kol),0) P_kom from artikli left outer join izlaz on (artikli.a_id = izlaz.i_id) left outer join magkol on (artikli.a_id = magkol.k_art) and magkol.k_mag0 right outer join vrste v1 on v1.v_id=artikli.a_vrs left outer join vrste v2 on v2.v_id=v1.v_nad where artikli.a_sif='R3728' group by 1,2,3 everyting works OK, except that last sum() : coalesce(sum(i zlaz.i_kol),0) P_kom It gives me sum result muliplied with number of occurences of that article ID in magkol table. Well, it may not be what you want, Zoran, but it is what you ask for. To rather get what you want, you could try changing your query to something like: with tmp(a_sif, P_kom) as (select a.a_sif, coalesce(sum(i.i_kol), 0) from artikli a left join izlaz I on a.a_id = i.i_id where a.a_sif='R3728' and exists(select * from vrste v where v.v_id=a.a_vrs)) select a.a_sif, v1.v_txt, v2.v_txt, coalesce(sum(m.k_total),0) total, coalesce(sum(m.k_nabcen * m.k_total),0) vrednost, coalesce(sum(m.k_nabcen * m.k_total),0)/ coalesce(sum(m.k_total),1) nc, t.P_kom from artikli a join tmp t on a.a_sif = t.a_sif join vrste v1 on v1.v_id=a.a_vrs left join magkol m on a.a_id = m.k_art) and m.k_mag 0 left join vrste v2 on v2.v_id=v1.v_nad group by 1, 2, 3, 7 Note that I removed your right join since your where clause logically and practically makes it an inner join. Not knowing your data, I don't know whether the EXISTS can be removed or not or if you can replace some of the left joins with simply join. HTH, Set
Re: [firebird-support] Query too slow
Hi; I am using FB 2.5.3. in a fast environment (win8 64bit - QuadCore 3.4 Ghz - 8 GB ram). If I run this query it goes very very fast and returns 84 records: Preparing query: select distinct PROC from GEST where (GEST.FSAL between '20080801' and '20080812') order by PROC Prepare time: 0.007s Field #01: GEST.PROC Alias:PROC Type:STRING(10) PLAN SORT ((GEST NATURAL)) But if I run this one, it will return 84 records but it needs 10 minutes !!!: Preparing query: select PROC.PROC from PROC where (PROC.PROC in ( select distinct PROC from GEST where (GEST.FSAL between '20080801' and '20080812'))) order by PROC Prepare time: 0.003s Field #01: PROC.PROC Alias:PROC Type:STR ING(10) PLAN SORT ((GEST NATURAL)) PLAN (PROC ORDER PROC_PK) Executing... Done. -1258819480 fetches, 0 marks, 28351438 reads, 0 writes. 0 inserts, 0 updates, 0 deletes, 11439 index, 962254304 seq. Delta memory: 25308 bytes. Total execution time: 0:10:53 (hh:mm:ss) Script execution finished. PROC is a table with 11400 rows. Even if the query optimizer takes the worse path (analizing one by one) it seems that 10 minutes is too much ... In my opinion, both may run very quick. Why is the second one taking that long?. Well, your query does tell Firebird to find the DISTINCT possible values of PROC in a subset of GEST 11400 times. Sure, an ideal optimizer would have been able to see that your subselect wasn't correlated (not referencing the outer select) and that you didn't really bother about whether the subselect returned distinct records or not. Newer versions of Firebird do try to transform IN(SUBSELECT) into EXISTS(SELECT...), but I guess that your DISTINCT makes it give up. I think someone once told (probably Ann) that one of the early InterBase betas (or maybe it was even before that) had an optimizer that always chose the quickest way to return a result set. It's only problem was that it could use days to find which way was the quickest! There are several fair ways to reach the result you want: 1) The standard way to write your type of query select p.PROC from PROC p where exists(select * from GEST g where g.PROC = p.PROC and g.FSAL between '20080801' and '20080812') order by PROC 2) Quicker than option 1, but the result could differ a bit and you may want to add DISTINCT select p.PROC from PROC p join GEST g on p.PROC = g.PROC where g.FSAL between '20080801' and '20080812' 3) Logically similar to your original query in that you treat things like two statements and put them together. A nice way to do things if you construct your SQL dynamically. with tmp(PROC) as (select distinct PROC from GEST where (FSAL between '20080801' and '20080812') select p.PROC from PROC p join tmp g on p.PROC = t.PROC Myself, I sometimes use IN (constant values) (I even used it earlier today), but I stopped using IN(subselect) after 1 or 2 attempts 15 years ago. I've never missed it since. By the way, your original query can benefit from an index for GEST.FSAL, but cannot use an index for PROC.PROC. My second and third suggestion above could use indexes for GEST.FSAL and PROC.PROC, whereas my first suggestion only could use indexes for GEST.FSAL and GEST.PROC. HTH, Set
SV: [firebird-support] Re: Grouping without sorting alphabetically
Thanks Set for starting me on the right track. Please see my finalized query below which gets me to the expected result. Good to see that you got the result you wanted, Bhavbhuti! I do have two comments regarding your final result: 1) Why do you use ORDER BY within the CTEs? In the outer select, yes, there ORDER BY is useful, but within a CTE, I think ORDER BY is mainly useful in combination with window functions, which aren't available until Firebird 3. With CTEs like yours, I'd say avoid ORDER BY within the CTE. 2) Using cteFinalOrder is of course one possible option. However, I think it would be simpler to read the code if you just had joined cteTestGroups and cteTitleOrder directly in your main select. Though I do admit that is a matter of preference, and there's nothing wrong in using cteFinalOrder like you do. Set
Re: [firebird-support] How to join records
On Feb 16, 2015, at 4:22 AM, josef.gschwendt...@quattro-soft.de [firebird-support] firebird-support@yahoogroups.com wrote: we have 2 tables For each record in Table1 there are 2 records in Table2. Table1 (T1) == 1 2 Table2 (T2) T2T1 == 11 21 32 42 What is the cheapest way to get a dataset like below? T1T2aT2b === 1 12 2 34 Can't guarantee it's the cheapest, but you might try select T1.T1, max T2.T1 T2a, max T2.T2 T2B from T1 inner join T2 on T1.T1 = T2.T1 group by T1.T1 I think Ann meant select T1.T1, min(T2.T2) T2a, max(T2.T2) T2b --use quotation marks only if you want case sensitivity or special characters in the names, if not it is better to skip them from T1 join T2 on T1.T1 = T2.T1 group by T1.T1 An alternative to this if there are always two matching records, is: select t1.t1, t2_1.t2 T2a, t2_2.t2 T2b from t1 join t2 t2_1 on t1.t1 = t2_1.t1 join t2 t2_2 on t1.t1 = t2_2.t1 and t2_1.t2 t2_2.t2 Don't know how these two options compare performancewise, I guess Anns solution is quicker or equally quick. Anns solution will work if the number of matches varies (mine won't), whereas mine more easily extends to include other fields matching t2a and t2b. Needless to say, both Anns and my solution can be modified so that they will fit other situations. For more complex situations, you may want to consider EXECUTE BLOCK. Set
RE: [firebird-support] Re: Grouping without sorting alphabetically
Dataset: 1Haemogram Report 1Routine Haemogram 1Haemogram Report 5Diff. Count (Mature Cells) 1Haemogram Report 10 Special Investigations 2Haemogram Report 1Diff. Count (Mature Cells) 3C-Reactive Protine1 4Urine Analysis1Physical Examination 4Urine Analysis8Chemical Examination 4Urine Analysis10 Microscopic Examination of Centrifugalised Deposit 5Haemogram Report 1E.S.R. Bhavbhutis attempt: WITH cteTestGroups AS (SELECT MIN(A.iSrNo) AS iGroupSrNo , B.cTitle , MIN(C.iSrNo) AS iSubGroupSrNo , C.cSubGroup FROM sReqSlipTestGroup A JOIN mTestGroups B ON B.iID = A.iTestGroupID JOIN lTestGroupsTest C ON C.iPID = B.iID JOIN mTests D ON D.iID = C.iTestID WHERE A.iPID = 1 GROUP BY B.lNewPage , A.iSrNo , B.cTitle , C.cSubGroup ORDER BY 1, 2, 3, 4) SELECT cteTG.* FROM cteTestGroups cteTG My final output I want something like this: 1 Haemogram Report 1 Routine Haemogram 1 Haemogram Report 5 Diff. Count (Mature Cells) 1 Haemogram Report 10 Special Investigations 5 Haemogram Report 1 E.S.R. 3 C-Reactive Protine 1 4 Urine Analysis 1 Physical Examination 4 Urine Analysis 8 Chemical Examination 4 Urine Analysis 10 Microscopic Examination of Centrifugalised Deposit ie. The Row 4 is merged with row 2 and row 9 becomes row 4 The idea is that all the cTitle are clubbed together but in the order they were originally selected, thus MIN(A.iSrNo). The second grouping is cSubGroup, together but in the order they were defined, thus MIN(C.iSrNo) I think an additional CTE may be what you want: WITH cteTestGroups (iGroupSrNo, cTitle, iSubGroupSrNo, cSubGroup AS (SELECT MIN(A.iSrNo), B.cTitle, MIN(C.iSrNo), C.cSubGroup FROM sReqSlipTestGroup A JOIN mTestGroups B ON B.iID = A.iTestGroupID JOIN lTestGroupsTest C ON C.iPID = B.iID JOIN mTests D ON D.iID = C.iTestID WHERE A.iPID = 1 GROUP BY B.lNewPage, A.iSrNo, B.cTitle, C.cSubGroup), cteOrderGroups(cTitle, cOrder) AS (select cTitle, min(iGroupSrNo) from cteTestGroups group by 1) SELECT cteTG.* FROM cteTestGroups cteTG JOIN cteOrderGroups cteOG on cteTG.cTitle = cteOG.cTitle ORDER BY cteOG.cOrder, cteTG.iSubGroupSrNo Maybe things could be simplified further, but this at least ought to work. Set
RE: [firebird-support] Re: Speed issues
I am having similar issues. Are there any resources to read up on this? What do you mean by similar issues, Louis? Zoran first showed us his SQL and plan, so that we more or less could see that his SQL, plan and indexes seemed good (that's arguably the most common reason for speed problems). Unfortunately I don't know of any generally good resources for fixing speed issues with Firebird (excepting this list and sometimes firebird-devel, possibly The Firebird book by Helen Borrie can have some tips as well, I don't know), and I doubt there exist any good resources focusing particularly on speed problems with big tables (whereas I cannot rule out there could exist some good resources for more general speed problems). I never answered Zorans question since I've no experience with tables with more than 100 million rows and Seans answers seemed sensible. I noticed two things that they haven't discussed (but that I don't know how relevant would be) and that is that we haven't seen the DDL of his tables (wondering whether any of the tables involved have lots of fields or large CHAR and VARCHAR fields that could affect performance) and that we don't know how many rows would be returned from the same query if DISTINCT was removed. Set
Re: [firebird-support] nesting - weight calculation
There is at least a good alternative, Frank Ingermann showed how to do something very similar when calculating the amount of ingredients required when baking a marble cake at a Firebird conference a few years ago. He used a recursive CTE. Whether a recursive CTE is simpler than a recursive procedure or not depends partly on implementation, partly on what you mean by simple and partly on who you ask. I liked Franks solution. Set __ Hello, there is a table with all Material (stock), each one has a number. Now, in a second table there is listed the material that is a part of the Material. For example: Material ABC Material BCD Material XXX Material OMV Material KOM Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD can assigned to XXX, OMV, ABC. Now I would like to know what is the total weight of the main material, (total weight from abc + amount of bcd + amount of xxx ABC has 2 XXX, XXX has 2 OMV Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so on The structure Table Mainmaterial PartNo Description Table Material PartNo of Mainmaterial PartNoA = assigned Material Amount (of PartNoA) Is there a simpler statement possible than my stored procedure (recursive) Thank you.
Re: [firebird-support] Unlock record
I saw the same as you, and made the following test: - Opened my application. - Executed the option which locks a record (executes a SELECT * FROM TABLE1 WHERE (FIELD1 = 'some value') WITH LOCK). - Opened the database with IB_SQL. - Looked in table MON$STATEMENTS. I found many records. Many of them with MON$SQL_TEXT null. Only one of them had MON$TIMESTAMP not null. - I deleted the only record with MON$TIMESTAMP not null. - I opened another instance of IB_SQL and verified that the record I locked at the first step was still locked. Am I undestanding something wrong or this doesn´t work? Another idea to manually unlock a record? Regards Eduardo Finally got temporary access to a computer and can confirm that deleting from mon$statements is not enough when using WITH LOCK (I never use WITH LOCK myself). However, I did get access to this record (using Fb 2.5) by deleting from MON$ATTACHMENTS. There may or may not be other, less intrusive ways to get such access, though I failed when I tried MON$TRANSACTIONS. Set
Re: [firebird-support] Unlock record
I looked in a database, and in MON$STATEMENTS tabla I have the following fields: MON$STATEMENT_ID MON$ATTACHMENT_ID MON$TRANSACTION_ID MON$STATE MON$TIMESTAMP MON$SQL_TEXT MON$STAT_ID How can I detect which is the record I have to delete to unlock my record? This is the only table I have to modify? I typically just look at the mon$sql_text with a mon$timestamp that seems to be the correct one. HTH, Set Mensaje original --- May be I can modify or delete some record in any MON$ table or do some other thing? I typically locate and delete records from MON$STATEMENTS. Mind you, that is 2.5 and typically when I have a long- running query. Don't know whether deleting from this table is possible or safe with 2.1 or sensible WITH LOCK.
SV: [firebird-support] Unlock record
May be I can modify or delete some record in any MON$ table or do some other thing? I typically locate and delete records from MON$STATEMENTS. Mind you, that is 2.5 and typically when I have a long-running query. Don't know whether deleting from this table is possible or safe with 2.1 or sensible WITH LOCK. HTH, Set
Re: [firebird-support] working days between two dates
Hi Tomas! I just mean week days... Something like DateDiff() but only counting from monday to friday. I have a function in Delphi for that. I could make an UDF I think. I just wander if there is something already done. I thought it was something very used... probably is not? I don't know whether this is common or not (can't remember anyone asking for it before on this list), but if you don't care about special days it could be implemented as simple as this: execute block (fromdate date = :fromdate, todate date = :todate) returns (weekdays integer) as declare variable checkdate date; begin weekdays = 0; checkdate = fromdate; while (checkdate = todate) do begin if (checkdate todate - 6) then begin weekdays = weekdays + 5; checkdate = checkdate + 7; end else begin if (extract(weekday from checkdate) between 1 and 5) then weekdays = weekdays + 1; checkdate = checkdate + 1; end end suspend; end There should be other ways to do this in Firebird as well (some of them better), this is just one way that quickly comes to mind. HTH, Set
[firebird-support] Re: Deadlock exception occurs but it shouldn't?
The main purposes of explicit locks are (1) to prevent expensive handling of update conflict errors in heavily loaded application this is the exact reason I try to avoid conflicts. My threads can wait but they should not raise exceptions. I always though that it is possible to avoid such conflicts on the database level. In some cases it might be possible to avoid lock conflickts by using INSERT rather than UPDATE. Typically, you may have a trigger or something similar that inserts into a summary table, and then you have a separate process running at regular intervals that update the summary tables. E.g. you could have an inventory table originally with three items, and then insert into this table when things are sold or bought. Inventory Product InStock 175 --Items originally in stock 2 350 --Items originally in stock 354 --Items originally in stock 1 -5 --Sold five items 1 -2 --Sold two items 3 -8 --Sold eight items 1 100 --Bought hundred more items 1 -3 --Sold three items This does require programs using SUM(InStock) rather than just InStock when selecting, but you should never get lock conflicts. Every night (or every hour or week or whenever, just make sure it doesn't run several instances concurrently) you could run the following procedure: CREATE PROCEDURE UpdateStock AS DECLARE VARIABLE id INTEGER; DECLARE VARIABLE NoOfItems INTEGER; BEGIN FOR SELECT Product, sum(InStock) FROM Inventory GROUP BY 1 INTO :id, :NoOfItems DO BEGIN DELETE FROM INVENTORY WHERE Product = :id; INSERT INTO INVENTORY(Product, InStock) VALUES (:id, :NoOfItems); END END Don't know whether this is applicable to your situation or not, but HTH, Set
[firebird-support] Re: performance of subselect with group by
Hello, thanks Set, makes my test unnecessary! But I'm thinking a little bit about my original problem. Would it make sense to add a tracker entry for optimization of subselects without reference to outer query? I think that they should get evaluated and transformed to something like a or connected list of simple compares. So delete from test where Id in ( select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 ) could be transformed by the engine to delete from test where Id = 5 or Id = 6 or Id = 89 in case of select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 would return 5,6,89 I've found several similar questions in my mail archive so I think I'm not the only one with such a problem. A common use case would be to remove duplicate entries for example to create a unique index. Any comments? My guess is that it is too early to add this to Firebird. Sure, there are situations where a non-correlated subselect would be benefitial, but how would Firebird know when? In your case (since you're deleting from the same table as you're selecting from), I agree that this could be simple. However, normally the select would be against a different table and then I think things are not as simple. Assuming we were talking about two different tables: I don't know how Firebird translates a query like yours into EXISTS, but theoretically it could be something like: delete from DeleteFromTable t where exists(select * from test t2 left join test t3 on t2.reference = t3.reference and t2.key = t3.key and t2.id t3.id where t.id = t2.id and t3.id is null) If DeleteFromTable was a smallish table, and test was large and sensible indexes were in place, then my rewritten query would be quicker if your 'min subquery' returned a lot of rows. On the other hand, if your subquery returned few rows, then executing the subselect like you suggest would indeed be quicker. If you add a WHERE clause to your subselect, it is even thinkable that, say, WHERE MyField = 1 would be quicker using your solution, whereas WHERE MyField = 2 would be quicker using mine. And the only way for Firebird to know, would be to have histograms. Histograms was removed from things to be implemented in version 3 (I think it was announced during the conference, at least I'm certain that I've heard this), I hope they will appear in Firebird 4. I would think that once histograms are in place, cases like yours are amongst those that can be considered for optimization (although I don't know anything about the source of Firebird). You are definitely not the only one with this kind of problem, I would appreciate it myself if it was possible for deletes and updates to use indexes similar to what is possible with selects (e.g. allowing for JOIN). However, my knowledge of SQL and Firebird is not good enough to formulate how to do this in a sensible way. Also, I think that as users of Firebird it is possible to rewrite our slow queries to more performant ones in most cases, so to me this problem is not all too big. I have never needed expression indexes myself, but have you looked into writing one to see if this can make your query execute (considerably) faster? Sorry for not agreeing with you in that this is a useful optimization just yet, if you're lucky people knowing something about the source code of Firebird will disagree with me. Set
Re: [firebird-support] Performance optimation?
Hello, I save values in some tables (simpler description) First a Table who saved the timestamp of the mensuration Table A timestamps ID primary key TS timestamp Second a Table with the measured data (25 records/measured sensors will be saved every 10 Minutes, one record in Table A, 25 in Table B) Table B mensuration ID primary key ID_counter integer of item to measure ID_Timestamp foreign key of Table A Value (double precision) Now I would like to make an analysis. At the time, I do this: for select cast(ts as date) as mz from tablea where ts = “criteria from” and ts “criteria to” group by mz) into :messzeit do begin f_messwert = null; MESSWERTE = ''; for select a.id_counter, sum(a.value) from tableb a left join tablea b on a.id_timestamp = b.id where cast(b.ts as date) = :messzeit group by a.id_counter into :i_zae, :f_messwert do begin if(f_messwert is null) then f_messwert = 0; MESSWERTE = MESSWERTE || cast(:i_zae as varchar(4)) || '=' || cast(:f_messwert as varchar(8)) || ';'; end suspend; end The Result is one returned record for each day (day, conter 1 = 123; counter 2 = 222;…) It takes a long time but I must integrate the tablea on the second part of the statement. How can I optimize this in firebird? One thing to notice, Olaf, is that where cast(b.ts as date) will never use an index (if you have an index on TS). I would suggest changing that part of the query to something like: where cast(b.ts as date) = :messzeit and b.ts between :messzeit and :messzeit + 1 The first line limits the result to what you want, the second uses an index (if you have one). Maybe you could replace your two 'for select's with one, like this: begin messzeit2 = null; for select cast(a.ts as date), b.id_counter, sum(b.value) from tableb b join tablea a on b.id_timestamp = a.id where a.ts = “criteria from” and a.ts “criteria to” group by 1, 2 into :messzeit, :i_zae, :f_messwert do begin if (messzeit messzeit2) then begin messzeit2 = messzeit; f_messwert = null; MESSWERTE = ''; end if(f_messwert is null) then f_messwert = 0; MESSWERTE = MESSWERTE || cast(:i_zae as varchar(4)) || '=' || cast(:f_messwert as varchar(8)) || ';'; end suspend; end Does this work, and is it any faster? If this is a new project that should end up with a database lasting for quite some time, I would recommend you to reconsider your primary key. The reason being that primary keys ought to never have any business meaning (doesn't really matter if it is an integer, GUID or whatever). If you are certain that requirements will never change, then fine, but if things later could change so that there could be several records with the same timestamp, then this is something that is considerably simpler to fix if the timestamp is not a primary key. HTH, Set ++ 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/
[firebird-support] Re: performance of subselect with group by
Hello, Thanks for the answer. How can I optimize if I want to use a DML command in conjunction with a subselect, e.g. delete from test where Id in ( select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 ) Hi Björn! I don't think there is any simple way to make a delete with a subselect as the only part of a where clause perform great on largish tables. That is, using EXECUTE BLOCK (which doesn't exist on older Firebird versions) should perform OK: execute block as declare variable id integer; begin for select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 into :id do delete from test where Id = :id; end HTH, Set
[firebird-support] Re: performance of subselect with group by
I don't think there is any simple way to make a delete with a subselect as the only part of a where clause perform great on largish tables. That is, using EXECUTE BLOCK (which doesn't exist on older Firebird versions) should perform OK: execute block as declare variable id integer; begin for select min(t.Id) FROM test t group by t.reference, t.key having count(*) 1 into :id do delete from test where Id = :id; end HTH, Set well, that's not the answer I wanted to get, but I've to deal with that fact. I've to check wether EXECUTE STATEMENT can execute EXECUTE BLOCKs, as the DELETE Statement is build dynamically in a proc. Interesting, Björn, I'd expect execute block to work wherever a query could be used, but hadn't tried it inside EXECUTE STATEMENT until you said you would have to try. However, it made me curious, so I wrote: execute block returns(myanswer varchar(32)) as declare variable es varchar(500); begin es = 'execute block returns(ma varchar(32)) as ' || ' declare variable es2 varchar(500); ' || ' begin ' || ' es2 = ''execute block returns(ma2 varchar(32)) as ' || ' begin ' || ' select Hooray from rdb$database into ma2; ' || ' suspend; '|| ' end''; ' || ' execute statement es2 into :ma; ' || ' suspend; ' || ' end'; execute statement es into :myanswer; suspend; end just to see if it worked. It actually returned Hooray, so yes, EXECUTE STATEMENT can execute EXECUTE BLOCK and they can even be nested within each other! Set
Re: [firebird-support] group by date
Any ideas? Hello @ll, I would like to group my records by day or month. The date is saved as timestamp. If I do the following, SELECT EXTRACT(day FROM m.messzeit), COUNT(*) as CountMessages FROM te_messzeiten m GROUP BY EXTRACT(day FROM m.messzeit) I get all days (1 to 31), but I need group by 1.1.2014, 2.1.2014.. – daily bzw. 1 2014, 2 2014 – monthly How can I realize this? A few minutes after you asked this question, Mark gave you a very good answer: group by day: GROUP BY CAST(m.messzeit AS DATE) or less efficient: GROUP BY EXTRACT(YEAR from m.messzeit), (EXTRACT(month FROM m.messzeit), EXTRACT(day from m.messzeit) or if you want to group by month in year: GROUP BY EXTRACT(YEAR from m.messzeit), (EXTRACT(month FROM m.messzeit) Mark ++ 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] Grouping SQL counts
FB 1.5x I have a SQL statement that returns the results I want--giving me a count on the detail dataset (ClientRegHistList is a detail list on ClientRegHis, more info below) select C.RegDate, Count(Client_ID) from ClientRegHist C, ClientReghistList CL where (CL.ClientRegHist_ID = C.ClientRegHist_ID) and CL.RegType = 0 and (C.Company_ID = 128) group by 1; I have a second SQL that is exactly the same except changing the 0 to a 1 in line 5. I would like to combine the two datasets into o ne result set--any hints on how to do this? Thank you, Ed Dressel Huan is right in that your problem description is ambiguous, Ed, there are several possible answers to your question. However, I think there are a limited number of possible ways to read your question, so below are a couple of possibilities that may or may not work. My memory is poor, I haven't used Fb 1.5 for one or two years and I'm never use COUNT(fieldname) myself (I always use COUNT(*) or COUNT(DISTINCT fieldname), so no guarantees that all four of them will work. Even though not strictly speaking required, I also changed from SQL-89 to SQL-92 (explicitly using JOIN rather than joining in the WHERE clause). a) Getting the sum of 0 and 1: select C.RegDate, Count(Client_ID) from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType in (0, 1) and C.Company_ID = 128 group by 1; b) Counting each separately: select C.RegDate, CL.RegType, Count(Client_ID) from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType in (0, 1) and C.Company_ID = 128 group by 1, 2; c) Having all the lines of both queries in the same result set (you may not be able to separate them): select C.RegDate, Count(Client_ID) from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType = 0 and C.Company_ID = 128 group by 1 union all select C.RegDate, Count(Client_ID) from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType = 1 and C.Company_ID = 128 group by 1; d) Having one column for each in the result set: select C.RegDate, Count(case when CL.RegType = 0 then Client_ID else null end) as RegType0, Count(case when CL.RegType = 1 then Client_ID else null end) as RegType1, from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType in (0, 1) and C.Company_ID = 128 group by 1; Do any of these four answer your question? HTH, Set
Re: [firebird-support] Like and database entry -
where name like 'Ab%' returns any entry of the table, where name start with Ab except those ones containing character -. Using starting with or containing the same database entries are missing. Firebird simply doesn't exclude entries depending on which character follows what you're searching for. It might be like Helen said (different case) or maybe it can be something like a damaged index. Fb 2.5.1 has a problem with multifield indexes that can contain null, and I wouldn't surprise me if this could lead to apparently missing records (although I haven't heard or experienced this, just that GROUP BY can return duplicates). If you are using Fb 2.5.1 or the database has been in Fb 2.5.1 and you haven't restored the database since, then either upgrade to a newer version (and backup/restore your database) or try avoiding multi-field indexes. If this isn't the case, Rainer, then please provide us with a more detailed description of your problem, including DDL of the table, your SQL statement, chosen PLAN and a few examples of records that should be returned including some that aren't. Also, it might be helpful if you include which Firebird version, character set and collation you are using. Many of us would be greatly surprised (an understatement) if you could provide a reproducible test case demonstrating such a severe Firebird error. HTH, Set
RE: [firebird-support] Re: Firebird database seems to influence multi user ability
I have been an interbase guy about fifteen years ago and loved to see it go open source. I used to work with Paul Beach (Hello, if you're here,Paul) Yes, Paul Beach is still here, he’s working with other InterBase and Firebird experts at IB Phoenix. Here you can see who they are: http://www.ibphoenix.com/about/contact another mystery to me. First of all, I was using SYSDBA for all of my actions. It worked with masterkey! As far as I remember this should be changed in any serious software, shouldn't it? Well, I could agree that no one ought to run with the default password. However, I would say that software should not automatically change the Firebird password. It is fine if your system is the only system on your computer using Firebird, but let’s say there already exists another program on your computer that uses Firebird, you install your program and change the password, then you may risk that the original program no longer works. Hence, I’d say some kind of intelligence ought to be present when installing Firebird, and leaving this responsibility to a human administrator of the machine is one common way to do it (it may vary depending on what your system does). A system ought to tell the users how to install Firebird and change password. Of course, they can also provide a Firebird installer if they want to. If they do, they could change the password when installing Firebird, but never do any automatic change of the SYSDBA password to an existing system. Using the default password makes your database vulnerable to anyone with access to the database, but generally you should limit who has access to your database and only give access through aliases so that no one knows where the database is physically stored. Then the vulnerability will be limited to those who have access. Of course, SYSDBA can normally look at, modify or delete any object. In many cases this will be very bad, in other cases it might not matter equally much. If anyone gets physical access to your Firebird database, then you may consider all sensitive data lost. Put a copy of the database on any machine, install Firebird and you get access with the default password. Another thing is that I was looking into the tables and missed RDB$user. The table just is not there. Did that change recently? Are you sure that RDB$USER was a table? I’m used to users being defined in the Firebird server itself (probably stored in security2.fdb in your case, 15 years ago I think it will have been isc4.gdb), whereas their rights to any object in a database is stored in the database itself (probably in RDB$USER_PRIVILEGES, although I must admit I don’t know). I don’t quite understand what an RDB$USER table in your own database should be used for, although, again, I must admit that my knowledge of the system tables are limited to what I’ve used myself (which doesn’t include RDB$USER). HTH, Set
RE: [firebird-support] Why this similar to is wrong?
select
iif('em...@testdomain.com' similar to
'([_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+)','ok','fail')
from rdb$database
Says 'invalid string' and 'invalid pattern', but the pattern it´s the same in
other languages for validate email address.
I know nothing about regular expressions, but
http://www.firebirdsql.org/refdocs/langrefupd25-similar-to.html#langrefupd25-similar-to-building-regexps
says There is no default escape character; rather, the user specifies one when
needed:
and then gives four examples:
'Peer (Poire)' similar to 'P[^ ]+ \(P[^ ]+\)' escape '\'-- true
'Pera [Pear]' similar to 'P[^ ]+ #[P[^ ]+#]' escape '#'-- true
'Päron-Äppledryck' similar to 'P%$-Ä%' escape '$' -- true
'Pärondryck' similar to 'P%--Ä%' escape '-' -- false
HTH,
Set
RE: [firebird-support] Left outer join with stored procedure
Hello All, I have a table regioni with 164 rows (r_id), and also stored procedure which returns some data like (r_id, value). When I try to use select regioni _ID, stored_procedure_value from regioni left outer join stored_procedure (:DATE1,:DATE2) I only get number 20 rows for the R_ID which is returned by procedure. I cant manage to get all 164 rows from regioni table matched with values from stored_procedure, where value exists. Can someno help with this ? If you provide more information, I’m sure someone on this list can help, Zoran, but I cannot see that the parts that you’ve provided so far contains any reason for only 20 rows to be returned (mind you, I’m not used to queries like yours, with only parameters in the call to stored_procedure – as far as I can see, this query ought to return the same stored_procedure_value for all rows). The most common reason for a problem like yours, is that the LEFT tuple is referred to in the WHERE clause (when it should be in the LEFT JOIN), but you don’t seem to have any WHERE clause. By the way, I expect regioni_ID to be the same as R_ID? Set
RE: [firebird-support] Re: Cross database update
Dear Thomas, thank you for your answer. I have experienced that pssing by the trigger is functioning well. The only thing wrong is that the loop has no effect on the data of the records. And I hoped that someone could check the statement syntaxwise. Maybe I am doing something wrong in my logic. Is the use of the for select do loop correct as stated? Maybe I have to use an update or insert statement except? Your FOR..SELECT seems fine, what’s unknown to us (proprietary to IB Expert) is the ibeblock and the use of two separate connections (Firebird does support transactions spanning several connections as well as ON EXTERNAL, but not treating two connections as something you can mix inside a stored procedure). I think your error is in your execute block and that you either have to explicitly pass values or declare variables as input parameters in your execute block. Take a look at http://www.firebirdsql.org/refdocs/langrefupd20-execblock.html. Moreover, I’d recommend trying to get some kind of error message rather than having no code in the EXCEPT block (hiding things from the user may be OK, it is normally not what you want when you have a problem puzzling you). HTH, Set
RE: [firebird-support] sum data into single row per day
Thank you Svein, that's what I tried but kept getting: SQL error code = -104. Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause). and yet it now works. I thought it must have been because I was accessing code in the iif(...). I must have had a typo that I could not see for looking. Anyway, that now works perfectly, without the ',' after SH. Sorry about not removing the comma after copying. I think your error might have been doing IIF(SUM... rather than SUM(IIF... Logically, that is using an aggregate to decide which single value to return (and hence, needs to be grouped by), whereas SUM(IIF takes a single value to determine which value should be used in the aggregation and doesn't need aggregation. When I try IIF(SUM..., I do get -104 as well. Set
RE: [firebird-support] sum data into single row per day
for select iif(n.code=1,sum(n.amount),0), iif(n.code=2,sum(n.amount),0), iif(n.code=3,sum(n.amount),0), n.sell_date from new_table n where n.sell_date between :fromdate and :uptodate group by n.sell_date,n.code into :stock, :repair, :sh, :sell_date This gives the correct totals but not in the correct format: SELL_DATE STOCK REPAIR SH 07.11.2014 0 0 30 10.11.2014 160 0 0 10.11.2014 0 20 0 11.11.2014 100 0 0 11.11.2014 0 22000 11.11.2014 0 0 500 What I want is this format, one row per day: SELL_DATE STOCK REPAIR SH 07.11.2014 0 0 30 10.11.2014 160 20 0 11.11.2014 100 2200500 You almost got it right, Alan, just remember to only group only on things you want to produce a separate row. This is the query you want: select sell_date, sum(iif(code=1, amount,0)) stock, sum(iif(code=2, amount,0)) repair, sum(iif(code=3, amount,0)) SH, from new_table where n.sell_date between :fromdate and :uptodate group by sell_date Set
[firebird-support] Re: Why does the optimizer choose NATURAL for this tiny table?
Hi Sean/Dmitry, I've mingled my answers to your suggestions. Does the plan change to INDEX if the condition would be: S2) on p.icd10 in (t.icd10, t.icd3) S3) on p.icd10 = t.icd10 or p.icd10 = t.icd3 D2) ON p.icd10 = t.icd10 OR p.icd10 = t.icd10 No, these all keep the original plan: PLAN JOIN (JOIN (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)), JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), I INDEX (PK_ICD10)) D1) ON p.icd10 = t.icd10 S1/D3) ON p.icd10 = substring(t.icd10 from 1 for 3) These change the plan to: PLAN JOIN (MERGE (SORT (JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), SORT (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR, I INDEX (PK_ICD10)) The version is Firebird 2.5.1. I doubt there are much to win by using an index, the ICD10_PURRES p (lookup) table contains only 112 rows, each of which contains one integer, one char(4) and one char(3) field. Set
[firebird-support] Why does the optimizer choose NATURAL for this tiny table?
Fb 2.5.1 I have an EXECUTE BLOCK statement, for which a small part goes NATURAL where I'd expect it to use an index associated with a UNIQUE CONSTRAINT. I would expect JOIN icd10_purres p ON p.icd10 IN (t.icd10, substring(t.icd10 from 1 for 3)) to use an index, maybe something along the lines of: PLAN JOIN (P INDEX(U_ICD10_PURRES_ICD10, U_ICD10_PURRES_ICD10), ... However, the optimizer goes NATURAL. ICD10_PURRES is a tiny table, but I thought 112 records might be enough for an index to be useful Or is the possibility of reducing the number of potential records by 99% not enough to warrant the use of an index? Other tables in the query are considerably bigger (NPR contains 7 million, NPR_KRG_ID half a million and NPR_TILSTAND 10 million). This is part of the following FOR SELECT: for with tmp (id_npr, avdnr, reshid, icd10, innskrivingsdato, aar, omsorgsniva, diagnosenr) as (select n.id_npr, n.tjenesteenhetkode, n.tjenesteenhetreshid, iif(substring(t.tilstand from 4 for 1) = '.', substring(t.tilstand from 1 for 3)||substring(t.tilstand from 5 for 1), substring(t.tilstand from 4 for 1)), n.innskrivingsdato, extract(year from n.innskrivingsdato), n.omsorgsniva, t.nr from npr n join npr_krg_id kid on n.npr_krg_id = kid.npr_krg_id join npr_tilstand t on n.ID_NPR = t.ID_NPR where kid.fnr_kryptert = :fnr_k and not n.innskrivingsdato between current_date-90 and current_date) select t.id_npr, t.avdnr, t.reshid, t.icd10, t.innskrivingsdato, t.aar, p.icd10_c, t.diagnosenr, t.omsorgsniva, g.gruppe, i.beskrivelse from tmp t join icd10_purres p on p.icd10 in (t.icd10, substring(t.icd10 from 1 for 3)) join icd10_gruppe g on g.icd10 = p.icd10_c and not exists(select * from icd10_gruppe g2 where g2.icd10 = g.icd10 and g.gruppe g2.gruppe) left join ICD10 i on t.icd10 = i.kode into ... do ... for which the optimizer creates this plan: PLAN JOIN (JOIN (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)), JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), I INDEX (PK_ICD10)) Eventually, this EXECUTE BLOCK will be turned into a STORED PROCEDURE. Currently, I'm looking at running the procedure maybe half a million times in a loop, later it will be run daily or weekly, but then only on a few thousand records each time. I haven't tried running things yet, but would expect running the EXECUTE BLOCK once to finish quickly, whereas half a million times hopefully would take a few hours and not days. I'm just puzzled and am asking because I would like to increase my understanding... Set
RE: [firebird-support] What is wrong with transactions here?
Hi! I have some lines in firebird.log like this: === cxv.servers.netTue Nov 4 11:37:38 2014 Sweep is started by SWEEPER Database SkyNetInt OIT 1221095, OAT 1231335, OST 1231313, Next 139981575359934 cxv.servers.netTue Nov 4 11:37:46 2014 Sweep is finished Database SkyNetInt OIT 1248701, OAT 1248707, OST 1248707, Next 1248709 === That's housekeeping for transactions. Depending on how applications are connect to the database and how the applications are handling their connects, statements and commits - you will see a gap between the oldest active and the next transaction. I don't think this can be the reason for next transaction showing 139981575359934 originally, Marcus - that would require there to be about 1 million transactions per second for five years of which one of the oldest is still active (I would expect this to be more than Firebird can handle and certainly more than enough to test the patience of any user of the database). Though I've no idea what the reason can be and leave this for others to answer, Set
RE: [firebird-support] update optimization problem
Hi. in firebird 2.5 Simple descrip tables table_ud ud_id integer not null primary key field_ud varchar(6) row count: 383322 table_tmp ud_id integer cnd integer index (cnd) row count: 617 UPDATE table_ud ud SET ud.field_ud = '201401' WHERE ud.ud_id in (SELECT t.ud_id FROM table_tmp t where t.cnd = 2) stadistic table_ud read no index: 383322 Why? This table can grow to millions of rows How do I optimize it ? Hi Germán! Normally, I would use UPDATE table_ud ud SET ud.field_ud = '201401' WHERE EXISTS(SELECT * FROM table_tmp t where ud.ud_id = t.ud_id AND t.cnd = 2) But I think that Firebird 2.5 have fixed the general problem with IN(subselect) already (a common problem with Fb 1.5 was that the subselect would be executed for each potential row, but I think that would mean that if there were 25 rows with cnd 2, then the count would be 9583050, and not merely 383322). It could well be that Firebird is still fairly slow on UPDATE queries where the only delimiting factor is an EXISTS. If you want less rows read, I would suggest using EXECUTE BLOCK AS DECLARE VARIABLE UDID INTEGER; BEGIN FOR SELECT UD_ID FROM TMP WHERE CND = 2 INTO :UDID DO UPDATE TABLE_UD SET UD.FIELD_UD = '201401' WHERE UD_ID = :UDID; END This ought to reduce your number of reads, but it is of course more tedious to write and probably less similar to how it would be done in other databases. HTH, Set
Re: [firebird-support] How do I count the number of duplicate rows in a table?
Greetings All, Firebird 1.5.3 Should be elementary but, I'm drawing a blank on how to accomplish this. I have a table (ACCT_CASE_COURT) that contains these fields (among others): ACCT_CASE_COURT_IDINTEGERNOT NULLPK ACCT_IDINTEGERNOT NULL CASE_IDSMALLINTNOT NULL CASE_NUMBERVARCHAR(20)NOT NULL ... How do I form a SQL Select statement that would contain CASE_NUMBER in the first column (sorted) and number of times that the case number is found in the table in the second column (CNT)? From your description, I'd say the answer is simply SELECT CASE_NUMBER, COUNT(*) CNT FROM ACCT_CASE_COURT GROUP BY CASE_NUMBER (feel free to add ORDER BY CASE_NUMBER, I didn't since all existing Firebird versions happens to do it implicitly when having GROUP BY). If this is not the answer, then please try to formulate your question differently so that we understand what you want. HTH, Set
Re: [firebird-support] PSQL: Getting both aggregation and individual rows
Hi! I tried this approach before, but it violates my only run GET_DATA once requirement :-( Well, with EXECUTE BLOCK and ORDER BY it may be possible to satisfy your MIN requirement, but I can't logically understand how it even theoretically wold be possible to know the SUM before having gone through all records. Window functions in Fb 3 is probably the best answer to your question, but I'd still guess Fb would do two passes under the hood. Not that I think it matters too much, I assume the first pass to be more costly than the second. Have you measured how much more time is required to return the entire result set if having the aggregate functions compared to a simpler query without them (but with ORDER BY since that makes it more easily comparable)? Set On 21.10.2014 21:15, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote: What about FOR WITH TMP(MyDateTime, MySum) as (SELECT MIN(dateandtime), SUM(Value) FROM gen_data(...)) SELECT g.id, g.name, g.dateandtime, g.value, t.MyDateTime, tMySum FROM gen_data(...) g CROSS JOIN tmp t Of course, you may want a different join to CROSS JOIN. HTH, Set
Re: [firebird-support] VarChars sometimes begin with single or double quote
I've not been able to find any topic that comes close to addressing my question, and it seems so basic, I'm guessing that I'm missing something fundamental. I have queries that specify for ranges in varchar fields and return those and populate my VirtualStrings in a standard alphabetical grouping. The database I work with has some strings / varchar fields _begin_ with a _single_ or a _double_ quote, for emphasis or for identifying a literary work, etc., for output in components where there is not an italic feature. Using the standard = or = works fine for varchar fields that begin with numbers and letters, but skips over those which begin with ''' single-quote or '' double-quote. Is there some schema which I could have followed to find my way to the a nswer to this conundrum of mine? Ideally, I would prefer to have the varchar fields which begin with a single-quote or a double-quote collated in the proper order according to the first letter, regardless of upper or lowercase, or presence or lack of single- or double-quote. No, Barry, this is not fundamental to a database (I would admit that it could be considered fundamental to a programming language). I would recommend you to create an auto-generated proxy column and use this for your sorting (whether or not you include it in your result set). E.g. something like: CREATE TABLE TEST_SORTING ( PKINTEGER NOT NULL, MYSTRING VARCHAR(50) CHARACTER SET ISO8859_1, STRIPPED VARCHAR(50) CHARACTER SET ISO8859_1, CONSTRAINT PK_TEST_SORTING PRIMARY KEY (PK) ); SET TERM ^^ ; CREATE TRIGGER TEST_SORTING_UCQ FOR TEST_SORTING ACTIVE BEFORE INSERT OR UPDATE POSITION 0 AS declare variable s varchar(50); declare variable US varchar(50); begin if (new.MyString '') then begin us = ''; s = upper(new.MyString); while (s '') do begin if (substring(s from 1 for 1) not in (, '')) then us = us || substring(s from 1 for 1); s = substring(s from 2 for character_length(s)); end new.stripped = us; end end ^^ SET TERM ; ^^ HTH, Set
Re: [firebird-support] PSQL: Getting both aggregation and individual rows
What about FOR WITH TMP(MyDateTime, MySum) as (SELECT MIN(dateandtime), SUM(Value) FROM gen_data(...)) SELECT g.id, g.name, g.dateandtime, g.value, t.MyDateTime, tMySum FROM gen_data(...) g CROSS JOIN tmp t Of course, you may want a different join to CROSS JOIN. HTH, Set Fra: firebird-support@yahoogroups.com [firebird-support@yahoogroups.com] Sendt: 21. oktober 2014 09:39 Til: Firebird Support Emne: [firebird-support] PSQL: Getting both aggregation and individual rows Hi! Is there a simple way to get an aggregation of some query as well as the individual rows from within a PSQL? I.e., I have a stored procedure GEN_DATA which produces individual rows (ID, Name, DateAndTime, Value). I am processing the output of GEN_DATA in another stored procedure PROCESS_DATA: FOR SELECT id, name, dateandtime, value FROM gen_data(...) INTO ... DO ... Within this loop, I need the individual records, but I also need some aggregations on them, i.e. MIN(dateandtime) or SUM(value). If I could run GEN_DATA twice, then it would be easy (in the first run I would calculate the aggregations, in the second run the individual values), but I can't. If I could perform the processing in my application (rather than the stored procedure), it would be easy, too (I would simply traverse the result dataset of GEN_DATA twice), but again I can't do that. Would Common Table Expressions or perhaps Derived Tables help me? Thanks, Josef
RE: [firebird-support] Request new feature - better perfomance
Maybe I was not clear as I should be but here is an example in Firebird: select something,anotherthing,(select sum(thirdthing) from second b where b.something = a.something) total from a where (select sum(thirdthing) from second b where b.something = a.something) 10 tray sentence: select a.someting, a.anotherthing, b.total from first a inner join ( select someting,sum(thirdhing) as total from second group by someting ) b on a.someting=b.someting where b.total10 Other alternatives (though I guess whether or not the performance is acceptable will depend on table content): a) select something, anotherthing, (select sum(thirdthing) from second b where b.something = a.something having sum(thirdthing) 10) total from a b) with tmp(something, total) as (select something, sum(thirdthing) from b group by 1 having sum(thirdthing) 10) select a.someting, a.anotherthing, b.total from a join tmp on a.something = tmp.something c) execute block returns(something integer, anotherthing varchar(5) collate whatever, Total integer) as begin for select a.something, a.anotherthing, sum(b.thirdthing) from a join b on a.something = b.something group by 1, 2 into :Something, :AnotherThing, :Total do begin if (Total 10) then Suspend; end end d) select a.something, a.anotherthing, sum(b.thirdthing) Total from a join b on a.something = b.something group by 1, 2 having sum(b.thirdthing) 10 The result of a) differs from the others in that it will return a line (with Total = NULL if sum 10) for all rows of a. b) should return identical results to your original query, whereas I think c) and d) will double the result of Total if there are two occurrences of a.something (triple if three, quadrouple if four etc). in Oracle it looks like select something,anotherthing,(select sum(thirdthing) from second b where b.something = a.something) total from a where total 10 and subquery executed only once but in Firebird subquery is executed twice. When you have very complex subquery and possible two or three times using total value in where clause it makes great impact on perfomance and of course it is very bad query but in Oracle it works like charm. Don't know Oracle, but I admit that it is sometimes beneficial to tweak the SQL statement in Firebird to improve performance. I neither know whether or not it would feasible or if it would improve performance to allow the syntax you suggest, I think Dmitry (or similar) once wrote on this list that whilst being duplicate in terms of syntax and plan, it added almost nothing to the execution time since things would already be in the cache. As for the syntax, I'm not used to the result of a subselect being available in the where clause (it would take me a bit of time to get accustomed to results of subselects being available in the where clause, but not the result of aggregate functions not in a subselect), but I may agree that it could make large statements more easily readable. HTH, Set
RE: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
ACCT_CASE: Case Management table ACCT_IDINTEGER NOT NULL PK CASE_ID SMALLINT NOT NULL PK CLT_ID INTEGER NOT NULL FK to CLIENT table Need this for the JOIN DEBTOR_CASE_DEBT: Allows for multiple PERSON's to be associated with a DEBT ACCT_IDINTEGER NOT NULL PK CASE_ID SMALLINT NOT NULL PK DEBT_NO SMALLINT NOT NULL PK PERSON_ID INTEGER NOT NULL PK STATUS_DATE TIMESTAMP NOT NULL STATUS_CODE CHAR(1) NOT NULL What am I attempting to do? I need to know how many records are in the DEBTOR_CASE_DEBT table that have a STATUS_DATE between '09/01/14' and '09/30/14' and the STATUS_CODE = 'B (Bankruptcy Filed) and is for a specific CLT_ID (thus the join to ACCT_CASE to use CLT_ID). I do not want to include the PERSON_ID when fetching a COUNT() of the record, I only need to know how many debts are in this status for the client. So only concerned with ACCT_ID, CASE_ID and DEBT_NO. So this SQL will return the correct number of records, now I just have to figure out how to return a count in one record. SELECT DISTINCT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO FROM DEBTOR_CASE_DEBT DCD JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE AND DCD.STATUS_CODE = 'B' AND AC.CLT_ID = :V_CLT_ID Did I provide enough information this time? If not feel free to ask... This is close to a perfect problem description, Mike, well done! The only (minor) thing lacking is a reason for you not wanting CLT_ID included... I can think of two possible solutions: a) SELECT COUNT(DISTINCT DCD.ACCT_ID||'-'||DCD.CASE_ID||'-'||DCD.DEBT_NO) FROM DEBTOR_CASE_DEBT DCD JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE AND DCD.STATUS_CODE = 'B' AND AC.CLT_ID = :V_CLT_ID b) SELECT COUNT(*) FROM DEBTOR_CASE_DEBT DCD WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE AND DCD.STATUS_CODE = 'B' AND EXISTS(SELECT * FROM ACCT_CASE AC WHERE AC.ACCT_ID = DCD.ACCT_ID AND AC.CASE_ID = DCD.CASE_ID AND AC.CLT_ID = :V_CLT_ID) Myself, I generally prefer to have single field primary keys, one benefit of this is that you can use solution a) without having to do tricks with concatenation. HTH, Set
RE: [firebird-support] BLOB
Text, binary, it's all the same to gbak. Try the -g suggestion - if gbak is cleaning out garbage, it's slow. But, surely, the garbage has to be cleaned out sooner or later, and doing it in a backup run is the cheapest way to do it, as every record is being visited anyway? (Compared to, say, doing a COUNT(*) to clean garbage, which will visit records it doesn't need to, or, worst of all, letting the cost land at random on whichever poor sod next does a query on the table with the garbage.) Hi Tim! The original question was: 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 ? If the original database should not be replaced by the restored version, then it is of course sensible to clear garbage. However, I suggested trying this switch since this could be an alternative way to discover whether the problem was (mainly) related to poor transaction handling and not the blob itself. I'm actually surprised that Tiberiu notices a problem at all, a table containing 3000 rows and blobs of up to 400 characters should be peanuts for Firebird. Set
[firebird-support] Re: Simultaneous inserts / selects
All values for inserts are converted to string so there are no parameters. Sorry to hear that. MyStatement.SQL.Text:=’INSERT INTO MyTable(Field1, Field2) VALUES (:Param1, :Param2)’; MyStatement.Prepare; while not eof(InputFile) do begin readln(InputFile); MyStatement.Params[0].AsInteger:=IntegerFromFile; MyStatement.Params[1].AsString:=StringFromFile; MyStatement.Execute; end MyTransaction.Commit; is magnitudes faster (5 000 records per second could be feasible) than while not eof(InputFile) do begin readln(InputFile, s); MyStatement.SQL.Text:=s; MyStatement.Prepare; MyStatement.Execute; end MyTransaction.Commit; I only know of two ways to go if you want decent performance for largish inserts, one is to use external tables (I’ve never used them, but I trust they are the quickest way), the other statements that you prepare before the loop and use with parameters. Sorry, Set
Re: [firebird-support] DATEDIFF(HOUR...) returns 1 for a 45 minutes interval
Hi all I would have attached a screen shot of the sample data but I guess that is not possible. What I am trying to achieve right now is get the time difference in hh:mm format to the user tStartTime and tEndTime field values 26.08.2014, 08:38:00.00026.08.2014, 09:45:00.000 26.08.2014, 10:00:00.00026.08.2014, 10:45:00.000 26.08.2014, 11:00:00.00026.08.2014, 12:00:00.000 26.08.2014, 11:30:00.00026.08.2014, 12:15:00.000 26.08.2014, 00:15:00.00026.08.2014, 13:30:00.000 and the following is the query SELECT sProdLineWorkProcess.*, (DATEDIFF(HOUR, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime)) AS nHoursWorked , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime) - (DATEDIFF(HOUR, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime) * 60)) AS nHoursWorked , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime)) AS nTotalMinutesWorked FROM sProdLineWorkProcess JOIN tProdLineWork ON tProdLineWork.iID = sProdLineWorkProcess.iPID WHERE CAST(tProdLineWork.tDt AS DATE) = '2014-08-26' --GROUP BY sProdLineWorkProcess.iEmployeeID The calculated field values are as follows: 1767 04545 1060 1-1545 1315795 Please see the 4th row, the diff between 11:30 and 12:15 is 1 Hour and -15 mins whereas difference between 10:00 and 10:45 is 0 hour and 45 mins. Please advise on what I need to do correctly to get 0 hour and 45 mins for both 2nd and 4th row. If you only care about hours and minutes, then your attempt is pretty close to one possible solution, Bhavbhuti. Try something similar to (assuming sProdLineWorkProcess has a unique ID field): with tmp(ID, nHours, nMinutes) as (SELECT plwp.ID, DATEDIFF(HOUR, plwp.tStartTime, plwp.tEndTime), DATEDIFF(MINUTE, plwp.tStartTime, plwp.tEndTime) - (DATEDIFF(HOUR, plwp.tStartTime, plwp.tEndTime) * 60) FROM sProdLineWorkProcess plwp JOIN tProdLineWork plw ON plw.iID = plwp.iPID WHERE CAST(tProdLineWork.tDt AS DATE) = '2014-08-26') select plwp.*, iif(nMinutes 0, nHours - 1, nHours) nHoursWorked, iif(nMinutes 0, nMinutes + 60, nMinutes) nMinutesWorked from tmp join sProdLineWorkProcess plwp on tmp.ID = plwp.ID HTH, Set
Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Obviously the logic of my application is not the best, but it needs to pass through a special interface, that transforms classic cobol read/write routines, in sql statements. This brings to the need to have a single commit after a single insert command of a single record. I know this is not good, but knowing this and knowing that I cannot change this, I only want to know if I can get the best for my Firebird Installation of 2.5.3 SuperServer onto Windows Server 2012 R2 x64. OK, commit after every single insert will slow things down considerably and may be the reason for your time trouble (it is similar to having trouble with a car being slower than a bike, and the reason being that you have an additional requirement to start and stop every 5 meters). I'm not certain (because I've never had a similar requirement and know very little about CommitRetaining), but maybe changing from Commit to CommitRetaining might make it possible for a prepared statement to survive the CommitRetaining (?) and then only do a proper Commit occationally. It could at least be worth a try if feasible. I'm trying several settings, some others have suggested in this thread. About your test (thanks for that), I have created the table, than copied and pasted the SQL loop, but I get this error: Preparing statement: execute block returns (i integer) as declare variable i2 integer Error: *** IBPP::SQLException *** Context: Statement::Prepare( execute block returns (i integer) as declare variable i2 integer ) Unexpected end of command - line 2, column 21 This is probably due to IBPP believing the ; to be the end of the statement. Try to 'SET TERM ^^ ;' before execute block, change the final end of the execute block to end^^ and then do 'SET TERM ; ^^' at the end. Sorry for not being able to help more, Set
Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Hi Sean, thanks for the contribution. Some answers to your requests: 1) Logic of application. It is a Microfocus Cobol legacy application, with latest (2014) x64 runtime. The long time is obviously not relative to a simple sql command, but to the overall execution. We have developed a dedicated interface (a Delphi x64 service and dll), cobol uses to read from vision indexed cobol files, read into Firebird table if record exist and than insert the record. It is to populate new tables, in order to use a different application with firebird database. The same application runs on windows server 2003 32bit, windows server 2008 64 and SLES 11 SP1 x64. The interface and dll’s are the same, just like the cobol program. The problem is related to the fact that I expected a real big difference between old or very old hardware in RAID 1 configuration, and this brand new hardware, instead it gained only 20/30 %. Consider this: another application, that runs only in cobol environment, without database, has passed from 2 hrs to 15 minutes !! Just to say, that surely we can improve our legacy application or the Delphi interface. But if I compare the identical application, with very different machines, I see a little improvement dispite the big difference in hardware (Firebird is always 2.5) I'm not surprised, I think Firebird SuperServer will only use one core. I created a table: CREATE TABLE TEST ( ID INTEGER NOT NULL, MYINT INTEGER, CONSTRAINT PK_TEST PRIMARY KEY (ID) --for this test an important primary key ); Then I executed the following query on the empty table: execute block returns (i integer) as declare variable i2 integer; begin i = 0; i2 = 0; while (i 100) do begin i = i+1; i2 = i2+i; while (i2 10) do i2 = i2 - 1; update or insert into test(id, myint) values (:i2, :i); end suspend; end 16.5 seconds later the query had looped and inserted or updated 1 millon times, 4 rows where inserted, the rest of the times things were updated. Maybe you should add half a second for the commit afterwards. This on a computer that is a few years old and nothing special. The database is Firebird 2.5, don't remember whether it is 2.5.1 or 2.5.2. Now, this is very different from your import from a text file. Still, I hope it is enough to show that 9 minutes to check and insert 35000 records is more than what is normally neccessary. You could either continue to try to improve the environment and maybe get the import to finish within 5 or 7 minutes after some further optimization. Or you could try to discover and fix the real problem through telling us more about what is actually going on (what does your SQL look like, what PLANs are used, which indexes are used, do you use prepared statements or create 35000 separate statements and how many transactions?) and hopefully get the import to finish in less than one minute. HTH, Set ++ 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/
[firebird-support] RE: Firebirdsql adding new charsets to database
If your database has the right ODS version, i.e. that it is created with Firebird 2.5, then you can just use the character set – i.e. CREATE TABLE MyTable( MyChineseField Varchar(50) character set GB18030, MyNorwegianField Varchar(50) character set iso8859_1 collate no_no, MyASCIIField Varchar(50) character set ASCII); I’ve never used it myself, but a quick search on the Internet indicates that FBClone possibly could help you change the character set of an existing database. HTH, Set From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 16. september 2014 00:07 To: firebird-support@yahoogroups.com Subject: [firebird-support] Firebirdsql adding new charsets to database Dear Firebirdsql Community, I want to add GB18030 charset to an existing firebirdsql database. How can I do that? Is that even doable, or will that require a new database to be created? Firebird database engine version: 2.5.2 Thank you! Regards, Vardan Minasyan Regards, Vardan Minasyan 949-645-1019 x2 If you need to send us a file, follow the link below. https://quickbooksconsultant.sharefile.com/r/r487ddfe0d9c490f8 [Non-text portions of this message have been removed]
Re: [firebird-support] Select based upon date between two date fields
The sql is below. It works fine until I add the BETWEEN statement. Hopefully
you can point out something simple that I am doing wrong.
SELECT description, WOReqd, Addontype as AddonTypeID, RatebookID FROM
rentaladdons LEFT JOIN rentalratebook on rentaladdons.ratebookID =
rentalratebook.ratebookID WHERE addonID = '1002' and cast('09/15/2014' as
date) BETWEEN rentalratebook.fromdatetime and
rentalratebook.todatetime
You are using the wrong approach, you need to try:
SELECT
description, WOReqd, Addontype as AddonTypeID, RatebookID
FROM rentaladdons
LEFT JOIN rentalratebook on rentaladdons.ratebookID
=rentalratebook.ratebookID
WHERE
addonID = '1002'
and rentalratebook.fromdatetime = cast('09/15/2014' as date)
and rentalratebook.todatetime = cast('09/15/2014' as date)
Why is his approach wrong, Sean? It might be unusual to have the constant on
the left side of a comparison, but I'm sure I've done it when it is the easiest
way to write a query. I'm pretty sure date BETWEEN date AND date should
work regardless of which dates are constant, parameters or field names.
I think his error has nothing to do with the BETWEEN, but rather the reason is
that he didn't only add the BETWEEN, he also added the LEFT JOIN and at least
RatebookID exists in both tables (probably giving him the ambiguous field name
error). He may just have assumed it was the BETWEEN that gave him the error,
rather than try the statement without the BETWEEN.
I'd change the query to:
SELECT ra.description, ra.WOReqd, ra.Addontype as AddonTypeID, ra.RatebookID
FROM rentaladdons ra
JOIN rentalratebook rr on ra.ratebookID = rr.ratebookID
WHERE ra.addonID = '1002' AND '09/15/2014' BETWEEN rr.fromdatetime and
rr.todatetime
(change ra to rr if I guess wrong which table some fields belong to)
LEFT JOIN is sometimes good to use, but in your case you use fields from
rentalratebook also in your WHERE clause, effectively turning the LEFT JOIN
into an [INNER] JOIN. [INNER] JOIN gives the optimizer more of a choice
regarding which PLAN to generate, so in this query, you're probably better off
not using LEFT (LEFT JOINs can sometimes be useful, but this is not one of
those cases). Moreover, there's no need to cast 09/15/2014 to date, it doesn't
harm doing it, but you don't need to.
One further thing to note, is that you use date between datetime and
datetime. 09/15/2014 is between 09/14/2014 00:00:01 and 09/15/2014 23:59:59,
but it is not between 09/15/2014 00:00:01 and 09/15/2014 23:59:59. Moreover, I
would not trust that it was between 09/15/2014 00:00:00 and 09/15/2014
23:59:59, since I would be uncertain whether 09/15/2014 00:00:00 was an exact
value or an approximation that could be slightly less or slightly more than
09/15/2014.
HTH,
Set
Re: [firebird-support] SQL Error -104
i just don't get it what the heck i'm doing wrong. I would like to have something like that: select ba.artno from mov_invoices mi, bas_articles ba where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00' and case when (ba.artno like '90__') then '9000' when (ba.artno like '80__') then '8000' when (ba.artno like '70__') then '7000' when (ba.artno like '60__') then '6000' when (ba.artno like '50__') then '5000' when (ba.artno like '40__') then '4000' when (ba.artno like '30__') then '3000' when (ba.artno like '20__') then '2000' when (ba.artno like '10__') then '1000' end any ideas somebody? The reason this fails, Johannes, is that all parts a the WHERE clause should evaluate to a Boolean value and your query doesn't. mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00' will evaluate to true or false (or null), but you have no equality, between or similar for your case statement (you have only specified one side of the comparison - it's the same as specifying mi.invdate and forgetting between and the dates). Also, there's no need for you to use CASE in your case. Try something like: select ba.artno from bas_articles ba join mov_invoices mi on substring(ba.artno from 1 for 2) || '00' = mi.something where mi.invdate between '2014-05-01 00:00:00' and '2014-08-01 00:00:00' I'm guessing that your case statement creates a value that should be compared to a value in mov_invoices, and that you do not want to return articles not related to the invoice. If I'm wrong, replace JOIN with CROSS JOIN, remove the ON part and move the substring to the WHERE clause. HTH, Set
SV: [firebird-support] Firebird Embedded corruptions
Hi, We have shipped Firebird Embedded bundled together with our product for a few years now and the system is currently in production at several thousand of our customer's sites. Currently we are using Firebird Embedded 2.5.1 with the latest .NET-driver and a stack consisting of Castle Active Record on top on NHibernate and the system is running on the latest versions of Windows. All is well and Firebird has served us good so far with the exception of database corruptions that gets reported from a new set of customers every week. For some of them it is possible to instruct the customer on how to repair the databases themselves, but some of the databases are unfortunately so heavily corrupted that they need to be sent to us for repairing (which is a tedious work that steals time from other tasks). Most of them corruptions are normally found in the tables that gets the most writes, but I guess that is only natural. We are now at the planning stage for the next major release of our product and we are thus rethinking if Firebird really is a good choice, because of this. Lots of effort has gone into solving this problem on our side, so I think the normal prerequisites has already been put into place (e.g using forced writes and so forth), but our system needs to be up and running 24x7, which means that it is not possible to schedule periodic backup/restore cycles and my personal theory is that Firebird embedded gets corrupted over time if you are not doing this regularly. So I have have a few questions that I would appreciate if someone could answer: 1. Is it feasible to run Firebird Embedded 24x7 in a setup where there are no scheduled backup/restore cycles. If not, how often should this be performed to ensure that the database does not get corrupted. 2. Most of our customers are not using a UPS. From my experiments I have not managed to create a corrupted database by turning of the power while doing a large set of writes (in a session running in VirtualBox). Could someone please confirm that this is indeed safe when you are running with synchronized writes turned on? 3. Are there any operations on a live database that should be avoided to minimize the risk of corruptions? 4. Just read a discussion about whether it is needed or not to call fb_shutdown to stop Firebird Embedded. Could this be the reason why we are getting corruptions? Should we change our service to perform this call when it is stopped? 5. I have also seen discussions of turning of automatic sweeps of the database (and doing them manually instead). Is this a likely source of corruptions for our setup? Thanks in advance. Maybe are there no certain answers to my questions, but any pointers in the right direction would be very appreciated. Firebird has been a real workhorse for us and we would rather like to keep it. Hei Jan! The one thing I try to avoid, is running DDL (CREATE, ALTER, DROP table|trigger|stored procedure) on a database in use. Maybe I'm overly careful, but not all too long ago, a colleague caused some problems when he did ALTER MyTable DROP MyField; while he simultaneously had another transaction having uncommitted changes to MyField in one record. I think (but have no experience), that possible reasons for corruption could include file system backups of the database while it is in use (exclude the database file(s) from such backups, rather use gbak for the backup, and include the resulting file in the system backup), and possibly anti-viruses preventing Firebird from doing it's work (though I would expect this to result in the database being unaccessible, not corrupted). Another thing that's only affecting Fb 2.5.1, is that this version has an error relating to compund indices (requiring backup/restore or rebuilding such indices if upgrading to 2.5.2). Though I doubt this error would cause data corruptions involving more than the index. Others will be able to give you a more thorough answer, despite having used Firebird since it's inception (0.9.4), I've very little experience with corruptions (undoubtedly related to only working on a handful of databases with about 20 simultaneous users). HTH, Set
Re: [firebird-support] How merge two queries
Hallo, I have two queries: First (results: ANNO, IMPONIBILE): select EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO, SUM(DC.IMPORTO) AS IMPONIBILE from DOC_CORPO DC, DOC_TESTA DT, VOCI V WHERE DT.ID = DOC_TESTA_ID AND DT.DOCUMENTO_ID 'PRO' AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013 AND DC.VOCE_ID = V.ID AND V.CONTRIBUTI = 1 GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) Second (results: ANNO, IMPOSTA): select EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO, SUM(DC.IMPORTO) AS IMPOSTA from DOC_CORPO DC, DOC_TESTA DT WHERE DT.ID = DOC_TESTA_ID AND DT.DOCUMENTO_ID 'PRO' AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013 AND DC.VOCE_ID = 'ENPACL' GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) There is a way to merge those two queries to obtain the same result in one query that results: ANNO, IMPONIBILE, IMPOSTA? Hi, Luigi! You could try something like (I'm assuming that DOC_TESTA_ID belongs to DC and that ID is the primary key of VOCI): select EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO, SUM(IIF(V.ID is not null, DC.IMPORTO, null)) AS IMPONIBILE, --sum only those that are in V SUM(IIF(DC.VOCE_ID = 'ENPACL', DC.IMPORTO, null)) AS IMPOSTA --sum only those that have correct DC.VOCE_ID from DOC_CORPO DC join DOC_TESTA DT on DC.DOC_TESTA_ID = DT.ID left join VOCI V on DC.VOCE_ID = V.ID and V.CONTRIBUTI = 1 --assuming 0 or 1 match where DT.DOCUMENTO_ID 'PRO' and DT.DATA_DOCUMENTO between '2013-01-01' and '2013-12-31' --BETWEEN can use an index, EXTRACT cannot and (DC.VOCE_ID = 'ENPACL' or V.ID is not null) --to avoid all rows that doesn't fit either of the requirements group by EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) HTH, Set
[firebird-support] Re: Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
CASHBOXID is already know. It is a reference to a table that we don't touch in the transaction. This table contains the current total amount of a cash box at the begging of the day. So when I try to load data for a cash box a java code checks is there record for this cash box for current day. If there is no such record it calculates the total current amount and calls the insert that adds a record to this table. Usually inserting record with same value for primary key will create error and probably will not block the execution of the statement. User is sysdba. There is no security restrictions for access to database. End users have no access to database server at all. They all use code that runs in application server to access or modify data. That is why I'm not restricting the access to the database for the moment. I'm not sure it is a lock conflict. But I suspect that something is locked somewhere and that forces the execution of the statement to stop and to lock all other inserts that other connections try to execute. As I said I need ideas or hints what to do in order to find the real problem when this situation happens again. One more thing. When I try to stop application server when this happens all connections that are blocked including the initial one stop the shutdown of the server. When I try to kill firebird process that initially blocked the execution all other processes are unlocked and server is shutdown after that. But in this case I try guess which process is that. When I look in the monitoring tables I can't see the pid of the process of each statement. Have you considered trying something like (written using a text editor, so there could well be errors): CREATE GENERATOR PK_GEN; CREATE TABLE CASH_CASHBOX_DAY_AMMOUNT ( PK INTEGER NOT NULL, CASHBOXID INTEGER NOT NULL, DATE_TIME NUMERIC( 18, 0) NOT NULL, AMMOUNTNUMERIC( 18, 0), CONSTRAINT PK_CASH_CASHBOX_DAY_AMMOUNT PRIMARY KEY (PK) ); ALTER TABLE CASH_CASHBOX_DAY_AMMOUNT ADD CONSTRAINT FK_CASH_CASHBOX_DAY_AMMOUNT FOREIGN KEY (CASHBOXID) REFERENCES CASH_CASHBOX (ID); CREATE INDEX IDX_CCDA_CASHBOXID ON CASH_CASHBOX_DAY_AMMOUNT(CASHBOXID); CREATE INDEX IDX_CCDA_DATE_TIME ON CASH_CASHBOX_DAY_AMMOUNT(DATE_TIME); SET TERM ^^ ; CREATE TRIGGER CASH_CASHBOX_DAY_AMMOUNT_PK ACTIVE BEFORE INSERT AS BEGIN if (new.PK IS NULL) then new.PK = GEN_ID(PK_GEN, 1); END ^^ SET TERM ; ^^ If this makes the problem disappear for INSERTs, then that problem is you inserting identical CASHBOXID and DATE_TIME combinations. If this is the problem, you'd probably want to change a few selects in your programs from (e.g.) select cashboxid, date_time, ammount from cash_cashbox_day_ammount to select cashboxid, date_time, sum(ammount) ammount from cash_cashbox_day_ammount group by 1, 2 since there now can be duplicates. Another thing is that you now may want to do some occational housekeeping, allowing for duplicates to occur is not the same as wanting there to remain duplicates of CASHBOXID and DATE_TIME over time in your database. I'd recommend you to run this statement regularly, e.g. once each night or week (change it to a stored procedure if you want to - and if this table is huge, you may want to add another trigger-populated field with the insert and update time - so that you can exclude rows not changed/inserted recently from the calculation): execute block as declare variable cbid integer; declare variable dt numeric(18,0); declare variable total_amount numberic(18,0); begin for select cashboxid, date_time, sum(ammount) from cash_cashbox_day_ammount group by 1, 2 having count(*) 1 into :cbid, :dt, :total_amount do begin delete from cash_cashbox_day_ammount where cashbox_id = :cbid and date_time = :dt; insert into cash_cashbox_day_ammount(cashboxid, date_time, ammount) values(:cbid, :dt, :total_amount); end end HTH, Set
RE: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
This is the definition of the table that locks on insert: CREATE TABLE CASH_CASHBOX_DAY_AMMOUNT ( CASHBOXID INTEGER NOT NULL, DATE_TIME NUMERIC( 18, 0) NOT NULL, AMMOUNTNUMERIC( 18, 0), CONSTRAINT PK_CASH_CASHBOX_DAY_AMMOUNT PRIMARY KEY (CASHBOXID, DATE_TIME) ); ALTER TABLE CASH_CASHBOX_DAY_AMMOUNT ADD CONSTRAINT FK_CASH_CASHBOX_DAY_AMMOUNT FOREIGN KEY (CASHBOXID) REFERENCES CASH_CASHBOX (ID); It is simple insert that just inserts single record. There is no triggers or generators. Primary key value is know before insert is executed. I see two likely ways an insert can fail here: 1) The CASHBOXID doesn’t exist or has been inserted in another transaction that is not yet committed (possible solution: insert into CASH_CASHBOX and CASH_CASHBOX_DAY_AMMOUNT in the same transaction) 2) A record with the same CASHBOXID and DATE_TIME already exists Can be avoided by using a new field populated through a generator as the primary key – but you would then probably have to also implement a ‘duplicate resolution’-button or similar in your program to fix up those cases where there are several records with the same CASHBOXID and DATE_TIME Either of these ought to give you an error message (which would be good to post on this list). Your problem could of course be related to something completely different (like Mark suggests – or something stupid like the user having no insert rights to the table), but if it is the inserts themselves that are the problem, I see no other possible reason for a lock conflict. Set
[firebird-support] RE: Case insensitive search on a memo field
Hi, I'm using Firebird 2.5.3, and I am looking for text in a memo field, eg. I take it you mean BLOB or VARCHAR, I've never heard of memo fields in Firebird? Select * from mytable t where t.mymemo like '%find me%' The will only find find me and not FIND ME or Find me or Find Me etc I'm tempted to do something like Select * from mytable t where lowercase(t.mymemo) like '%find me%' but since this memo field could be enormous, I'm guessing that wold be horribly inefficient. Is there a better way? I think SELECT * FROM MYTABLE WHERE MYMEMO CONTAINING 'FIND ME' is case insensitive. One, probably better, alternative would be to use a case insensitive collation, but I doubt collations can be used in blobs (it should work in a varchar). And you're right, it will be slow since no indexes can be used (hopefully, we're not talking about more than a few million rows in this table?)... HTH, Set
RE: [firebird-support] Case insensitive search on a memo field
Select * from mytable t where t.mymemo like '%find me%' You can create a computed index for this: CREATE INDEX idxname ON mytable COMPUTED BY (lowercase(mymemo)) Such an index does not help for LIKE '%...', it can only be used if the first character is a real character (i.e. not % or _). Moreover, the index will not be used if you use a parameter rather than constant with LIKE. Set
RE: [firebird-support] Insert ot update statements locks a table and all other instances that try to run same statement are lcoked to
They all use READ COMMITTED isolation. It is an application that runs in JBoss App server so I have single datasource defined that uses READ COMMITTED. Blocked statements are insert and update. Selects are not blocked. Why are inserts blocked? That should not happen unless you a) don’t use generators to fill values into your primary keys or b) have a unique constraint or similar (like triggers limiting the number of child records per master record) I can understand why UPDATEs (including the UPDATE OR INSERT statement) result in lock conflicts and that can be unavoidable and something you have to try to minimize rather than eliminate. INSERTs, on the other hand, should only cause lock conflicts if your business logic determines it should be so. Maybe you could show us how these lock conflicts occur (what does a typical insert statement that causes a lock conflict look like and are there any constraints/unique requirements to any of the fields?) Set
RE: [firebird-support] Query stop working after upgrade from 2.1.5 to 2.1.6
Hi, I found that after upgrade from FireBird 2.1.5 Update 1 to 2.1.6 my query stop working with error Dynamic SQL Error SQL error code = -104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause). The same error occur on 2.5.3 select ke.I_ID_GRUPY, ke.C_SYMBOL, ( select first 1 cs.C_OPIS from T_CENY_SKLADNIKI as cs left join T_CENY_W_OKRESIE as cwo on (cwo.I_ID_CENY_OKRS = cs.I_ID_CENY_OKRS) where cs.C_SYMBOL = ke.C_SYMBOL and cwo.I_ID_CENNIKA = any (select I_ID_CENNIKA from R_KOSZTY as kk where kk.I_ID_KOSZTY = ko.I_ID_KOSZTY) group by cs.C_OPIS ) as C_OPIS_X, sum(ke.N_VALUE) as N_VALUE_SUMA, sum(ke.N_VATIN) as N_VATIN_SUMA, sum(ke.N_COSTNETTO) as N_COSTNETTO_SUMA, sum(ke.N_COSTBRUTTO) as N_COSTBRUTTO_SUMA, ke.SI_WYKLADNIK, ke.SI_IDJEDNOSTKA, ke.SI_STPOTYPE, ke.SI_CURRENCYPERVALUE from R_KOSZTY k left join R_KOSZT_OKRESY as ko on (ko.I_ID_KOSZTY = k.I_ID_KOSZTY) left join R_KOSZT_ELEMENTY as ke on (ke.I_ID_KOSZT_OKRESY = ko.I_ID_KOSZT_OKRESY) where ke.SI_USEINSUMMARY = 1 and k.I_ID_KOSZTY = any (select ID from IDY_DO_ZAPYTANIA) group by ke.I_ID_GRUPY, ke.C_SYMBOL, C_OPIS_X, SI_WYKLADNIK, SI_IDJEDNOSTKA, SI_STPOTYPE, SI_CURRENCYPERVALUE order by max(ke.I_ORDER_INDEX) Any idea how to overcome this issue? Hi Macma! I would have tried this query (I've assumed that ID is the primary key of IDY_DO_ZAPYTANIA) and checked whether the result was as desired (admittedly, I'm not used to using MIN in a subselect): select ke.I_ID_GRUPY, ke.C_SYMBOL, (select min(cs.C_OPIS) from T_CENY_SKLADNIKI as cs join T_CENY_W_OKRESIE as cwo on cwo.I_ID_CENY_OKRS = cs.I_ID_CENY_OKRS join R_KOSZTY kk on cwo.I_ID_CENNIKA = kk.I_ID_CENNIKA where cs.C_SYMBOL = ke.C_SYMBOL and kk.I_ID_KOSZTY = ko.I_ID_KOSZTY) as C_OPIS_X, sum(ke.N_VALUE) as N_VALUE_SUMA, sum(ke.N_VATIN) as N_VATIN_SUMA, sum(ke.N_COSTNETTO) as N_COSTNETTO_SUMA, sum(ke.N_COSTBRUTTO) as N_COSTBRUTTO_SUMA, ke.SI_WYKLADNIK, ke.SI_IDJEDNOSTKA, ke.SI_STPOTYPE, ke.SI_CURRENCYPERVALUE, max(ke.I_ORDER_INDEX) I_ORDER from R_KOSZTY k join R_KOSZT_OKRESY as ko on ko.I_ID_KOSZTY = k.I_ID_KOSZTY join R_KOSZT_ELEMENTY as ke on ke.I_ID_KOSZT_OKRESY = ko.I_ID_KOSZT_OKRESY join IDY_DO_ZAPYTANIA z on k.I_ID_KOSZTY = z.ID where ke.SI_USEINSUMMARY = 1 group by ke.I_ID_GRUPY, ke.C_SYMBOL, 3, SI_WYKLADNIK, SI_IDJEDNOSTKA, SI_STPOTYPE, SI_CURRENCYPERVALUE order by 12 I don't know whether your error is a result of grouping by C_OPIS_X (I didn't think it was allowed), ordering by a field you do not select (I added it to the output, change back if this isn't the problem) or something else. Using LEFT JOIN as opposed to [INNER] JOIN reduces the options for the optimizer - it is sometimes useful as part of optimization, but only after experiencing the original query being slow. If this doesn't solve your problem, please tell us more about your table definitions - I've no clue which fields are unique or having lots of duplicates in your tables, nor which indexes/keys you've defined and that limits the possible modifications I (and others on this list) can do to your query. HTH, Set PS! I've never used Fb 2.1 myself, only 0.9.4, 1.5 and 2.5.
Re: [firebird-support] Query stop working after upgrade from 2.1.5 to 2.1.6
Hi, I overcome this issue with this query select ke.I_ID_GRUPY, ke.C_SYMBOL, ( select first 1 cs.C_OPIS from T_CENY_SKLADNIKI as cs left join T_CENY_W_OKRESIE as cwo on (cwo.I_ID_CENY_OKRS = cs.I_ID_CENY_OKRS) where cs.C_SYMBOL = ke.C_SYMBOL and cwo.I_ID_CENNIKA = any (select I_ID_CENNIKA from R_KOSZTY as kk where kk.I_ID_KOSZTY = any (select ID from IDY_DO_ZAPYTANIA)) ) as C_OPIS_X, sum(ke.N_VALUE) as N_VALUE_SUMA, sum(ke.N_VATIN) as N_VATIN_SUMA, sum(ke.N_COSTNETTO) as N_COSTNETTO_SUMA, sum(ke.N_COSTBRUTTO) as N_COSTBRUTTO_SUMA, ke.SI_WYKLADNIK, ke.SI_IDJEDNOSTKA, ke.SI_STPOTYPE, ke.SI_CURRENCYPERVALUE from R_KOSZTY k left join R_KOSZT_OKRESY as ko on (ko.I_ID_KOSZTY = k.I_ID_KOSZTY) left join R_KOSZT_ELEMENTY as ke on (ke.I_ID_KOSZT_OKRESY = ko.I_ID_KOSZT_OKRESY) where ke.SI_USEINSUMMARY = 1 and k.I_ID_KOSZTY = any (select ID from IDY_DO_ZAPYTANIA) group by ke.I_ID_GRUPY, ke.C_SYMBOL, C_OPIS_X, SI_WYKLADNIK, SI_IDJEDNOSTKA, SI_STPOTYPE, SI_CURRENCYPERVALUE order by max(ke.I_ORDER_INDEX) I have to change where kk.I_ID_KOSZTY = ko.I_ID_KOSZTY to kk.I_ID_KOSZTY = any (select ID from IDY_DO_ZAPYTANIA). Good to hear you overcome your issue. I took a look at your database and query this evening, and to my surprise your original query executed fine on 2.5.2 (26539)! (well, not yielding any result due to IDY_DO_ZAPYTANIA being empty, but removing that table gave 12 rows). An alternative query you could use (up to you to choose, you could try both if this is just a small test database and you have a bigger real database), is: with tmp (C_SYMBOL, I_ID_KOSZTY, C_OPIS_X) as (select cs.C_SYMBOL, kk.I_ID_KOSZTY, min(cs.C_OPIS) from T_CENY_SKLADNIKI as cs join T_CENY_W_OKRESIE as cwo on cwo.I_ID_CENY_OKRS = cs.I_ID_CENY_OKRS join R_KOSZTY kk on cwo.I_ID_CENNIKA = kk.I_ID_CENNIKA group by 1, 2) select ke.I_ID_GRUPY, ke.C_SYMBOL, t.C_OPIS_X, sum(ke.N_VALUE) as N_VALUE_SUMA, sum(ke.N_VATIN) as N_VATIN_SUMA, sum(ke.N_COSTNETTO) as N_COSTNETTO_SUMA, sum(ke.N_COSTBRUTTO) as N_COSTBRUTTO_SUMA, ke.SI_WYKLADNIK, ke.SI_IDJEDNOSTKA, ke.SI_STPOTYPE, ke.SI_CURRENCYPERVALUE from R_KOSZTY k join R_KOSZT_OKRESY as ko on ko.I_ID_KOSZTY = k.I_ID_KOSZTY join R_KOSZT_ELEMENTY as ke on ke.I_ID_KOSZT_OKRESY = ko.I_ID_KOSZT_OKRESY join IDY_DO_ZAPYTANIA z on k.I_ID_KOSZTY = z.ID left join tmp t on ke.C_SYMBOL = t.C_SYMBOL and ko.I_ID_KOSZTY = t.I_ID_KOSZTY where ke.SI_USEINSUMMARY = 1 group by ke.I_ID_GRUPY, ke.C_SYMBOL, t.C_OPIS_X, SI_WYKLADNIK, SI_IDJEDNOSTKA, SI_STPOTYPE, SI_CURRENCYPERVALUE order by max(ke.I_ORDER_INDEX) HTH, Set
RE: [firebird-support] conditional select
Hi, I'm looking at a system that I have not touched for some years and wonder if there is a way to improve this code. This all works, but is there a better or more efficient way? I can't think of how to combine them into one 'where' statement. Basically I look for a part based on increasingly tight conditions. /* search conditions 0 partno only, 1 partno location 2 partno Trans_type 3 partno location Trans_type */ input parameters locsearch char(20), locsearchtype integer, loclocation char(1), loctransaction char(1) begin for selects.partno,s.morefields. from stock s where (:locsearchtype=0 and s.partno=:locsearch) or(:locsearchtype=1 and s.partno=:locsearch and s.location=:loclocation) or(:locsearchtype=2 and s.partno=:locsearch and s.trans_type=:loctransaction) or(:locsearchtype=3 and s.partno=:locsearch and s.trans_type=:loctransaction and s.location=:loclocation) into :partno_out,:morefields_out. do suspend; end Hi Alan! If you use Fb 2.5, I suppose you could try: for selects.partno,s.morefields. from stock s where s.partno = :locsearch and s.location is not distinct from coalesce(:loclocation, s.location) and s.trans_type is not distinct from coalesce(:loctransaction, s.trans_type) If :loclocation is null, then every record should match (is not distinct from differs from equality comparison by including cases where s.location is null). I don't think there's any need for locsearchtype... If you cannot use is distinct from or coalesce, you should at least move s.partno outside of the 'or' bit. HTH, Set
RE: [firebird-support] Difficult condiion question
Hello, the following sql statement is almost done, just one thing is problematic. In the table tlager_sum I have two types of amounts, one of typ 1, one of typ 2. I just need the one of typ1. But if the material not in the table listet or just für typ 2, and there is a minimum inventory in the tteile set, then should I get this record too. Expect for one constellation it works fine: It was set a minimum inventory and in the table tlager_sum It was present a record with an amount of typ 2 - then I don't get this record. How can I solve this problem in firebird? In Microsoft Access I can create a subquery how gets me only typ 1, whats in firebird the best way? SELECT TTEILE.TEILENR, TTEILE.BEZEICHNUNG, TLAGER_SUM.MENGE, TTEILE.MINB, TLAGER_SUM.TYP, TTEILE.TYP FROM TTEILE LEFT JOIN TLAGER_SUM ON TTEILE.TEILENR = TLAGER_SUM.TEILENR WHERE (((TLAGER_SUM.MENGE)[minb]) AND ((TLAGER_SUM.TYP)=1) AND ((TTEILE.TYP)=1)) OR (((TLAGER_SUM.MENGE) Is Null) AND ((TTEILE.MINB)0) AND ((TTEILE.TYP)=1)) ORDER BY TTEILE.TEILENR; Hi Olaf, I don't quite understand your question, but it is possible that you just need to add 'AND TLAGER_SUM.TYP = 1' to your left join. Below is the sql after I tried to tidy it a bit and simplify to see if that helped me understand your question (by the way, I don't understand [minb], is that some kind of parameter?). HTH, Set SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP FROM TTEILE TT LEFT JOIN TLAGER_SUM LS ON TT.TEILENR = LS.TEILENR AND LS.TYP = 1 AND LS.MENGE[minb] AND LS.TYP=1 WHERE TT.TYP=1 AND (LS.MENGE IS NOT NULL OR TT.MINB0) ORDER BY TT.TEILENR;
RE: [firebird-support] Difficult condiion question
I think I have it: SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP FROM TTEILE TT LEFT JOIN TLAGER_SUM LS ON TT.TEILENR = LS.TEILENR AND LS.TYP = 1 WHERE TT.TYP=1 AND (LS.MENGE IS NOT NULL AND LS.MENGEtt.minb OR TT.MINB0 and ls.menge is null) ORDER BY TT.TEILENR; The and ls.typ = 1 should exclude all records from tlager_-sum there not typ 1? It was fine! Thank you Can you tell me what about the statements before the where conditions? Until now I don't know about. Your syntax returns me too many records, some of them has entries in the table tlager_sum, but the amount and the present typ (1) returns me this statement, it is null. Good to hear that you figured it out, Olaf. Sorry for misleading you and writing code that returned records if TT.MINB 0 regardless of ls.menge. You can still reduce your query by one line if you want, LS.MENGE IS NOT NULL can simply be removed. The statements before the WHERE condition are part of the LEFT JOIN. Typically, that's where you put criteria relating to the right table. If you write such criteria in the WHERE clause, they could (logically speaking) turn the LEFT JOIN into an INNER JOIN. Set
RE: [firebird-support] Case-insensitive, unique, multi-column index
The problem comes with the second. (1, 'Bob Jacobs'), (2, 'Bob Jacobs'), (3, 'BOB JACOBS'), and (4, 'bob jacobs') constitute an acceptable set of rows; (2, 'Bill Hafner') and (2, 'BILL HAFNER') do not. That is, given a value for CORPORATION_S_KEY, there shouldn't be case-variants for the NAME value. There are at least 3 ways of solving this, the simplest (which I recommend if you don't need case sensitive sorting) would be to use a case insensitive collation for the field (exists for many character sets). The second option is to use a computed index: CREATE UNIQUE INDEX I_TEST_NAME ON TEST computed by(ID||'::'|| upper(NAME)); or just a computed index that you use in a unique constraint. The third option is to have an extra field that you populate through a BEFORE INSERT trigger: new.UC_Equivalent = upper(NAME). There may exist other options as well, Svein Erling
Re: [firebird-support] Getting Invalid Request BLR at offset 49 ?
If (:In_vin = :Out_BikeLast8Vin) then BEGIN Update Total_sales_to_part_cost_match set TSPM_BIKEYEAR = :Out_BikeYear,TSPM_BIKEMAKE = :Out_BikeMake, TSPM_BIKEMODEL = :Out_BikeModel, TSPM_LAST8VIN = :Out_BikeLast8Vin; Given that your stored procedure will update (and return) at most one row (as far as I understand your domain) and this is something that changes data (instead of only producing rows), I would keep it an executable stored procedure and change the way you execute it. Just a minor comment that's irrelevant to the original question: The update statement has no WHERE clause, so all records of Total_sales_to_part_cost_match will be updated. Set
RE: [firebird-support] Create Date of Database Object
Hi, Is it possible to see from the systems tables when a database object was created? For instance I want to try and find out when a specific trigger was created on the database, is this possible? table: MON$DATABASE field: MON$CREATION_DATE I think that is just the creation date (or restore date if restored) of the database, if there had been a creation date of the trigger, I would have expected that to be in the RDB$TRIGGERS table. However, I don't think there is such a field, and that it is more a matter of implementing a company policy to store the creation date of triggers in the trigger itself or it's description. I doubt there is a way to find out when a trigger was created if whomever wrote the trigger didn't do this, but I hope I'm wrong. Set
SV: [firebird-support] Firebird caching query results
In my environment I have a software which insert new rows on a certain table. This software and the firebird server is running on the same machine. I have a software running on a client which query the the server. The problem is there are some sort of cache, because I'm getting the old rows. If I query the database from the the server works fine (I get the new rows), but when I'm in na cliente I always get the same old rows from hours ago. Your problem is not related to any cache, it is related to Firebird using versioning of records rather than locking. One version of a record may be visible to one transaction, whereas another version of the same record is visible to other transactions. This is a feature that gives you as a user more control. Transactions are fundamental to Firebird, almost everything you do happen in a transaction. Depending on how you set it up, the transaction may either see records as they were when you started the transaction, or the last committed version. In many cases, it is important to make sure transactions are not running indefinitely - e.g. in our system things are rolled back one hour after the transaction started so that people having left a transaction open doesn't hinder Firebird from doing garbage collection. Systems under heavy load will require shorter transactions, whereas tiny systems may not notice any problems even with long-running transactions. The simplest answer to your particular problem, will be to commit your transaction and then reopen the query. But it would be better if you read more about transactions in Firebird. Another thing that may be of interest, is events. This can be used to notify your client that things have changed, so that the client can take appropriate action and reload data. Though that is a more advanced subject that can wait until you master transactions. HTH, Set
RE: [firebird-support] Skip records
Thomas, I usually solve this kind of problem with recusive CTEs providing a counter for the records returned, thus a simple select statement can nicely filter on this kind of property without row numbers functionality. Can you provide an example, please. I, for one, can't imagine how a CTE could be used for such a purpose. My imagination may be slightly more vivid than yours, Sean, I even think I answered something on this list with a similar recursive CTE a few years ago. WITH RECURSIVE TMP(Level, PK, fields) as (SELECT 1, min(PK), whatever FROM table WHERE whatever UNION ALL SELECT t.Level + 1, t1.PK, fields FROM TMP t JOIN table t1 on t.PK t1.PK LEFT JOIN table t2 on t2.PK TMP.PK and t2.PK t1.PK WHERE whatever and t2.PK is null) SELECT * FROM TMP WHERE MOD(Level, 5) = 0 This has the severe drawback that it doesn't work if there's more than 1024 records (I think, at least there's a very limited max depth of recursion), I consider it less intuitive than EXECUTE BLOCK, and expect it to be slower. But I agree that it would be interesting to see Thomas' solution. Don't know whether windowing functions can be used in the WHERE clause, but when Firebird 3 is released, it would be tempting to try things like WHERE MOD(ROW_NUMBER() OVER (ORDER BY something), 5) = 2 Set
SV: [firebird-support] How to truncate text to fit in a db field the easiest way in Firebird?
$CATEGORY=str_replace(','',$_REQUEST['CATEGORY']);
I convert html entities to text i can use on the web pages.
I use this for being able to retrieve it back as normal characters.
I use this on all my inserts.
And, then i want to put it in the category field in the table.
Let's say the category field is 20 characters in length.
If user is writing a lot of the text will expand, even if it is set to stop
at 20 characters. Because it adds extra characters to the converted variable.
and if i strip the characters to fit the db field, it can be that i loose one
' character, and then it is fucked up because it needs to of the ' for each .
How can i best do this? How can i make the text fit in the db field the
easiest way without getting the truncated string error?
Is there another easier way?
Just keep your inserts as they are and then run
UPDATE table
SET CATEGORY = SUBSTRING(CATEGORY FROM 1 FOR 19)
WHERE PrimaryKey = :NewPK AND --or some other way to only update recently
inserted rows
(SUBSTRING(CATEGORY FROM 20 FOR 1) = AND
SUBSTRING(CATEGORY FROM 19 FOR 2) '') OR
(SUBSTRING(CATEGORY FROM 18 FOR 3) = AND
SUBSTRING(CATEGORY FROM 17 FOR 4) '')
to delete orphan ' at the last position.
This should cover those situations where the html contains a ' or '' as the
last characters to be stored (if the html can contain ''' or at the end,
you'll have to add another pair or two of lines).
I've never done this myself, but I think it should work.
HTH,
Set
