INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Update Table

Update Table

(OP)
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

RE: Update Table

Look at triggers.

RE: Update Table

(OP)
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

RE: Update Table

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.

RE: Update Table

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

RE: Update Table

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close