Modify xml path type to a simple solution

sapator · February 22, 2017, 8:50am

Hi.

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.

Thanks.

Kristen · February 22, 2017, 9:35am

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

sapator · February 22, 2017, 9:52am

Hi.
No it does not change anything.

So i guess I must live with that?
Thanks.

Kristen · February 22, 2017, 10:01am

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)

sapator · February 22, 2017, 10:29am

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).
Thanks.

Kristen · February 22, 2017, 10:36am

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)

sapator · February 22, 2017, 11:16am

Thanks.
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

yosiasz · February 22, 2017, 9:19pm

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

JeffModen · February 23, 2017, 12:42am

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?

Kristen · February 23, 2017, 8:21am

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 ...

Kristen · February 23, 2017, 8:22am

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 ...

sapator · February 24, 2017, 1:51pm

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

sapator · February 24, 2017, 2:11pm

Bizdate function

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

Kristen · February 25, 2017, 11:41am

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.

yosiasz · February 27, 2017, 4:31pm

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

sapator · February 28, 2017, 9:25am

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) .
Thanks

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

Thanks.

Kristen · February 28, 2017, 9:32am

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.

Kristen · February 28, 2017, 9:33am

If you just want to strip off the TIME then

CONVERT(Date, Configure_strValue)

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

sapator · February 28, 2017, 9:44am

OK.
Thanks for that

sapator · February 28, 2017, 11:06am

Hmm.
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)
		
		SET @dtmDate = CONVERT(DATETIME,CONVERT(VARCHAR(10),@dtmDate,20))
		
	   select CONVERT(Date, Configure_strValue)   ----
       FROM tblConfigure         ----
       WHERE Configure_strName = 'StartTimeOfDay'  --------- 1900-01-01
						
		IF @intTransDateTime >= @intStartTimeOfDay
		    BEGIN	
		
			SET @dtmBizDate =(DATEADD(hh, @intStartTimeOfDay, @dtmDate))
		    END
		ELSE
		    BEGIN
		        SET @dtmBizDate  = (DATEADD(dd, (-1),@dtmDate))
			SET @dtmBizDate  =(DATEADD(hh, @intStartTimeOfDay, @dtmBizDate))
		    END			
		select @dtmBizDate
	--END