#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0

    File size enlarge each time procedure is called


    Hi,

    I have a database with around 7MB in file size.
    Inside there is a few procedures and one is giving me a headache.

    Inside of an procedure is a output variable type blob, where a few for-select-do loops are putting some data.
    There is no insert, update or delete in that procedure, just a few for-select-do loops.
    All data are filled in one output variable (type blob) witch usually return around 20KB of data inside of an blob.

    What is the problem ?

    When I call this procedure database enlarge itself for around 80MB per call, again, again and again..... I tried to call this procedure a few times and database became an 1,4GB.

    When I backup it and restore it returns to original size of 7MB.

    Any clue ?

    P.S. I use Firebird 2.5.1.26351 64bit on WIndows 7.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Are you committing your work?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by clivew
    Are you committing your work?
    Yes, I'd tried with commit and rollback but results are the same.

    It is happening if I do it from exe code or if I do it from firebird database administration toll.

    File size become larger before commit or rollback.

    I tried with latest firebird daily build, but result is the same.

    It is strange, because there is no insert, update or delete command inside procedure.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    You would probably need to post your code to get any other meaningful suggestions.

    I can understand the file growing the first time. The data has to go somewhere.
    However, if you are deleting the data between iterations the next iteration should be able to reuse the space unless each new blob is bigger than the prior one and the free space is too fragmented to hold it.

    Clive
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by clivew
    You would probably need to post your code to get any other meaningful suggestions.

    I can understand the file growing the first time. The data has to go somewhere.
    However, if you are deleting the data between iterations the next iteration should be able to reuse the space unless each new blob is bigger than the prior one and the free space is too fragmented to hold it.

    Clive
    Here is the code :
    Code:
    SET TERM ^ ;
    
    create or alter procedure NAPUNI_SVE (
        IN_KOMERCIJALISTA integer not null)
    returns (
        REZULTAT blob sub_type 1 segment size 100 character set ISO8859_2 collate ISO8859_2)
    as
    declare variable A_ARTIKAL varchar(10) character set ISO8859_2 collate ISO8859_2;
    declare variable A_NAZIV varchar(50) character set ISO8859_2 collate ISO8859_2;
    declare variable A_JM integer;
    declare variable A_BREND integer;
    declare variable A_S varchar(1) character set ISO8859_2 collate ISO8859_2;
    declare variable A_E varchar(1) character set ISO8859_2 collate ISO8859_2;
    declare variable A_NL varchar(1) character set ISO8859_2 collate ISO8859_2;
    declare variable A_VRSTA integer;
    declare variable A_GRUPA integer;
    declare variable A_KOMITENT varchar(5) character set ISO8859_2 collate ISO8859_2;
    declare variable A_PIB integer;
    declare variable A_GRAD integer;
    declare variable A_KO_SIF varchar(10) character set ISO8859_2 collate ISO8859_2;
    declare variable A_POSLOVNICA varchar(5) character set ISO8859_2 collate ISO8859_2;
    declare variable A_ADRESA varchar(50) character set ISO8859_2 collate ISO8859_2;
    
    begin
    
     a_s = '|';
     a_e = ':';
     a_nl = ASCII_CHAR(13);
    
     rezultat = '#tables#';
     /* -------------- jm ----------------------------------------------- */
      rezultat = rezultat  || a_nl || 'jm';
    
      for select jm, naziv from JME
       into :a_jm, :a_naziv do
        begin
         rezultat = rezultat || a_nl || :a_jm
                             || a_s || :a_naziv;
        end
    
      rezultat = rezultat || a_e ; 
     /* ----------------- brendovi ------------------------------------- */
      rezultat = rezultat  || a_nl || 'brendovi';
    
      for select brend, naziv from brendovi
       into :a_brend, :a_naziv do
        begin
         rezultat = rezultat || a_nl || :a_brend
                             || a_s || :a_naziv;
        end
    
      rezultat = rezultat || a_e ; 
     /* ------------------ vrste ---------------------------------------*/
      rezultat = rezultat  || a_nl || 'vrste';
    
      for select vrsta, naziv from vrste
       into :a_vrsta, :a_naziv do
        begin
         rezultat = rezultat || a_nl || :a_vrsta
                             || a_s || :a_naziv;
        end
    
      rezultat = rezultat || a_e ; 
     /* -------------------- grupe -------------------------------------- */
      rezultat = rezultat  || a_nl || 'grupe';
    
      for select grupa, naziv from grupe
       into :a_grupa, :a_naziv do
        begin
         rezultat = rezultat || a_nl || :a_grupa
                             || a_s || :a_naziv;
        end
    
      rezultat = rezultat || a_e ; 
     /* --------------------------- artikli ------------------------------*/
     rezultat = rezultat || a_nl || 'artikli';
    
     FOR SELECT artikal from hh_artikli
      where komercijalista = :in_komercijalista
      order by artikal
       into :a_artikal do
        begin
         SELECT naziv, jm, brend, vrsta, grupa  FROM artikli
          where artikal = :a_artikal
          and aktivan = 1 /* za svaki slucaj */
          into :a_naziv, :a_jm, :a_brend, :a_vrsta, :a_grupa;
    
         rezultat = rezultat || a_nl || :a_artikal
                             || a_s || :a_naziv
                             || a_s || :a_jm
                             || a_s || :a_brend
                             || a_s || :a_vrsta
                             || a_s || :a_grupa
                             || a_s || 'INS';
        end
    
        rezultat = rezultat || a_e ; 
    
    
    
      /* -------------------------- gradovi ------------------------------ */
      rezultat = rezultat  || a_nl || 'gradovi';
    
      /*for select grad, naziv from gradovi */
      /* samo gradovi od poslovnica i komitenata */
      for select distinct gradovi.grad, gradovi.naziv from gradovi
           inner join komitenti on (gradovi.grad = komitenti.grad)
           inner join komercijalisti_pos on (komitenti.komitent = komercijalisti_pos.komitent)
           where  komercijalisti_pos.komercijalista = 1
          union
           select distinct gradovi.grad, gradovi.naziv from poslovnice
            inner join gradovi on (poslovnice.grad = gradovi.grad)
            inner join komercijalisti_pos on (poslovnice.komitent = komercijalisti_pos.komitent) and (poslovnice.poslovnica = komercijalisti_pos.poslovnica)
            where komercijalisti_pos.komercijalista = 1
       into :a_grad, :a_naziv do
        begin
         rezultat = rezultat || a_nl || :a_grad
                             || a_s || :a_naziv;
        end
    
      rezultat = rezultat || a_e ; 
      /* ---------------------------- komitenti -------------------------- */
     rezultat = rezultat || a_nl || 'komitenti';
    
     FOR SELECT komitent from hh_komitenti
      where komercijalista = :in_komercijalista
      order by komitent
       into :a_komitent do
        begin
        SELECT naziv, pib, grad, ko_sif  FROM komitenti
          where komitent = :a_komitent
          and aktivan = 1
          into :a_naziv, :a_pib, :a_grad, :a_ko_sif;
    
         if (:a_pib is null) then
          a_pib = 0;
    
         if (:a_grad is null) then
          a_grad = 0;
    
         rezultat = rezultat || a_nl || :a_komitent
                             || a_s || :a_naziv
                             || a_s || :a_pib
                             || a_s || :a_grad
                             || a_s || :a_ko_sif
                             || a_s || 'INS';
        end
    
        rezultat = rezultat || a_e ; 
    
    
    
     /* ---------------------------- poslovnice ------------------------- */
     rezultat = rezultat || a_nl || 'poslovnice';
    
     FOR SELECT komitent,poslovnica from hh_poslovnice
      where komercijalista = :in_komercijalista
      order by komitent,poslovnica
       into :a_komitent, :a_poslovnica do
        begin
        SELECT naziv, adresa, grad, ko_sif  FROM poslovnice
          where komitent = :a_komitent
          and poslovnica = :a_poslovnica
          and aktivan = 1
          into :a_naziv, :a_adresa, :a_grad, :a_ko_sif;
    
         if (:a_adresa is null) then
          a_adresa = ' ';
    
         if (:a_grad is null) then
          a_grad = 0;
    
         rezultat = rezultat || a_nl || :a_komitent
                             || a_s || :a_poslovnica
                             || a_s || :a_naziv
                             || a_s || :a_adresa
                             || a_s || :a_grad
                             || a_s || :a_ko_sif
                             || a_s || 'INS';
        end
    
       rezultat = rezultat || a_e ;
    
     
    end^
    
    SET TERM ; ^
    If it helps, I'd tried to use sweep but result is the same.

    Thx
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Sorry I do not have a solution.
    I am guessing that Firebird does not know when it is safe to discard the data.
    Perhaps it just returns a pointer to the bytes rather than a full copy.
    Manipulating blobs probably involves a lot of hidden storage issues that are affecting your
    situation.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by clivew
    Sorry I do not have a solution.
    I am guessing that Firebird does not know when it is safe to discard the data.
    Perhaps it just returns a pointer to the bytes rather than a full copy.
    Manipulating blobs probably involves a lot of hidden storage issues that are affecting your
    situation.
    Maybe you are write.

    I replaced in procedure variable type from blob to varchar 32000 and everything is ok.
    While it was with blob variable type, procedure was taking about 7 sec to complete, now with varchar it complete under 1 sec and file size stays around 7MB.

    Thx
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    maybe it's bug in Firebird? Why don't you ask about this in Firebird's group to see what they will answer. There is a chance some of Firebird's developers to see the topic and give you more detailed answer is this behavior as designed or it's an undiscovered bug.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    Originally Posted by mIRCata
    maybe it's bug in Firebird? Why don't you ask about this in Firebird's group to see what they will answer. There is a chance some of Firebird's developers to see the topic and give you more detailed answer is this behavior as designed or it's an undiscovered bug.
    Well , I made bug report , on firebird bug tracker (CORE-3951) but no answer till now.

    I posted question on firebird yahoo group [firebird support] , but no answer , yet.

    Do you have any recommendation to post it somewhere else ?

    Thank You
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    No. My next suggestion after Firebird's group was going to be the bug tracker, but you already post it there. Now you can only wait for response.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    6

    Thumbs up


    The question is? Why to use one BLOB field, if one VARCHAR do it the same result?

    Remember: BLOB fields using PAGEs in DB, not as one field common!

    The Firebird can be using COMMITs (hide) by update the content of BLOB, so, your procedure can be writing and make BLOB PAGEs without your knowing!

    Note: "FOR SELECT" need on SUSPEND declaration!

    Look this:

    http://www.firebirdsql.org/refdocs/langrefupd21-psql-forselect.html

    http://www.volny.cz/iprenosil/interbase/ip_ib_strings.htm

    http://www.firebirdsql.org/refdocs/langrefupd21.html

    http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi164.htm

    http://msdn.microsoft.com/en-us/library/0t1k839z(v=vs.80).aspx

    http://docs.oracle.com/javadb/10.3.3.0/ref/rrefblob.html

    http://www.firebirdsql.org/manual/migration-mssql-data-types.html

    http://www.devrace.com/en/fibplus/articles/2161.php
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    6
    Rep Power
    0
    The question is? Why to use one BLOB field, if one VARCHAR do it the same result?
    Well , it is because length of result is more then 32K - varchar limit

    My current solution is using varchar and "if" statement in each for-select-loop, for checking result length more then 30k - if result is more then 30k it make commit in one blob filed in database.

    This way it work perfect, it is 10 times faster then using blob directly and this procedure don't affect to database size.
    I'd tried to use suspend, but procedure returns just one record - without suspend it work just fine.

    Thank you for the answers and links.

IMN logo majestic logo threadwatch logo seochat tools logo