-----Eric van Loon wrote: ----- >Hi *SM-ers! >My TSM Operational Reporter creates a daily report for our Windows >guys. >Part of this report is a SQL query which returns all missed files. >This >is the query: > >select - > actlog.nodename as "Node Name", - > substr(char(actlog.date_time), 1, 16) as "Date/Time", - > substr(actlog.message, 26) as "Message" - >from actlog,nodes - >where - > actlog.nodename=nodes.node_name - >and (actlog.msgno=4005 or actlog.msgno=4007 or actlog.msgno=4018 or >actlog.msgno=4037 or actlog.msgno=4987) - >and (actlog.date_time between '%s' and '%s') - >and (nodes.contact like 'Windows%%') - >order by actlog.nodename > >The problem is that the query is taking more that 3 hours to >complete, >probably because it's using non-indexed database tables. >Does anyone know how to make this query more efficient?
In my experience, sub-queries perform better than joins. With this approach, you would select appropriate fields from the ACTLOG table, and one of the criteria in the WHERE clause would be: nodename in (select node_name from nodes where contact like 'Windows%')
