title: Providex Query Performance
body: <p>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:</p>
  
  <p>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.</p>
  
  <p>But the tables and the where clauses in the 8 minute run time version are:</p>
  
  <p>(The first parameter is the lower bound of the user-selected date range, the second is the upper bound.)</p>
  
  <pre><code>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" &gt;= @p_dr 
  AND "AR_InvoiceHistoryHeader"."OrderDate" &lt;= @p_d2
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>