I'm running into a problem with the speed of a query (coming out of Crystal Reports), and it appears that it is not using the indexes that I have built on some tables. When I run with two tables, my dataset comes back in a matter of seconds. When I add the third, it takes a long time (hour plus) to return. It appears to me that it is not properly using indexes.
Although I originally thought this was a problem with Crystal Reports, when I tried duplicating this query using MS Access (linked to the tables), it acted the same way.
Any help that you would have would be greatly appreciated.
Here's the specifics:
UD101 table has 166K records. I have an index of Company, ShortChar08, and Date01.
UD101A table has 527K records. I have an index Company and ChildKey1. (Childkey1 contains a job number.)
JobProd has about 338K records. I have an index on Company and Job Number.
When I link the UD101 and UD101A tables together (by primary keys), and run a query with a where clause like 'ud101.Company = "xxx" and ud101.ShortChar08 = "yyy" and ud101.date01 in (date range)': I get a dataset of about 800 records back almost instantly.
When I link in the JobProd table, linking ud101a.Company to JobProd.Company, and ud101a.Childkey1 to JobProd.JobNum (again, both indexed), then run the same query, it takes hours to run.
On a side note, I can run a query like (where ud101a.company = "xxxx" and ud101a.Childkey1 = "yyyy"), or (where JobProd.company = "xxxx" and JobProd.JobNum= "yyyy"), and either of them will return in seconds. That confirms the indices are working and it's not doing a full table scan.
Does anyone have any ideas?
Although I originally thought this was a problem with Crystal Reports, when I tried duplicating this query using MS Access (linked to the tables), it acted the same way.
Any help that you would have would be greatly appreciated.
Here's the specifics:
UD101 table has 166K records. I have an index of Company, ShortChar08, and Date01.
UD101A table has 527K records. I have an index Company and ChildKey1. (Childkey1 contains a job number.)
JobProd has about 338K records. I have an index on Company and Job Number.
When I link the UD101 and UD101A tables together (by primary keys), and run a query with a where clause like 'ud101.Company = "xxx" and ud101.ShortChar08 = "yyy" and ud101.date01 in (date range)': I get a dataset of about 800 records back almost instantly.
When I link in the JobProd table, linking ud101a.Company to JobProd.Company, and ud101a.Childkey1 to JobProd.JobNum (again, both indexed), then run the same query, it takes hours to run.
On a side note, I can run a query like (where ud101a.company = "xxxx" and ud101a.Childkey1 = "yyyy"), or (where JobProd.company = "xxxx" and JobProd.JobNum= "yyyy"), and either of them will return in seconds. That confirms the indices are working and it's not doing a full table scan.
Does anyone have any ideas?