January 28, 2009 at 12:47 pm
I have a reporting SQL server that has 16 processors.
It is used only for running 3 report queries, that are pretty long intensive statements, that bring back several hundered thousand rows.
I noticed that the Degree of Parellism is set at the default 0.
I was thinking of experimenting with my three queries, by adding the MAXDOP hint.
I would then test them and see if the return times improved.
What do you all think?
And is there a way I can add the hint to the top of each query, or do I need to add it to many parts throughout the query? (after every orderby for example)
Thanks!
January 28, 2009 at 1:36 pm
MAXDOP is a QUERY option
So you have to specify it per-query (at the end of the query)
I am not sure that in your case you want to do that!
* Noel
January 28, 2009 at 1:44 pm
Thanks Noel.
I was really just going to give it a shot and see what happens.
Here's the proc, sorry it's a long one..
Can anyone tell me where the hint would go?
ALTER PROC [dbo].[rpt_OrderSummary_rob]
@fromDate DATETIME,
@thruDate DATETIME,
@companyCRN VARCHAR(150) = NULL,
@deptCRN VARCHAR(50) = NULL,
@reportType VARCHAR(50) = 'Company Summary',
@excludeInvalidCRNs BIT = 1,
@reportID INT = NULL,
@reportTitle VARCHAR(255) = NULL
--DECLARE@fromDate DATETIME,
--@thruDate DATETIME,
--@companyCRN VARCHAR(150),
--@deptCRN VARCHAR(50),
--@reportType VARCHAR(50),
--@excludeInvalidCRNs BIT,
--@reportID INT,
--@reportTitle VARCHAR(255)
--
--set @fromDate = '11/01/2008'
--set @thruDate = '11/20/2008 11:59:00 PM'
--set @companyCRN = '00021 | Chicago Title'
--set @deptCRN = 'ALL'
--set @reportType = 'Order Summary' -- Types: Company Summary, Order Summary
--set @excludeInvalidCRNs = 1
--set @reportID = NULL
--set @reportTitle = NULL
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF (@fromDate IS NULL) BEGIN SELECT @fromDate = MIN(eventTime) FROM eventItems_rob END
ELSE SET @fromDate = CAST(CONVERT (varchar, @fromDate, 101) + ' 00:00:00 AM' AS DATETIME)
IF (@thruDate IS NULL) BEGIN SELECT @thruDate = MAX(eventTime) FROM eventItems_Rob END
ELSE SET @thruDate = CAST(CONVERT (varchar, @thruDate, 101) + ' 11:59:59.997 PM' AS DATETIME)
IF (LTRIM(RTRIM(@companyCRN)) = 'ALL') BEGIN SET @companyCRN = NULL END
IF (LTRIM(RTRIM(@deptCRN)) = 'ALL') BEGIN SET @deptCRN = NULL END
DECLARE @company VARCHAR(100), @fromCoCRN VARCHAR(50), @thruCoCRN VARCHAR(50)
IF @companyCRN IS NOT NULL BEGIN
SET @company = SUBSTRING(@companyCRN,CHARINDEX('|',@companyCRN) + 2,150)
SET @companyCRN = SUBSTRING(@companyCRN,1, CHARINDEX('|',@companyCRN) - 2)
END
-- Check if this report is limitted by ranges of company CRNs.
IF @reportID IS NULL BEGIN
SELECT @reportID = reportID FROM reports (NOLOCK) WHERE reportTitle = @reportTitle
END
SELECT @fromCoCRN = fromCompanyCRN FROM reportCRNRanges WHERE reportID = @reportID
SELECT @thruCoCRN = thruCompanyCRN FROM reportCRNRanges WHERE reportID = @reportID
create table #events (
logIDINT,
coIDINT,
deptIDINT,
coCRNVARCHAR(50),
deptCRNVARCHAR(50),
serviceIDINT,
stateVARCHAR(50),
countyVARCHAR(100),
orderIDINT,
orderNoVARCHAR(128),
userIDINT,
eventVARCHAR(256),
eventValueVARCHAR(150),
sourceVARCHAR(50),
extTaxInfoSearchBIT DEFAULT 0,
excludeCurrPlantBIT DEFAULT 0,
includeBackPlantBIT DEFAULT 0,
backPlantImageBIT DEFAULT 0,
startTimeDATETIME,
endTimeDATETIME,
PI2LineCountINT,
eventDuration DATETIME,
DurationINT,
companyVARCHAR(50),
departmentVARCHAR(50),
titleUnitVARCHAR(50)
)
INSERT #events
EXEC GetTPDetails_rob_new
@fromDate
,@thruDate
,@companyCRN
,@deptCRN
,@fromCoCRN
,@thruCoCRN
-- Identify orders converted from Super-Search and don't count them as new orders.
CREATE TABLE #convertedOrders (
orderIDINT,
orderNoVARCHAR(128),
plantOrderBIT DEFAULT 0,
taxOrderBIT DEFAULT 0
)
INSERT INTO #convertedOrders
SELECT DISTINCT e.orderID, e.orderNo, 0, 0
FROM #events e JOIN eventInformation_rob i ON i.logID = e.logID
WHERE i.infoName LIKE 'Imported%'
CREATE NONCLUSTERED INDEX pk_ORDERID ON #convertedOrders(ORDERID asc)
UPDATE #convertedOrders SET plantOrder = 1
FROM #convertedOrders c
JOIN eventItems_rob ei ON ei.orderID = c.orderID
JOIN eventInformation_rob i ON i.logID = ei.logID
WHERE i.infoName IN ('ImportedNameSearches','ImportedPropertySearches')
UPDATE #convertedOrders SET taxOrder = 1
FROM #convertedOrders c
JOIN eventItems_rob ei ON ei.orderID = c.orderID
JOIN eventInformation_rob i ON i.logID = ei.logID
WHERE i.infoName = 'ImportedTaxSearches'
UPDATE #events SET orderNo = ei.orderNo
FROM #events e
JOIN eventItems_rob ei ON ei.eventValue = e.serviceID
WHERE e.orderNo = '[NONE]' AND ei.eventTime BETWEEN @fromDate AND @thruDate AND ei.orderNo <> '[NONE]'
AND (ei.event = 'ServiceAddedToOrder' OR ei.event LIKE 'Search-%')
AND ISNUMERIC(ei.eventValue) = 1
-- Identify back plant requests
UPDATE #events SET excludeCurrPlant = 1 FROM #events e JOIN eventInformation_rob i ON i.logID = e.logID WHERE i.infoName = 'ExcludeCurrentPlant'
UPDATE #events SET includeBackPlant = 1 FROM #events e JOIN eventInformation_rob i ON i.logID = e.logID WHERE i.infoName = 'IncludeBackPlant'
UPDATE #events SET backPlantImage = 1 WHERE event LIKE 'Image-Backplant%'
-- Translate the county abbreviations.
UPDATE #events SET county = dbo.GetCountyAbbrev(state, county)
-- Assign a single user to all events associated with each order so that we can prevent
--extra rows being returned when we group the records below. Otherwise, we will count
--the same order multiple times just because several people performed name/property/tax
--searches on it.
-- For this solution the first user that touched the order during the reporting period
--will be considered the primary user.
------------------------------------------------------------
--JMR 3/31/2007 Speed up this portion as row counts increase
CREATE NONCLUSTERED INDEX __ix_order ON #events
(county ASC, coCRN ASC, deptCRN ASC, orderNo ASC)
------------------------------------------------------------
UPDATE #events SET userID =
(SELECT TOP 1 userID FROM #events e2
WHERE e2.coCRN = e.coCRN AND e2.deptCRN = e.deptCRN AND e2.county = e.county AND e2.orderNo = e.orderNo
ORDER BY e2.logID)
FROM #events e
create TABLE #summary (
companyCRNVARCHAR(50),
deptCRNVARCHAR(50),
companyVARCHAR(50),
deptVARCHAR(50),
countyVARCHAR(100),
titleUnitVARCHAR(100),
userIDINT,
userNameVARCHAR(50),
orderNoVARCHAR(128),
plantOrderINT,
taxOrderINT,
inquiryCntINT,
cnpCntINT,
xrefCntINT,
nameCntINT,
propertyCntINT,-- Just the current plant
propertyBackPlantCntINT,-- Just the back plant
propertyBothPlantCntINT,-- Both current and back plants
taxCntINT,
addressCntINT,
documentCntINT,
ownerCntINT,
corpCntINT,
corpListCntINT,
notaryCntINT,
notaryListCntINT,
partnerCntINT,
partnerListCntINT,
ggCntINT,
recordedDocsINT,
mapsINT,
starterTicorINT,
starterOtherINT,
backPlantImageCntINT,
odiCntINT,
userCompanyVARCHAR(50),
plantOrderCreatedDATETIME,
taxOrderCreatedDATETIME,
extTaxInfoSearchCntINT,
source VARCHAR(50),
startTimeDATETIME,
endTimeDATETIME,
ODSPageCntINT,
DurationDATETIME
)
INSERT INTO #summary
SELECT e.coCRN, e.deptCRN, ei.company, ei.department, e.county,
ei.titleUnit, e.userID, NULL, e.orderNo, 0, 0, 0, 0, 0,
SUM(CASE WHEN ei.event = 'Search-Name' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Property' AND e.excludeCurrPlant = 0 AND e.includeBackPlant = 0THEN 1 ELSE 0 END),-- Just the current plant
SUM(CASE WHEN ei.event = 'Search-Property' AND e.excludeCurrPlant = 1 AND e.includeBackPlant = 1THEN 1 ELSE 0 END),-- Just the back plant
SUM(CASE WHEN ei.event = 'Search-Property' AND e.excludeCurrPlant = 0 AND e.includeBackPlant = 1THEN 1 ELSE 0 END),-- Both current and back plants
SUM(CASE WHEN ei.event = 'Search-Tax' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Tax-Address' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Instrument' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Tax-Owner'THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Corp-Detail' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Corp-List'THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Notary-Detail' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Notary-List' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Partnership-Detail' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-Partnership-List' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Search-GrantorGrantee'THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Image-Recorded'THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event LIKE 'Image-Map-%' THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Starter-Ticor'THEN 1 ELSE 0 END),
SUM(CASE WHEN ei.event = 'Starter' OR ei.event = 'Starter-Safeco' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.backPlantImage = 1 THEN 1 ELSE 0 END),-- Back plant image
SUM(CASE WHEN ei.event = 'ODSRequestFulfilled' THEN 1 ELSE 0 END),
NULL, NULL, NULL,
SUM(CASE WHEN e.extTaxInfoSearch = 1THEN 1 ELSE 0 END), e.Source, NULL, NULL,
SUM(e.PI2LineCount), NULL
FROM #events e
JOIN eventItems_rob ei (NOLOCK) ON ei.logID = e.logID
GROUP BY e.coCRN, e.deptCRN, ei.company, ei.department, e.county, ei.titleUnit, e.userID, e.orderNo, e.Source
/*******************************************/
--UPDATE #summary SET userName = ei.userName, userCompany = ei.userCompany
--FROM #summary s
--JOIN eventItems ei (NOLOCK) ON ei.userID = s.userID
--WHERE ei.eventTime BETWEEN @fromDate AND @thruDate
UPDATE #summary
SET userName = --ei.userName
(select top 1 username from eventitems_rob ei where ei.userid=s.userid
and ei.eventTime BETWEEN @fromdate AND @thrudate)
,userCompany =
(select top 1 userCompany from eventitems_rob ei where ei.userid=s.userid
and ei.eventTime BETWEEN @fromdate AND @thrudate)
from #summary s
/*****************************/
--Updating the PlantOrderCreated Date
UPDATE #summary
SET plantOrderCreated = updValueTable.EventTime
FROM
#summary tempSummary,
(SELECT
a.CompanyCRN,a.DeptCRN,a.OrderNo,a.Source,MIN(b.EventTime) EventTime
FROM
#summary a JOIN EventItems_rob b
ON a.CompanyCRN = b.CompanyCRN AND a.DeptCRN = b.DepartmentCRN
AND a.OrderNo = b.OrderNo
WHERE
(a.propertyCnt > 0 OR propertyBackPlantCnt > 0 OR propertyBothPlantCnt > 0 OR a.nameCnt > 0)
AND a.OrderNo != '[NONE]' AND a.OrderNo IS NOT NULL
-- Exclude orders converted from Super-Search.
AND b.orderID NOT IN (SELECT orderID FROM #convertedOrders c WHERE c.plantOrder = 1)
GROUP BY
a.CompanyCRN,a.DeptCRN,a.OrderNo,a.Source
) updValueTable
WHERE
tempSummary.CompanyCRN = updValueTable.CompanyCRN
AND tempSummary.DeptCRN = updValueTable.DeptCRN
AND tempSummary.OrderNo = updValueTable.OrderNo
AND (ISNULL(tempSummary.PropertyCnt,0) >= 1 OR ISNULL(tempSummary.propertyBackPlantCnt,0) >= 1 OR ISNULL(tempSummary.propertyBothPlantCnt,0) >= 1 OR ISNULL(tempSummary.NameCnt,0) >= 1)
--Updating the TaxOrderCreated Date
UPDATE #summary
SET taxOrderCreated = updValueTable.EventTime
FROM
#summary tempSummary,
(SELECT
a.CompanyCRN,a.DeptCRN,a.OrderNo,a.Source,MIN(b.EventTime) EventTime
FROM
#summary a JOIN EventItems_rob b
ON a.CompanyCRN = b.CompanyCRN AND a.DeptCRN = b.DepartmentCRN
AND a.OrderNo = b.OrderNo
WHERE
a.taxCnt > 0 AND a.OrderNo != '[NONE]' AND a.OrderNo IS NOT NULL
-- Exclude orders converted from Super-Search.
AND b.orderID NOT IN (SELECT orderID FROM #convertedOrders c WHERE c.taxOrder = 1)
GROUP BY
a.CompanyCRN,a.DeptCRN,a.OrderNo,a.Source
) updValueTable
WHERE
tempSummary.CompanyCRN = updValueTable.CompanyCRN
AND tempSummary.DeptCRN = updValueTable.DeptCRN
AND tempSummary.OrderNo = updValueTable.OrderNo
AND ISNULL(tempSummary.TaxCnt,0) >= 1
-- Identify new orders.
UPDATE #summary SET plantOrder = 1
FROM #summary
WHERE orderNo <> '[NONE]'
AND (nameCnt > 0 OR propertyCnt > 0 OR propertyBackPlantCnt > 0 OR propertyBothPlantCnt > 0)
AND plantOrderCreated BETWEEN @fromDate AND @thruDate
UPDATE #summary SET taxOrder = 1
FROM #summary s
WHERE orderNo <> '[NONE]'
AND taxCnt > 0
AND taxOrderCreated BETWEEN @fromDate AND @thruDate
UPDATE #summary SET inquiryCnt = nameCnt + propertyCnt + propertyBackPlantCnt + propertyBothPlantCnt + addressCnt + documentCnt + ownerCnt + ggCnt,
cnpCnt = corpCnt + notaryCnt + partnerCnt
--xrefCnt = addressCnt + documentCnt + ownerCnt,
--cnpCnt = corpCnt + corpListCnt + notaryCnt + notaryListCnt + partnerCnt + partnerListCnt
--Create count totals table
CREATE TABLE #summaryTotals(
companyCRNVARCHAR(50),
deptCRNVARCHAR(50),
plantOrderTotalINT,
taxOrderTotalINT,
inquiryCntTotalINT,
cnpCntTotalINT,
recordedDocsTotalINT,
mapsTotalINT,
starterTicorTotalINT,
starterOtherTotalINT,
backPlantImageCntTotalINT,
odiCntTotalINT,
taxCntTotalINT,
extTaxInfoSearchCntTotalINT,
nameCntTotalINT,
propertyCntTotalINT,
propertyBackPlantCntTotalINT,
propertyBothPlantCntTotalINT,
addressCntTotalINT,
documentCntTotalINT,
ownerCntTotalINT,
corpCntTotalINT,
notaryCntTotalINT,
partnerCntTotalINT,
ggCntTotalINT,
sourceVARCHAR(50),
PageCountTotalINT
)
INSERT INTO #summaryTotals
SELECT
companyCRN
,deptCRN
,SUM(plantOrder) as plantOrderTotal
,SUM(taxOrder) as taxOrderTotal
,SUM(inquiryCnt) as inquiryCntTotal
,SUM(cnpCnt) as cnpCntTotal
,SUM(recordedDocs) as recordedDocsTotal
,SUM(maps) as mapsTotal
,SUM(starterTicor) as starterTicorTotal
,SUM(starterOther) as starterOtherTotal
,SUM(backPlantImageCnt) as backPlantImageCntTotal
,SUM(odiCnt) as odiCntTotal
,SUM(taxCnt) as taxCntTotal
,SUM(extTaxInfoSearchCnt) as extTaxInfoSearchCntTotal
,SUM(nameCnt) as nameCntTotal
,SUM(propertyCnt) as propertyCntTotal
,SUM(propertyBackPlantCnt) as propertyBackPlantCntTotal
,SUM(propertyBothPlantCnt) as propertyBothPlantCntTotal
,SUM(addressCnt) as addressCntTotal
,SUM(documentCnt) as documentCntTotal
,SUM(ownerCnt) as ownerCntTotal
,SUM(corpCnt) as corpCntTotal
,SUM(notaryCnt) as notaryCntTotal
,SUM(partnerCnt) as partnerCntTotal
,SUM(ggCnt) as ggCntTotal
,source
,SUM(ODSPageCnt) as PageCountTotal
FROM #summary
GROUP BY companyCRN, deptCRN, source
IF (LTRIM(RTRIM(@reportType)) = 'Order Summary') BEGIN
CREATE TABLE #summaryImage(
companyCRNVARCHAR(50),
deptCRNVARCHAR(50),
companyVARCHAR(50),
deptVARCHAR(50),
countyVARCHAR(100),
titleUnitVARCHAR(100),
userNameVARCHAR(50),
orderNoVARCHAR(128),
plantOrderINT,
taxOrderINT,
inquiryCntINT,
cnpCntINT,
recordedDocsINT,
mapsINT,
starterTicorINT,
starterOtherINT,
backPlantImageCntINT,
odiCntINT,
taxCntINT,
nameCntINT,
propertyCntINT,
propertyBackPlantCntINT,
propertyBothPlantCntINT,
addressCntINT,
documentCntINT,
ownerCntINT,
corpCntINT,
notaryCntINT,
partnerCntINT,
ggCntINT,
userCompanyVARCHAR(50),
plantOrderCreatedDATETIME,
taxOrderCreatedDATETIME,
extTaxInfoSearchCntINT,
sourceVARCHAR(50),
plantOrderTotalINT,
taxOrderTotalINT,
inquiryCntTotalINT,
cnpCntTotalINT,
recordedDocsTotalINT,
mapsTotalINT,
starterTicorTotalINT,
starterOtherTotalINT,
backPlantImageCntTotalINT,
odiCntTotalINT,
taxCntTotalINT,
extTaxInfoSearchCntTotalINT,
nameCntTotalINT,
propertyCntTotalINT,
propertyBackPlantCntTotalINT,
propertyBothPlantCntTotalINT,
addressCntTotalINT,
documentCntTotalINT,
ownerCntTotalINT,
corpCntTotalINT,
notaryCntTotalINT,
partnerCntTotalINT,
ggCntTotalINT,
LineNumberBIGINT,
SourceLineNumberBIGINT,
PageCountTotalINT,
startTimeDATETIME,
endTimeDATETIME,
PI2LineCountINT,
eventDurationDATETIME,
OrderSequenceINT
)
--SELECT * --, LineNumberTotal, SourceLineNumberTotal, startTime, endTime, PI2LineCount, Duration
--FROM
INSERT INTO #summaryImage
SELECT s.companyCRN, s.deptCRN, company, dept, s.county, titleUnit,
userName, s.orderNo, plantOrder, taxOrder,
inquiryCnt, cnpCnt, --xrefCnt,
recordedDocs, maps, starterTicor, starterOther, backPlantImageCnt, odiCnt,
taxCnt, nameCnt, propertyCnt, propertyBackPlantCnt, propertyBothPlantCnt, addressCnt, documentCnt, ownerCnt,
--corpCnt, corpListCnt, notaryCnt, notaryListCnt, partnerCnt, partnerListCnt,
corpCnt, notaryCnt, partnerCnt, ggCnt,
userCompany,plantOrderCreated,taxOrderCreated,extTaxInfoSearchCnt,s.source
,plantOrderTotal
,taxOrderTotal
,inquiryCntTotal
,cnpCntTotal
,recordedDocsTotal
,mapsTotal
,starterTicorTotal
,starterOtherTotal
,backPlantImageCntTotal
,odiCntTotal
,taxCntTotal
,extTaxInfoSearchCntTotal
,nameCntTotal
,propertyCntTotal
,propertyBackPlantCntTotal
,propertyBothPlantCntTotal
,addressCntTotal
,documentCntTotal
,ownerCntTotal
,corpCntTotal
,notaryCntTotal
,partnerCntTotal
,ggCntTotal
,ROW_NUMBER() OVER(PARTITION BY s.companyCRN, s.deptCRN ORDER BY s.companyCRN, s.deptCRN, s.source, s.county, s.orderNo) AS LineNumber
--,LineNumberTotal
,ROW_NUMBER() OVER(PARTITION BY s.companyCRN, s.deptCRN, s.source ORDER BY s.companyCRN, s.deptCRN, s.source, s.county, s.orderNo) AS SourceLineNumber
--,SourceLineNumberTotal
,PageCountTotal
,e.startTime
, e.endTime
,e.PI2LineCount
,e.eventDuration
,ROW_NUMBER() OVER(PARTITION BY s.companyCRN, s.deptCRN, s.dept, s.source, s.county, s.orderNo, s.titleUnit, s.userName
ORDER BY s.companyCRN, s.deptCRN, s.dept, s.source, s.county, s.orderNo, s.titleUnit, s.userName) AS OrderSequence
FROM #summary s
LEFT JOIN
(select coCRN, deptCRN, department, county, source, orderNo, startTime, endTime, PI2LineCount, eventDuration
from #events e
where PI2LineCount IS NOT NULL AND event = 'ODSRequestFulfilled'
) as e
ON s.companyCRN = e.coCRN
AND s.deptCRN = e.deptCRN
AND s.source = e.source
AND s.county = e.county
AND s.orderNo = e.orderNo
AND s.dept = e.department
JOIN #summaryTotals t
ON s.companyCRN = t.companyCRN
AND s.deptCRN = t.deptCRN
AND s.source = t.source
UPDATE #summaryImage
SET
--companyCRN = ''
--,deptCRN = ''
--,company = ''
--,dept = ''
--,county = ''
--,titleUnit = ''
--,userName = ''
--,orderNo = ''
plantOrder = 0
,taxOrder = 0
,inquiryCnt = 0
,cnpCnt= 0
,recordedDocs = 0
,maps = 0
,starterTicor = 0
,starterOther = 0
,backPlantImageCnt = 0
,odiCnt = 0
,taxCnt = 0
,nameCnt = 0
,propertyCnt = 0
,propertyBackPlantCnt = 0
,propertyBothPlantCnt = 0
,addressCNt = 0
,documentCnt = 0
,ownerCnt = 0
,corpCnt = 0
,notaryCnt = 0
,partnerCnt = 0
,ggCnt = 0
,userCompany = ''
,plantOrderCreated = ''
,taxOrderCreated = ''
,extTaxInfoSearchCnt = 0
--,source = ''
,plantOrderTotal = 0
,taxOrderTotal = 0
,inquiryCntTotal = 0
,cnpCntTotal = 0
,recordedDocsTotal = 0
,mapsTotal = 0
,starterTicorTotal = 0
,starterOtherTotal = 0
,backPlantImageCntTotal = 0
,odiCntTotal = 0
,taxCntTotal = 0
,extTaxInfoSearchCntTotal = 0
,nameCntTotal = 0
,propertyCntTotal = 0
,propertyBackPlantCntTotal = 0
,propertyBothPlantCntTotal = 0
,addressCntTotal = 0
,documentCntTotal = 0
,ownerCntTotal = 0
,corpCntTotal = 0
,notaryCntTotal = 0
,partnerCntTotal = 0
,ggCntTotal = 0
WHERE OrderSequence > 1
IF @excludeInvalidCRNs = 0
BEGIN
SELECT s.*, LineNumberTotal, SourceLineNumberTotal
FROM #summaryImage s
JOIN
(select companyCRN, deptCRN, count(*) as LineNumberTotal
from #summaryImage
group by companyCRN, deptCRN
) as lt
ON s.companyCRN = lt.companyCRN
AND s.deptCRN = lt.deptCRN
JOIN
(select companyCRN, deptCRN, source, count(*) as SourceLineNumberTotal
from #summaryImage
group by companyCRN, deptCRN, source
) as st
ON s.companyCRN = st.companyCRN
AND s.deptCRN = st.deptCRN
AND s.source = st.source
ORDER BY s.companyCRN, s.deptCRN, s.Source, county, s.orderNo, LineNumber
END
ELSE
BEGIN
SELECT s.*, LineNumberTotal, SourceLineNumberTotal
FROM #summaryImage s
JOIN
(select companyCRN, deptCRN, count(*) as LineNumberTotal
from #summaryImage
group by companyCRN, deptCRN
) as lt
ON s.companyCRN = lt.companyCRN
AND s.deptCRN = lt.deptCRN
JOIN
(select companyCRN, deptCRN, source, count(*) as SourceLineNumberTotal
from #summaryImage
group by companyCRN, deptCRN, source
) as st
ON s.companyCRN = st.companyCRN
AND s.deptCRN = st.deptCRN
AND s.source = st.source
WHERE s.companyCRN IS NOT NULL AND s.deptCRN IS NOT NULL
AND LTRIM(RTRIM(s.companyCRN)) <> '' AND LTRIM(RTRIM(s.deptCRN)) <> ''
AND LTRIM(RTRIM(s.companyCRN)) <> '0' AND LTRIM(RTRIM(s.deptCRN)) <> '0'
ORDER BY s.companyCRN, s.deptCRN, s.Source, county, s.orderNo, LineNumber
END
END
ELSE BEGIN -- DEFAULT: @reportType = 'Company Summary'
IF @excludeInvalidCRNs = 0
BEGIN
--Invalid Company and Department CRNs are included in the resultset
SELECT companyCRN, deptCRN, company, dept, county, titleUnit,
userName, orderNo, plantOrder, taxOrder,
inquiryCnt, cnpCnt, --xrefCnt,
recordedDocs, maps, starterTicor, starterOther, backPlantImageCnt, odiCnt,
taxCnt, nameCnt, propertyCnt, propertyBackPlantCnt, propertyBothPlantCnt, addressCnt, documentCnt, ownerCnt,
--corpCnt, corpListCnt, notaryCnt, notaryListCnt, partnerCnt, partnerListCnt,
corpCnt, notaryCnt, partnerCnt, ggCnt,
plantOrderCreated,taxOrderCreated,userCompany,extTaxInfoSearchCnt,source
,0 AS plantOrderTotal
,0 AS taxOrderTotal
,0 AS inquiryCntTotal
,0 AS cnpCntTotal
,0 AS recordedDocsTotal
,0 AS mapsTotal
,0 AS starterTicorTotal
,0 AS starterOtherTotal
,0 AS backPlantImageCntTotal
,0 AS odiCntTotal
,0 AS taxCntTotal
,0 AS extTaxInfoSearchCntTotal
,0 AS nameCntTotal
,0 AS propertyCntTotal
,0 AS propertyBackPlantCntTotal
,0 AS propertyBothPlantCntTotal
,0 AS addressCntTotal
,0 AS documentCntTotal
,0 AS ownerCntTotal
,0 AS corpCntTotal
,0 AS notaryCntTotal
,0 AS partnerCntTotal
,0 AS ggCntTotal
,0 AS LineNumber
,0 AS LineNumberTotal
, startTime
,endTime
, 0 AS PageCountTotal
,Duration
FROM #summary
ORDER BY Source, companyCRN, deptCRN, county, titleUnit, orderNo, userName
END
ELSE
BEGIN
--Invalid Company and Department CRNs are excluded in the resultset
SELECT companyCRN, deptCRN, company, dept, county, titleUnit,
userName, orderNo, plantOrder, taxOrder,
inquiryCnt, cnpCnt, --xrefCnt,
recordedDocs, maps, starterTicor, starterOther, backPlantImageCnt, odiCnt,
taxCnt, nameCnt, propertyCnt, propertyBackPlantCnt, propertyBothPlantCnt, addressCnt, documentCnt, ownerCnt,
--corpCnt, corpListCnt, notaryCnt, notaryListCnt, partnerCnt, partnerListCnt,
corpCnt, notaryCnt, partnerCnt, ggCnt,
plantOrderCreated,taxOrderCreated,userCompany,extTaxInfoSearchCnt,source
,0 AS plantOrderTotal
,0 AS taxOrderTotal
,0 AS inquiryCntTotal
,0 AS cnpCntTotal
,0 AS recordedDocsTotal
,0 AS mapsTotal
,0 AS starterTicorTotal
,0 AS starterOtherTotal
,0 AS backPlantImageCntTotal
,0 AS odiCntTotal
,0 AS taxCntTotal
,0 AS extTaxInfoSearchCntTotal
,0 AS nameCntTotal
,0 AS propertyCntTotal
,0 AS propertyBackPlantCntTotal
,0 AS propertyBothPlantCntTotal
,0 AS addressCntTotal
,0 AS documentCntTotal
,0 AS ownerCntTotal
,0 AS corpCntTotal
,0 AS notaryCntTotal
,0 AS partnerCntTotal
,0 AS ggCntTotal
,0 AS LineNumber
,0 AS LineNumberTotal
, 0 AS PageCountTotal
FROM #summary
WHERE companyCRN IS NOT NULL AND deptCRN IS NOT NULL
AND LTRIM(RTRIM(companyCRN)) <> '' AND LTRIM(RTRIM(deptCRN)) <> ''
AND LTRIM(RTRIM(companyCRN)) <> '0' AND LTRIM(RTRIM(deptCRN)) <> '0'
ORDER BY Source, companyCRN, deptCRN, county, titleUnit, orderNo, userName
END
END
--DROP TABLE #events
--DROP TABLE #convertedOrders
--DROP TABLE #summary
--DROP TABLE #summaryTotals
--IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects o where o.xtype in ('U') AND o.id = OBJECT_ID(N'tempdb..#summaryImage'))
--DROP TABLE #summaryImage
January 28, 2009 at 2:30 pm
I am sure MAXDOP will not help you anywhere there.
You must check the procedure piece by piece. On a first look it seems that too much IO is going on.
I would try to reduce that as much as possible but that requires more knowledge than just a raw look at the queries.
Avg Number or rows affected on those tables, are all necessary indexes in place, etc ...
* Noel
January 29, 2009 at 7:45 am
Why do you want to mess with MAXDOP? Your default setting is 0, which means that SQL SErver will use as many processors as it has available to it. Maybe you want to look at adjusting the paralellism threshold up or down.
Is the concern that the query is going parallel and that's causing problems or that it's not going parallel and you think it should?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2009 at 8:03 am
The maxdop query hint goes at the end of a statement not the entire sproc.
For example
Create Procedure sp_test (@parm1 varchar(50))
as
--statement 1
select colb from table2 where col3 = @parm1
--statement 2
select col1 from tablea where col1 = @parm1
option (MAXDOP 1)
I agree with the other posts however. You need to analyze this procedure more to determine if MAXDOP will be of any use. First find out which statement is the slowest and/or uses the most resources (e.g. page reads). Then look at the execution plan. If it is as optimized as possible (e.g. using index seeks etc) and it is using parallelism then trying the MAXDOP hint might be worthwhile. But if the statement is not using parallelism then don't bother trying the hint.
January 29, 2009 at 8:24 am
The thing is, MAXDOP is a way to limit paralellism. A lot of times, in the OLTP type systems I work in, parallel queries cost more than they provide benefit. But, in BI systems, which it sounds like you have, they frequently provide more benefits than they cost. You haven't shown that you need or want to limit the use of multiple processors.
Also, just as an aside, you can use a plan guide as a mechanism to test out the use of the query hint without rewriting the query. Look it up in BOL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2009 at 9:14 am
You guys are right.
I tried it and it was no real benefit.
Thanks very much for your time, and knowledge.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply