I'm managing a query against a providex database that people use within MAS 90. The query has three tables became a member of together, and it has been slow although not unbearably, taking 8 minutes per run. The query includes a fair quantity of conditions within the where clause:

I am likely to omit the choose area of the query since it's lengthy and straightforward, just a listing of fields in the three tables that were designed within the results.

However the tables and also the where clauses within the 8 minute run time version are:

(The very first parameter may be the lower bound from the user-selected time frame, the second reason is top of the bound.)

FROM  "AR_InvoiceHistoryDetail" "AR_InvoiceHistoryDetail", 
"AR_InvoiceHistoryHeader" "AR_InvoiceHistoryHeader", "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 works out that another date area within the same table must be the one which the Time Frame is in comparison with. And So I transformed an order Dates in the finish from the WHERE clause to InvoiceDate. I've not had the query run effectively whatsoever yet. And I have anxiously waited over 40 minutes. I've no treatments for indexing since this is a MAS 90 database that we don't think I'm able to directly alter the database qualities of.

What might cause this type of large (a minimum of 5 fold) difference in performance. One thing OrderDate may have been indexed while InvoiceDate wasn't? I've attempted BETWEEN clauses however it does not appear to operate within the providex dialect. I'm while using ODBC interface through .Internet during my custom report engine. I've been debugging the report which is running in the database execution point after i requested Versus to interrupt All, in the same place in which the 8 minute report was waiting, so it's probably either something during my query or something like that within the database that's messed up.

If it is only the situation that InvoiceDates aren't indexed, what else can one do within the providex dialect of SQL to optimize the performance of those queries? Must I alter the order of my criteria? This report will get recent results for a particular sales rep and that's why the SMC clause is available. The last clauses are for that inner joins, and also the last clause is perfect for the time frame.

I made use of the same time frame both in the OrderDate and InvoiceDate versions and also have went all of them mulitiple occasions and also got exactly the same results.

I have not used at all providex before.

Searching switched up this reference article around the syntax for creating a catalog.

Overlooking your query, there's three tables and five criteria. A couple of the factors are "join criteria", and three criteria are blocking criteria:

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

I'm not sure how good SalespersonNo is perfect for restricting return results, but it may be good to include a catalog on that.

I still have no idea precisely why it had been so slow, but we'd one other issue using the results from the query (we switched to using OrderDate). We were not getting a few of the results due to the character from the IM1 table.

And So I added a Left Outer Join after i determined Providex's syntax for your. And for whatever reason, despite the fact that we have 3 tables, it runs much faster now.

The brand 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 a minimum of I learned a lot of realm of Providex Sql along the way.

I've not used .Internet so my question may show lack of knowledge, however in Access you have to make use of a SQL Pass-Through query to wring any improvements from ProvideX, if several table is involved.