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
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
Talk To Other Members
RE: Possibilities for Performance Gain
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)