March 4, 2008 at 12:19 pm
Well then - you can stop looking - because there's your reason. In general - you should be avoiding running searches on things that aren't/can't be indexed. Running a pattern search on something as big as a NTEXT is gonig to completely derail your performance.
At this point - you might care to look at a full-text index or at switching your NTEXT to something manageable. Otherwise - you're "stuck" with SLOOOOOOOOW scan operations to find stuff.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 12:20 pm
I don't think you are going to get much better improvement. Also, it is possible a nonclustered index on [State] and [InActive:] columns may have been a better choice since [Incident Description] can't be included in the index. With the clustered index you are actually traversing the data pages, and those appear to be hugh from looking at the DDL of the underlying table.
There should be a better choice for a clustered index on the table, but that isn't something I am going to even try to tackle.
{Edit}
I also agree with what Matt is saying above.
😎
March 4, 2008 at 12:24 pm
Add a full-text index to the description field, add a regular index to the other two fields in the Where clause, and take all the excess selects out of the sub-query. That's how you'll get the best results you're going to get.
Look up full-text indexing in Books Online, that'll get you started on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2008 at 12:37 pm
That view DDL does not reference the table DDL that was provided. Is this important?
If it was easy, everybody would be doing it!;)
March 4, 2008 at 1:03 pm
You know, I didn't even notice that, but with the lack of formatting, it was easy to miss.
😎
March 4, 2008 at 1:17 pm
Trader Sam (3/4/2008)
That view DDL does not reference the table DDL that was provided. Is this important?
I have to admit, I didn't even try to read that view and parse it. No way. There isn't enough aspirin in the world.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 4, 2008 at 1:24 pm
GSquared (3/4/2008)
Trader Sam (3/4/2008)
That view DDL does not reference the table DDL that was provided. Is this important?I have to admit, I didn't even try to read that view and parse it. No way. There isn't enough aspirin in the world.
Neither did I - especially with the quotes everywhere. Just makes it too darned difficult to read.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 1:29 pm
I agree with you there, G. I noticed the last 3 words were the 'from', and it wasn't the same table as the one in the table DDL. Then I got to looking for the field 'Incident Description' 'cause it was a 'like' in the original query and I wanted to see how big the field was (thinking that may be a source of issues), and it wasn't in the table either, but it was in the view (I didn't read all that gobby-d-gook, I used IE's 'Find on this page'). 😎
If it was easy, everybody would be doing it!;)
March 4, 2008 at 2:07 pm
I noticed this is 3rd party software (Magic or perhaps SDE). If you have vendor support, call them and have them help. They have helped me in the past.
-- Cory
March 4, 2008 at 2:52 pm
I added full text index on the DESCRIPTION column and ran the query. It did not improve the performance.
I believe syntax is also different from the query while using full text indexes, and the same query will not make use of the full text index on the DESCRIPTION column.
March 4, 2008 at 3:06 pm
Look in BOL (Books Online) for CONTAINS.
😎
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply