Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Query seems to be constrained by single core CPU in multiple core server - any suggestions? Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 5:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348

First of all you need to make sure the Sourcetable (a1, a2,a3,a4,a5) have clustered index on (CompanyIdentifier,FieldIDNumber)

Second, if I understand correctly those joined tables are actually the same one joined multiple times to itself. Then you better join it once and put all FieldID's into a single IN condition.
Then you may put the returned values into different columns in SELECT part using cross-tab (pivot) kind of query.

Third - the table "a" must be a derived table with grouping by CustomerIdentifier inside, so it returns a single record per Customer.

And the last.
It's just my speculation because I did not the the actual query, but I recon you do not need those joins at all.
One simple cross-tab query (considering you've got that clustered index in place) will do the job easily and quickly.
You may find good examples for such queries in BOL or on this site.

Post #1450837
Posted Thursday, May 9, 2013 4:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
Eugene Elutin (5/8/2013)JPG is not very good idea for posting query plan. You better save it as XML. You need to post complete query if you want relevant help. Also, posting DDL of involved database objects would be helpful.


Apologies for not doing this in my first post, thought it would simplify matters to just ask about a table joining on itself but I should have known better. The full query plan with obfuscated names is attached as a .sqlplan file (xml wasn't a permitted type but the xml is in the file).

Here is the complete query. I've moved the a-related filters from the JOIN clause to the WHERE clause per your suggestion:
SELECT  
a.CompanyIdentifier
, COALESCE(a.Data, a1.Data)
, COALESCE(a2.Data, a3.Data)
, a4.Data
FROM SourceTable a
LEFT JOIN SourceTable a1
ON
a.CompanyIdentifier = a1.CompanyIdentifier
AND a1.FieldIDNumber = 2
AND LEFT(a1.CompanyIdentifier, 5) != 'EXCLU'
AND a1.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
LEFT JOIN SourceTable a2
ON
a.CompanyIdentifier = a2.CompanyIdentifier
AND a2.FieldIDNumber = 3
AND LEFT(a2.CompanyIdentifier, 5) != 'EXCLU'
AND a2.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
LEFT JOIN SourceTable a3
ON
a.CompanyIdentifier = a3.CompanyIdentifier
AND a3.FieldIDNumber = 4
AND LEFT(a3.CompanyIdentifier, 5) != 'EXCLU'
AND a3.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
LEFT JOIN SourceTable a4
ON
a.CompanyIdentifier = a4.CompanyIdentifier
AND a4.FieldIDNumber = 5
AND LEFT(a4.CompanyIdentifier, 5) != 'EXCLU'
AND a4.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
WHERE
a.FieldIDNumber = 1
AND LEFT(a.CompanyIdentifier, 5) != 'EXCLU'
AND a.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
GROUP BY
a.CompanyIdentifier
, a.Data
, a1.Data
, a2.Data
, a3.Data
, a4.Data
ORDER BY
a.CompanyIdentifier
, a.Data
, a1.Data
, a2.Data
, a3.Data
, a4.Data
ASC


Here is the DDL for the table and its primary key:
CREATE TABLE SourceTable
(
CompanyIdentifier [varchar](9) NOT NULL,
PKColumn1NotPartOfQuery [int] NOT NULL,
PKColumn2NotPartOfQuery [varchar](1) NOT NULL,
PKColumn3NotPartOfQuery [smallint] NOT NULL,
FieldIDNumber [decimal](5, 0) NOT NULL,
IndicatorRecordContainsAlphanumericNoteRatherThanData [varchar](4) NOT NULL,
PKColumn4NotPartOfQuery [smallint] NOT NULL,
Data[varchar](8000) NULL,
PRIMARY KEY CLUSTERED
(
CompanyIdentifier ASC,
PKColumn1NotPartOfQuery ASC,
PKColumn2NotPartOfQuery ASC,
PKColumn3NotPartOfQuery ASC,
FieldIDNumber ASC,
IndicatorRecordContainsAlphanumericNoteRatherThanData ASC,
PKColumn4NotPartOfQuery ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
) ON [PRIMARY]



  Post Attachments 
ExecutionPlan.sqlplan (3 views, 110.22 KB)
Post #1451002
Posted Thursday, May 9, 2013 5:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
Sergiy (5/8/2013)

First of all you need to make sure the Sourcetable (a1, a2,a3,a4,a5) have clustered index on (CompanyIdentifier,FieldIDNumber)


Many thanks for your suggestions Sergiy.

Yes there is a clustered index that includes both of those columns, however it also includes a few additional columns that are not used in this particular query but which are used in other queries. Would it speed things up if I added a non-clustered index on just the columns that are used in join criteria and filters of this query, despite the fact that they are all contained in the clustered index/primary key?


Second, if I understand correctly those joined tables are actually the same one joined multiple times to itself. Then you better join it once and put all FieldID's into a single IN condition. Then you may put the returned values into different columns in SELECT part using cross-tab (pivot) kind of query.

And the last.
It's just my speculation because I did not the the actual query, but I recon you do not need those joins at all.
One simple cross-tab query (considering you've got that clustered index in place) will do the job easily and quickly.
You may find good examples for such queries in BOL or on this site.



Yes correct - it is the same table joined to itself multiple times. Interesting suggestion, I will look into this and test it.


Many thanks for your help, much appreciated.
Post #1451027
Posted Thursday, May 9, 2013 5:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
Your query doesn't look very logical, especially in its select list part as it doesn't correspond to order by.
Would be beneficial if you could post some test data (in a form of INSERT statements).
For now you can try the following query, which does the same job without any joins.

SELECT  
a.CompanyIdentifier
, COALESCE(MAX(CASE WHEN a.FieldIDNumber = 1 THEN a.Data ELSE NULL END)
,MAX(CASE WHEN a.FieldIDNumber = 2 THEN a.Data ELSE NULL END))
, COALESCE(MAX(CASE WHEN a.FieldIDNumber = 3 THEN a.Data ELSE NULL END)
,MAX(CASE WHEN a.FieldIDNumber = 4 THEN a.Data ELSE NULL END))
, MAX(CASE WHEN a.FieldIDNumber = 5 THEN a.Data ELSE NULL END) AS Data
FROM SourceTable a
WHERE LEFT(a.CompanyIdentifier, 5) != 'EXCLU'
AND a.IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
GROUP BY a.CompanyIdentifier
ORDER BY a.CompanyIdentifier

If the record with FieldIDNumber = 1 and not "EXCLU" exists for every CompanyIdentifier, the above query should produce the same results (given that only one record per CompanyIdentifier, FieldIDNumber may exist).
Also, you don't use PKColumn1NotPartOfQuery columns. What are they about?


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451028
Posted Thursday, May 9, 2013 5:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 7,231, Visits: 13,712
Here's a basic crosstab query to get you started. It might just work
SELECT 
CompanyIdentifier,
, COALESCE(Field1,Field2)
, COALESCE(Field3,Field4)
, Field5
FROM (
SELECT
CompanyIdentifier,
Field1 = MAX(CASE WHEN FieldIDNumber = 1 THEN Data END),
Field2 = MAX(CASE WHEN FieldIDNumber = 2 THEN Data END),
Field3 = MAX(CASE WHEN FieldIDNumber = 3 THEN Data END),
Field4 = MAX(CASE WHEN FieldIDNumber = 4 THEN Data END),
Field5 = MAX(CASE WHEN FieldIDNumber = 4 THEN Data END)
FROM SourceTable
--WHERE LEFT(CompanyIdentifier, 5) != 'EXCLU' -- NOT SARGABLE
WHERE CompanyIdentifier NOT LIKE 'EXCLU%' -- SARGABLE
AND IndicatorRecordContainsAlphanumericNoteRatherThanData = ''
GROUP BY CompanyIdentifier
) d
ORDER BY CompanyIdentifier

EDIT: Quick today, Eugene


“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1451029
Posted Thursday, May 9, 2013 5:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067


	--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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451036
Posted Thursday, May 9, 2013 5:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451049
Posted Thursday, May 9, 2013 6:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 7,231, Visits: 13,712
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1451076
Posted Thursday, May 9, 2013 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 8:39 AM
Points: 47, Visits: 227
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.
Post #1451099
Posted Thursday, May 9, 2013 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 7,231, Visits: 13,712
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1451101
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse