December 7, 2011 at 5:09 pm
Hey Guys
I have an issue where an application is running a few different select queries with 'like' operators on a large table in the datebase. These queries in turn are blocking any update statements that are run against the particular table. My question is: Will the queries perform better if they complete a non clustered index scan as opposed to a clusetered index scan?
December 7, 2011 at 5:17 pm
matthew.peters (12/7/2011)
Hey GuysI have an issue where an application is running a few different select queries with 'like' operators on a large table in the datebase. These queries in turn are blocking any update statements that are run against the particular table.
Yeah, probably doing a table lock due to needing to scan.
My question is: Will the queries perform better if they complete a non clustered index scan as opposed to a clusetered index scan?
Welll... ummmm... ish? Most likely: No.
If the update has to change any information in the non-clustered indexes, they'll be just as blocked. This includes clustered index columns.
Snapshot isolation to the rescue here. It's your best bet, just watch the TempDB pain you might incur. After that, it comes down to optimization to try to reduce the volume of pain you receive from those search queries. We could probably help you get some optimization out of them if we could see the underlying schema and .sqlplans, but that's up to you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2011 at 5:53 pm
Can you post the most standard searches along with it's actual execution plan?
Maybe we can turn it into a seek which would be loads better.
December 7, 2011 at 6:06 pm
Here is the query and I have attached the plan. I assumed a like operator required a index scan? Anyway any help would be appreciated, I have created a non clustered index which the query uses as the primary source for the data
SELECT "PC0".pxObjClass AS "pxObjClass", "PC0".pzPVStream AS "PC0pzPVStream", "PC0".pzInsKey as "pxInsHandle"
FROM pca_work "PC0"
WHERE ( "PC0".UWType LIKE '%CoverNote%' )
AND ( "PC0".pxObjClass LIKE '%Wesfarmers-Ins-Work-Policy-Com-Business-Broker-NB%' )
AND ( "PC0".pxObjClass NOT LIKE '%ren%' )
AND "PC0".IsIntUWPolicy IS NULL
ORDER BY "PC0".pxCreateDateTime
December 7, 2011 at 6:47 pm
LIKE 'string%' (no % at the begining) can use a seek. Those will indeed scan.
That's an estimated plan, I need the actual one.
What you can do with search queries is optimize the "standard" filters and then hope the like '%whatever%' won't kill it. Since you have all and and no ors it could have been possible for you here.
December 7, 2011 at 6:54 pm
matthew.peters (12/7/2011)
Here is the query and I have attached the plan. I assumed a like operator required a index scan? Anyway any help would be appreciated, I have created a non clustered index which the query uses as the primary source for the dataSELECT "PC0".pxObjClass AS "pxObjClass", "PC0".pzPVStream AS "PC0pzPVStream", "PC0".pzInsKey as "pxInsHandle"
FROM pca_work "PC0"
WHERE ( "PC0".UWType LIKE '%CoverNote%' )
AND ( "PC0".pxObjClass LIKE '%Wesfarmers-Ins-Work-Policy-Com-Business-Broker-NB%' )
AND ( "PC0".pxObjClass NOT LIKE '%ren%' )
AND "PC0".IsIntUWPolicy IS NULL
ORDER BY "PC0".pxCreateDateTime
As Ninja said... like 'xyz%' can seek. like '%xyz' can't. This is basically non-optimisable. I don't know how wide pca_work is however so an index covering the needed columns might at least speed things up, but might not, but you're on the right track.
The only piece of this I might see being helpful is making sure that IsIntUWPolicy is the leading edge of your index, but only if it consistently checks for NULL.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 15, 2011 at 8:47 pm
Hey Guys apologies for the delay, I've uploaded the proper execution plan. Thanks for all the help so far
December 15, 2011 at 11:07 pm
As stated before, if you could get rid of the leading % it may be able to do a seek on the non-clustered index idx_nonClus_pca_work3. If you can INCLUDE the pzInsHandle and pzPVStream columns in idx_nonClus_pca_work3 you could also remove the need to do a Key Lookup. This will increase the index size and possibly the index maintenance, but that is something you can measure and decide on.
If dirty reads are acceptable, the NOLOCK table hint could be a simple work around, or you could look at Snapshot isolation as suggested above.
December 15, 2011 at 11:12 pm
Hey Thanks for the reply, I was actually looking to turn off table locking for the specific tables as there was some clear evidence in some traces that I ran that lock escalation was occurring as a result of these queiries and in turn blocking any update statements. However I will look into snapshot isolation as well
December 16, 2011 at 4:12 am
.
December 16, 2011 at 5:21 am
Evil Kraig F (12/7/2011)
matthew.peters (12/7/2011)
Here is the query and I have attached the plan. I assumed a like operator required a index scan? Anyway any help would be appreciated, I have created a non clustered index which the query uses as the primary source for the dataSELECT "PC0".pxObjClass AS "pxObjClass", "PC0".pzPVStream AS "PC0pzPVStream", "PC0".pzInsKey as "pxInsHandle"
FROM pca_work "PC0"
WHERE ( "PC0".UWType LIKE '%CoverNote%' )
AND ( "PC0".pxObjClass LIKE '%Wesfarmers-Ins-Work-Policy-Com-Business-Broker-NB%' )
AND ( "PC0".pxObjClass NOT LIKE '%ren%' )
AND "PC0".IsIntUWPolicy IS NULL
ORDER BY "PC0".pxCreateDateTime
As Ninja said... like 'xyz%' can seek. like '%xyz' can't. This is basically non-optimisable. I don't know how wide pca_work is however so an index covering the needed columns might at least speed things up, but might not, but you're on the right track.
The only piece of this I might see being helpful is making sure that IsIntUWPolicy is the leading edge of your index, but only if it consistently checks for NULL.
Wondering,
what you are saying is that like '%abc' cannot be optimisable
during a quick test:
like '%abc% is table scanning
like 'abc%' is using index
like '%abc' is using index
Or am i missunderstanding something in the explaination?
December 16, 2011 at 5:25 am
Few things are not optimizable at all.
With a covering index, '%abc%' would still SCAN, but it would likely be a faster scan than a full table scan.
'abc%' = possible seek*
'abc' = possible seek*
'%abc' normally scans but there's a trick to convert that into a seek. care to take a guess? 🙂
*Possible seek => http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx
December 16, 2011 at 7:58 am
I tried but could not see your plan for some reason and also therefore cannot determine how many rows you are scanning. However, just wondering if you can improve performance by using an index on the date column along with a BETWEEN to narrow the row count. Also, if you are consistently looking for rows including certain values is it possible to add a separate indexed column to the table for those well-known values?
December 16, 2011 at 8:02 am
deanroush (12/16/2011)
I tried but could not see your plan for some reason and also therefore cannot determine how many rows you are scanning. However, just wondering if you can improve performance by using an index on the date column along with a BETWEEN to narrow the row count. Also, if you are consistently looking for rows including certain values is it possible to add a separate indexed column to the table for those well-known values?
The plan works for me. Did you save as .sqlplan? This webserver's converts the extension to .xml making it a little more useless.
I don't see any filter on dates so I'm not sure what you are talking about.
December 16, 2011 at 8:03 am
matthew.peters (12/7/2011)
Hey GuysI have an issue where an application is running a few different select queries with 'like' operators on a large table in the datebase. These queries in turn are blocking any update statements that are run against the particular table. My question is: Will the queries perform better if they complete a non clustered index scan as opposed to a clusetered index scan?
Any reason why you're no using snapshot isolation for the read operations?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply