SQL Server Performance

Performance Issue with ntext

Discussion in 'T-SQL Performance Tuning for Developers' started by bomma_murali, Jun 6, 2005.

  1. bomma_murali New Member

    Hi All,

    I am doing an project in which we need to capture huge text (more than 8000 chars).
    so I used ntext as the column data type.
    while using this column in simple select queries it is taking long time to get the results displayed.
    Could any one help me in optimizing the query to get the results faster.

    Thanks in advance.

    Regards,
    Murali
  2. dineshasanka Moderator

    Murali,
    is there any possibility of using text type instead of ntext Other than that there is no way (As I know) to optimize the performance. You can't have indexes on ntext field either

    In Addition, when using select try to select only the necessary fields.
  3. satya Moderator

    Are you sure the data will be constantly more than 8000 chars for that column?

    Books online outlines the tip of using SP_TABLEOPTION in this regard to gain the performance from the columns that uses text/ntext.

    By default, ntext or text datatype data is not stored with the rest of your row's data in the table because that data can exceed well over a gigabtye. Instead, ntext or text data has a pointer stored in the row to indentify the data page where the actual data is stored.

    You can execute this SP as:

    EXEC sp_tableoption 'mytablename', 'text in row', '6500'

    This will make the adjustment the next time the data is updated.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. FrankKalis Moderator

  5. surendrakalekar New Member

    Use text field if it is possible.
    If it is possible and that column is not used frequently, try to keep that column(ntext) in separate table and make relationship between two tables.


    Surendra Kalekar

  6. bomma_murali New Member

    Hi dineshasanka,

    I can use text but the data should be unicode. I am not aware of the impact of changing the ntext to text. Could you tell me will it cause any data loss.

    Also I am selecting necessary fields in the select statements.

    Regards,
    Murali
  7. bomma_murali New Member

    Hi Satya,Frank,

    Thanks for the suggestion .
    I will try the "text in row" option.

    Frank: My query is a simple select statement.
    FYI
    the query I am using to fetch the records is.
    "select case_number_id,Generic_text,user_response from generic_answers order by case_number_ID"

    In the above query "user_response" column is of ntext data type.
    Currently there are 19000 records in the DB.

    The above query takes 2min time to fetch all the records.
    In the above mentioned table case_number_id is a foreign key.

    could you suggest me a solution.

    Regards,
    Murali
  8. dineshasanka Moderator

    yes there is no way that you can change it to text as you are using unicode
  9. mmarovic Active Member

    Why do you need to return all rows from the table? Can you redesign your process/GUI to select just top N rows?
  10. ranjitjain New Member

    Hi Murali,
    I think one more thing you can change in the query is removing orde by clause.
    Do this ordering on client side as removing this will help in performance.


    quote:Originally posted by bomma_murali
    the query I am using to fetch the records is.
    "select case_number_id,Generic_text,user_response from generic_answers order by case_number_ID"

    could you suggest me a solution.
    Regards,
    Murali
  11. FrankKalis Moderator

    Piggy-backing on mmarovic: Is there really no need for a WHERE clause? Retrieving 19,000 rows with BLOBS data over a network sounds like a lot of overhead to me.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  12. satya Moderator

    Are you sure the data will be constantly more than 8000 chars for that column?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. bomma_murali New Member

    Hi All,

    Satya:No,the data will not be constantly more than 8000 chars for the "user_response" column.
    It is the clients requirement that they dont want any limit to the data entered.
    That's why we used ntext.

    Frank: Yes, there is a need for a where clause. but there is one senario that doesnot require where clause.
    My idea is to achieve performance for the whole data than the filtered data.

    If I am going wrong any where please correct me.

    Regards,
    Murali

  14. satya Moderator

    If the data is not more than 8000 chars then its better to use VARCHAR for performance consideration.
    You can control/manage the entered data divide into 2 columns if required.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. mmarovic Active Member

    quote:My idea is to achieve performance for the whole data than the filtered data.
    Do you mean you read all data and filter them on client? If so, that is not very good idea.
  16. bomma_murali New Member

    Hi All,

    Mmarovic:No,I will filter first from the client itself then pass the query to the DB.
    The performance issue I am facing is in generating the reports.
    Here the end user will select the date range and some products. In the products list there is an all option.
    IF the user selects "all" option I need to pull all the available data from the DB.
    My idea is to achive the performance to the "all" option i.e if I can achieve all option, the other options also works fine right?

    Please correct me if I am wrong in explaining the issue.

    Regards,
    Murali

  17. mmarovic Active Member

    That report doesn't produce any aggregation since it is returning text column. It means user can choose to review 19000 rows. After he has 19000 rows displayed he will never review more then 50, maybe 100. Better change your strategy. Either force user to filter rows based on additional criteria or try some paging technique.
  18. FrankKalis Moderator

    In addition to that, it might be a good idea if you retrieve the text column only when requested by the user and not generally. I think nobody will read a report containing 19,000 columns without any aggregation.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  19. mmarovic Active Member

    Good idea Frank. In other words if you insist to display 19000 rows, at least display text data later on demand.

Share This Page