SQLTeam.com | Weblogs | Forums

Modify xml path type to a simple solution


SELECT OrderH_intID, OrderTH_strScreenName AS ScreenName, OrderTH_strMovieName AS MovieName, OrderTH_dtmSessionDateTime AS ShowTime, dbo.fnGetBizDateOnly(OrderTH_dtmSessionDateTime) AS ShowDate,
       STUFF((SELECT ',' + OrderTH_strSeatRowId + OrderTH_strSeatNum
              FROM VISTAIT.dbo.tblOrderTicketHistory OTHS
              WHERE OTHS.OrderH_intID = OTH.OrderH_intID 
              ORDER BY OrderTH_strSeatRowId, RIGHT('000'+CAST(OrderTH_strSeatNum AS VARCHAR(3)),3)
              FOR XML PATH(''), TYPE)
             .value('.','NVARCHAR(100)'),1,1,'') AS Seats,
			 count(OTH.OrderTH_strSeatRowId) as TotalNumSeats
FROM VISTAIT.dbo.tblOrderTicketHistory OTH
GROUP BY OrderH_intID, OrderTH_strScreenName, OrderTH_strMovieName, OrderTH_dtmSessionDateTime

This will result to:
7 NULL 10.000 Π.Χ. 2008-09-03 12:05:00.000 2008-09-03 00:00:00.000 A10,A11 2

We are using this in a query so we can split the seats in one row. As I've seen this XML function takes up a HUGE amount of data for just one row (8000 B).

I'm wondering if there is a better solution than the XML one so I can reduce the data consumption for the query.


Interesting, not come across that downside.

Any chance the "right aligned" sort is causing that? maybe change the sort (for test purposes) to just sort by

ORDER BY OrderTH_strSeatRowId, OrderTH_strSeatNum

and see if that changes anything (other than wrecking the Alpha/Numeric sorting ...)

I don't know of another, easy, way of comma-delimited a sub-query of values. Before the XML solution we used use a UDF


1 Like

No it does not change anything.

So i guess I must live with that?

Is it likely to be a problem?

UDF no better presumably? (Your Query Stats show "Table Values Function" so maybe that is for a UDF? (even thought the SQL shown is for XML ... hence why I am not sure)

No it's not a problem right now, I just wanted to know if I can make it a little less consuming.
UDF. I have a function in there " dbo.fnGetBizDateOnly" but it returns a datetime object.

Should I try a UDF or leave it at that? As I've said, there is not a problem currently.
As per your example I should make a function for the UDF, so isn't the function slow down the query.
Or is there a way not to call a function (If so I would appreciate a small example).

Yes, UDF functions have a reputation for being slow. Depends how many rows your report will contain. A table valued function will perform much better (but that is not the type in the example link I posted)

Only other way to do it (which I doubt is sensible) is to pass all the relevant data to the Client APP and have that do the Presentation side (concatenating the "Seats" list)

1 Like

No I wouldn't want to use the application in order to handle the data as this will force a double calculation (one on SQL side and one on APP side).

OK, so I guess there isn't much sense on losing time to change this. As I've said it's not a problematic query.

Thanks again :slight_smile:

what does that udf do fnGetBizDateOnly? how simple or how complicated is that puppy?

If you look more closely, they're all estimated values.

The best question goes unanswered, though. Why are you doing this to perfectly good data? What is the end use of this data?

Hehehe ... I read the O/P and assumed I knew the answer to that one!! (Always a dangerous approach ...)

I assumed that the End User wanted to see a "list of available seats, sorted into a sequence that made sense to Humans". May be something completely different though ... :slight_smile:

I'd definitely go with that. We have this type of stuff in our code Here and There and I've never know it to be a problem. But there again ... I've never looked closely ...

Yes the answer is as Kristen suggested. The end user needs to see the available seats sorted.
thanks again

Bizdate function

DECLARE @dtmBizDate datetime,
		@intTransDateTime     	int,
	 	@intStartTimeOfDay	int
		SET @intTransDateTime = DATEPART(hh, @dtmDate)
		SELECT @intStartTimeOfDay = SUBSTRING(Configure_strValue,1,2) 
		FROM tblConfigure 
		WHERE Configure_strName = 'StartTimeOfDay'
		IF @intTransDateTime >= @intStartTimeOfDay 
				SET @dtmBizDate = @dtmDate
				SET @dtmBizDate  = (DATEADD(dd, (-1),@dtmDate))
		RETURN @dtmBizDate

If performance is the main consideration then I would avoid converting dates to string (and then comparing them). The act of converting date (integer) to string has traditionally (I haven;t checked in recent versions) been much slower than an all-numeric calculation.

Faster to check that a DateColumn is >= @StartDate AND DateColumn < @StartDatePlusOne

Where @StartDate is already "rounded" to 00:00:00 on the initial date, and @StartDatePlusOne is roudned to 00:00:00 on the day AFTER the last date to be included.

WHERE MyDate >= '20170224 00:00:00' AND MyDate < '20170225 00:00:00'

to selecting anything ON 24-Feb-2017

If an index is available on [MyDate] then SQL can just do a "range" on that index to find all matching rows, no CONVERT etc. involved.

how many rows do you have in table tblConfigure ? and is 'StartTimeOfDay' coming in as a parm in this function?
How many rows do you have about in tblOrderTicketHistory?

I am trying to see if instead of a udf you could create maybe a #tmp table you could join tblOrderTicketHistory to

Kristen, thanks

		select Configure_strValue
	 FROM tblConfigure 
	 WHERE Configure_strName = 'StartTimeOfDay'

will give 06:00AM .
Am a little confused as to how will I use WHERE MyDate >= '20170224 00:00:00' AND MyDate < '20170225 00:00:00'
(obviously with '20170224 06:00:00' , but this may change on the tblConfigure table) .

yosiasz the tblConfigure has 300 rows give or take and tblOrderTicketHistory has currently 6340946 rows.


select Configure_strValue,
       DATEADD(Day, DATEDIFF(Day, 0, Configure_strValue), 0) AS [StartOfDay],
       DATEADD(Day, DATEDIFF(Day, 0, Configure_strValue)+1, 0) AS [StartOfNextDay]
FROM tblConfigure 
WHERE Configure_strName = 'StartTimeOfDay'

Use different units for "Day" if you need start-of-month, or year, or hour, etc.

1 Like

If you just want to strip off the TIME then

CONVERT(Date, Configure_strValue)

might be the answer - its easier to type, at least!

1 Like

Thanks for that :slight_smile:

Spoke too fast.
I'm not sure where should this be used to replace because i get 1900-01-01

declare @dtmDate as datetime
	set @dtmDate = '20170215'
		DECLARE @dtmBizDate datetime
		declare @intTransDateTime     	int
	 	declare @intStartTimeOfDay	int
		SET @intTransDateTime = DATEPART(hh, @dtmDate)
	   select CONVERT(Date, Configure_strValue)   ----
       FROM tblConfigure         ----
       WHERE Configure_strName = 'StartTimeOfDay'  --------- 1900-01-01
		IF @intTransDateTime >= @intStartTimeOfDay
			SET @dtmBizDate =(DATEADD(hh, @intStartTimeOfDay, @dtmDate))
		        SET @dtmBizDate  = (DATEADD(dd, (-1),@dtmDate))
			SET @dtmBizDate  =(DATEADD(hh, @intStartTimeOfDay, @dtmBizDate))
		select @dtmBizDate