Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Posts
    13

    Unanswered: tables/database takes a long time to open

    when i open (select * from mytablename) in pcc its really fast, but when i open it in my software it takes a long time to open.
    i am using vb.net as the front end, with the connection string as

    Dim openString = "Provider=PervasiveOLEDB;Data Source = MY DATASOURCE NAME ; = MY SERVER NAME "
    is there something wrong with my connection string?

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    How much of a difference in time are you talking about?
    When a query is executed in PCC, by default, only the first 40 or so records are read. This is true if you "execute into grid".
    Your connection string should be:
    "Providider=PervasiveOLEBD;Data Source=<Pervasive Database Name>;Location=<PervasiveServerName>"
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Oct 2008
    Posts
    13
    close to one minute or more

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Is that just when opening the connection or actually executing the query? Can you post code that shows where the delay is actually occurring?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Oct 2008
    Posts
    13
    its when executing the query;

    The communication protocols of the server are;

    Enable Auto Reconnect - UNCHECKED
    TCP/IP Multihomed - CHECKED
    Auto Reconnect timeout - 180
    TCP/IP Port - 1583
    Listen IP Address - 0.0.0.0
    Supported protocols;
    SPXII - UNCHECKED
    TCP/IP - CHECKED


    the code is vb.net, and a sample of the code culled from the project is;

    Imports System.Data.OleDb

    Public Class frmCustomer


    **** connection string
    Dim openString = "Provider=PervasiveOLEDB;Data Source= DATABASE NAME"
    Dim conn As New OleDbConnection(openString)

    Private Sub frmCustomer_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


    If conn.State = ConnectionState.Closed Then
    conn.Open()
    End If

    Dim sqlSEL As String = "SELECT Code, staffno, AccountName, adrreess1 FROM CUSTOMERS ORDER by code"

    Dim cmd As New OleDbCommand(sqlSEL, conn)
    Dim da As New OleDbDataAdapter(cmd)

    Dim dtListCustomers As New DataTable

    Try

    dtListCustomers.Clear()
    da.Fill(dtListCustomers)


    _flex.Grid.DataSource = dtListCustomers


    Catch ex As Exception
    MsgBox(Err.Description)
    Finally
    conn.Close()
    End Try

    End Sub

    End Class

    best regards

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    A couple of questions:
    - If you run the query (SELECT Code, staffno, AccountName, adrreess1 FROM CUSTOMERS ORDER by code) in PCC and "Execute into Text"? How long does it take? What if you use other tools like ODBC Test?
    - Does the field "Code" have an index on it?
    - You're saying the slowness happens when executing the "da.Fill(dtListCustomers)"? What happens if you use a DataReader and Execute the QUery using an ExecuteReader?
    - Is there any change if you change the connection string to: "Provider=PervasiveOLEDB;Data Source= DATABASE NAME;Location=locahost"
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Oct 2008
    Posts
    13
    Yes the table has a code has an index field.

    I think using datareader will not have much impact on the speed from other trials i made.

    I will try the ‘;Location=locahost’ in the connection string because that's the only new thing i see.

    But does it make any difference in using:
    "Provider=PervasiveOLEDB;Data Source= DATABASE NAME;Location=locahost"

    And without the localhost;
    "Provider=PervasiveOLEDB;Data Source= DATABASE NAME;Location"

    And how about the settings in the communication protocols?
    Are my settings okay?
    Should the port number be part of the connection string too?

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Quote Originally Posted by nii cs View Post
    Yes the table has a code has an index field.
    What kind of index? What kind of field is code?

    Quote Originally Posted by nii cs View Post
    I think using datareader will not have much impact on the speed from other trials i made.
    Maybe not, but I'm just trying to rule things out.

    Quote Originally Posted by nii cs View Post
    I will try the ‘;Location=locahost’ in the connection string because that's the only new thing i see.

    But does it make any difference in using:
    "Provider=PervasiveOLEDB;Data Source= DATABASE NAME;Location=locahost"

    And without the localhost;
    "Provider=PervasiveOLEDB;Data Source= DATABASE NAME;Location"
    Yes, it does make a difference. I would expect an error if you just have "Location" without specifying a value. The Location parameter specifies the server name. Even if the data is local, sometimes specifying localhost helps.

    Quote Originally Posted by nii cs View Post
    And how about the settings in the communication protocols?
    Are my settings okay?
    Should the port number be part of the connection string too?
    You do not need the port number unless it is not the standard value. I didn't see anything out of the ordinary with the communication settings. That being said, it's not a communication issue. If the slowness occurred on the connection open, it might be communication related. That it happens when executing the query, it indicates something else. Either it's taking longer to read the data off the disk or something else is happening.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  9. #9
    Join Date
    Oct 2008
    Posts
    13
    Lets say my data source = SALES and my server = HPSERVER, how would i structure the connection string with the word 'localhost'.

    Is 'localhost' a contant or a variable in the connection string?

  10. #10
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    If your server name is HPSERVER, then you would have "Location=HPSERVER" in the connection string. 'localhost' is an alias for the local machine.
    Remember, the Location paramater should be set to the machine name where the data and PSQL engine reside.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  11. #11
    Join Date
    Oct 2008
    Posts
    13
    after a series of research i notice that it is a performance tuning issue. after i have done performance tuning especially increasing the size of data for 'Cache Allocation size in MB' the speed of data access has improved tremendously.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •