performance question: constant vs. field content
-
Hello. Does anybody know for sure whether there is a GENERAL performance difference between the following approaches?:
SELECT
MachineID,
ProductionEntry
FROM MachineProduction
WHERE
MachineID = @Machine
AND
SELECT
@Machine AS MachineID,
ProductionEntry
FROM MachineProduction
WHERE
MachineID = @Machine
_/_/_/ paramind _/_/_/ -
Try both and compare the plans.
My guess is that there is no measurable difference.
/Kenneth
-
That's why I asked, if there is some known working pattern query optimizer uses. I'm trying to figure out, whether there could be a difference at 100.000.000 rows while there's no measurable difference at 10.000.000. I'm simply not gonna load 100.000.000 rows to find out
_/_/_/ paramind _/_/_/ -
Well, can't really see that there would be any difference from the optimizer's point of view, since it deals primarily with finding the data, not how to display what it finds.
The SARG is MachineID, so you still have to access those datapages in order to find the rows in the first place. I don't think that the optimizer would care if you're assigning the column value into a variable and returning that instead of the column. (It's still always a single value anyway)
It may yield some differences overall, though, since the var assignment probably costs some cpu, given enough volume to work one.
Guess it's one of those 'it depends' things
/Kenneth
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply