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.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Seleting from XML datatype into Table very slow

Author  Topic  pgorry Starting Member 1 Post Posted - 2009-07-09 : 16:57:02 Hi all, I have been struggling with this for a while and can not find a solution. I have an XML document in and XML datatype that I need to do some processing on. I create a table variable and select the data from the document into the table. There are only 94 rows in the resulting table, yet the statement takes at least 0.5 of a second to run. If I just do the select from the XML and not the insert it is almost instant (0.013 sec) and likewise if I write 100 insert statements they run equally as quick. I have tried inserting into a temp table and a physical table, with no improvements. Also if I run it for 1 column, or say the top 10 rows it is much quicker. It looks like it is a linear increase in time, like its doing different conversion on the insert versus the select. So what is it about the SELECT\INSERT statement that makes this so slow?? Is there any way around this. If I change how this works it may mean the rewrite of a rather large Stored Procedure (700 lines) (sorry for the length of the post due to the XML) Code below, Many thanks for you help. DECLARE @CurrentFilter AS XML SET @CurrentFilter = '<doc><Filters><FilterID>1</FilterID><TypeName>Section</TypeName><TypeID>2</TypeID><TypeValue>Mens</TypeValue><State>1</State><col>1</col></Filters><Filters><FilterID>2</FilterID><TypeName>Section</TypeName><TypeID>3</TypeID><TypeValue>Womens</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>3</FilterID><TypeName>Category</TypeName><TypeID>17</TypeID><TypeValue>Accessories</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>4</FilterID><TypeName>Category</TypeName><TypeID>18</TypeID><TypeValue>Bags</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>5</FilterID><TypeName>Category</TypeName><TypeID>19</TypeID><TypeValue>Denim</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>6</FilterID><TypeName>Category</TypeName><TypeID>14</TypeID><TypeValue>Dresses</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>7</FilterID><TypeName>Category</TypeName><TypeID>20</TypeID><TypeValue>Footwear</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>8</FilterID><TypeName>Category</TypeName><TypeID>21</TypeID><TypeValue>Headwear</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>9</FilterID><TypeName>Category</TypeName><TypeID>6</TypeID><TypeValue>Hoodies</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>10</FilterID><TypeName>Category</TypeName><TypeID>36</TypeID><TypeValue>Jumpers</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>11</FilterID><TypeName>Category</TypeName><TypeID>37</TypeID><TypeValue>Leggings</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>12</FilterID><TypeName>Category</TypeName><TypeID>5</TypeID><TypeValue>Outerwear</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>13</FilterID><TypeName>Category</TypeName><TypeID>24</TypeID><TypeValue>Pants</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>14</FilterID><TypeName>Category</TypeName><TypeID>23</TypeID><TypeValue>Shirts</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>15</FilterID><TypeName>Category</TypeName><TypeID>25</TypeID><TypeValue>Shorts</TypeValue><State>0</State><col>2</col></Filters><Filters><FilterID>16</FilterID><TypeName>Category</TypeName><TypeID>38</TypeID><TypeValue>Skirts</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>17</FilterID><TypeName>Category</TypeName><TypeID>39</TypeID><TypeValue>Sleepwear</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>18</FilterID><TypeName>Category</TypeName><TypeID>43</TypeID><TypeValue>Sweaters</TypeValue><State>0</State><col>2</col></Filters><Filters><FilterID>19</FilterID><TypeName>Category</TypeName><TypeID>41</TypeID><TypeValue>Swimwear</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>20</FilterID><TypeName>Category</TypeName><TypeID>35</TypeID><TypeValue>Tanks</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>21</FilterID><TypeName>Category</TypeName><TypeID>16</TypeID><TypeValue>Tees</TypeValue><State>1</State><col>2</col></Filters><Filters><FilterID>22</FilterID><TypeName>Category</TypeName><TypeID>32</TypeID><TypeValue>Tops [Long Sleeve]</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>23</FilterID><TypeName>Category</TypeName><TypeID>33</TypeID><TypeValue>Tops [Short Sleeve]</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>24</FilterID><TypeName>Category</TypeName><TypeID>34</TypeID><TypeValue>Tops [Sleeveless]</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>25</FilterID><TypeName>Category</TypeName><TypeID>40</TypeID><TypeValue>Underwear</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>26</FilterID><TypeName>Category</TypeName><TypeID>31</TypeID><TypeValue>Vests</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>27</FilterID><TypeName>Manufacturer</TypeName><TypeID>20</TypeID><TypeValue>10 Deep</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>28</FilterID><TypeName>Manufacturer</TypeName><TypeID>22</TypeID><TypeValue>3 Sixteen</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>29</FilterID><TypeName>Manufacturer</TypeName><TypeID>52</TypeID><TypeValue>3Sixteen</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>30</FilterID><TypeName>Manufacturer</TypeName><TypeID>24</TypeID><TypeValue>Acapulco Gold</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>31</FilterID><TypeName>Manufacturer</TypeName><TypeID>42</TypeID><TypeValue>Alphanumeric</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>32</FilterID><TypeName>Manufacturer</TypeName><TypeID>30</TypeID><TypeValue>Better Off Dead</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>33</FilterID><TypeName>Manufacturer</TypeName><TypeID>46</TypeID><TypeValue>Boxfresh</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>34</FilterID><TypeName>Manufacturer</TypeName><TypeID>35</TypeID><TypeValue>Brixton</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>35</FilterID><TypeName>Manufacturer</TypeName><TypeID>45</TypeID><TypeValue>Bruxe</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>36</FilterID><TypeName>Manufacturer</TypeName><TypeID>39</TypeID><TypeValue>Commune</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>37</FilterID><TypeName>Manufacturer</TypeName><TypeID>16</TypeID><TypeValue>Creative Recreation</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>38</FilterID><TypeName>Manufacturer</TypeName><TypeID>1</TypeID><TypeValue>Crooks & Castles</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>39</FilterID><TypeName>Manufacturer</TypeName><TypeID>11</TypeID><TypeValue>Dissizit</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>40</FilterID><TypeName>Manufacturer</TypeName><TypeID>25</TypeID><TypeValue>Elm</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>41</FilterID><TypeName>Manufacturer</TypeName><TypeID>49</TypeID><TypeValue>ELM Company</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>42</FilterID><TypeName>Manufacturer</TypeName><TypeID>26</TypeID><TypeValue>Estate LA</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>43</FilterID><TypeName>Manufacturer</TypeName><TypeID>31</TypeID><TypeValue>Fallacy Of Rome</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>44</FilterID><TypeName>Manufacturer</TypeName><TypeID>29</TypeID><TypeValue>False</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>45</FilterID><TypeName>Manufacturer</TypeName><TypeID>33</TypeID><TypeValue>Five Four</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>46</FilterID><TypeName>Manufacturer</TypeName><TypeID>10</TypeID><TypeValue>Franco Shade</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>47</FilterID><TypeName>Manufacturer</TypeName><TypeID>17</TypeID><TypeValue>Frank 151</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>48</FilterID><TypeName>Manufacturer</TypeName><TypeID>27</TypeID><TypeValue>Gost</TypeValue><State>0</State><col>2</col></Filters><Filters><FilterID>49</FilterID><TypeName>Manufacturer</TypeName><TypeID>36</TypeID><TypeValue>Goumada</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>50</FilterID><TypeName>Manufacturer</TypeName><TypeID>23</TypeID><TypeValue>Hellz Bellz</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>51</FilterID><TypeName>Manufacturer</TypeName><TypeID>18</TypeID><TypeValue>Homeroom</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>52</FilterID><TypeName>Manufacturer</TypeName><TypeID>40</TypeID><TypeValue>In4mation</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>53</FilterID><TypeName>Manufacturer</TypeName><TypeID>32</TypeID><TypeValue>Know1edge</TypeValue><State>0</State><col>2</col></Filters><Filters><FilterID>54</FilterID><TypeName>Manufacturer</TypeName><TypeID>44</TypeID><TypeValue>LRG</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>55</FilterID><TypeName>Manufacturer</TypeName><TypeID>38</TypeID><TypeValue>Ludwig</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>56</FilterID><TypeName>Manufacturer</TypeName><TypeID>4</TypeID><TypeValue>Married To The Mob</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>57</FilterID><TypeName>Manufacturer</TypeName><TypeID>41</TypeID><TypeValue>Mighty Healthy</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>58</FilterID><TypeName>Manufacturer</TypeName><TypeID>47</TypeID><TypeValue>Mishka</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>59</FilterID><TypeName>Manufacturer</TypeName><TypeID>34</TypeID><TypeValue>Penfield</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>60</FilterID><TypeName>Manufacturer</TypeName><TypeID>19</TypeID><TypeValue>Second Son</TypeValue><State>0</State><col>2</col></Filters><Filters><FilterID>61</FilterID><TypeName>Manufacturer</TypeName><TypeID>5</TypeID><TypeValue>Staple</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>62</FilterID><TypeName>Manufacturer</TypeName><TypeID>12</TypeID><TypeValue>The Blackmarkers</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>63</FilterID><TypeName>Manufacturer</TypeName><TypeID>48</TypeID><TypeValue>The Drug Store</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>64</FilterID><TypeName>Manufacturer</TypeName><TypeID>43</TypeID><TypeValue>The Lovewright Co</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>65</FilterID><TypeName>Manufacturer</TypeName><TypeID>51</TypeID><TypeValue>Tits</TypeValue><State>0</State><col>2</col></Filters><Filters><FilterID>66</FilterID><TypeName>Manufacturer</TypeName><TypeID>28</TypeID><TypeValue>Triumvir</TypeValue><State>0</State><col>2</col></Filters><Filters><FilterID>67</FilterID><TypeName>Manufacturer</TypeName><TypeID>7</TypeID><TypeValue>Twelve Bar</TypeValue><State>0</State><col>2</col></Filters><Filters><FilterID>68</FilterID><TypeName>Manufacturer</TypeName><TypeID>75</TypeID><TypeValue>TwelveBar</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>69</FilterID><TypeName>Manufacturer</TypeName><TypeID>37</TypeID><TypeValue>Two In The Shirt</TypeValue><State>-1</State><col>2</col></Filters><Filters><FilterID>70</FilterID><TypeName>Color</TypeName><TypeID>1</TypeID><TypeValue>Black</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>71</FilterID><TypeName>Color</TypeName><TypeID>2</TypeID><TypeValue>Blue</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>72</FilterID><TypeName>Color</TypeName><TypeID>3</TypeID><TypeValue>Brown</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>73</FilterID><TypeName>Color</TypeName><TypeID>5</TypeID><TypeValue>Green</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>74</FilterID><TypeName>Color</TypeName><TypeID>6</TypeID><TypeValue>Grey</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>75</FilterID><TypeName>Color</TypeName><TypeID>7</TypeID><TypeValue>Multi</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>76</FilterID><TypeName>Color</TypeName><TypeID>8</TypeID><TypeValue>None</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>77</FilterID><TypeName>Color</TypeName><TypeID>9</TypeID><TypeValue>Orange</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>78</FilterID><TypeName>Color</TypeName><TypeID>11</TypeID><TypeValue>Purple</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>79</FilterID><TypeName>Color</TypeName><TypeID>12</TypeID><TypeValue>Red</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>80</FilterID><TypeName>Color</TypeName><TypeID>14</TypeID><TypeValue>White</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>81</FilterID><TypeName>Color</TypeName><TypeID>15</TypeID><TypeValue>Yellow</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>82</FilterID><TypeName>Price</TypeName><TypeID>1</TypeID><TypeValue>Under $25</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>83</FilterID><TypeName>Price</TypeName><TypeID>2</TypeID><TypeValue>$25 - $50</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>84</FilterID><TypeName>Price</TypeName><TypeID>3</TypeID><TypeValue>$50 - $75</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>85</FilterID><TypeName>Price</TypeName><TypeID>4</TypeID><TypeValue>$75 - $100</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>86</FilterID><TypeName>Price</TypeName><TypeID>5</TypeID><TypeValue>$100 - $125</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>87</FilterID><TypeName>Price</TypeName><TypeID>6</TypeID><TypeValue>$125 - $150</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>88</FilterID><TypeName>Price</TypeName><TypeID>7</TypeID><TypeValue>$150 - $175</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>89</FilterID><TypeName>Price</TypeName><TypeID>8</TypeID><TypeValue>$175 - $250</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>90</FilterID><TypeName>Price</TypeName><TypeID>9</TypeID><TypeValue>Over $250</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>91</FilterID><TypeName>Special</TypeName><TypeID>new</TypeID><TypeValue>New Arrivals</TypeValue><State>-1</State><col>1</col></Filters><Filters><FilterID>92</FilterID><TypeName>Special</TypeName><TypeID>sale</TypeID><TypeValue>Sale Items</TypeValue><State>0</State><col>1</col></Filters><Filters><FilterID>93</FilterID><TypeName>SortDir</TypeName><TypeID>ASC</TypeID><TypeValue /><State>0</State><col>1</col></Filters><Filters><FilterID>94</FilterID><TypeName>PageSize</TypeName><TypeID>20</TypeID><TypeValue /><State>0</State><col>1</col></Filters></doc>' DECLARE @Filter AS Table ( FilterID int Identity(1,1), TypeName varchar(20), TypeID varchar(20), TypeValue varchar(100), State varchar(100), col varchar(100) ) SELECT @CurrentFilter = FilterXML FROM bmfCustomerFilters WITH (NOLOCK) WHERE FilterID = '026e4a02-cc3b-48f0-89db-cb8a524b4568' select getdate() INSERT INTO @Filter (TypeName, TypeID, TypeValue, State, col) SELECT x.d.value('TypeName[1]','VARCHAR(50)'), x.d.value('TypeID[1]', 'varchar(20)'), x.d.value('TypeValue[1]', 'varchar(100)'), x.d.value('State[1]', 'varchar(100)'), x.d.value('col[1]', 'varchar(100)') FROM @CurrentFilter.nodes('/doc/Filters') x(d) select getdate()