May 16, 2025 at 12:40 pm
Hi,
I would love to hear if anyone can tell me why a CAST in a query can "resolve" this performance issue.
Same symptom on test server with a copy of the database.
weblab_reportlog = 207 850 195 rows
Requisitions = 30 143 466 rows
DBCC FREEPROCCACHE
SELECT [LID], [Datestamp], [logmessage], [Reporttype], [UserID] FROM [dbo].[weblab_reportlog] WHERE
LID IN (SELECT LID FROM Requisitions WHERE RequisitionID = 30254830) -- Takes 40 seconds.
As this was in produktion I had a look at the schemas and saw that the column LID had different data types in the two different tables.
[weblab_reportlog].[LID] = varchar(100)
[Requisitions].[LID] = varchar(50)
For the sake of it I throw in a CAST.
DBCC FREEPROCCACHE
SELECT [LID], [Datestamp], [logmessage], [Reporttype], [UserID] FROM [dbo].[weblab_reportlog] WHERE
LID IN (SELECT CAST(LID AS VARCHAR(50)) FROM Requisitions WHERE RequisitionID = 30254830)
Now the result returned instantly.
But also notice that the CAST should have been the other way around menaning should have been CAST(LID AS VARCHAR(100)) instead of CAST(LID AS VARCHAR(50)).
I thought that VARCHAR(x) vs VARCHAR(y) wouldn't make that much of a difference?
And in this cast I actually CASTed VARCHAR(50) to VARCHAR(50) ?!
Execution plans:
Bad plan:
https://www.brentozar.com/pastetheplan/?id=1S3p0Rys9y
Plan with CAST in query:
https://www.brentozar.com/pastetheplan/?id=7m5YW7O3I4
Good plan after index rebuild:
https://www.brentozar.com/pastetheplan/?id=01i8KaAyY7
Have a nice weekend
May 16, 2025 at 4:01 pm
Solid catch with that CAST —Really sometimes it's the smallest tweak that makes all the difference.
Also it came as surprise how just a VARCHAR(100) vs VARCHAR(50) can silently mess up the plan. Seems SQL Server sees the mismatch and slips in an internal conversion, killing index usage — especially inside subqueries.
That CAST you added basically forced the engine to stop guessing and behave. It’s wild how just making the type explicit flips the whole execution plan around.
Seen this pattern too — SQL Server gets touchy with string lengths during JOIN or IN, even when both are varchar.
Appreciate the plan links — super helpful to walk through the difference .
May 17, 2025 at 5:22 am
Thank you very much for your feedback. It is much appreciated.
I guess I'll have to review the data types in the future. For now I've just added a job that rebuilds the index which also "solves" the problem.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply