Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

CTE Expand / Collapse
Author
Message
Posted Wednesday, April 14, 2010 5:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 24, 2011 12:39 PM
Points: 9, Visits: 36
Yes, the examples were very helpful indeed. Thank you!
Post #903682
Posted Thursday, April 15, 2010 9:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:00 PM
Points: 22,992, Visits: 31,470
First, glad we could help.

Second, I knew others would provide you with links that would help you understand the FOR XML. Thanks to you all as well.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #904147
Posted Thursday, April 15, 2010 10:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
Here is another excellent article on Stuff and For XML
http://www.sqlservercentral.com/articles/Test+Data/61572/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #904181
Posted Monday, December 19, 2011 1:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 3:09 PM
Points: 2, Visits: 10
I am not sure what the execution times for the following query would be as I have not implemented any hints or indexes on the temp table created however probably you can give this a shot (The only time lag here would actually be the replace statements as mentioned below.... try it out an let me know )
select reportid,replace(replace(Val,'<User2>',''),'</User2>','')
from
(select distinct a.reportid,
--(select top 1 User2 from #tbl b where b.reportid=a.reportid) as Test,
(select distinct User2+' ' from #tbl c where c.reportid = a.reportid FOR XML PATH('')) as Val
from #tbl a) x

This would give you the following result:-
Post #1223659
Posted Saturday, July 20, 2013 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 4, 2013 9:54 PM
Points: 9, Visits: 9
CTE, temp table with index or table variable... which one is more performant?
Post #1475755
Posted Saturday, July 20, 2013 4:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
rahulraghvn (7/20/2013)
CTE, temp table with index or table variable... which one is more performant?


None of the above. Use the XML method that was posted.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1475782
Posted Monday, July 22, 2013 8:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:12 PM
Points: 3,609, Visits: 5,220
LutzM (4/14/2010)
Maybe this link will be of some help, too:
http://www.sqlservercentral.com/Forums/Topic679572-338-2.aspx
Barry did a great job explaining how the STUFF ... FOR XML PATH actually works.


SQLRNNR (4/15/2010)
Here is another excellent article on Stuff and For XML
http://www.sqlservercentral.com/articles/Test+Data/61572/


Innocently but blatantly hijacking this thread, it is coincidental that I was recently searching for an article on FOR XML PATH, running across both of these excellent explanations.

However I was looking for a bit more. Specifically, I'm wondering if anyone knows of an article that not only explains how it works and also gives examples that adds the special handling for when the content of the column contains one of the restricted characters? I recently saw someone post a performance comparison of the "standard" approach next to the one with special character handling, and I can't even find that at the moment.

It seems that all of the ones I've run across either show it without special character handling or with, and only Jeff's examines the performance.

If the article explained performance as well it would be a bonus. Thanks.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1476331
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse