Performance Issue!!Please help me

Question

Post reply

Performance Issue!!Please help me

Nisha N.Kumar

Ten Centuries

Points: 1100
More actions
October 25, 2007 at 12:12 am

#126415

Hi ,
Could anyone help me out on this...
It is not possible to create index on text data type.
So once we run a query it will definitely go for table scan.Therefore it wil decrease the performance.
In order to increase the performance of the query(Change Table Scan to Seek) what should I do?
Thanks in advance

Viewing 14 posts - 1 through 13 (of 13 total)

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

okhere SSC-Addicted Points: 443 More actions · Answer 1

Hi,

If your text data is a maximum of 8000, then you may use varchar(8000) instead of text data type.

Osama

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Have you considered inplementing full-text indexing?

Could you possibly post the schema and the query so we can take a look?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nisha N.Kumar Ten Centuries Points: 1100 More actions · Answer 3

I want text data type only.My text is more than 8000 char

What should I do in this scenario?

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 4

try out these samples..

technet.microsoft.com/library/ms345118.aspx

technet.microsoft.com/en-us/library/ms345121.aspx

Nisha N.Kumar Ten Centuries Points: 1100 More actions · Answer 5

Create Table ItemDetail(Itemid int, ItemCodeDescription text)

The table contains 20 million records.

Now I want to increase the performance ie.Change the table scan to seek

for the following query

SELECT * FROM ItemDetail WHERE ItemcodeDescription like 'Chocolate%'

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

Hi

Your best bet is to created a varchar (8000 or less) column if that is possible. then create a index on that.

"Keep Trying"

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

Nisha,

I think you should think of implementing full-text indexing capabilities.

--Ramesh

okhere SSC-Addicted Points: 443 More actions · Answer 8

Hi,

So, as mentioned above, you may indeed find the best performance is to use the Ful Text Searching.

If you don't want to do that and want to explore things, at the risk of not being Best Practice, provided the number of rows is also not too large, then you may do the following:

1. estimate the maximum size. If it is more than 8000, what is it?? 80000 perhaps??

2. Define 10 columns each 8000 .. and in addition to the original Text column, which you still have, you will also store the same info again in those 10 varchar extra columns but must make sure that the long text is portioned across those 10 extra columns. You can find the last occurrence of a blank and portion based on that.

3. In all your T-SQL queries, you must use:

if Varcharcol1 like '%chocolate' or Varchacol2 like '%chocolate' or .. Varchacol10 like '%chocolate' then ...

Perhaps this lazy solution can help a bit .. but I personally would create a full text searching service as others described above.

Osama

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Full text does sound like the best option.

If you don't want to go full text, and your queries are always of the form

SELECT FROM ItemDetail WHERE ItemcodeDescription like 'abc%' then what you can try is create a varchar colum, round about 20 characters wide and put the first 20 characters of the text field in there. Then index that varchar column and change your queries so that they are of the form

DECLARE @value VARCHAR(40), @shortValue = varchar(21)

SET @value = 'Vanilla mocca coffee with hazelnut syrup'

SET @ShortValue = LEFT(@Value,20) + '%'

SET @value = @value + '%'

SELECT FROM ItemDetail WHERE ItemcodeDescription like @value and ItemcodeDescrShort like @ShortValue -- ItemcodeDescrShort is the indxes varchar(20)

That way, you should, if the data is selective enough, get a seek on that index with lookups and an additional filter based on the text field like.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Osama Kandil (10/25/2007)
3. In all your T-SQL queries, you must use:
if Varcharcol1 like '%chocolate' or Varchacol2 like '%chocolate' or .. Varchacol10 like '%chocolate' then ...

With wildcards at the begnning of the string, that's also not seekable on any index. The only way that construction can be queries is with a full table scan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

okhere SSC-Addicted Points: 443 More actions · Answer 11

Thank you. Of course. I meant it at the end of the string. But I think with 200 million records, Full Text index is the way to go.

🙂

Osama

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 12

Is there a good reason for using TEXT? It is marked for deprecation.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

Since this is a 2k5 forum... my recommendation would be to convert the date in the TEXT column to a VARCHAR(MAX) column and then implement Full-text indexing as Gail originally suggested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)