Update Table

[Djbell]

Hi All

I have been left in charge of a VB.Net program that pulls data form a Pervasive 9.1 database. The program pulls data from a View in the Database, unfortuantly this view isnt the fastest in the world and it hangs the VB.Net program while it pulls the current data. My thoughts were to create an update table within Pervasive that gets updated everytime data in this view changes My program would then pull the data from the table rather than the view, I am hoping this will speed up the data pull from the program as no queries are being run, just a Select Statement to the table. My problem is, I have never used Pervasive and have no idea how to do this. I dont even know if it is possible to run an update on a table as soon as data has changed.

Any help would be appreciated.

Cheers

Djbell

 

[ettienne]

Look at triggers.

 

[Djbell]

Hi

Thanks for the reply, but as I said I have never used Pervasive before so I really have no clue, a little more explanation into triggers and what they are would be great.

Regards

Djbell

 

[ettienne]

From the Pervasive documentation:
Triggers are actions defined on a table that you can use to enforce consistency rules for a database. They are dictionary objects that identify the appropriate action for the DBMS to perform when a user executes a SQL data modification statement on that table.
To declare a trigger, use the CREATE TRIGGER statement.
CREATE TRIGGER CheckCourseLimit;

The maximum size for a trigger name is 30 characters.
To delete a trigger, use the DROP TRIGGER statement.
DROP TRIGGER CheckCourseLimit;

You cannot invoke a trigger directly; they are invoked as a consequence of an INSERT, UPDATE, or DELETE action on a table with an associated trigger.

 

[mirtheil]

A better option would be to avoid triggers and make sure the query is optimized. Triggers in PSQL can break other things. Once a trigger is created, Btrieve access to that table for the triggered operation is disabled. For example, if you create an Insert trigger, Btrieve inserts will return a status 149 for that table.

A few questions about the VB.NET program:
- How many records does it retrieve?
- Does it need all of them? For example, don't perform a "select * from table" query when you only need one record.
- Is the query using an index (in a WHERE clause or ORDER BY clause)?
- How long does the VB.NET program "hang" while getting the data?
- How long does the query take when executed into text mode in the Pervasive Control Center?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[Djbell]

Hi Guys

Thanks for the replys.

@Mirtheil to answer your questions.

- How many records does it retrieve? Its not many records, its only around 120 records, but it is updated nearly every second with new data (The database is the backend to a Data Collection System, my program takes this data and displays vital information on Display Systems)
- Does it need all of them? For example, don't perform a "select * from table" query when you only need one record. The database selection has been taken down as far as possible, the 120 records are what is required to run the VB Display program.
- Is the query using an index (in a WHERE clause or ORDER BY clause)? Yes, I have been told the indexes are up to date.
- How long does the VB.NET program "hang" while getting the data? The program hangs for about half a second, but when its pulling the records every second then its very noticible. and looks like the program is stuttering.
- How long does the query take when executed into text mode in the Pervasive Control Center? Not sure on this one as I cannot get my view to run in text mode only.

I dont think the problem lies in the PSQL database and query, think its more to do with the speed of the Pervasive ODBC link, always found it a bit clunky and slow. I thought maybe accessing a table instead of a Query would increase performance.

Regards

Djbell