Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 2012-07-05 : 19:31:53
|
| Anyway to optimize this query? Looks like the xml part is taking too long to run and in sql profiler it has a large read and write. SELECT AD.ADID, AD.ForeignSource, CASE AD.ForeignSource WHEN 45 THEN 1 WHEN 30 THEN 2 WHEN 42 THEN 3 WHEN 34 THEN 4 WHEN 33 THEN 5 END AS 'ordernumber' FROM ( select --count(*) SubAd.adid, SubAd.ForeignSource, SubAd.AdAttributes, SubAd.ForeignID from ad SubAd where SubAd.ForeignSource in (30, 34, 33, 42, 45) and SubAd.Status =1 ) AD CROSS APPLY AD.AdAttributes.nodes('/mix[1]/attributes[1]') AS D(col) WHERE AD.ForeignID = @mls OR ( col.value( '(//attribute[1][@id="32"]/item[1]/@value)[1]', 'varchar(255)') = @Address AND col.value( '(//attribute[1][@id="108"]/item[1]/@value)[1]', 'varchar(255)') = @Unit AND @Unit IS NOT NULL ) ORDER BY 'ordernumber'--END |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2012-07-07 : 22:44:35
|
| Create XML indexes for the column |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-08 : 16:10:36
|
| why are you using // inside xpath? it will cause it to search for all xml branches for the condition. why not give correct path if you know path is static within XML node?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2012-07-24 : 16:54:15
|
| I don't know. I didn't write that. What should I do? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-24 : 17:01:53
|
quote:
Originally posted by basicconfiguration
I don't know. I didn't write that. What should I do?
You need to refine it with more clearer xquery path if you can determine it beforehand ie hierarchy is fixed------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
SQL XML tuning