READTEXT advantages over SELECT | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

READTEXT advantages over SELECT

Hi, I am trying to get the contents of a table (very basic). My table used to have only int and varchar fields so I just used to do a simple SELECT Title, Teaser, Content FROM etc. I converted the "Content" field to TEXT because I needed to have more than 8000 characters. While my SELECT FROM still does work, I am wondering if there would be any performance improvement in using READTEXT, and if so, how to use the READTEXT function to bring in the text together with the rest of the fields in a simple view like my SELECT statement (I must be doing it wrong because I can’t seem to do that). Or shall I just stick with SELECT ? Thanks in advance ! Julien Desmottes
READTEXT is used to handle text/image values and starting from a specified offset and reading the specified number of bytes. In conjunction with READTEXT use the TEXTPTR function to obtain a valid text_ptr value. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
So I can just assume that if I want the full text field, I’m fine and am not affecting performance by just using SELECT ?
If you’re involving TEXT datatypes then I suggest to deal with READTEXT.
Refer thru books online for more information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
The select will work fine to read the text column, although will be cut off by default 32K and can be extended to 2GB by a setting (I’m on holiday and I can’t off the top of my head remember what the setting is called) Readtext will be slower especially if you are doing it from a client machine rather than the server Cheers
Twan
Twan, ARe you referring about sp_tableoption N’MyTable’, ‘text in row’, ‘ON’?
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Funny because I didn’t use TextInRow options at all which I assume it then defaults to using 256 bytes for it (if I remember correctly), however with Select I’ve been able to retrieve over 80K of text with no problems… Julien Desmottes
I agree using READTEXT will have specific performance issues over SELECT statement, but its defined to manage TEXT/NTEXT datatypes in SQL server. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
OK, well I guess I’ll stick with SELECT for now then if performance is better, and probably change to READTEXT if I need subsets of the information… Thanks a lot !
Hi Guys, I meant SET TEXTSIZE. By default it is set to 4K, meaning text up to 4K will come back when doing a SELECT. The ODBC/OLEDB driver sets this to 2GB when you first connect. So aslong as you are using ODBC or OLEDB then you can get back up to 2GB of text data Cheers
Twan
Twan, anyway the originator opts to proceed with SELECT as of now and may consider using READTEXT if required, so its upto them to be or not to be. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
OK that’s why, since I’m accessing it through OLE DB, I’m getting all the data. I’ve tried up to 100k with select and it seems fast but I wanted to make sure I wasn’t affecting performance too much, however if Select is even faster then apart from retrieving only subsets of information then I guess I don’t see any use for READTEXT… Funny there’s no option to READTEXT and have an easy option to read the whole text.. Thanks to both of you Julien Desmottes
Julien If you’re comfortable with the current option then better to go with it.
Good luck. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>