problems

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
heme

problems

Post by heme » Thu 10 Feb 2005 19:59

Hi

I have your new PostgreSQLDirect .NET driver. (pgsqlnet200std.msi)

my postgre-server is connected to my pc with a 10Mbit ethernet LAN.

i use VB .NET 2003

I will load data from my big table.
the table has 600000 records.
i have test it with MS access (with the original ODBC-driver).
after the open of the table access need 3minutes to load the table.
a test with DAO 3.6 gives the same time.
with the EMS postgreSQL manager the data will come after 1:30 minutes.

now i test it with your driver.

after the command DA.Fill(DS) the cpu goes to 20% and the network works with 50-80% for 2 minutes
and then the network goes to 0%, and the cpu goes to 95%.
the ram storage allocation goes to 900MByte and there comes no result.
after 6minutes the cpu goes to 0% and there is still no result.
after 10 minutes i break the programm.

with your example DATASET there is the same result.


can you repair that?

Code: Select all

CREATE TABLE "public"."t_benutzerstammdaten_voll" (
  "bes_id" SERIAL, 
  "bes_vorname" VARCHAR(20) NOT NULL, 
  "bes_nachname" VARCHAR(20) NOT NULL, 
  "bes_strasse" VARCHAR(20), 
  "bes_plz" VARCHAR(8), 
  "bes_ort" VARCHAR(20), 
  "bes_land" VARCHAR(20), 
  "bes_telefon" VARCHAR(20), 
  "bes_fax" VARCHAR(20), 
  "bes_mobil" VARCHAR(20), 
  "bes_email" VARCHAR(30), 
  "bes_benutzername" VARCHAR(20) NOT NULL, 
  "bes_gesperrt" BOOLEAN DEFAULT false NOT NULL, 
  "aend_datum" TIMESTAMP(0) WITHOUT TIME ZONE, 
  "aend_user" VARCHAR(20), 
  "bes_r_buchhalter" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_ergebnismanager" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_franchisenehmer" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_quotenmanager" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_geschaeftsleitung" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_livespielerfiliale" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_spielerfiliale" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_spielerterminal" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_superadmin" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_wartung" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_wettangebotmanager" BOOLEAN DEFAULT false NOT NULL, 
  "bes_r_wirt" BOOLEAN DEFAULT false NOT NULL, 
  "bes_lan_id" INTEGER, 
  CONSTRAINT "ivoll_bes_pkey" PRIMARY KEY("bes_id")
) WITH OIDS;
this is the VB-Code:

Code: Select all

Option Explicit On 
Imports System
Imports System.Data
Imports CoreLab.PostgreSql

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Vom Windows Form Designer generierter Code "

    Public Sub New()
        MyBase.New()

        ' Dieser Aufruf ist für den Windows Form-Designer erforderlich.
        InitializeComponent()

        ' Initialisierungen nach dem Aufruf InitializeComponent() hinzufügen

    End Sub

    ' Die Form überschreibt den Löschvorgang der Basisklasse, um Komponenten zu bereinigen.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    ' Für Windows Form-Designer erforderlich
    Private components As System.ComponentModel.IContainer

    'HINWEIS: Die folgende Prozedur ist für den Windows Form-Designer erforderlich
    'Sie kann mit dem Windows Form-Designer modifiziert werden.
    'Verwenden Sie nicht den Code-Editor zur Bearbeitung.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents Panel1 As System.Windows.Forms.Panel
    Friend WithEvents btnConnect As System.Windows.Forms.Button
     Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.Panel1 = New System.Windows.Forms.Panel
        Me.btnConnect = New System.Windows.Forms.Button
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.Panel1.SuspendLayout()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(0, 40)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(672, 381)
        Me.DataGrid1.TabIndex = 1
        '
        'Panel1
        '
        Me.Panel1.Controls.Add(Me.btnConnect)
        Me.Panel1.Dock = System.Windows.Forms.DockStyle.Top
        Me.Panel1.Location = New System.Drawing.Point(0, 0)
        Me.Panel1.Name = "Panel1"
        Me.Panel1.Size = New System.Drawing.Size(672, 40)
        Me.Panel1.TabIndex = 2
        '
        'btnConnect
        '
        Me.btnConnect.Location = New System.Drawing.Point(16, 4)
        Me.btnConnect.Name = "btnConnect"
        Me.btnConnect.Size = New System.Drawing.Size(128, 32)
        Me.btnConnect.TabIndex = 1
        Me.btnConnect.Text = "Connect"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(672, 421)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.Panel1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.Panel1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Friend WithEvents g_Conn As New CoreLab.PostgreSql.PgSqlConnection

    Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
        Cursor.Current = Cursors.WaitCursor
        Dim DS As New DataSet
        Dim TB As DataTable
        Dim DA As New PgSqlDataAdapter
        Dim cmd As PgSqlCommand

        g_Conn.Host = "10.6.12.1"
        g_Conn.Database = "test"
        g_Conn.Name = "PostGreVerbindung"
        g_Conn.ConnectionTimeout = 8
        g_Conn.UserId = "PostGre"
        g_Conn.Password = "xxx"

        g_Conn.Open()

        Dim SQL As String = "SELECT * FROM T_Benutzerstammdaten_voll"

        cmd = New PgSqlCommand(SQL, g_Conn)
        DA.SelectCommand = cmd
        DA.Fill(DS)
        cmd.Dispose()
        TB = DS.Tables(0)
        DataGrid1.DataSource = TB
        g_Conn.Close()
        Cursor.Current = Cursors.Default
    End Sub

