Pervasive

I have a couple of small apps im writing using VB.net 2008 and PSQL v10 I have two seperate issues with the apps, the first being the first time it access the DB it takes ~10 seconds to complete the transaction. every transaction after this is instant, or close enough to not notice a slowdown in the program. Its not causing huge issues but i would like to be able to fix it.

The second is related (I think) where i get the ODBC Timeout error when I try to access the DB . I am trying to pull out one number from the DB so its not a huge data pull. The correlation between the two is only the command string im using.

1st Problem string = "SELECT TOP 1 testno FROM vacpumps.header ORDER BY testno DESC"

2nd Problem = "Select top 1 * from MONITOR.events WHERE tag_no = " + CStr(tag.TagNo) + " ORDER BY event_no DESC"

 now these databases have been around for a good couple of years, and using the old system im changing from where they were accessed through the ActiveX component in VB6 had no such slowdown issues.

as stated im using the managed provider using psqlcommand to set the command and reader to cycle through the results. I have no such issues when im searching not using the TOP command, and the first command works a treat after the first instance. and for reference the 1st DB is only 100mb or so in size and the 2nd is 3GB

 Just to make sure I understand your problem:

You have two queries in two apps.  Are they accessing the same database or different databases?

The first query (vacpumps.header) takes 10 seconds on the first run but is near instant after that.  Is the testno field an index on the vacpumps.header table?

The second query gets an ODBC timeout.  Is tag_no an indexed field?  What about event_no?  Are you setting a CommandTimeout or ConnectionTimeout?  How long does the query run through the Pervasive Control Center or ODBC Test?   

1) You say that you are pulling just one number, but that is not what the engine has to deal with.  It must be able to take your data set (every record in header), order it by the testno field in descending order, and then select the top record and return that field.  Look at your header table and verify that there is an index defined that STARTS with the testno field.  Even better, use DDF Builder to confirm that the file matches the SQL definitions using the "Check Table" feature.  If there is no index, or if the index definition doesn't match the Btrieve definition, then this system will result in a table-scan -- a process whereby the entire table must be read. 

So why is it fast the second time?  The first time you run it, the entire 100MB file is likely being loaded into cache from the disk.  This takes time.  The second time, it is accessed from RAM -- nice & quick.

2) I suspect a similar issue with the second query, but the WHERE clause makes it more complicated.  An ideal index would be: "CREATE INDEX bestkey ON events(tag_no, event_no DESC)", but this may not exist.  The next best index will be on tag_no as the first field of ANY index, though if there are a lot of duplicate records, then even this will be time-consuming.

They are two separate databases, they are both indexed according to the tags. the first has the testno as its own, and the second has both eventno and tag_no indexed. I thought it would be reading the table , but able to use the indexes to sort through quicker.

Is there a better way for it to search as basically I want the latest entry in the database, is there a better way to word the search so I dont get the holdup to grab one entry?

 Ahh, Chrome is failing with my formatting.

 If you've got an index on the proper field but it doesn't seems to be using that index, you should check the Query Plan.  Use the following to enable and disable it.  Once enabled, run your query  (and any other queries) you want to test.  Then disable it.

  set qryplanoutput = 'c:\qry.qpf';
    <execute your select statement>
     set qryplanoutput = null;

Once you've run the queries use the Query Plan Viewer from the PCC/Tools menu to open and view the plan.

You might also try running the query using ODBC Test (or other ODBC Tool) to test your queries. 

The code you gave me executes. but i dont seem to get a file generated on the c:\

I tried it both in code, which uses the remote DB, and locally in pervasive control centre. it executed locally, but didnt generate the file and through code, it seemed to execute without error but the reader(in vb.net) gave me the following.

"In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user."

 using the following string.

set qryplanoutput = 'c:\qry.qpf';

SELECT TOP 1 testno FROM vacpumps.header ORDER BY testno DESC;

set qryplanoutput = null;

and for extra info on this one, I had the size wrong, the header table is only 6mb in size.

Ok I got it working in PCC, by setting the qyplan=on and setting the path then executing the command after.

 I get the following ,

Root Query - FCalc ->Cnt -> Ordered Temp Table -> header[t1]

1st sub query = subquery -> Ordered Temp Table -> header[t1]

and thats it.

Not sure how to do it via the managed provider in vb.net yet, havent used the second DB as its 3gb on the remote server and i cant use PCC over our network

 I did a couple of tests with DEMODATA (since I don't have your database).  I ran the following statements:

set qryplanoutput = 'c:\qry.qpf';
SELECT TOP 1 section FROM class ORDER BY section DESC;
SELECT TOP 1 name FROM class ORDER BY name DESC;
set qryplanoutput = null;

The field Section does not have an index while name does.  I ran the queries using ODBC Test (rather than PCC so I only had to deal with the original query and not the count query PCC runs).  When I ran the query using "section", I see a query plan almost identical to yours with the "Ordered Temp Table".  When I ran the one using "name", I see "Class (Class_Name *)" indicating it is using the index Class_Name. 

Did you run the Check Database in DDF Builder that BtrieveBill suggested? 

Ran all the DB's through the DDF Builder and it says they all passed.

 Could it be the Managed provider in VB.NET is not using the index's ? It lists the keys in the server explorer, but i am not using that for this and from what you are saying, it isnt using them in the searches I am doing, even though the fields are indexed

 is there a way to force it to use a specific key in its search?

 Have you tried other interfaces (PCC, ODBC Test) to run your queries?  Do they have the same behavior? I haven't seen any problems like this that are specific to one interface (ADO.NET for example). 

What does your code look like in VB.NET?  

What happens if you change your VB.NET to run the query I posted against DEMODATA?  Does it show the same behavior?  

When I run it in PCC I get an instant result. its only when I use vb that im getting the delay.

the code is basically as follows.

Dim dbCon As New Pervasive.Data.SqlClient.PsqlConnection(DBVacPumps)

dbCommand.CommandText = "SELECT TOP 1 testno FROM vacpumps.header ORDER BY testno DESC"

   While dbReader.Read

   End While

End Using

dbcon.close()

Thats pretty much it. Happy to take any advice as still fairly new to the .NET world.

Dont have the DEMODATA DB on the server so I cant test the other.

Unless the DEMODATA DB was removed, it should be there.  It's part of every PSQL install.

What does your connection string look like?  I don't see anything out of the ordinary with your code.  I'll try to test your code to see if I see any strange behavior. 

What version of the Managed Provider (Pervasive.Data.SqlClient.dll) are you referencing in your code?

Have you added any timings to your code to see where the slow down is?  Is it on the .Open, the  .ExecuteReader, the .Read or a combination of all of those? 

The slowdown occurs during the .executereader 

It happens during debugging so its easy to isolate.

 I put your code into a project, changed the connection string to my server, changed the SQL statement to DEMODATA since I don't have your data, and ran it. 

I found a consistent 3 second time for the .Open to occur.  Every thing else happened very quickly.  I did not see any difference between a first run and subsequent runs. 

I was not using 3.0 however.  I only have PSQL v11 so I was using the 3.2 provider.  

A couple other questions.  Is the server (the Host in the connection string) on the same LAN or is it at another site?   If I sent you the DEMODATA database, would you be able to test with it?

The server is on the same LAN, physically only 50m or so away from me.

I have the DEMODATA set up now on the server so if you want me to do any testing im more than happy to.

Yeah we only have psql v 10 here, and they wont upgrade which is annoying and limits me to vb.net 2008.