How do I optimize a query with a text column?

  • I am looking for a way to optimize a SELECT query that includes a column with a data type text. I cannot change the table.

    I am open to any and all suggestions!

    Thanks!

    HawkeyeDBA

  • Would my only option be to create a Full Text index on the table?

    Disrgard - this is irrelevant to the query.

  • What exact SELECT query you want to optimize?

    Are you doing text search? If not, then creating full text index is irrelevant.

    Why do not change your TEXT column to VARCHAR(MAX) as a first thing?

    _____________________________________________
    "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]

  • Thanks for your reply 🙂

    And, you're right, making it a full-text index is irrelevant for this query.

    I can't change the data type unfortunately, other ideas are welcome!

    Thanks again

  • i would think it depends on the query; if the query is returning a million rows with text data, there's not a lot to do;

    but depending on the query, you can do the typical things like making sure the query is SARG-able, that proper indexes exist to support the query, and consider whether any include columns would assist the query with existing indexes.

    what is the query you are running that is performing poorly? can you post the actual execution plan?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hawkeye_DBA (3/12/2013)


    Thanks for your reply 🙂

    And, you're right, making it a full-text index is irrelevant for this query.

    I can't change the data type unfortunately, other ideas are welcome!

    Thanks again

    Again, you can not OPTIMIZE some hypothetical SELECT query!

    You need to tell what query you want to optimize, what exact structure you have (so, complete DDL of table eg. indexes is required). Also, you need to include current query execution plan.

    Without the above, I guess, I have only one "hypothetical" idea for you: upgrade your server hardware.

    _____________________________________________
    "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]

  • My query is structured like this:

    SELECT

    COLMAIN_ID, COL1, COL2, COL3, COL4,

    COL5, COL6, COL7, COL8, COL9,

    COLText_10, 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';

    There's indexes on:

    -COL17_PK (Clustered)

    -COL1

    -COLMAIN_ID

    -COL16

    -COL19

    -COL30

    -COL31

    This is a vendor db so I cannot remove any indexes but I can add them.

    The execution plan is:

    Select Cost: 0% <---- Clustered Index Scan (Clustered) MyTable.COL17_PK Cost: 100%

    I only get an index Seek if I remove the Text column.

    On a side note, I need to "split" the table based on the COL1 value later on in a view so I am planning to add to new filtered indexes for that column.

  • This is not a hypothetical query. Each COL represents a real column in a real table and I posted my real execution plan.

    I will not divulge the column or table names in an open forum as this is a secure database.

    If you want to be helpful than do so, otherwise please move on to another topic as I do not have time or the desire to respond to rudeness in this community, it is simply not acceptable.

    Thank you

  • Hawkeye_DBA (3/12/2013)


    My query is structured like this:

    SELECT

    COLMAIN_ID, COL1, COL2, COL3, COL4,

    COL5, COL6, COL7, COL8, COL9,

    COLText_10, 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';

    There's indexes on:

    -COL17_PK (Clustered)

    -COL1

    -COLMAIN_ID

    -COL16

    -COL19

    -COL30

    -COL31

    This is a vendor db so I cannot remove any indexes but I can add them.

    The execution plan is:

    Select Cost: 0% <---- Clustered Index Scan (Clustered) MyTable.COL17_PK Cost: 100%

    I only get an index Seek if I remove the Text column.

    On a side note, I need to "split" the table based on the COL1 value later on in a view so I am planning to add to new filtered indexes for that column.

    Could you please provide table DDL?

    Please read the article from the link at the bottom of my signature, if you need a tip about how to do this.

    _____________________________________________
    "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]

  • If you're not searching the text column itself why not simply select the primary key column from your query into a temp table and inner join it to your original table so you at least have a subset to work with that's smaller?

  • Sure, it's:

    [dbo].[MyTable](

    COLMAIN_ID [int] NOT NULL,

    COL1 [char](12) NOT NULL,

    COL2 [char](4) NOT NULL,

    COL3 [datetime] NULL,

    COL4 [int] NOT NULL,

    COL5 [int] NOT NULL,

    COL6 [char](4) NOT NULL,

    COL7 [char](10) NOT NULL,

    COL8 [int] NOT NULL,

    COL9 [datetime] NULL,

    COLText_10 [text] NULL,

    COL11 [numeric](1, 0) NOT NULL,

    COL12 [numeric](1, 0) NOT NULL,

    COL13 [char](4) NOT NULL,

    COL14 [char](15) NOT NULL,

    COL15 [char](11) NOT NULL,

    COL16 [datetime] NULL,

    COL17_PK [int] NOT NULL,

    COL18 [char](15) NOT NULL,

    COL19 [numeric](2, 0) NOT NULL,

    COL20 [int] NOT NULL,

    COL21 [int] NOT NULL,

    COL22 [int] NOT NULL,

    COL23 [numeric](2, 0) NOT NULL,

    COL24 [int] NOT NULL,

    COL25 [char](5) NOT NULL,

    COL26 [numeric](4, 0) NOT NULL,

    COL27 [datetime] NULL,

    COL28 [datetime] NULL,

    COL29 [numeric](4, 0) NOT NULL,

    COL30 [datetime] NULL,

    COL31 [char](11) NOT NULL,

    COL32 [numeric](1, 0) NOT NULL,

    CONSTRAINT [MyTable_COL17_PK] PRIMARY KEY CLUSTERED

    (

    [COL17_PK] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  • Thank you for your suggestion, I appreciate your time and thoughts.

    I will try this and post back.

  • This took 30s longer.

    Perhaps there is nothing that can be done, there is over 2 million rows in the table and I have to select each column, including the text column.

    Poor design is all I can chalk it up to.

    Thanks again

  • So you are saying that if you remove text column from query you have index seek?

    so the following query should use index seek then:

    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';

    How slow the following query is:

    SELECT COL17_PK, COLText_10

    FROM dbo.MyTable

    WHERE COL1 = 'MyValue';

    ?

    _____________________________________________
    "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]

  • Thanks,

    yes, without the column it uses the index.

    The execution time is 1m 24s

Viewing 15 posts - 1 through 15 (of 17 total)

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