It currently takes 10 seconds to return 6000 rows what can I change in the sql and not in the table/indexes to improve performace.
SELECT B.origem, D.coddestino, DH.coddestinohotel, B.dataida, B.datavolta, B.codbuscapassagem_lote, (SELECT ISNULL(SUM(ISNULL(valor, 1) * ISNULL(valormoeda, 1)), 0) / 2 AS Expr1 FROM dbo.destinohotel_valores AS DV WHERE (DATA BETWEEN B.dataida AND DATEADD(d, -1, B.datavolta)) AND ( (SELECT COUNT(*) AS Expr1 FROM dbo.destinohotel_valores AS DH2 WHERE (coddestinohotel = DV.coddestinohotel) AND (DATA BETWEEN B.dataida AND DATEADD(d, -1, B.datavolta))) = DATEDIFF(DAY, B.dataida, B.datavolta)) AND (coddestinohotel = DH.coddestinohotel)) AS valorHotel, DATEDIFF(DAY, B.dataida, B.datavolta) AS dias, (B.valor + B.valordu + B.taxas) + B.valor * 0.1 + 75 AS ValorAereo, ISNULL(ISNULL(DHV.regimealimentacao, DHV2.regimealimentacao), '') AS RegimeAlimentacao, ISNULL(ISNULL(DHV.descricaoquarto, DHV2.descricaoquarto), '') AS DescricaoQuarto FROM dbo.destino AS D INNER JOIN dbo.destinohotel AS DH ON D.coddestino = DH.coddestino INNER JOIN dbo.buscapassagem_lote AS B ON B.destino = D.aeroporto LEFT OUTER JOIN dbo.destinohotel_valores AS DHV ON DHV.coddestinohotel = DH.coddestinohotel AND DHV.data = B.dataida LEFT OUTER JOIN dbo.destinohotel_valores AS DHV2 ON DHV2.coddestinohotel = DH.coddestinohotel AND DHV2.data = B.datavolta WHERE (B.datahoracaptura >= DATEADD(d, -2, GETDATE())) GROUP BY B.origem, D.coddestino, DH.coddestinohotel, B.dataida, B.datavolta, B.codbuscapassagem_lote, B.valor, B.valordu, B.taxas, DHV.regimealimentacao, DHV.descricaoquarto, DHV2.regimealimentacao, DHV2.descricaoquarto HAVING ( (SELECT ISNULL(SUM(ISNULL(valor, 1) * ISNULL(valormoeda, 1)), 0) / 2 AS Expr1 FROM dbo.destinohotel_valores AS DV WHERE (DATA BETWEEN B.dataida AND DATEADD(d, -1, B.datavolta)) AND ( (SELECT COUNT(*) AS Expr1 FROM dbo.destinohotel_valores AS DH2 WHERE (coddestinohotel = DV.coddestinohotel) AND (DATA BETWEEN B.dataida AND DATEADD(d, -1, B.datavolta))) = DATEDIFF(DAY, B.dataida, B.datavolta)) AND (coddestinohotel = DH.coddestinohotel)) > 0)