Paul Malan wrote: > > You're right, it is still too long. And it turns out it's taking > about 15 seconds on average, even slower than I thought. I updated > the execution plan scrshot and the indexes as well. (I did add the > index to all three applicable tables.) The link again is > http:www.redmondinc.com/sos/index.html
The costliest step is still the final aggregation in order to perform the DISTINCT. I'm afraid I can't see enough details in the graphical execution plans to see what is really going on there. (Which join type is hidden behind the popup?) > I had never seen your technique of using what looks like a subquery in > the join segment of a query - I'm guessing further optimization is > available using another one of those? If that actually eliminates rows early in the process it can help a lot. Can you give me the recordcount of the following queries: SELECT DISTINCT proj_code, cust_job_num, cust_code, order_code FROM ordr; SELECT DISTINCT tkt_code, tkt_date, truck_code, po, truck_net_wgt, hler_code, order_code FROM tick; SELECT DISTINCT prod_descr, order_code FROM ordl; (We don't need the same query for the truc table since truc.truck_code is the primary key and hence by definition unique.) Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189058 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
