[firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, Tested again with FB 2.5.1 & FB 2.5.5, apparently, this is not a problem from FB 3.0, but it was already appeared since FB 2.5. Same machine, database, query & engine, SC FB 2.5.1 & SC FB 2.5.2 runs a lot faster than FB 2.5.5 & FB 3.0. Regards, Anton
[firebird-support] Create or Alter Procedure: Token Unknown doing a Replace...
FB 2.14 - Delphi 2007 - IBDAC TIBCScript
Although I'm certain this is not a Delphi problem, I've included a copy of the
commands used to create the SQL. Those commands work in FlameRobin, and in
IBExpert. I used both to create working DDL, which I copied into my delphi
program. Any help or suggestions will be greatly appreciated. I've been beating
my head against this for hours. Ugh.
When I execute the script below, Everything appears to work fine until it
reaches the line that says:
Script1.SQL.Add(' S = Replace(S, ''BB'', ''B''); ');
However : Script1.SQL.Add(' S = Replace(S, ''A'', ); '); seems to work.
Odd.
So the "BB" line is where I get the following error message. I don't
understand why since the single letter replacement seems to work. I tried other
letter combinations such as 'YY' or 'SSS', but nothing seems to work once there
are more than one character.
Error Message:
Project ABS3.exe raised exception class EIBCError with message '
Dynamic SQL Error
SQL error code = -104
Token unknown - line 57, column 19
SSS'.
Code Sample:
Script1:= TIBCScript.Create(nil);
Script1.Delimiter := '||'
begin
Script1.SQL.Clear;
Script1.SQL.Add('CREATE OR ALTER PROCEDURE STANDARDIZENAMEADDRESS (');
Script1.SQL.Add('S D_SEARCHFIELD) ');
Script1.SQL.Add('RETURNS ( ');
Script1.SQL.Add('RESULT D_SEARCHFIELD) ');
Script1.SQL.Add('AS ');
Script1.SQL.Add('begin ');
Script1.SQL.Add(' S = Upper(S);');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove Spaces. Do three times. ');
Script1.SQL.Add(' S = Replace(S, '' '', ); ');
Script1.SQL.Add(' S = Replace(S, '' '', ); ');
Script1.SQL.Add(' S = Replace(S, '' '', ); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove apostrophy ');
Script1.SQL.Add(' S = Replace(S, ''#39'', ); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove Vowels ');
Script1.SQL.Add(' S = Replace(S, ''A'', ); ');
Script1.SQL.Add(' S = Replace(S, ''E'', ); ');
Script1.SQL.Add(' S = Replace(S, ''I'', ); ');
Script1.SQL.Add(' S = Replace(S, ''O'', ); ');
Script1.SQL.Add(' S = Replace(S, ''U'', ); ');
Script1.SQL.Add(' S = Replace(S, ''Y'', ); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove non-alphanumeric ');
Script1.SQL.Add(' S = Replace(S, Chr(96), ); ');
Script1.SQL.Add(' S = Replace(S, ''~'', ); ');
Script1.SQL.Add(' S = Replace(S, ''!'', ); ');
Script1.SQL.Add(' S = Replace(S, ''@'', ); ');
Script1.SQL.Add(' S = Replace(S, ''#'', ); ');
Script1.SQL.Add(' S = Replace(S, ''$'', ); ');
Script1.SQL.Add(' S = Replace(S, ''%'', ); ');
Script1.SQL.Add(' S = Replace(S, ''^'', ); ');
Script1.SQL.Add(' S = Replace(S, ''&'', ); ');
Script1.SQL.Add(' S = Replace(S, ''*'', ); ');
Script1.SQL.Add(' S = Replace(S, ''('', ); ');
Script1.SQL.Add(' S = Replace(S, '')'', ); ');
Script1.SQL.Add(' S = Replace(S, ''_'', ); ');
Script1.SQL.Add(' S = Replace(S, ''-'', ); ');
Script1.SQL.Add(' S = Replace(S, ''{'', ); ');
Script1.SQL.Add(' S = Replace(S, ''}'', ); ');
Script1.SQL.Add(' S = Replace(S, ''['', ); ');
Script1.SQL.Add(' S = Replace(S, '']'', ); ');
Script1.SQL.Add(' S = Replace(S, ''|'', ); ');
Script1.SQL.Add(' S = Replace(S, ''\'', ); ');
Script1.SQL.Add(' S = Replace(S, '':'', ); ');
Script1.SQL.Add(' S = Replace(S, '';'', ); ');
Script1.SQL.Add(' S = Replace(S, '', ); ');
Script1.SQL.Add(' S = Replace(S, Chr(60), ); ');
Script1.SQL.Add(' S = Replace(S, Chr(62), ); ');
Script1.SQL.Add(' S = Replace(S, Chr(63), ); ');
Script1.SQL.Add(' S = Replace(S, ''/'', ); ');
Script1.SQL.Add(' S = Replace(S, '','', ); ');
Script1.SQL.Add(' S = Replace(S, Chr(46), ); ');
Script1.SQL.Add(' ');
Script1.SQL.Add('-- Remove Double Letters ');
Script1.SQL.Add(' S = Replace(S, ''BB'', ''B''); ');
// Error occurs in the above line. =
Script1.SQL.Add(' S = Replace(S, ''CC'', ''C''); ');
Script1.SQL.Add(' S = Replace(S, ''DD'', ''D''); ');
Script1.SQL.Add(' S = Replace(S, ''FF'', ''F''); ');
Script1.SQL.Add(' S = Replace(S, ''GG'', ''G''); ');
Script1.SQL.Add(' S = Replace(S, ''HH'', ''H''); ');
Script1.SQL.Add(' S = Replace(S, ''JJ'', ''J''); ');
Script1.SQL.Add(' S = Replace(S, ''KK'', ''K''); ');
Script1.SQL.Add(' S = Replace(S, ''LL'', ''L''); ');
Script1.SQL.Add(' S = Replace(S, ''MM'', ''M''); ');
Script1.SQL.Add(' S = Replace(S, ''NN'', ''N''); ');
Script1.SQL.Add(' S = Replace(S, ''PP'', ''P''); ');
Script1.SQL.Add(' S = Replace(S, ''QQ'', ''Q''); ');
[firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
01.05.2016 19:49, trsk...@yahoo.com wrote: > > This is the Query plan from SS FB 3.0 > > PLAN (GET_STCK NATURAL) This is just a different output for select from procedure GET_STCK, v3.0 does not show all its internals as v2.5 did. You'd better compare per table performance counters (indexed/non-indexed reads) after execution. > This could be a big problem, all queries/stored procedures/triggers must > be test again for its performance. This is always required when migrating to the new major version. Dmitry
Re: [firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, > This is the Query plan from SS FB 3.0 > > PLAN (GET_STCK NATURAL) > > and here is the Query plan from SC FB 2.5.2 > > PLAN (G_T3 NATURAL)(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A > INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (TM_D), A INDEX (T2_T1), C INDEX > (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F > INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))(M_TT INDEX > (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B > INDEX (T1_M_DL), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX > (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G > INDEX (RDB$PRIMARY22)))(M_TT INDEX (RDB$PRIMARY2))(A INDEX > (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (T1_M_DL), A INDEX > (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX > (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))SORT > ((G_T3 NATURAL)) > > This could be a big problem, all queries/stored procedures/triggers must > be test again for its performance. Are you sure that you didn't deactivate indexes during the restore? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
[firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, This is the Query plan from SS FB 3.0 PLAN (GET_STCK NATURAL) and here is the Query plan from SC FB 2.5.2 PLAN (G_T3 NATURAL)(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (TM_D), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (T1_M_DL), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (T1_M_DL), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))SORT ((G_T3 NATURAL)) This could be a big problem, all queries/stored procedures/triggers must be test again for its performance. regards, Anto
[firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, About Db cache, found the problem. Now I could set Db Cache per database at 2 GB (16384 x 131072), try to set at 4GB (16384 x 262144), no error on gfix, but it just crashed when tried to connect, no error message shown. Regards, Anto.
Re: [firebird-support] Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, 1. will be good to see this query plans 2. will be good to see exact error message 3. also check your db by gfix –validate –full regards, Karol Bieniaszewski From: mailto:firebird-support@yahoogroups.com Sent: Sunday, May 1, 2016 8:35 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems Hi all, Just migrating from SC FB 2.5.2 to SS FB 3.0 (both in Win x64), i found a few problems : 1) Query that runs about 2-3 secs in SC FB 2.5.2, runs minutes in SS FB 3.0 Query is something like this : Select a.col1,b.col1 from tbl1 a, tbl2 b where a.id = b.id and b.id in (select c.id from tbl3 c) SS FB 3.0 uses different query plan that not use indices, no wonder it was so slow. Changed query to : Select a.col1,b.col1 from tbl1 a, tbl2 b, tbl3 c where a.id = b.id and b.id= c.id Runs around 1-2 secs in SS FB 3.0, but runs minutes in SC FB 2.5.2 2) Since in FB 3.0, SS is able to use SMP & shared Db Cache, I tried to set Db Cache to 512 MB via gfix. It was without error. First connection is OK & I could saw that the actual Db cache size is around 300 MB. But after connection closed & tried to connect again, FB raised an error, something like connection lost. Changed Db Cache to 256 MB fixed th e problem. I think, for modern computer, 256 MB is too small. Regards, Anto.
[firebird-support] Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi all, Just migrating from SC FB 2.5.2 to SS FB 3.0 (both in Win x64), i found a few problems : 1) Query that runs about 2-3 secs in SC FB 2.5.2, runs minutes in SS FB 3.0 Query is something like this : Select a.col1,b.col1 from tbl1 a, tbl2 b where a.id = b.id and b.id in (select c.id from tbl3 c) SS FB 3.0 uses different query plan that not use indices, no wonder it was so slow. Changed query to : Select a.col1,b.col1 from tbl1 a, tbl2 b, tbl3 c where a.id = b.id and b.id= c.id Runs around 1-2 secs in SS FB 3.0, but runs minutes in SC FB 2.5.2 2) Since in FB 3.0, SS is able to use SMP & shared Db Cache, I tried to set Db Cache to 512 MB via gfix. It was without error. First connection is OK & I could saw that the actual Db cache size is around 300 MB. But after connection closed & tried to connect again, FB raised an error, something like connection lost. Changed Db Cache to 256 MB fixed the problem. I think, for modern computer, 256 MB is too small. Regards, Anto.
