re: T-SQL Tuesday #002: Is it XML, or Not?!?

Brad Schulz — Tue, 12 Jan 2010 22:55:57 GMT

Hi Adam...

I wrote about this very thing about a month ago and again last week:

http://bradsruminations.blogspot.com/2009/12/delimited-string-tennis-anyone.html

http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html

In the first link above, I gave an explanation as to WHY the query is so slow.

I like your FOR XML PATH(''),TYPE approach... it's similar to the .query('.') approach I mentioned in the second link above.

--Brad

re: T-SQL Tuesday #002: Is it XML, or Not?!?

Dan — Thu, 06 Oct 2011 23:53:40 GMT

I actually have a question, rather, I need help figuring out why the code below runs 300% faster in the dev environment than in the QA env.

Some one suggested that replace the following construct with the one below, however, the runtime did not change.

@request.value('(/searchRequest/mlsNumber)[1]'

@request.value('(/searchRequest/mlsNumber/text())[1]'

Please help.

Thanks,

Dan

DECLARE @request xml

DECLARE @latitudeMax float

DECLARE @latitudeMin float

DECLARE @longitudeMax float

DECLARE @longitudeMin float

DECLARE @agentUsername varchar(30)

DECLARE @firmCode varchar(30)

DECLARE @areas table

(

AreaID int

, Name varchar(200)

)

select @Request = '<searchRequest><header><requestType>ListingSearch</requestType></header><zipCode>91342</zipCode><propertyType><type>0</type></propertyType><listingStatus><status>5</status></listingStatus></searchRequest>'

--INSERT INTO ListingSearchIndex

-- (RequestID, SearchKey)

SELECT 470 , svo.MlsNum

FROM

mls_unified_svo_tbl svo (nolock)

WHERE

svo.LoadOnInternet = 1

AND svo.std IS NOT NULL

AND svo.snd IS NOT NULL

AND (svo.Status IN (5, 30, 45)

(svo.Status IN (10, 20)

AND DATEDIFF(day, svo.StatusDate, GETDATE()) < 365

)

AND

CASE WHEN @request.exist('/searchRequest/mlsNumber') = 0

THEN 1

ELSE

CASE WHEN svo.MlsNum = @request.value('(/searchRequest/mlsNumber)[1]', 'varchar(20)') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/zipCode') = 0

THEN 1

ELSE

CASE WHEN svo.zip = @request.value('(/searchRequest/zipCode)[1]', 'varchar(500)') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/city') = 0

THEN 1

ELSE

CASE WHEN svo.city = @request.value('(/searchRequest/city)[1]', 'varchar(50)') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/address') = 0

THEN 1

ELSE

CASE WHEN SUBSTRING(svo.address, 1, LEN(@request.value('(/searchRequest/address)[1]', 'varchar(100)'))) = @request.value('(/searchRequest/address)[1]', 'varchar(100)') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/listingArea') = 0

THEN 1

ELSE

CASE WHEN svo.ar IN (SELECT AreaID FROM @areas) OR svo.city IN (SELECT Name FROM @areas) THEN 1

ELSE 0

END

END = 1

AND

CASE

WHEN @latitudeMin IS NULL OR @latitudeMax IS NULL OR @longitudeMin IS NULL OR @longitudeMax IS NULL THEN 1

ELSE

CASE WHEN svo.Latitude BETWEEN @latitudeMin AND @latitudeMax

AND svo.Longitude BETWEEN @longitudeMin AND @longitudeMax THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/price/low') = 0

THEN 1

ELSE

CASE WHEN svo.lp >= @request.value('(/searchRequest/price/low)[1]', 'bigint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/price/high') = 0

THEN 1

ELSE

CASE WHEN svo.lp <= @request.value('(/searchRequest/price/high)[1]', 'bigint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/yearBuilt/low') = 0

THEN 1

ELSE

CASE WHEN svo.yb >= @request.value('(/searchRequest/yearBuilt/low)[1]', 'smallint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/yearBuilt/high') = 0

THEN 1

ELSE

CASE WHEN svo.yb <= @request.value('(/searchRequest/yearBuilt/high)[1]', 'smallint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/sqFootage/low') = 0

THEN 1

ELSE

CASE WHEN svo.sf >= @request.value('(/searchRequest/sqFootage/low)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/sqFootage/high') = 0

THEN 1

ELSE

CASE WHEN svo.sf <= @request.value('(/searchRequest/sqFootage/high)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/bed/low') = 0

THEN 1

ELSE

CASE WHEN svo.br >= @request.value('(/searchRequest/bed/low)[1]', 'smallint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/bed/high') = 0

THEN 1

ELSE

CASE WHEN svo.br <= @request.value('(/searchRequest/bed/high)[1]', 'smallint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/bath/low') = 0

THEN 1

ELSE

CASE WHEN svo.ba >= @request.value('(/searchRequest/bath/low)[1]', 'decimal') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/bath/high') = 0

THEN 1

ELSE

CASE WHEN svo.ba <= @request.value('(/searchRequest/bath/high)[1]', 'decimal') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/parking/low') = 0

THEN 1

ELSE

CASE WHEN svo.pkg_num >= @request.value('(/searchRequest/parking/low)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/parking/high') = 0

THEN 1

ELSE

CASE WHEN svo.pkg_num <= @request.value('(/searchRequest/parking/high)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/lotSize/low') = 0

THEN 1

ELSE

CASE WHEN svo.lsz >= @request.value('(/searchRequest/lotSize/low)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/lotSize/high') = 0

THEN 1

ELSE

CASE WHEN svo.lsz <= @request.value('(/searchRequest/lotSize/high)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/soldPrice/low') = 0

THEN 1

ELSE

CASE WHEN svo.sp >= @request.value('(/searchRequest/soldPrice/low)[1]', 'bigint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/soldPrice/high') = 0

THEN 1

ELSE

CASE WHEN svo.sp <= @request.value('(/searchRequest/soldPrice/high)[1]', 'bigint') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/daysInStatus/low') = 0

THEN 1

ELSE

CASE WHEN DATEDIFF(d, svo.statusdate, GETDATE()) >= @request.value('(/searchRequest/daysInStatus/low)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/daysInStatus/high') = 0

THEN 1

ELSE

CASE WHEN DATEDIFF(d, svo.statusdate, GETDATE()) <= @request.value('(/searchRequest/daysInStatus/high)[1]', 'int') THEN 1

ELSE 0

END

END = 1

AND

CASE WHEN @request.exist('/searchRequest/propertyType') = 0

THEN 1

ELSE

CASE WHEN svo.property_type IN (SELECT Request.PropertyType.value('.', 'int')

FROM @request.nodes('/searchRequest/propertyType/type') Request(PropertyType)) THEN 1

ELSE 0

END

END = 1

AND

CASE

WHEN @firmCode IS NULL THEN 1

ELSE

CASE WHEN @firmCode IN (svo.ListBrokerCode1, svo.ListBrokerCode2, svo.ListBrokerCode3) THEN 1

ELSE 0

END

END = 1

AND

CASE

WHEN @agentUsername IS NULL THEN 1

ELSE

CASE WHEN @agentUserName IN (svo.ListAgentID1, svo.ListAgentID2, svo.ListAgentID3) THEN 1

ELSE 0

END

END = 1

AND ((@request.exist('/searchRequest/listingStatus') = 1

AND svo.status IN (SELECT Request.Status.value('.', 'int') FROM @request.nodes('/searchRequest/listingStatus/status') Request(Status))

)

(@request.exist('/searchRequest/listingStatus') = 0

AND svo.status NOT IN (40,25,15,35)

)

AND (@request.exist('/searchRequest/openhouse') = 0

OR EXISTS (SELECT ohl.MlsNum

FROM open_house_list ohl (nolock)

WHERE

ohl.MlsNum = svo.MlsNum

AND

ohl.public_yn = 'yes'

AND

ohl.openhouse_type IN ('new', 'review')

AND (@request.exist('/searchRequest/openhouse/low') = 0

OR ohl.openhouse_date >= @request.value('(/searchRequest/openhouse/low)[1]', 'datetime')

)

AND (@request.exist('/searchRequest/openhouse/high') = 0

OR ohl.openhouse_date <= @request.value('(/searchRequest/openhouse/high)[1]', 'datetime')

)

AND (@request.exist('/searchRequest/saleType') = 0

OR EXISTS (SELECT lst.MlsNum

FROM ListingSaleType lst (nolock)

WHERE

lst.MlsNum = svo.MlsNum

AND

(@request.exist('/searchRequest/saleType/auction') = 0

OR lst.IsAuction = 1

)

AND

(@request.exist('/searchRequest/saleType/foreclosure') = 0

OR lst.IsForeclosure = 1

)

AND

(@request.exist('/searchRequest/saleType/default') = 0

OR lst.IsDefault = 1

)

AND

(@request.exist('/searchRequest/saleType/reo') = 0

OR lst.IsREO = 1

)

AND

(@request.exist('/searchRequest/saleType/short') = 0

OR lst.IsShortPay = 1

)

AND

(@request.exist('/searchRequest/saleType/standard') = 0

OR lst.IsStandard = 1

)

AND

(@request.exist('/searchRequest/saleType/probate') = 0

OR lst.IsProbate = 1

)

T-SQL Tuesday #002: Is it XML, or Not?!?

re: T-SQL Tuesday #002: Is it XML, or Not?!?

re: T-SQL Tuesday #002: Is it XML, or Not?!?

re: T-SQL Tuesday #002: Is it XML, or Not?!?

T-SQL Tuesday #002: The Roundup

T-SQL Tuesday #002: The Roundup

re: T-SQL Tuesday #002: Is it XML, or Not?!?

re: T-SQL Tuesday #002: Is it XML, or Not?!?

re: T-SQL Tuesday #002: Is it XML, or Not?!?