0

Is there any significant decrease in performance, or other adverse effect by using 1-row tables instead of local variables?

instead of:

Declare @DT date = getdate()

use:

Select convert(date, getdate()) as DT into #DT

The reason I would want to do this is for code testing. When I want to run just a chunk of code, it is annoying if the code is say 100 lines down but needs several of my declarations to run.

instead of:

Line 104     select
Line 105        somecolumn
Line 106     where datadate = @DT

-- MUST DECLARE SCALAR @DT, EVEN THOUGH LINES 1-103 WERE PREVIOUSLY RUN

use:

Line 104     select
Line 105        somecolumn
Line 106     where datadate = (select DT from #DT)

-- NO ISSUE EXECUTING ONLY THESE 3 LINES, SINCE LINES 1-103 WERE PREVIOUSLY RUN

I just need to know if this is the same as far as execution time, and accuracy go. I know that the subquery will run over and over, but since #DT is one-row (and 1 column) when would things begin to lag?

VISQL
  • 1,960
  • 5
  • 29
  • 41
  • 3
    Can't YOU test both and tell US what's faster? My guess is that you won't see a difference at all, but I'm not sure why you're asking which is faster when you clearly have the ability to do this yourself. – Aaron Bertrand Aug 24 '12 at 18:31
  • I was hoping someone with more insight to architecture and optimization of execution could give more details beyond quoting something like "3 seconds vs 5 seconds with 1000 lines" – VISQL Aug 24 '12 at 19:35
  • 4
    Maybe you could try it in your scenario, and if observe tangible differences, you could ask about them. The open-ended question you've asked doesn't seem to me to be a good fit here, since any answer to "which is faster?" is going to be littered with assumptions and "it depends." – Aaron Bertrand Aug 24 '12 at 19:45

0 Answers0