End Class

Yuri
Posts: 140
Joined: Mon 08 Nov 2004 12:07

Post by Yuri » Fri 11 Feb 2005 12:51

Thank you for your sample.
We got your request and now examine the problem. As soon as we solve the problem, we'll let you know.

Yuri
Posts: 140
Joined: Mon 08 Nov 2004 12:07

Post by Yuri » Tue 22 Feb 2005 08:05

We tested on performance Fill for PostgreSQLDirect .NET, ODBC and Npgsql at the table t_benutzerstammdaten_voll with 600000. At the result of testing we got that on operation Fill ODBC and Npgsql drivers works quite longer than PostgreSQLDirect .NET.
Computer parameters:
* Microsoft Windows XP SP2
* Athlon 1.68 MHz
* RAM 512;
* Page File 768
* 100 Mbit Ethernet LAN
Test:

Code: Select all

 
    string pgDirectConnectionString = "host=192.168.0.110;database=testdb;user=postgres;password=postgres";
    string odbcConnectionString = "Protocol=6.4;LFConversion=1;Ksqo=1;UnknownSizes=0;Debug=0;" + 
           "UseServerSidePrepare=0;Parse=0;SERVER=192.168.0.110;ByteaAsLongVarBinary=0;" +
           "TextAsLongVarchar=1;DRIVER=PostgreSQL;ReadOnly=0;FakeOidIndex=0;ConnSettings=;" +
           "MaxLongVarcharSize=8190;MaxVarcharSize=254;Socket=4096;TrueIsMinus1=0;DisallowPremature=0;" +
           "DATABASE=testdb;Optimizer=1;UID=postgres;ShowOidColumn=0;UseDeclareFetch=0;" +
           "CancelAsFreeStmt=0;PWD=postgres;BI=0;CommLog=0;Fetch=100;UpdatableCursors=0;" +
           "ExtraSysTablePrefixes=dd_;UnknownsAsLongVarchar=0;RowVersioning=0;PORT=5432;BoolsAsChar=1;" +
           "ShowSystemTables=0";
    string npgConnectionString = "host=192.168.0.110;database=testdb;user=postgres;password=postgres";

    private TimeSpan Test(int provider, int rowCount) {

      IDbDataAdapter da = null;
      DataSet dataSet = new DataSet();
      string query = "SELECT * FROM t_benutzerstammdaten_voll limit " + rowCount;

      switch (provider) {
        case 0:
          da = new NpgsqlDataAdapter(query, npgConnectionString);
          break;
        case 1:
          da = new OdbcDataAdapter(query, odbcConnectionString);
          break;
        default:
          da = new PgSqlDataAdapter(query, pgDirectConnectionString);
          break;
      }
      da.SelectCommand.Connection.Open();

      DateTime startTime = DateTime.Now;
      da.Fill(dataSet);

      return DateTime.Now - startTime;
    }

Post Reply