Help with heavy query

[Hector Tino]

Hi !
I have a big problem with a fairly heavy query that gives me the availability of a hospital to make an appointment from the internet.
By itself takes a second for a specific date , the problem comes when I have to call 365 times to get the availability of a whole year , day by day . doctors showing voids of the year (this is a requirement ) .

I tried putting it in a loop that goes by queries like the one below and even creating a huge consultation with several joining together but it takes much and gives the feeling that has been hanging the system. anyone could give me some trick to improve the query ? is as follows :


SELECT count(habitaciones.tipoHabitacion) as total
FROM habitaciones
WHERE habitaciones.idhospital= '1' AND habitaciones.tipoHabitacion = '127'
AND habitaciones.online = '1'
AND habitaciones.idHabitacion NOT IN
(SELECT bloqueoHabitaciones.idHabitacion
FROM bloqueoHabitaciones WHERE bloqueoHabitaciones.idhospital = '1'
AND NOT (DATE(bloqueoHabitaciones.fechaInicial) >= DATE('" + fechaFin + "')
OR DATE(bloqueoHabitaciones.fechaFinal) <= DATE('" + fechaInicio + "')))
AND habitaciones.idHabitacion NOT IN
(SELECT reservas.idHabitacion FROM reservas WHERE reservas.idhospital = '1'
AND reservas.estado !='Eliminada' AND reservas.estado !='No Show' AND reservas.estado !='Reserva cancelada'
AND not (DATE(reservas.fechaEntrada) >= DATE('" + fechaFin + "')
OR DATE(reservas.fechaSalida) <= DATE('" + fechaInicio + "')))


Thank you very much in advance for your help!!