March 12, 2013 at 9:14 am
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
March 12, 2013 at 9:49 am
Would my only option be to create a Full Text index on the table?
Disrgard - this is irrelevant to the query.
March 12, 2013 at 9:56 am
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?
March 12, 2013 at 10:47 am
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
March 12, 2013 at 10:51 am
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
March 12, 2013 at 11:04 am
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.
March 12, 2013 at 11:08 am
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.
March 12, 2013 at 11:18 am
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
March 12, 2013 at 11:20 am
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.
March 12, 2013 at 11:25 am
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?
March 12, 2013 at 11:30 am
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]
March 12, 2013 at 11:31 am
Thank you for your suggestion, I appreciate your time and thoughts.
I will try this and post back.
March 12, 2013 at 11:37 am
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
March 12, 2013 at 11:47 am
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';
?
March 12, 2013 at 12:16 pm
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