That is a huge improvement! The execution time is lower than 10 seconds now - that's amazing.
For clarification, when I set up the index all I did was give it a name and tell it which column to index, then pressed OK. Nothing more to it? Do I need to do anything to tell the db to create the index, or does it just do that when I name it? That was really quite helpful, thanks again for your help. > > The delay is in the Sort/Distinct Sort step. The reason for that > step is that you have specified a DISTINCT in your query. If you > don't need that DISTINCT, remove it :-) > If you do need that DISTINCT, we are going to put it in another > place. Duplicate rows can only be the result of an inner join if > one or both of the source tables of that join have duplicate > rows. So if we move that DISTINCT to the table with the duplicate > rows and filter them out there, we don't have to do the distinct > later when the number of records has multiplied. > > So suppose the duplicate rows originate from the ordr table, try > the code below (adjust if the duplicates come from another table): > SELECT DISTINCT > t.tkt_code, > t.tkt_date, > t.truck_code, > t.po, > t.truck_net_wgt, > t.hler_code, > o.proj_code, > o.cust_job_num, > o.cust_code, > o.order_code, > k.curr_driv_empl_code, > l.prod_descr > FROM > tick t > INNER JOIN > (SELECT DISTINCT > proj_code, > cust_job_num, > cust_code, > order_code > FROM ordr > ) o > ON t.order_code = o.order_code INNER JOIN > truc k > ON t.truck_code = k.truck_code INNER JOIN > ordl l > ON t.order_code = l.order_code > WHERE (t.tkt_date BETWEEN '#arguments.start_date#' AND > '#arguments.end_date#') > > BTW, this was the reason for the question "How many of these rows > are relevant for the final result?". Obviously you can remove > rows from your tables without affecting the outcome of this > query. How many rows can you NOT remove without changing the > outcome? (Don't do that, strictly an exercise of the mind.) > > The other thing I noticed was that you don't have indexes on the > order_code field in the tick, ordr and ordl tables. Since you > join on this field, having it indexed might help by itself, and > if you get a merge join the outcome will be pre-ordered reducing > the work for the sort step. > > Jochem > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189038 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
