One of my query is performing slow for small records but for large records its working as expected. Foe example:
SELECT * FROM vwServwerHealthReport WHERE startDate >= '20/02/2018' AND startDate <= '06/03/2018'
-- fetching more than 6000 records in 0:02 seconds
SELECT * FROM vwServwerHealthReport WHERE startDate >= '02/03/2018' AND startDate <= '06/03/2018'
-- fetching approx 800 records in 02:05 seconds
I have checked execution plans as well and found that the execution plans of both the queries is different and the slow running query is using parallelism operator a lot but the fast query is not using any parallelism operation.
Initially I was assuming this to be a parameter sniffing problem and implemented "Recompile, Optimize for" hints but have not got the improvement.
Please let me know why parallelism is being used by slow running query even for small records and how can i resolve this issue.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `vwServwerHealthReport` ADD INDEX `vwservwerhealthrep_idx_startdate` (`startDate`);
SELECT * FROM vwServwerHealthReport WHERE vwServwerHealthReport.startDate >= '20/02/2018' AND vwServwerHealthReport.startDate <= '06/03/2018'