Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Out Of memory using transaction [DNET209] #217

Closed
firebird-automations opened this issue Feb 12, 2009 · 13 comments
Closed

Out Of memory using transaction [DNET209] #217

firebird-automations opened this issue Feb 12, 2009 · 13 comments

Comments

@firebird-automations
Copy link

Submitted by: Nicolas T (nftimmers)

Votes: 1

Hello

I am doing a conversion from a GDB database to a FDB database for a tables, but my database have around 3g, and after a few tests the table having aroung 1 milion register, if i use the transaction i got a out of memory exception, but if i remove the transaction the process go OK, i already tried to put a test and commit after 1000 registers in the Wile, but the problem persists.

Tanks

@firebird-automations
Copy link
Author

Commented by: @cincuranet

The code/test would be helpful.

@firebird-automations
Copy link
Author

Commented by: Nicolas T (nftimmers)

Hello Jiri sorry i was in vacation, you want i send a Example test or can put the code here ??

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Code will be better.

@firebird-automations
Copy link
Author

Commented by: Nicolas T (nftimmers)

        FbConnection GDBconn = new FbConnection\("User=SYSDBA;Password="\+txtGDBSenha\.Text\+";Database="\+txtGdb\.Text\+";Dialect=3;CHARSET=ISO8859\_1"\);
        FbConnection FDBconn = new FbConnection\("User=SYSDBA;Password=" \+ txtFDBSenha\.Text \+ ";Database=" \+ txtFDB\.Text \+ ";Dialect=3;CHARSET=ISO8859\_1"\);

        int contador = 0;

        string resverifica = "";
        string tabelaatual = "";

        try
        \{
            GDBconn\.Open\(\);
            FDBconn\.Open\(\);

            FbCommand GDBcmd = new FbCommand\("", GDBconn\);
            FbCommand FDBcmd = new FbCommand\("", FDBconn\);
            FbCommand FDBcmdexc = new FbCommand\("", FDBconn\);
            FbCommand FDBinsert = new FbCommand\("", FDBconn\);

            FbDataAdapter GDBdap = new FbDataAdapter\(\);
            FbDataAdapter FDBdap = new FbDataAdapter\(\);

            List<string\> colGDB = new List<string\>\(\);
            List<string\> colFDB = new List<string\>\(\);

            DataTable DtListaTabelas = new DataTable\(\);
            GDBcmd\.CommandText = "";
            GDBcmd\.CommandText \+= "select rdb$relation\_name AS NOME ";
            GDBcmd\.CommandText \+= "from rdb$relations ";
            GDBcmd\.CommandText \+= "where rdb$view\_blr is null ";
            GDBcmd\.CommandText \+= "and \(rdb$system\_flag is null or rdb$system\_flag = 0\); ";

            GDBdap\.SelectCommand = GDBcmd;
            GDBdap\.Fill\(DtListaTabelas\);

            txtcriticas\.Text = "";
            if \(cbLimpar\.Checked\)
            \{
                progressBar\.Maximum = DtListaTabelas\.Rows\.Count;
                for \(int exc = 0; exc < 3; exc\+\+\)
                \{
                    for \(int ext = 0; ext < DtListaTabelas\.Rows\.Count; ext\+\+\)
                    \{
                        Application\.DoEvents\(\);
                        progressBar\.Value = ext;
                        try
                        \{
                            FDBcmdexc\.CommandText = "DELETE FROM " \+ DtListaTabelas\.Rows\[ext\]\["NOME"\]\.ToString\(\);
                            FDBcmdexc\.ExecuteNonQuery\(\);
                        \}
                        catch \(Exception ex\)
                        \{
                            txtcriticas\.Text \+= "Exclusao \(" \+ exc \+ "\) " \+ ex\.Message \+ Environment\.NewLine;
                        \}
                    \}
                \}
            \}

            for \(int i = 0; i < DtListaTabelas\.Rows\.Count; i\+\+\)
            \{
                tabelaatual = DtListaTabelas\.Rows\[i\]\[1\]\.ToString\(\);

                resverifica = "";
                Application\.DoEvents\(\);
                progressBar\.Maximum = DtListaTabelas\.Rows\.Count;
                contador\+\+;

                FbTransaction FDBTrans = FDBconn\.BeginTransaction\(\);

                FDBcmd\.Transaction = FDBTrans;
                FDBinsert\.Transaction = FDBTrans;

                try
                \{
                    Application\.DoEvents\(\);
                    DataTable DtGDBres = new DataTable\(\);
                    GDBcmd\.CommandText = "";
                    GDBcmd\.CommandText \+= "SELECT \* FROM " \+ DtListaTabelas\.Rows\[i\]\[1\]\.ToString\(\);
                    GDBdap\.SelectCommand = GDBcmd;
                    GDBdap\.Fill\(DtGDBres\);

                    DataTable DtFDBres = new DataTable\(\);
                    FDBcmd\.CommandText = "";
                    FDBcmd\.CommandText \+= "SELECT \* FROM " \+ DtListaTabelas\.Rows\[i\]\[1\]\.ToString\(\);
                    FDBdap\.SelectCommand = FDBcmd;
                    FDBdap\.Fill\(DtFDBres\);

                    colGDB\.Clear\(\);
                    colFDB\.Clear\(\);

                    for \(int l = 0; l < DtGDBres\.Columns\.Count; l\+\+\)
                    \{
                        colGDB\.Add\(DtGDBres\.Columns\[l\]\.ColumnName\.ToString\(\)\);
                    \}
                    for \(int p = 0; p < DtFDBres\.Columns\.Count; p\+\+\)
                    \{
                        colFDB\.Add\(DtFDBres\.Columns\[p\]\.ColumnName\.ToString\(\)\);
                    \}

                    if \(resverifica == ""\)
                        resverifica = VerificaColunas\(colGDB,colFDB,DtListaTabelas\.Rows\[i\]\["DESCRICAO"\]\.ToString\(\)\);
                    
                    Application\.DoEvents\(\);
                    progressrg\.Maximum = DtGDBres\.Rows\.Count;
                    Application\.DoEvents\(\);

                    if \(resverifica == ""\)
                    \{
                        for \(int j = 0; j < DtGDBres\.Rows\.Count; j\+\+\)
                        \{
                            Application\.DoEvents\(\);
                            progressrg\.Value = j;
                            Application\.DoEvents\(\);

                            FDBinsert\.CommandText = "";
                            FDBinsert\.Parameters\.Clear\(\);

                            FDBinsert\.CommandText \+= " UPDATE OR INSERT INTO " \+ DtListaTabelas\.Rows\[i\]\["DESCRICAO"\]\.ToString\(\);
                            FDBinsert\.CommandText \+= " \(";

                            string camposa = "";
                            int x = 0;

                            for \(x = 0; x < DtGDBres\.Columns\.Count; x\+\+\)
                            \{
                                camposa \+= "," \+ DtGDBres\.Columns\[x\]\.ColumnName\.ToString\(\);
                            \}
                            camposa = camposa\.Substring\(1, camposa\.Length \- 1\);
                            FDBinsert\.CommandText \+= camposa;
                            FDBinsert\.CommandText \+= " \)";
                            FDBinsert\.CommandText \+= " VALUES \(";

                            camposa = "";
                            for \(x = 0; x < DtGDBres\.Columns\.Count; x\+\+\)
                            \{
                                camposa \+= ",@" \+ DtGDBres\.Columns\[x\]\.ColumnName\.ToString\(\);
                            \}
                            camposa = camposa\.Substring\(1, camposa\.Length \- 1\);
                            FDBinsert\.CommandText \+= camposa;
                            FDBinsert\.CommandText \+= " \)";

                            for \(x = 0; x < DtGDBres\.Columns\.Count; x\+\+\)
                            \{
                                FDBinsert\.Parameters\.Add\("@" \+ DtGDBres\.Columns\[x\]\.ColumnName\.ToString\(\),
                                                         DtGDBres\.Columns\[x\]\.DataType\)\.Value = DtGDBres\.Rows\[j\]\[DtGDBres\.Columns\[x\]\.ColumnName\.ToString\(\)\];
                            \}

                            FDBinsert\.ExecuteNonQuery\(\);
                        \}
                    \}
                    else
                    \{
                        throw new Exception\(resverifica \+ " Tabela = " \+ tabelaatual\);
                    \}

                    FDBTrans\.Commit\(\);
                \}
                catch \(Exception ex\)
                \{
                    FDBTrans\.Rollback\(\);
                    txtcriticas\.Text \+= ex\.Message\.ToString\(\) \+ " Tabela =  "\+ tabelaatual \+ " " \+ Environment\.NewLine;
                \}
                Application\.DoEvents\(\);
                progressBar\.Value = contador;
            \}
        \}
        catch \(Exception ex\)
        \{
            MessageBox\.Show\(ex\.Message\.ToString\(\)\);
        \}
        finally
        \{
            GDBconn\.Close\(\);
            FDBconn\.Close\(\);
        \}

