How do I optimize a query with a text column?

Question

Post reply

How do I optimize a query with a text column?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Silly idea, I recon, but would not hurt to try:

SELECT DT.*, T.COLText_10

FROM (

SELECT

COLMAIN_ID, COL1, COL2, COL3, COL4,

COL5, COL6, COL7, COL8, COL9,

COL11, COL12, COL13, COL14,

COL15, COL16, COL17_PK, COL18, COL19, COL20,

COL21, COL22, COL23, COL24, COL25,

COL26, COL27, COL28, COL29, COL30, COL31, COL32

FROM dbo.MyTable

WHERE COL1 = 'MyValue';

) DT

INNER JOIN dbo.MyTable T ON T.COL17_PK = DT.COL17_PK

The trouble is in too expensive Bookmark Lookup operation when a text column is involved.

Therefore optimizer shoses to go with clustered index scan.

See if my trick will help to fool the optimizer. 😛

_____________
Code for TallyGenerator

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Hawkeye_DBA (3/12/2013)
Thanks,
yes, without the column it uses the index.
The execution time is 1m 24s

So, just taking Text column out of your table takes 1 m 24 seconds?

What about if you tries to query for text column by PK column? Is it taking the same time?

What is timing for query without text column?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 3

Sergiy (3/12/2013)
Silly idea, I recon, but would not hurt to try:
SELECT DT.*, T.COLText_10
FROM (
SELECT
COLMAIN_ID, COL1, COL2, COL3, COL4,
COL5, COL6, COL7, COL8, COL9,
COL11, COL12, COL13, COL14,
COL15, COL16, COL17_PK, COL18, COL19, COL20,
COL21, COL22, COL23, COL24, COL25,
COL26, COL27, COL28, COL29, COL30, COL31, COL32
FROM dbo.MyTable
WHERE COL1 = 'MyValue';
) DT
INNER JOIN dbo.MyTable T ON T.COL17_PK = DT.COL17_PK
The trouble is in too expensive Bookmark Lookup operation when a text column is involved.
Therefore optimizer shoses to go with clustered index scan.
See if my trick will help to fool the optimizer. 😛

I think Sergiy probably nailed it here. When the optimizer considers "index seek to bookmark lookup (inc. pointer for off-row pages where LOB data (i.e., text datatype) is stored) to off-row pages", it probably just decides that "table scan to off-row pages" will be cheaper, especially if you are returning more than a couple thousand rows in this query.

Divide-and-conquer may help here. Sergiy's suggestion may work, unless the optimizer just resolves it to the same execution plan as your query. If you first insert the primary key values of the rows that satisfy your WHERE clause into a temp table (possible with an index on the primary key itself), then INNER JOIN that temp table to the base table on the primary key or add WHERE EXISTS (select 1 from #tempTable where primary key = primary key) to the main query, you may get better results. You'll likely see an index seek to populate the temp table with a very efficient join operation to the base table to return the appropriate rows.

Jason Wolfkill