Performance of _X_Factory.List_X_ByQuery in .NET with PostgresQL

I set up a simple db with one table with only a primary, unique key index using ERD in DB-VA. Using .NET 2.0 and PostgresQL 8.2, I try to insert 1,000,000 records into the table. After 10000 entries, my rate is about 20 inserts per second. This seems extremely slow.

The duration of these events in the PostgresQL log file is 0.000 msec for each event. For each second, there are roughly 20-21 transaction sets (SELECT, SET TRANSACTION … COMMITTED, INSERT, COMMIT), which confirms my measurements.

A manual query into the db using psql for ALL RECORDS (SELECT * from dummy) takes 16 msec, which would be 60 entries per second. And this is to retrieve the entire list of 10,000 entries!

Can someone help me? Is the .NET PostgresQL driver really this slow? Or is this a limitation of DB-VA?

I’ll post more as I find more…

Thanks,
-bdubs

                 System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
                sw.Start();
                while (i < 1000000)
                {
                    Dummy[] dummy_list = DummyFactory.ListDummyByQuery("id=" + i, null);
                    if (dummy_list == null || dummy_list.Length == 0)
                    {
                        Dummy dummy = DummyFactory.CreateDummy();
                        dummy.Id = i;
                        dummy.Save();
                    }

                    if (i % 100 == 0)
                    {
                        sw.Stop();
                        System.Diagnostics.Trace.WriteLine(string.Format("(dummy) Items per Second, count: {0:0.000}, {1}",
                            100.0f * (1000.0f / sw.ElapsedMilliseconds),
                            i));
                        sw.Reset();
                        sw.Start();
                    }
                    i++;
                } 

Hi bdubs,

Thank you for your post. I have forwarded your question to our engineers. I will come back to you as soon as possible.

Best regards,
Jick

Hi bdubs,

Please use session.Evict() to release object in session. See if it helps.

Best regards,
Jick