Providex Query Performace Problem

Question

I am running a query against a providex database that we use in MAS 90. The query has three tables joined together, and has been slow but not unbearably, taking about 8 minutes per run. The query has a fair number of conditions in the where clause:

I'm going to omit the select part of the query as its long and simple, just a list of fields from the three tables that are to be used in the results.

But the tables and the where clauses in the 8 minute run time version are:

(The first parameter is the lower bound of the user-selected date range, the second is the upper bound.)

FROM  "AR_InvoiceHistoryDetail" "AR_InvoiceHistoryDetail", 
"AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader", "IM1_InventoryMasterfile" 
"IM1_InventoryMasterfile" 
WHERE "AR_InvoiceHistoryDetail"."InvoiceNo" = "AR_InvoiceHistoryHeader"."InvoiceNo" 
AND "AR_InvoiceHistoryDetail"."ItemCode" = "IM1_InventoryMasterfile"."ItemNumber" 
AND "AR_InvoiceHistoryHeader"."SalespersonNo" = 'SMC' 
AND "AR_InvoiceHistoryHeader"."OrderDate" >= @p_dr 
AND "AR_InvoiceHistoryHeader"."OrderDate" <= @p_d2

However, it turns out that another date field in the same table needs to be the one that the Date Range is compared with. So I changed the Order Dates at the end of the WHERE clause to InvoiceDate. I haven't had the query run successfully at all yet. And I've waited over 40 minutes. I have no control over indexing because this is a MAS 90 database which I don't believe I can directly change the database characteristics of.

What could cause such a large (at least 5 fold) difference in performance. Is it that OrderDate might have been indexed while InvoiceDate was not? I have tried BETWEEN clauses but it doesn't seem to work in the providex dialect. I am using the ODBC interface through .NET in my custom report engine. I have been debugging the report and it is running at the database execution point when I asked VS to Break All, at the same spot where the 8 minute report was waiting, so it is almost certainly either something in my query or something in the database that is screwed up.

If its just the case that InvoiceDates aren't indexed, what else can I do in the providex dialect of SQL to optimize the performance of these queries? Should I change the order of my criteria? This report gets results for a specific salesperson which is why the SMC clause exists. The prior clauses are for the inner joins, and the last clause is for the date range.

I used an identical date range in both the OrderDate and InvoiceDate versions and have ran them all mulitiple times and got the same results.

Answer 1

I've never used providex before.

A search turned up this reference article on the syntax for creating an index.

Looking over your query, there's three tables and five criteria. Two of the criteria are "join criteria", and three criteria are filtering criteria:

AND "AR_InvoiceHistoryHeader"."SalespersonNo" = 'SMC'
AND "AR_InvoiceHistoryHeader"."OrderDate" >= @p_dr
AND "AR_InvoiceHistoryHeader"."OrderDate" <= @p_d2

I don't know how good SalespersonNo is for limiting return results, but it might be good to add an index on that.

Answer 2

I still don't know exactly why it was so slow, but we had another problem with the results coming from the query (we switched back to using OrderDate). We weren't getting some of the results because of the nature of the IM1 table.

So I added a Left Outer Join once I figured out Providex's syntax for that. And for some reason, even though we still have 3 tables, it runs a lot faster now.

The new query criteria are:

FROM  "AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader", 
{OJ "AR_InvoiceHistoryDetail" "AR_InvoiceHistoryDetail" 
LEFT OUTER JOIN "IM1_InventoryMasterfile" "IM1_InventoryMasterfile"
ON "AR_InvoiceHistoryDetail"."ItemCode" = 
"IM1_InventoryMasterfile"."ItemNumber" }
WHERE "AR_InvoiceHistoryDetail"."InvoiceNo" = 
"AR_InvoiceHistoryHeader"."InvoiceNo" AND 
"AR_InvoiceHistoryHeader"."SalespersonNo" = 'SMC' 
AND "AR_InvoiceHistoryHeader"."InvoiceDate" >= ? 
AND "AR_InvoiceHistoryHeader"."InvoiceDate" <= ?

Strange, but at least I learned more of the world of Providex Sql in the process.