I would like to inquire about a performance question regarding views. I have view that drives a report and the performance is not very good.
I believe all linking columns are indexed properly etc. T1 and T2 have indexed InvoiceNumber columns. T2 and T3 have indexed ItemCode columns The question is "does the table order matter"? The view has 3 tables and I have them in this order: InvoiceHeader ~10,000 rows T1 InvoiceDetail ~100,000 rows T2 SalesCategory ~300 rows. T3 The report has the following break headers: H1 - T1.InvoiceHeadr.CustAcct H2 - T3.SalesCategory.ItemCode H3 - T3.SalesCategory.CatA H4 - T3.SalesCategory.CatB The report sums YTD and MTD sales for each customer by Item Number, "Category A" and "Category B" There is one very large customer (detail records of about 50,000) that the report takes about 2 hours to print! Due to requirements I have in the report, I have passes set to (2) and output to PDF. Other customers with ~3,000 records take only 10-15 seconds to print. Any thoughts or the table order would be appreciated! Thank you, Bob Thompson Thompson Technology Consultants
