August 26, 2009 at 7:18 am
Is it possible to JOIN to the result of a StoreProcedure directly?
Previously I've been doing this by first Inserting the result of SP to a tempTable and then JOINING to that tempTable.
Thanks,
G
August 26, 2009 at 7:35 am
Nope. It's not possible to JOIN an EXECUTE. One more reason why I use table valued functions instead of procedures if possible.
August 26, 2009 at 7:42 am
The temptable approach (or table var) is what I've done in the past.
You can use TVF's, as Flo suggested, if you can rewrite the code.
August 26, 2009 at 8:10 am
Thanks for the suggestions ... will look into TVF approach.
August 26, 2009 at 9:00 am
You can also use OpenQuery (or one of its cousins) for this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 10:12 am
Since Flo brought up the TVF approach, I want to ask. Can anyone point me to documentation about the performance of TVFs v stored procedures? I always assumed that execution plans, parameter sniffing, etc, would still apply. But I've never seen it documented anywhere.
I'm not talking about inline table valued functions, BTW. I understand how they work.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2009 at 11:07 am
Bob Hovious (8/26/2009)
Since Flo brought up the TVF approach, I want to ask. Can anyone point me to documentation about the performance of TVFs v stored procedures? I always assumed that execution plans, parameter sniffing, etc, would still apply. But I've never seen it documented anywhere.I'm not talking about inline table valued functions, BTW. I understand how they work.
I've done tests myself and found that just wrapping code in a TVF adds about 3% to the execution time, even with no other change in the code. Can't say how universal that is, but it was consistently true on the tests I did.
What I did was create a proc that populated a table variable and returned it, and created a TVF that used the same code to populate the same table variable. Then I ran each 10,000 times and logged the run-time. TVF took 3% longer on average.
I've seen a similar effect on using a Numbers-table based string parser as an inline TVF or a direct cross-apply, vs having it populate a temp table for a multi-value TVF. Same query, doing the same thing on the same data, and the inline versions took measurably less time than the multi-value version. I don't remember the percentage increase, but it was substantial.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 2:01 pm
Depending on the query, I would also test against CTE. I have found the same sort of result with TVFs when compared to procs as GSquared has found. Some of the TVFs when converted to procs actually improved in performance by a factor of 2 to 3 times.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply