Query seems to be constrained by single core CPU in multiple core server - any suggestions?

Question

Query seems to be constrained by single core CPU in multiple core server - any suggestions?

Viewing 5 posts - 16 through 19 (of 19 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

--WHERE LEFT(CompanyIdentifier, 5) != 'EXCLU' -- NOT SARGABLE
WHERE CompanyIdentifier NOT LIKE 'EXCLU%' -- SARGABLE

Yeah, I thought about this one too, but if you check attached execution plan (from previous OP post), you will find that sql managed to do clustered index seek anyway for all joins except the last one...

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Actually, looks like SUBSTRING (LEFT) can be sargable now. Check what is compiled to. Exactly the same as NOT LIKE would:

"substring([Companies].[dbo].[SourceTable].[CompanyIdentifier] as [a].[CompanyIdentifier],(1),(5))<'EXCLU' AND

substring([Companies].[dbo].[SourceTable].[CompanyIdentifier] as [a].[CompanyIdentifier],(1),(5))>'EXCLU'"

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Eugene Elutin (5/9/2013)
--WHERE LEFT(CompanyIdentifier, 5) != 'EXCLU' -- NOT SARGABLE
WHERE CompanyIdentifier NOT LIKE 'EXCLU%' -- SARGABLE
Yeah, I thought about this one too, but if you check attached execution plan (from previous OP post), you will find that sql managed to do clustered index seek anyway for all joins except the last one...

Residual predicates - they're not included in the seek predicates. I'd be careful with the assumptions here...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

caspersql SSCrazy Points: 2276 More actions · Answer 4

Thanks so much for your help Eugene, Chris, Sergiy. I've rewritten the query according to your suggestions and it now runs much more efficiently. I learned a lot in the process, you guys are great teachers.

Just to tie up a few loose ends that were discussed:

Your query doesn't look very logical, especially in its select list part as it doesn't correspond to order by.

I wasn't sure if I should put COALESCE in the ORDER BY when I first wrote the query. In my now rewritten query I've changed that so that the ORDER BY corresponds to items in the SELECT list.

Also, you don't use PKColumn1NotPartOfQuery columns. What are they about?

Those are columns related to notes on the data which I'm excluding from my results with this line:

IndicatorRecordContainsAlphanumericNoteRatherThanData = ''

They are needed as part of the key for extracting notes on the data. This happens in a separate query.

So to summarize, the following sped up my query a lot:

-- As per Eugene's suggestion in his first post, I changed FieldIDNumber column from varchar to numeric. Then I removed brackets from the filter relating to FieldIDNumber so that my server could compare numbers rather than varchar data.

-- While doing that I noticed another bottleneck relating to a filter which I hadn't included in my first post. Originally instead of the filter:

IndicatorRecordContainsAlphanumericNoteRatherThanData = ''

,I was using a filter based on a different column that was not part of the primary key. That other column also tells me whether the record is a note or not but since it is not part of the key, using it was slowing my query dramatically. Changing that sped things up, which I did prior to posting the execution plan, complete query and DDL today.

I think the above two items were the primary reasons for the hit to CPU that I mentioned in my first post. After the above changes the primary bottleneck moved to storage i/o.

-- Then as per suggestions from Sergiy, Chris & Eugene, I changed to a crosstab query to eliminate the repeated joins on the same table. That resulted in a further significant speed boost.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Thanks for the feedback, and you're welcome. An actual plan for the revised query would be very interesting - if you have the time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden