Forum Discussion
Assistance Requested for SQL Execution Efficiency Issue
Hi, Chen.
Assuming these queries are against two tables contained in the same database:
- If you run the second query but change the table from [fy2022] to [fy2023], does it still run fast or slow the first time around?
- If it's slow the first time around, check that you have an index (NONCLUSTERED would almost certainly be the preferable type) on the [makerid] column of the [fy2023] table.
It's pure guesswork on my part but it sounds like your [fy2022] table has an index on the [makerid] column while your [fy2023] table does not.
Cheers,
Lain
Thank you for your response.
- Even after switching the tables in the first query, the execution remains fast.
- In both f2022 and f2023, there are no indexes on makerid.
I can upload screenshots of the execution plans for these queries, but they are in Chinese. I'm not sure if you'll be able to understand them.
sql1:
sql2:
Hi, Chen.
These seem to be the estimated execution plans. Are you able to instead include the actual execution plans?
Here's the button from SSMS for including actual execution plans.
The actual execution plan shows how much real time (in milliseconds) was spent at each stage:
If you can also add the following two lines to the end of each query after the "SORT BY" statement - only while you're capturing the actual execution plans (remove it again afterwards), that would be appreciated:
OPTION (RECOMPILE);Also, I have to correct myself as I wasn't paying proper attention to what I had written about the indexes.
In my previous reply, I've incorrectly asked about an index on [makerid] when I'd meant to refer to the [ledger] column.
Similarly, is the [id] column in the [User] table indexed?
Your execution plans do involve a lot of table scanning, however, this may not be an issue. It just means it might be worth looking into, and the actual execution plan will help quantify that.
Cheers,
Lain
The first image is of the execution plan for sql1 without OPTION (RECOMPILE).
The second image is of the execution plan for sql2 without OPTION (RECOMPILE).
The third image is of the execution plan for sql1 with OPTION (RECOMPILE).
The fourth image is of the execution plan for sql2 with OPTION (RECOMPILE).
Both f2022 and f2023 have the same indexes and table structure.
