Re: [firebird-support] Copy Table
Thank you Thomas Sometimes I may add fields to the master table and if I write all the fields explicitely, I will need to modify the stored procedure. I want to ensure to make an exact copy. So what I want to is: 1- Get the structure of the master table 2- Create a table with the year-month-day postfix 3- Copy all data from the master to the backup table If it's possible to do this within a stored procedure, I will go that way. If not, I'll do it from a client program. Now I do these manually: -Copy the ddl text, change the table name to let's say TblA -Create table TblA_2014_04_01 (columns..) -Insert into TblA_2014_04_01 select * from TblA
[firebird-support] Reading unnecessary records
I use the latest version of FB 2.5. There is a view for called PERSON. Each row represents a person and it shows information as address, name, hobbies, etc. There are 20 joined codification tables using LEFT JOIN. For example all cities are codified, hobbies, etc. The structure of the view is something like CREATE VIEW PersonView .. SELECT * FROM PersonTable P LEFT JOIN City ON City.ID = P.CityID LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ... and so on for the 20 codified tables. City.ID is a primary key, like all IDs for remaining codifications. How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records. For example, supposing PersonTable has 10 records. SELECT COUNT(*) FROM PersonView I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. In theory the optimizer could know that City.ID and Hobby.ID are both primary keys (or unique) and therefore only scan PersonTable. Another example: SELECT CityName FROM PersonView I woud expect that Firebird read 10 records for PersonTable and 10 for City table, but it reads 210. The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan. What options do I have? Thank you
Re: [firebird-support] Reading unnecessary records
We generate the queries on the fly rather than trying to use a view, precisely because of these problems. So if CityName is not required in the output, there's no JOIN to City. And if there's something in the WHERE clause such as HobbyCode 27 then we know that HobbyID can't be null, which means we can use a JOIN instead of a LEFT JOIN, and quite often that results in a better plan. But yes, it does involve hundreds of lines of quite complex code to analyse what information is required in a particular case and generate the appropriate query, and in many cases the query optimiser could, theoretically, have worked this out for itself, but it doesn't. On 04/04/2014 12:36, kokok_ko...@yahoo.es wrote: I use the latest version of FB 2.5. There is a view for called PERSON. Each row represents a person and it shows information as address, name, hobbies, etc. There are 20 joined codification tables using LEFT JOIN. For example all cities are codified, hobbies, etc. The structure of the view is something like CREATE VIEW PersonView .. SELECT * FROM PersonTable P LEFT JOIN City ON City.ID = P.CityID LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ... and so on for the 20 codified tables. City.ID is a primary key, like all IDs for remaining codifications. How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records. For example, supposing PersonTable has 10 records. SELECT COUNT(*) FROM PersonView I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. In theory the optimizer could know that City.ID and Hobby.ID are both primary keys (or unique) and therefore only scan PersonTable. Another example: SELECT CityName FROM PersonView I woud expect that Firebird read 10 records for PersonTable and 10 for City table, but it reads 210. The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan. What options do I have? Thank you -- Tim Ward
Re: [firebird-support] Reading unnecessary records
I agree with Tim. Whe dont uwe views too becase this speed problem. Em 04/04/2014 08:51, Tim Ward t...@telensa.com escreveu: We generate the queries on the fly rather than trying to use a view, precisely because of these problems. So if CityName is not required in the output, there's no JOIN to City. And if there's something in the WHERE clause such as HobbyCode 27 then we know that HobbyID can't be null, which means we can use a JOIN instead of a LEFT JOIN, and quite often that results in a better plan. But yes, it does involve hundreds of lines of quite complex code to analyse what information is required in a particular case and generate the appropriate query, and in many cases the query optimiser could, theoretically, have worked this out for itself, but it doesn't. On 04/04/2014 12:36, kokok_ko...@yahoo.es wrote: I use the latest version of FB 2.5. There is a view for called PERSON. Each row represents a person and it shows information as address, name, hobbies, etc. There are 20 joined codification tables using LEFT JOIN. For example all cities are codified, hobbies, etc. The structure of the view is something like CREATE VIEW PersonView .. SELECT * FROM PersonTable P LEFT JOIN City ON City.ID = P.CityID LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ... and so on for the 20 codified tables. City.ID is a primary key, like all IDs for remaining codifications. How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records. For example, supposing PersonTable has 10 records. SELECT COUNT(*) FROM PersonView I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. In theory the optimizer could know that City.ID and Hobby.ID are both primary keys (or unique) and therefore only scan PersonTable. Another example: SELECT CityName FROM PersonView I woud expect that Firebird read 10 records for PersonTable and 10 for City table, but it reads 210. The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan. What options do I have? Thank you -- Tim Ward
Re: [firebird-support] Copy Table
You can do this automatically, I'll write down an example later this weekend, ok? Thomas Am 04.04.2014 13:12, schrieb atuncce...@duzen.com.tr: Thank you Thomas Sometimes I may add fields to the master table and if I write all the fields explicitely, I will need to modify the stored procedure. I want to ensure to make an exact copy. So what I want to is: 1- Get the structure of the master table 2- Create a table with the year-month-day postfix 3- Copy all data from the master to the backup table If it's possible to do this within a stored procedure, I will go that way. If not, I'll do it from a client program. Now I do these manually: -Copy the ddl text, change the table name to let's say TblA -Create table TblA_2014_04_01 (columns..) -Insert into TblA_2014_04_01 select * from TblAn bsp; -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Copy Table
From: atuncce...@duzen.com.tr Sent: Friday, April 04, 2014 6:12 AM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Copy Table Thank you Thomas Sometimes I may add fields to the master table and if I write all the fields explicitely, I will need to modify the stored procedure. I want to ensure to make an exact copy. So what I want to is: 1- Get the structure of the master table 2- Create a table with the year-month-day postfix 3- Copy all data from the master to the backup table If it's possible to do this within a stored procedure, I will go that way. If not, I'll do it from a client program. Now I do these manually: -Copy the ddl text, change the table name to let's say TblA -Create table TblA_2014_04_01 (columns..) -Insert into TblA_2014_04_01 select * from TblA How will you handle adding the new fields to existing backup tables? How will the procedure know which is a new field and which is not without looking at the structure of all the past tables created? IMHO, creating new tables for each month will become more of a nightmare as time goes by. I would think the better solution would be to create one backup table with a field for the year and month to distinguish the records. Trying to keep up with dynamic DDL changes for an unknown number of tables seem to be more of a pain than it's worth. Then again, maybe I'm just too old school about databases and still believe the best way to control DDL is outside the database, not inside. :) Woody (TMW)
RE: [firebird-support] Reading unnecessary records
The structure of the view is something like CREATE VIEW PersonView .. SELECT * FROM PersonTable P LEFT JOIN City ON City.ID = P.CityID LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ... and so on for the 20 codified tables. City.ID is a primary key, like all IDs for remaining codifications. How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records. For example, supposing PersonTable has 10 records. SELECT COUNT(*) FROM PersonView I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. Where are you getting those details? Firebird reports every row that is being read, including internal index rows/data. Also the query PLAN details makes the distinction between fetches reads which were resolved by engine page cache and reads (actual reads from disk/or OS disk cache) So, without seeing the fetch details, it is not possible to say how your problem can be solved. The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan. With that size of database, it is likely that the database cache size setting is low and should be increased. To answer that question, you will need to specific which version of the engine you are running? Classic, SuperClassic or SuperServer. Sean
Re: [firebird-support] Copy Table
Hi Woody, I think, it's neither intended to, nor necessary to alter structure of old backup tables to meet current version. If I understood right, it's just intended to have a snapshot of the data on regular basis for backup purpose. I would not call it old school, but a design decission: I'm really used to have my databases do their stuff on their own, without any other piece of software... Thomas *From:* atuncce...@duzen.com.tr mailto:atuncce...@duzen.com.tr *Sent:* Friday, April 04, 2014 6:12 AM *To:* firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com *Subject:* Re: [firebird-support] Copy Table Thank you Thomas Sometimes I may add fields to the master table and if I write all the fields explicitely, I will need to modify the stored procedure. I want to ensure to make an exact copy. So what I want to is: 1- Get the structure of the master table 2- Create a table with the year-month-day postfix 3- Copy all data from the master to the backup table If it's possible to do this within a stored procedure, I will go that way. If not, I'll do it from a client program. Now I do these manually: -Copy the ddl text, change the table name to let's say TblA -Create table TblA_2014_04_01 (columns..) -Insert into TblA_2014_04_01 select * from TblA How will you handle adding the new fields to existing backup tables? How will the procedure know which is a new field and which is not without looking at the structure of all the past tables created? IMHO, creating new tables for each month will become more of a nightmare as time goes by. I would think the better solution would be to create one backup table with a field for the year and month to distinguish the records. Trying to keep up with dynamic DDL changes for an unknown number of tables seem to be more of a pain than it's worth. Then again, maybe I'm just too old school about databases and still believe the best way to control DDL is outside the database, not inside. :) Woody (TMW) -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Copy Table
I want to create monthly backups of some tables by executing a procedure. For ex, lets say I have a table named as COMPANY, and every month I want to create a backup table named as COMPANY_BCK_2014_01, COMPANY_BCK_2014_02 and copy the existing data into these backup tables. Unfortunately, Firebird doesn't support the CTAS (CREATE TABLE ... AS SELECT ...) syntax. Feel free to vote on that tracker issue: http://tracker.firebirdsql.org/browse/CORE-796 -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
RE: [firebird-support] Reading unnecessary records
I get these data from an old app called Interbase PLAN Analizer, that works pretty well. It shows me reads and also the plan. I can see how Firebird includes in the plan all codification tables, when it is not strictly necessary. Something changed in FB 2.5 version. I used 1.0 for a long time and the new one is slower in some cases. I use ClassicServer because of resilience offered by the multiple processes. I know that it is less efficient, but the number of concurrent users are really low, between 2-10. In any case, when I do tests, I am in my own dev PC and I am the only user. Probably I could improve tuning the database, but the bottle neck is the plan, that it is not optimized. As other users say in this forum about this thread, it is a huge problem. They, as probably me, cannot use views because they are slow. The solution is to built on the fly the SQL statement, but it is complex and we are doing the Firebird job. I would like to find a better way. Thank you
Re: [firebird-support] Copy Table
Consider attached procedure. It does not take care of calculated fields,
though. That can easily be added. You might consider to add the feature
of executing the statements on another, possibly remote, database/server
(execute statement on external), but you've to be aware of possible
performance issues during export.
Thomas
Am 04.04.2014 13:12, schrieb atuncce...@duzen.com.tr:
Thank you Thomas
Sometimes I may add fields to the master table and if I write all the
fields explicitely, I will need to modify the stored procedure. I want
to ensure to make an exact copy. So what I want to is:
1- Get the structure of the master table
2- Create a table with the year-month-day postfix
3- Copy all data from the master to the backup table
--
Mit freundlichen Grüßen,
Thomas Beckmann
Diplom-Informatiker
Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de
ASSFINET-Logo
*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de
http://www.assfinet.de/
Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331
Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
nicht gestattet.
--
set term ^ ;
create procedure P_CPYTBL (
TBL varchar(31),
SUFFIX varchar(10))
as
declare variable CRT_STMT varchar(16000);
declare variable INS_STMT varchar(16000);
begin
for with recursive
CTE_FLD as (select
trim(rf.RDB$RELATION_NAME) as TBL,
' ' || cast(trim(rf.RDB$FIELD_NAME) as varchar(16000)) as FLD,
' ' || trim(rf.RDB$FIELD_NAME) || ' ' ||
case f.RDB$FIELD_TYPE
when 7 then 'smallint'
when 8 then 'integer'
when 10 then 'float'
when 12 then 'date'
when 13 then 'time'
when 14 then 'char('||f.RDB$FIELD_LENGTH||')'
when 16 then iif(f.RDB$FIELD_SUB_TYPE = 1,
'numeric('||f.RDB$FIELD_PRECISION||','||(-1*f.RDB$FIELD_SCALE)||')', 'bigint')
when 27 then 'double precision'
when 35 then 'timestamp'
when 37 then 'varchar('||f.RDB$FIELD_LENGTH||')'
when 40 then 'cstring('||f.RDB$FIELD_LENGTH||')'
when 261 then 'blob' || coalesce(' sub_type
'||f.RDB$FIELD_SUB_TYPE, '') || coalesce(' segment size
'||f.RDB$SEGMENT_LENGTH, '')
end || coalesce(' ' || coalesce(rf.RDB$DEFAULT_SOURCE,
f.RDB$DEFAULT_SOURCE), '') as DECL,
rf.RDB$FIELD_POSITION + 1 as NXT_POS
from RDB$RELATION_FIELDS rf
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE
left join RDB$RELATIONS r on r.RDB$RELATION_NAME =
trim(rf.RDB$RELATION_NAME) || :SUFFIX
where rf.RDB$FIELD_POSITION = 0 and rf.RDB$RELATION_NAME = :TBL and
r.RDB$RELATION_ID is null
union all select
f0.TBL,
f0.FLD || ',' || ascii_char(10) || ' ' || trim(rf.RDB$FIELD_NAME) as
FLD,
f0.DECL || ',' || ascii_char(10) ||
' ' || trim(rf.RDB$FIELD_NAME) || ' ' ||
case f.RDB$FIELD_TYPE
when 7 then 'smallint'
when 8 then 'integer'
when 10 then 'float'
when 12 then 'date'
when 13 then 'time'
when 14 then 'char('||f.RDB$FIELD_LENGTH||')'
when 16 then iif(f.RDB$FIELD_SUB_TYPE = 1,
'numeric('||f.RDB$FIELD_PRECISION||','||(-1*f.RDB$FIELD_SCALE)||')', 'bigint')
when 27 then 'double precision'
when 35 then 'timestamp'
when 37 then 'varchar('||f.RDB$FIELD_LENGTH||')'
when 40 then 'cstring('||f.RDB$FIELD_LENGTH||')'
when 261 then 'blob' || coalesce(' sub_type
'||f.RDB$FIELD_SUB_TYPE, '') || coalesce(' segment size
'||f.RDB$SEGMENT_LENGTH, '')
end || coalesce(' ' || coalesce(rf.RDB$DEFAULT_SOURCE,
f.RDB$DEFAULT_SOURCE), '') as DECL,
rf.RDB$FIELD_POSITION + 1 as NXT_POS
from CTE_FLD f0
join RDB$RELATION_FIELDS rf on rf.RDB$FIELD_POSITION = f0.NXT_POS and
rf.RDB$RELATION_NAME = f0.TBL
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE)
select first 1
'create table ' || TBL || :SUFFIX || ' (' || ascii_char(10) || DECL ||
')' as CRT_STMT,
'insert into ' || TBL || :SUFFIX || ' (' || ascii_char(10) || FLD || ')'
|| ascii_char(10) ||
'select ' || ascii_char(10) || FLD || ascii_char(10) || ' from ' || TBL
as INS_STMT
from CTE_FLD
order by NXT_POS desc
into :CRT_STMT, :INS_STMT
do
begin
execute statement
Re: [firebird-support] Copy Table
Voted. Feel free to vote on that tracker issue: http://tracker.firebirdsql.org/browse/CORE-796 -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
RE: [firebird-support] Reading unnecessary records
I get these data from an old app called Interbase PLAN Analizer, that works pretty well. It shows me reads and also the plan. I can see how Firebird includes in the plan all codification tables, when it is not strictly necessary. Something changed in FB 2.5 version. I used 1.0 for a long time and the new one is slower in some cases. True, but v1.0 would generate invalid results in some corner cases. Result accuracy must always trump pure performance. Probably I could improve tuning the database, but the bottle neck is the plan, that it is not optimized. But you are assuming that because the PLAN shows that a non-referenced table is actually being read during execution. That may not be the case. The real test to confirm is running the queries and looking at the total of READs and FETCHes. Given the small size of the page cache for typical Classic deployments, and the size of the database, it is possible that the reads are to load the index data alone. Separately: - Please provide the schema for the tables and views, as well as the PLAN which is generated - what is the database page size? - In your v1.0 vs. v2.5 performance tests, did you use the exact same database for the comparison As other users say in this forum about this thread, it is a huge problem. They, as probably me, cannot use views because they are slow. Views are no slower than the same as standalone SQL, this has been tested/benchmarked and confirmed. I am not convinced that your numbers prove that non-referenced JOIN tables actually impact performance (whether in View or standalone query) Sean
Re: [firebird-support] Copy Table
Oops, it seems, I can not attach any files
===
create or alter procedure P_CPYTBL (
TBL varchar(31),
SUFFIX varchar(10))
as
declare variable CRT_STMT varchar(16000);
declare variable INS_STMT varchar(16000);
begin
for with recursive
CTE_FLD as (select
trim(rf.RDB$RELATION_NAME) as TBL,
' ' || cast(trim(rf.RDB$FIELD_NAME) as varchar(16000)) as FLD,
' ' || trim(rf.RDB$FIELD_NAME) || ' ' ||
case f.RDB$FIELD_TYPE
when 7 then 'smallint'
when 8 then 'integer'
when 10 then 'float'
when 12 then 'date'
when 13 then 'time'
when 14 then 'char('||f.RDB$FIELD_LENGTH||')'
when 16 then iif(f.RDB$FIELD_SUB_TYPE = 1,
'numeric('||f.RDB$FIELD_PRECISION||','||(-1*f.RDB$FIELD_SCALE)||')',
'bigint')
when 27 then 'double precision'
when 35 then 'timestamp'
when 37 then 'varchar('||f.RDB$FIELD_LENGTH||')'
when 40 then 'cstring('||f.RDB$FIELD_LENGTH||')'
when 261 then 'blob' || coalesce(' sub_type
'||f.RDB$FIELD_SUB_TYPE, '') || coalesce(' segment size
'||f.RDB$SEGMENT_LENGTH, '')
end || coalesce(' ' || coalesce(rf.RDB$DEFAULT_SOURCE,
f.RDB$DEFAULT_SOURCE), '') as DECL,
rf.RDB$FIELD_POSITION + 1 as NXT_POS
from RDB$RELATION_FIELDS rf
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE
left join RDB$RELATIONS r on r.RDB$RELATION_NAME =
trim(rf.RDB$RELATION_NAME) || :SUFFIX
where rf.RDB$FIELD_POSITION = 0 and rf.RDB$RELATION_NAME = :TBL
and r.RDB$RELATION_ID is null
union all select
f0.TBL,
f0.FLD || ',' || ascii_char(10) || ' ' ||
trim(rf.RDB$FIELD_NAME) as FLD,
f0.DECL || ',' || ascii_char(10) ||
' ' || trim(rf.RDB$FIELD_NAME) || ' ' ||
case f.RDB$FIELD_TYPE
when 7 then 'smallint'
when 8 then 'integer'
when 10 then 'float'
when 12 then 'date'
when 13 then 'time'
when 14 then 'char('||f.RDB$FIELD_LENGTH||')'
when 16 then iif(f.RDB$FIELD_SUB_TYPE = 1,
'numeric('||f.RDB$FIELD_PRECISION||','||(-1*f.RDB$FIELD_SCALE)||')',
'bigint')
when 27 then 'double precision'
when 35 then 'timestamp'
when 37 then 'varchar('||f.RDB$FIELD_LENGTH||')'
when 40 then 'cstring('||f.RDB$FIELD_LENGTH||')'
when 261 then 'blob' || coalesce(' sub_type
'||f.RDB$FIELD_SUB_TYPE, '') || coalesce(' segment size
'||f.RDB$SEGMENT_LENGTH, '')
end || coalesce(' ' || coalesce(rf.RDB$DEFAULT_SOURCE,
f.RDB$DEFAULT_SOURCE), '') as DECL,
rf.RDB$FIELD_POSITION + 1 as NXT_POS
from CTE_FLD f0
join RDB$RELATION_FIELDS rf on rf.RDB$FIELD_POSITION =
f0.NXT_POS and rf.RDB$RELATION_NAME = f0.TBL
join RDB$FIELDS f on f.RDB$FIELD_NAME = rf.RDB$FIELD_SOURCE)
select first 1
'create table ' || TBL || :SUFFIX || ' (' || ascii_char(10) ||
DECL || ')' as CRT_STMT,
'insert into ' || TBL || :SUFFIX || ' (' || ascii_char(10) || FLD
|| ')' || ascii_char(10) ||
'select ' || ascii_char(10) || FLD || ascii_char(10) || ' from '
|| TBL as INS_STMT
from CTE_FLD
order by NXT_POS desc
into :CRT_STMT, :INS_STMT
do
begin
execute statement :CRT_STMT with autonomous transaction;
execute statement :INS_STMT with autonomous transaction;
end
end
===
Am 04.04.2014 18:52, schrieb Thomas Beckmann:
Consider attached procedure. It does not take care of calculated fields,
though. That can easily be added. You might consider to add the feature
of executing the statements on another, possibly remote, database/server
(execute statement on external), but you've to be aware of possible
performance issues during export.
Thomas
Am 04.04.2014 13:12, schrieb atuncce...@duzen.com.tr:
Thank you Thomas
Sometimes I may add fields to the master table and if I write all the
fields explicitely, I will need to modify the stored procedure. I want
to ensure to make an exact copy. So what I want to is:
1- Get the structure of the master table
2- Create a table with the year-month-day postfix
3- Copy all data from the master to the backup table
--
Mit freundlichen Grüßen,
Thomas Beckmann
Diplom-Informatiker
Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de
ASSFINET-Logo
*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de
http://www.assfinet.de/
Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331
Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
RE: [firebird-support] Reading unnecessary records
I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. In theory the optimizer could know that City.ID and Hobby.ID are both primary keys (or unique) and therefore only scan PersonTable. The engine/optimizer does know that. The engine, however, must check whether the matching key does exist, which requires reading the index structures. -- which raises a READ or FETCH operation! So, a count of 210 is not unreasonable. Sean
[firebird-support] Re: Reading unnecessary records
04.04.2014 21:07, Leyne, Sean wrote: But you are assuming that because the PLAN shows that a non-referenced table is actually being read during execution. That may not be the case. Firebird does not eliminate unreferenced tables from retrieval, so he's correct. There's no difference in this regard between v1.x and v2.x though. The real test to confirm is running the queries and looking at the total of READs and FETCHes. Checking page level statistics is a waste of time in this case. The engine perfectly reports record level statistics on a per table basis, this is a much better proof. Views are no slower than the same as standalone SQL, this has been tested/benchmarked and confirmed. View is no slower than its replacement as a derived table, but it can be slower than logically the same but streamlined query. Dmitry
[firebird-support] syntax for select case ...
Hello, I am new to Firebird, I have experience using MS SQL.I am trying to convert some code that performs various checks but having problems with the syntax. One thing I need to convert is SQL to detect whether a table exists. I have tried these in Squirrel and isql connected to an embedded db version Firebird 2.5 I tried the following SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION' works fine. I went on to: select case when exists(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION') then 1 else 0 end; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 2, column 62 SQLState: 42000 ErrorCode: 335544569 I thought maybe firebird wants named result so I tried select (case when exists(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION') then 1 else 0 end) as result; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 2, column 70 SQLState: 42000 ErrorCode: 335544569 I am now down to some very basic queries that work in MS SQL but not in firebird: SELECT CASE WHEN 4 = 5 THEN 1 WHEN 5 = 6 THEN 2 ELSE 0 END; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 5, column 2 SQLState: 42000 ErrorCode: 335544569 or SELECT (CASE WHEN 4 = 5 THEN 1 WHEN 5 = 6 THEN 2 ELSE 0 END) as result; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 5, column 10 SQLState: 42000 ErrorCode: 335544569 I have looked at the following documentation but I don't see what I am doing wrong http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25.html thank you for any help you can give, Jim s
Re: [firebird-support] syntax for select case ...
Hi James, your query has to look like select something from relation Your example queries look like select something Taking your first example, you may write SELECT case RDB$RELATION_NAME when 'SCHEMA_VERSION' then 1 else 0 end FROM RDB$RELATIONS to achieve the second. I'd write SELECT ii(RDB$RELATION_NAME = 'SCHEMA_VERSION', 1, 0) FROM RDB$RELATIONS but this is identical in respect of performance. Am 04.04.2014 15:30, schrieb James Schumacher: Hello, I am new to Firebird, I have experience using MS SQL. I am trying to convert some code that performs various checks but having problems with the syntax. One thing I need to convert is SQL to detect whether a table exists. I have tried these in Squirrel and isql connected to an embedded db version Firebird 2.5 I tried the following SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION' works fine. I went on to: select case when exists(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION') then 1 else 0 end; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 2, column 62 SQLState: 42000 ErrorCode: 335544569 I thought maybe firebird wants named result so I tried select (case when exists(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION') then 1 else 0 end) as result; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 2, column 70 SQLState: 42000 ErrorCode: 335544569 I am now down to some very basic queries that work in MS SQL but not in firebird: SELECT CASE WHEN 4 = 5 THEN 1 WHEN 5 = 6 THEN 2 ELSE 0 END; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 5, column 2 SQLState: 42000 ErrorCode: 335544569 or SELECT (CASE WHEN 4 = 5 THEN 1 WHEN 5 = 6 THEN 2 ELSE 0 END) as result; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 5, column 10 SQLState: 42000 ErrorCode: 335544569 I have looked at the following documentation but I don't see what I am doing wrong http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25.html thank you for any help you can give, Jim s -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * 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/
Odp: [firebird-support] Reading unnecessary records
Hi, Did you use order by or group by in this view? Regards, Karol Bieniaszewski - Reply message - Od: kokok_ko...@yahoo.es Do: firebird-support@yahoogroups.com Temat: [firebird-support] Reading unnecessary records Data: pt., kwi 4, 2014 13:36 I use the latest version of FB 2.5. There is a view for called PERSON. Each row represents a person and it shows information as address, name, hobbies, etc. There are 20 joined codification tables using LEFT JOIN. For example all cities are codified, hobbies, etc. The structure of the view is something like CREATE VIEW PersonView .. SELECT * FROM PersonTable P LEFT JOIN City ON City.ID = P.CityID LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ... and so on for the 20 codified tables. City.ID is a primary key, like all IDs for remaining codifications. How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records. For example, supposing PersonTable has 10 records. SELECT COUNT(*) FROM PersonView I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. In theory the optimizer could know that City.ID and Hobby.ID nbsp; are both primary keys (or unique) and therefore only scan PersonTable. Another example: SELECT CityName FROM PersonView I woud expect that Firebird read 10 records for PersonTable and 10 for City table, but it reads 210. The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan. What options do I have? Thank you
Re: [firebird-support] syntax for select case ...
Thank you for the very clear answer! I am no big fan of MS and I guess I should not be surprised that MS SQL allows non-standard constructs. I will need to unlearn some things. I just looked at this description of SQL-2003 and I see how the From clause is not optional. http://savage.net.au/SQL/sql-2003-2.bnf.html thanks again, Jim s On 4/4/2014 1:21 PM, Thomas Beckmann wrote: Hi James, your query has to look like select something from relation Your example queries look like select something Taking your first example, you may write SELECT case RDB$RELATION_NAME when 'SCHEMA_VERSION' then 1 else 0 end FROM RDB$RELATIONS to achieve the second. I'd write SELECT ii(RDB$RELATION_NAME = 'SCHEMA_VERSION', 1, 0) FROM RDB$RELATIONS but this is identical in respect of performance. Am 04.04.2014 15:30, schrieb James Schumacher: Hello, I am new to Firebird, I have experience using MS SQL. I am trying to convert some code that performs various checks but having problems with the syntax. One thing I need to convert is SQL to detect whether a table exists. I have tried these in Squirrel and isql connected to an embedded db version Firebird 2.5 I tried the following SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION' works fine. I went on to: select case when exists(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION') then 1 else 0 end; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 2, column 62 SQLState: 42000 ErrorCode: 335544569 I thought maybe firebird wants named result so I tried select (case when exists(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'SCHEMA_VERSION') then 1 else 0 end) as result; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 2, column 70 SQLState: 42000 ErrorCode: 335544569 I am now down to some very basic queries that work in MS SQL but not in firebird: SELECT CASE WHEN 4 = 5 THEN 1 WHEN 5 = 6 THEN 2 ELSE 0 END; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 5, column 2 SQLState: 42000 ErrorCode: 335544569 or SELECT (CASE WHEN 4 = 5 THEN 1 WHEN 5 = 6 THEN 2 ELSE 0 END) as result; Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 5, column 10 SQLState: 42000 ErrorCode: 335544569 I have looked at the following documentation but I don't see what I am doing wrong http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25.html thank you for any help you can give, Jim s ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * 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] Reading unnecessary records
Em 4/4/2014 09:31, fabianoas...@gmail.com escreveu: I agree with Tim. Whe dont uwe views too becase this speed problem. Em 04/04/2014 08:51, Tim Ward t...@telensa.com mailto:t...@telensa.com escreveu: We generate the queries on the fly rather than trying to use a view, precisely because of these problems. So if CityName is not required in the output, there's no JOIN to City. And if there's something in the WHERE clause such as HobbyCode 27 then we know that HobbyID can't be null, which means we can use a JOIN instead of a LEFT JOIN, and quite often that results in a better plan. But yes, it does involve hundreds of lines of quite complex code to analyse what information is required in a particular case and generate the appropriate query, and in many cases the query optimiser could, theoretically, have worked this out for itself, but it doesn't. On 04/04/2014 12:36, kokok_ko...@yahoo.es mailto:kokok_ko...@yahoo.es wrote: I use the latest version of FB 2.5. There is a view for called PERSON. Each row represents a person and it shows information as address, name, hobbies, etc. There are 20 joined codification tables using LEFT JOIN. For example all cities are codified, hobbies, etc. The structure of the view is something like CREATE VIEW PersonView .. SELECT * FROM PersonTable P LEFT JOIN City ON City.ID = P.CityID LEFT JOIN Hobby ON Hobby.ID = P.HobbyID ... and so on for the 20 codified tables. City.ID is a primary key, like all IDs for remaining codifications. How can I optimize this view? My problem is that Firebird uses a really slow plan because it reads ALL codification records. For example, supposing PersonTable has 10 records. SELECT COUNT(*) FROM PersonView I would expect that Firebird only read 10 record, however it reads 10 + 10x20 = 210 records. In theory the optimizer could know that City.ID and Hobby.ID are both primary keys (or unique) and therefore only scan PersonTable. Another example: SELECT CityName FROM PersonView I woud expect that Firebird read 10 records for PersonTable and 10 for City table, but it reads 210. The real problem is that I have millions of records in the database, and a simple consult can take minutes when it could take few seconds with an optimized plan. What options do I have? Thank you -- Tim Ward I disagree... The problem does not lies on the view per se, but in the query... If you don't need a particular table information, having it on the view is useless... So if you have the need of a distinct rescult set only one view would not handle this. So the on the fly generated query is not better than the view because it's better optimzed than a view would be, but because it's a diferent query. Getting back to the original question: Do you really need OUTER JOINS ? Then you use all LEFT JOIN's the optimizer could just choose the order of the first to scan table between the tables that are on the left of an OUTER JOIN, in this case, there is no option, since there is just on table. If the Columns of the Foreign Keys could be null and in fact you really need the unrelated records, you really need the OUTER JOIN, and a query like this: select * from Person LEFT JOIN City on (City.CityID = Person.CityID) where City.Name = 'Paris' will do a full scan on table person and then use the PK index on City.CityID, where the ideal plan would be Use an index on City.Name and then do an index scan on the Foreign Key index of Person.CityID This is not possible (in the current version) since the optimizer should start with one of the tables on the left side of an OUTER JOIN Perhaps one day the optimizer could analyze this query and get to the conclusion that it could be better written as select * from Person JOIN City on (City.CityID = Person.CityID) where City.Name = 'Paris' So the problem is not on the view per se, but on the way the query is written. Give it a try, change your LEFT JOIN's to INNER JOIN's and se how it performs, be it a directly query or using a VIEW see you !
[firebird-support] Snapshot back up from VB6
Hi Everyone, Just wondering how or if it is possible to do a snapshot back up of a Firebird database from VB6? I can get the location of the database but whenever I try to copy the file to my back up drive with in VB6 I get a error 70 Permission denied. I Do not want to use any external applications to back up the database Can someone help or point me in the right direction? Thanks Andy