@firebird-automations
Copy link
Author

Modified by: Nicolas T (nftimmers)

priority: Major [ 3 ] => Critical [ 2 ]

@firebird-automations
Copy link
Author

Commented by: @cincuranet

With this code, I'm not able to run it (not taking into account need to prepare it). I.e. the line tabelaatual = DtListaTabelas.Rows[i][1].ToString(); is wrong.

Post a simple code, that's ready to be run (preferably console app) and shows the error.

@firebird-automations
Copy link
Author

Commented by: @cincuranet

And BTW did you tried 2.5 FirebirdClient?

@firebird-automations
Copy link
Author

Commented by: A. Murat Ozdemiray (ozdemiray)

I have a similar problem. I use Firebird server 2.1.1, and .NET Provider 2.5. When I start a transaction and delete some records in that transaction, I observe that Firebird server memory usage increases. even though I commit that transaction, the memory usage does not decrease. Whenever I close the connection, the memory usage decreases to previous state.

In http://www.firebirdfaq.org/faq118/ says that "If you are programming the Firebird C API directly, check your code. Otherwise, it might be a bug in the connectivity library you use.", so I thought it is a problem of the driver.

@firebird-automations
Copy link
Author

Commented by: @cincuranet

Without a working test case it's useless to even comment. To fix it, I need to see the problem on some exmple.

@firebird-automations
Copy link
Author

Commented by: Nicolas T (nftimmers)

Hello everyone

sorry for comment late

I Found the problem and is thar .net framework dont alow you to databing a datatable with 1 milion registrys

but what i saw was the using of a transaction makes the out of memory hapens early.

Tanks !

@firebird-automations
Copy link
Author

Commented by: @cincuranet

I just fixed DNET274, with similar behavior, probably. Maybe this is fixed. Anyway, until I got working test case, I'm closing it as incomplete.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => Closed [ 6 ]

resolution: Incomplete [ 4 ]

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Component: http://ASP.NET Providers [ 10090 ] =>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants