Fábio Prado@mpungan
Eu até gostaria de te ajudar MAIS, mas infelizmente é difícil por questões de tempo e acho que até agora ninguém respondeu por causa disso também!
SQL Tuning não é uma tarefa muito fácil! Em queries como essa é muito demorado analisar, entender e dar sugestões de melhorias. Eu levaria no mínimo 1 dia inteiro para entender o que você está fazendo e o que você precisa fazer para REALMENTE tunar essa query. Vou dar abaixo apenas algumas sugestões simples de coisas que vi rapidamente na query (e que talvez vc já até saiba):
1- Veja se é possível eliminar o DISTINCT;
2- Elimine todas as funções desnecessárias;
3- Substitua os outer joins por inner joins se possível;
4- Tire o ORDER BY do SQL. Fazer ordenação na aplicação normalmente é muito mais rápido;
5- Crie cautelosamente índices nas colunas de filtro e FKs, mas cuidado, os índices melhoram a performance das consultas, mas pioram a performance de INSERT, UPDATE e DELETE.
6- Utilize a cláusula WITH para tabelas que são referenciadas N vezes na instrução SQL.
[]s
Fábio Prado
http://www.fabioprado.net