Performance of Insert .. Values vs Insert Select UNION ALL

[druer]

I came across a newly released stored procedure from development that has several branches in the stored proc where it does a bunch of INSERT INTO using the values clause. Each block is similar to the following and has between 5-10 inserts:

INSERT INTO popup_single (name, result) VALUES (@POPUP_NAME, 'G9077       Stage I, low risk')
INSERT INTO popup_single (name, result) VALUES (@POPUP_NAME, 'G9078       Stage II, high risk') etc[/CODE
I copied out one of the blocks for fun and modified it to use selects with a UNION ALL similar to the following:[CODE]
INSERT INTO popup_single (name, result)
select @POPUP_NAME, 'G9077       Stage I, low risk' UNION ALL
select @POPUP_NAME, 'G9078       Stage II, high risk' UNION ALL ...etc

I'm baffled by the performance numbers that I'm seeing. In QA I see that if I just run the two blocks (with appropriate delete above them) the UNION ALL code takes up only about 10% of the total time, indicating it is much faster. However, if I do the same thing inside of the stored procedure, I find that the duration is actually longer with it instead which really baffles me.

Can any of you guys that have a much deeper understanding of what's going on behind the scenes explain to me why I'd see this kind of oddity in terms of performance?

 

[vongrunt]

There are few possible explanations... can you post exec plans for both cases?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)