473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Text column slows down select

Hi All,

We're running SQL Server 2000, SP3.

I have a stored procedure that consists of a single Select statement.
It selects a bunch of columns one of which is a column of data type
TEXT.
SP takes 30 sec to run which causes timeouts on the Front End.
When I comment out the Text column from the select it only takes 1
Sec.
Is there anything I can do about it? I know I can't index a Text
column. It's also not used in the where clause, so no need for
Full-Text Search.
But we absolutely have to have it in the Select clause.
Thanks for the help in advance.

~Narine
Jul 20 '05 #1
5 2544
Return less rows at once?

It's probably slow because it's sending back a lot of data -- send back less
data and it will be faster.
"Narine" <na***************@prurealty.com> wrote in message
news:56*************************@posting.google.co m...
Hi All,

We're running SQL Server 2000, SP3.

I have a stored procedure that consists of a single Select statement.
It selects a bunch of columns one of which is a column of data type
TEXT.
SP takes 30 sec to run which causes timeouts on the Front End.
When I comment out the Text column from the select it only takes 1
Sec.
Is there anything I can do about it? I know I can't index a Text
column. It's also not used in the where clause, so no need for
Full-Text Search.
But we absolutely have to have it in the Select clause.
Thanks for the help in advance.

~Narine

Jul 20 '05 #2
Performance wise Less is More but lso try deferring the select of the text.

If the front end displays a list of entries (e.g. Grid) and the text in a
seperate control, which gets displayed as the grid is scrolled down ...
retrieve into the grid and only select the first one. As the user changes
the selected row, retrieve just the displayed text data item.

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:cj**********@bob.news.rcn.net...
Return less rows at once?

It's probably slow because it's sending back a lot of data -- send back less data and it will be faster.
"Narine" <na***************@prurealty.com> wrote in message
news:56*************************@posting.google.co m...
Hi All,

We're running SQL Server 2000, SP3.

I have a stored procedure that consists of a single Select statement.
It selects a bunch of columns one of which is a column of data type
TEXT.
SP takes 30 sec to run which causes timeouts on the Front End.
When I comment out the Text column from the select it only takes 1
Sec.
Is there anything I can do about it? I know I can't index a Text
column. It's also not used in the where clause, so no need for
Full-Text Search.
But we absolutely have to have it in the Select clause.
Thanks for the help in advance.

~Narine


Jul 20 '05 #3

"David Rawheiser" <ra*******@hotmail.com> wrote in message
news:%u********************@bgtnsc05-news.ops.worldnet.att.net...
Performance wise Less is More but lso try deferring the select of the text.
If the front end displays a list of entries (e.g. Grid) and the text in a
seperate control, which gets displayed as the grid is scrolled down ...
retrieve into the grid and only select the first one. As the user changes
the selected row, retrieve just the displayed text data item.


I agree, but remember, there's also a tradeoff due to network round
trips, server connection, etc -- it takes time to retrieve any data every
time you make another request to the server. So the OP should test very
carefully to make sure that network traffic doesn't slow down the app when
more db calls are made. This is especially important in web-based
situations.
Jul 20 '05 #4
I assumed that he would test any advice given. The root of both of our
answers is less is more.

Perhaps I need to create a signature block for my postings - Any feed back
on the following would be appreciated.

1.Your mileage may vary.
2. Past results do not guarantee future returns.
3. Some settling of contents may have occurred during shipping.
4. This free advice come with an infinite money back guarantee, (as any
number time zero remains zero).
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:cj**********@bob.news.rcn.net...

"David Rawheiser" <ra*******@hotmail.com> wrote in message
news:%u********************@bgtnsc05-news.ops.worldnet.att.net...
Performance wise Less is More but lso try deferring the select of the

text.

If the front end displays a list of entries (e.g. Grid) and the text in a seperate control, which gets displayed as the grid is scrolled down ...
retrieve into the grid and only select the first one. As the user changes the selected row, retrieve just the displayed text data item.


I agree, but remember, there's also a tradeoff due to network round
trips, server connection, etc -- it takes time to retrieve any data every
time you make another request to the server. So the OP should test very
carefully to make sure that network traffic doesn't slow down the app when
more db calls are made. This is especially important in web-based
situations.

Jul 20 '05 #5

"David Rawheiser" <ra*******@hotmail.com> wrote in message
news:qX*********************@bgtnsc05-news.ops.worldnet.att.net...

Perhaps I need to create a signature block for my postings - Any feed back
on the following would be appreciated.


Personally, if I were to create that, it would have something along the
lines of: "Stop being so lazy, roll up your sleeves, and do it yourself!"
:)
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Richard Galli | last post by:
I want viewers to compare state laws on a single subject. Imagine a three-column table with a drop-down box on the top. A viewer selects a state from the list, and that state's text fills the...
11
by: Dan | last post by:
Hello all, I am getting records from a db and displaying the records to the user through a drop down menu in an asp page. Each record has 6 fields and I need to display them all to the user in...
3
by: google | last post by:
This is something I've done plenty of times in '97, but I can't seem to get it to work correctly in Access 2003. Say, for example, I have a form with an unbound combobox, the data source is a...
0
by: Nithin | last post by:
My code as an txt attachment. I have 2 drop down list boxes that on selection populate text boxes from my database table. I am able to display the correct values in these text boxes. I have 2...
8
by: Frank van Vugt | last post by:
Hi all, Boiling down a problem in one of my queries, I noticed this behaviour. # select version(); version ------------------------------------------------------------------------...
10
by: lorirobn | last post by:
Hi, I have a form with several combo boxes, continuous form format, with record source a query off an Item Table. The fields are Category, Subcategory, and Color. I am displaying descriptions,...
4
by: rszebras | last post by:
I inherited a database (as a novice at Access) and need to modify it to make it more efficient, i.e., the assignment form needs to autopopulate with the client's name, address, phone number, etc.,...
6
by: BerkshireGuy | last post by:
On an unbound form, I have a combobox called 'cboproducttype' and a text box called 'txtamountappliedfor'. I have an Add button that I would like the user to be able to hit once a product and...
0
by: JamesOo | last post by:
I have the code below, but I need to make it searchable in query table, below code only allowed seach the table which in show mdb only. (i.e. have 3 table, but only can search either one only,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.