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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2007-06-22 : 12:16:34
|
| I have table with one text column, five int columns, five char columns, and four datetime columns. The average number of bytes per row for the text column is probably around 6000 (it's storing an xml doc, don't ask me why). I believe the table is indexed as well as possible, but when I do a query like the following, it takes several minutes: SELECT xmlColumn FROM table WHERE intColumn = 27Mainly my question is this: I tried setting 'text in row' on for this table, as this page makes it sound like even if the total bytes for the column exceed the limit, the root structure is stored in-row and it should still be faster than having 'text in row' off.http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=trueBut my queries are even slower after turning it on, why would that be? |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2007-06-22 : 14:49:41
|
| What is your text in row value set to? I am willing to bet it is not a small number... So instead of holding just a pointer, every row now contains a huge XML doc... Longer rows means less rows per page... Less rows per page -- longer scan time... So check your query plan -- your query is probably scanning instead of doing index lookup. If there is an index on intColumn -- make sure your query is using it (update stats), if not -- consider adding one. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-22 : 15:47:55
|
| Yes, I set it at 7000 because the average row has 6000 bytes. I figured that was the the point, if the row contained the data it would be faster than if it was off-row, but I see what you're saying. There is an index on intColumn, though by default a table scan occurs instead of using the index, even after I update statistics. If I force it to use that index, a bookmark lookup occurs, which is actually faster, but only by about 2%. Those last two sentences are true both before and after turning on "text in row." I'll try lowering it from 7000 to 256 and see what happens. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-22 : 16:50:35
|
| Didn't help, same result. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2007-06-25 : 12:31:01
|
| I just discovered that if I insert the data in question into a temp table and then drop the temp table, that takes 75% less time than returning the data to Query Analyzer. Also if I run the query in QA on the server it takes 50% less time. Doesn't that mean that most of the time consumed by the original query is for transferring the data from the server to my computer? |
|
|
|
|
|
|
|
|
text in row