Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Question Unanswered: problems with slow sql

    hi.

    I'm having some trouble getting my pavasive sql to work fast.

    beyond that, i have a running project where my db server uses all the CPU,
    and I have found that it must be of the same reason.

    I have made an wary simple code to check the speed, up against a mssql database.
    I'm using ADO.NET 3.0.
    Code:
            using (PsqlConnection connection = CreateConnection())
            {
                using (PsqlCommand command = CreateCommand())
                {
                    command.Connection.Open();
                    DateTime now = DateTime.Now;
                    for (int i = 0; i < 10000; i++)
                    {
                        command.ExecuteReader().Close();
                    }
                    sb.AppendLine("pervasive   : " + (now - DateTime.Now).ToString());
                    command.Connection.Close();
                }
            }
            using (SqlConnection connection = CreateConnection1())
            {
                using (SqlCommand command = CreateCommand1())
                {
                    command.Connection.Open();
                    DateTime now = DateTime.Now;
                    for (int i = 0; i < 10000; i++)
                    {
                        command.ExecuteReader().Close();
                    }
                    sb.AppendLine("mssql   : " + (now - DateTime.Now).ToString());
                    command.Connection.Close();
                }
            }

    the sql statement id this : "SELECT * FROM PICTURE WHERE PICTUREID = 1234"

    Test result:
    the outcome og this test is:
    parvasive : 00:01:06
    mssql : 00:00:01


    And now I'm thinking what is wrong here, I hope som one can help me.
    Last edited by callekamel; 05-06-09 at 09:09.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    A few questions:
    - What version of PSQL are you using?
    - What version of Pervasive.Data.SqlClient.DLL are you using?
    - Is PICTUREID an index?
    - What data type is PICTUREID?
    - What are you cache settings?
    Just to be clear, it's taking one minute to execute 10,000 iterations of the query. Is that correct?
    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
    Feb 2009
    Posts
    4
    psql version is 10.13
    Pervasive.Data.SqlClient.DLL 3.0
    pictureid is not an index, it is an column, but i have a index on this column named picture002.
    pictureid is a Decimal.

    - What are you cache settings? i don't know

    and yes to the last question.

Posting Permissions

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