Categories
Database Development

Percentile SQL takes too long – need help optimizing

I have percentile query and I use its result for further calculations to generate response for frontend. However, it take about 23s for this query to finish:

SELECT distinct PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ProbabAl) OVER (PARTITION BY [dbo].[Table].[ClientId]) as PercentileX,
            PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ValueLeft) OVER (PARTITION BY [dbo].[Table].[ClientId]) as PercentileY
        FROM [dbo].[Table]   WHERE  Table.[ClientId] = '2'

I also tried using top (1) instead of distinct, that didn’t really help either. The problem is I need these values on the fly since where clause can have multiple filters that come from frontend and PercentileX and PercentileY can be any other column from that table (column titles for these also come from the frontend). Is there any other way to speed that up so it, so frontend doesn’t need to wait for that long?

Leave a Reply

Your email address will not be published. Required fields are marked *