1

This has been an on going issue at my place of work. Previously, we had a co-op programming student write us some applications using C# which ran well on his laptop, but once the applications were put to use on different computers, the connection to the pervasive database because painfully slow.

Now I am writing a simple VB.net application which need to query the Pervasive database, and I'm running into the same issue.

here's my code:

 'PSQL variables
        Dim myPsqlConnection As PsqlConnection = New PsqlConnection("ServerName=FILESERVER;ServerDSN=SAGE2")
        Dim queryString As String
        queryString = "SELECT NAME FROM CUSTOMER"
        Dim MyCommand As New PsqlCommand(queryString, myPsqlConnection)
        Dim MyReader As PsqlDataReader
        Dim tempCustname As String


        Try 'open conncetion to Pervasive DB
            myPsqlConnection.Open()
        Catch ex As Exception
            MsgBox("COULD NOT OPEN A CONNECTION TO THE DATABASE" & vbCrLf & ex.Message)
            Exit Sub
        End Try

        Try 'execute Pervasive query
            MyReader = MyCommand.ExecuteReader
        Catch ex As Exception
            MsgBox("QUERY ERROR" & vbCrLf & ex.Message)
            Exit Sub
        End Try
        If MyReader.HasRows = False Then
            MsgBox("NO RESULTS FOUND")
            Exit Sub
        End If
        While (MyReader.Read)
            tempCustname = MyReader("NAME").ToString()
            Customers.Items.Add(tempCustname)
        End While
        MyReader.Close()
        myPsqlConnection.Close()

I stepped through the program and it take about 30 seconds to get past the line where it opens the connection.

