SQLTeam.com | Weblogs | Forums

Modify xml path type to a simple solution


#1

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.


#2

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

http://stackoverflow.com/questions/7049028/user-defined-function-that-returns-the-values-of-a-single-column-concatenated-to


#3

Hi.
No it does not change anything.

So i guess I must live with that?
Thanks.


#4

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)


#5

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.


#6

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)


#7

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 :slight_smile:


#8

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


#9

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?


#10

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:


#11

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


#12

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


#13

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

#14

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.


#15

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


#16

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.


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


#18

If you just want to strip off the TIME then

CONVERT(Date, Configure_strValue)

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


#19

OK.
Thanks for that :slight_smile:


#20

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