August 28, 2024 at 1:42 pm
Using Derived Table in query on large data (12 crores and more) results in CPU utilization greater than 90% resulting in performance degradation.
Why Using Derived Table in query utilizes high CPU when it is created in memory
August 28, 2024 at 1:48 pm
please post actual explain plan of the problematic query as well as the full sql statement (and of any views/functions used by it) so we can help.
use https://www.brentozar.com/pastetheplan/ to upload the plan and post link to it.
August 28, 2024 at 2:25 pm
It's difficult to say without seeing the actual query.
You could insert the data into a temporary table, create helpful indexes on the temporary table, then query the temporary table. So remove the need to use a derived table.
It would be helpful if you could paste the query into this thread.
August 29, 2024 at 12:23 pm
Just to be clear, nothing in a derived table, in and of itself, is necessarily a problem for the optimizer, resulting in poor performance. It's the specific implementation of your specific derived table and how that code is running against the structures and indexes in your database that is the problem.
This is why people are asking for the code and execution plans. Just saying "derived table, bad performance" doesn't share enough information to help out because using a derived table isn't, necessarily, a bad thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply