×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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.

Students Click Here

Possibilities for Performance Gain

Possibilities for Performance Gain

Possibilities for Performance Gain

(OP)
Howdy,

I have what should be an easy query to execute but alas, it takes 30-40 seconds to actually run; lacking good analysis tools I'm having to make some guesses about where the fallbacks will be:


Guess 1:
One table has three fields (well a lot more, but only 3 that I am concerned with:
NumberA, NumberB, DateC --- all three of these are numeric and the format of data in DateC is YYYYMMDD (why they don't use a date field, I don't know but I suspect it is 20 years of legacy cobol code); there is Index_1 on NumberA, Index_2 on NumberB and a 3rd Index on DateC with no name where Duplicate and Modifiable are both greyed out;  does this indicate that there is a problem with the index?   I am using this table to join to another table where I query for a range of DateC and NumberB and then join with NumberA with another table in a classic INNER JOIN (actually written out as an innterjoin not an implied INNER JOIN).  
--- so 1) how do I tell if the index is working
--- so 2) how do I tell if the index is actually being used
--- would this be better rewritten as a correlated sub query (although those usually result in slower results especially for potentially large data)

Guess 2:
I have two LEFT OUTER JOINS to the same tabled aliased as E1 and E2 which go back to a table containing employee data so that I can resolve the name.  The source table has fields Emp1 and Emp2 which <should> be foreign key references back to the employee table (and I need to return names not numbers with the query).  I'm using RTRIM() on data returned and combining two fields together but RTRIM() and concatination should be scalars or constants and not actually affect the runtime;  If I remove the employee data, the query does not actually run in any better time in returning results to me; the employee table is not massive and maybe has 500 rows;  for my purposes in my application I would much prefer if this were implemented at the schema level with a true many to many relationship but again, going back with 20 years of legacy cobol schemas and the applicaiton that writes to this is still in cobol.  I've been through testing (i.e. my stop watch) to eliminate this as the cause of the query slowness)

My big table, which has most of the info DOES have indexes defined and I should be making use of them, that shouldn't be a big problem there if they are being used but thats hard to tell.

What does pervasive do or what is it capable of in terms of storing a query plan for better performance if I make this into a stored procedure; it would in theory be pretty simple 5 data values passed in for the 5 possible data variations in my query (start date, end date, start batch, end batch, location number) where there are indexes on all the dates (which are stored as numerics in the format above) and location is stored as integer.

As far as timing, within sql control center executing the query takes about 45 seconds for a moderate amount of data (not running this for a high volume data set yet), there is very very very little network throughput, actually, the network indicator registers no traffic to/from the database server until almost 40 seconds into the query during which time the blue bar is flashing across my screen at me.  I have similar results using MS Query through excel;  ultimately I need to run this query through a webpage (i'm working on querying it rhough the .NET psql data provider now)

thoughts?

MarkAtLMFJ

RE: Possibilities for Performance Gain

(OP)
Oh yes, before I forget:

1)  Pervasive 9.5 client & server
2)  Database box is dual xeons with 4gb of RAM with RAID-5 on the database volume and RAID-1 on the OS volume with physically separate discs (obvivously);  The box has the capability to be multi-homed but is not, using one connection to my GB switch.
3)  So far I'm working off my desktop which has 1.5gb of RAM with a 2.4ghz P4 and going into the same GB switch that the server is plugged into (which pretty much rules out network latency);  I've checked the switch tables to ensure that there are not bad entries which may be rerouting traffic to the lower network switches (which if I had I would have latency problems with all my servers)

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! Already a Member? Login

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