As for the specs (I'll do my best):

My computer is quite powerful (16 gb or ram, 6 core AMD processor at 3.0 Ghz per core, windows 7 home 64 bit)

Pervasive.Data.SqlClient Version 3.2

Compiling with Microsoft Visual Basic 2010 Express

There are very few computers on this network (about 7), and I have no such trouble with MS Access databases. Our server is new 2 years ago, and out network has been upgraded to a GB connection. I should note I know very little about databases in general, let alone connecting with VB. I'm not really the best person to be doing this, but I'm still the most qualified person in our small company.

exceptional exception
  • 527
  • 7
  • 26
  • 1
    Have you tried PSQL tools like Function Executor or Pervasive Control Center (PCC). Open files / tables, read records / execute SELECT queries. Do they still have the slow performance? – Trevor Jan 06 '15 at 13:07
  • I'm trying to figure out how to do that right now – exceptional exception Jan 06 '15 at 13:13
  • As near as I can tell, I don't have access to the Database though the Pervasive control center. I can query the Database though Excel via the DSN, and it's almost instant – exceptional exception Jan 06 '15 at 13:22
  • no ideas? Or it is just too Ambiguous? – exceptional exception Jan 06 '15 at 13:54
  • Are you using the same DSN from Excel and the VB program? – mirtheil Jan 06 '15 at 13:54
  • 2
    It's been years since I've worked with pervasive, and never via ADO.NET, but I would think that if you could access it via the Pervasive ADO.NET provider, that means you are making a direct connection (not via OLEDB) and therefore ought to be able to connect to it via the Pervasive Control Center as well. If you can't, then there-in may lay your problem and I would focus on resolving that first before going any further. Figure out how to get a connection using pervasive's tools before you try to figure out how to connect to it via ADO.NET. – Steven Doggart Jan 06 '15 at 14:02
  • @ mirtheil:I am using the same DNS when I connect from Excel @ Steven Doggart: Thanks for the input, but I have no Idea what to do with that. I don't know where to begin fixing the problem with the control center, and I have no idea what ADO or OLEDB is – exceptional exception Jan 06 '15 at 14:06
  • ADO and OLEDB are frameworks in windows which allow interchangeability of many different kinds of data sources, so, for instance, in theory, if you are accessing your data source via ADO, you don't have to care whether your data source is a SQL Server, Oracle, Access (Jet), or even an XML file. As long as you access it via ADO, the code would all be written the same way, regardless of the target data source type. Of course that's just theory. In actuality it's not *quite* that interchangeable. In any case, ADO.NET is the .NET managed library for accessing ADO-compliant data sources. – Steven Doggart Jan 06 '15 at 14:13
  • Are you sure on the DNS? The connection string you are using is "ServerName=FILESERVER;ServerDSN=SAGE2" which is going to access a Pervasive Engine DSN named "SAGE2" on the machine called "FILESERVER". Are you running this all locally or over a network? What happens if you change the connection string Server Name from "FILESERVER" to the actual IP address? What are the properties of the DSN Excel is using? – mirtheil Jan 06 '15 at 14:13
  • ADO.NET essentially supports plug-ins so third-party vendors can provide access to their own databases via .NET's ADO library. These ADO plug-ins are called ADO.NET Providers. `PsqlConnection` is the entry class to the Pervasive provider. So, when you use the `PsqlConnection` class, that means that you are using pervasive's managed .NET provider library to access the pervasive database via ADO. OLEDB is similar, but it is an older technology which is still supported in ADO.NET by using the `OleDbConnection` class. It's not as efficient as ADO, which is more of a direct connection. – Steven Doggart Jan 06 '15 at 14:17
  • OK, by some miracle of random tinkering, I managed to get access to the database through the control center. So now I'm off to Google how to use this ADO thing – exceptional exception Jan 06 '15 at 14:19
  • @mirtheil Ahh, ok, I'm not that familiar with pervasive's connection strings. So does that mean that Pervasive's provider is making an underlying OLEDB connection if you specify a DSN? It kind of sounds that way. – Steven Doggart Jan 06 '15 at 14:19
  • Or, rather, I should say ODBC, not to confuse things even further.... – Steven Doggart Jan 06 '15 at 14:27
  • @Steven, no, the Pervasive ADO.NET provider makes a direct connection to the Pervasive engine. It does not go through ODBC or OLEDB on the client side. The ADO.NET provider, OLEDB connection, and ODBC connection all make the same calls into the engine. The connection string does not need to specify a DSN. It can specify a database name or a DSN name. – mirtheil Jan 06 '15 at 14:37
  • @mirtheil So when you specify a DSN in the connection string, that's a DSN as defined on the server, not on the client? I'm assuming that would be slightly less efficient, right, since it would need to access the DSN via ODBC in order to resolve the actual database name before making the direct connection? Not that I'd expect a 30-second slow down just from that... – Steven Doggart Jan 06 '15 at 14:43
  • I've changed the connection string to Server Name=FILESERVER;Database Name=Dbname;User ID=myid;Password=pass; (generically) and it works, but it's no faster – exceptional exception Jan 06 '15 at 14:45
  • And is it just as slow when you connect via the control center? – Steven Doggart Jan 06 '15 at 14:49
  • No, actually. The control center is virtually instant, where as in VB it still takes about 30 seconds. I only changed the connection string, nothing else. Should I be using a different provider or importing something else? – exceptional exception Jan 06 '15 at 14:53
  • Interesting. No, I don't think you are doing anything wrong (though I would recommend using some `Using` blocks, but that is relatively minor and has nothing to do with performance). There may be some options that you can add to the connection string which could change performance, but I'm not sure what they would be. – Steven Doggart Jan 06 '15 at 15:01
  • Still some unusual behavior. If I don't run it for a minute or so, then it takes a good 30 seconds to connect again. But subsequent connections within a short time frame happen instantly. I don't get this – exceptional exception Jan 06 '15 at 15:14
  • And you don't notice that same behavior with the control center? – Steven Doggart Jan 06 '15 at 15:26
  • oddly, no. It seems perfectly responsive – exceptional exception Jan 06 '15 at 15:30
  • Try turning Connection Pooling and IPv6 off in the connection string. Add the following to your connection string: "EnableIPV6=False;Pooling=False;". Also, is this a Web app or a Windows app? – mirtheil Jan 06 '15 at 15:35
  • It is a windows app. The EnableIPV6=False seems to break the conncetion string, and Pooling=False doesn't seem to make a difference. – exceptional exception Jan 06 '15 at 15:50

1 Answers1

0

I've got it working now. The solution was to first install the database in the pervasive control center. First by selecting "new database" from the main window of the PCC and filling in the database name, location, username and password.

Next I changed the connection from pervasive ADO to OLEDB. My connection string now looks like this:

Dim myPsqlConnection As OleDbConnection = New OleDbConnection("Provider=PervasiveOLEDB;Data Source=DatabaseName;Location=FILESERVER")

Thanks to all everyone for your help!

exceptional exception
  • 527
  • 7
  • 26