January 17, 2009 at 7:50 am
I'm having a bear of a time trying to come up with the best indexes for this one proc and one table.
Any suggestions greatly appreciated.
I ran the proc thru the ETA, but it always says zero percent improvement. It takes about 20 minutes for this to run.
I think I've done about all I can on the proc side, I'm just looking for guidance on the best clustered/nonclusted setup.
Here's the table:
CREATE TABLE [dbo].[EventItems_rob](
[LogId] [int] NOT NULL,
[Company] [varchar](50) NOT NULL,
[CompanyId] [int] NOT NULL,
[Department] [varchar](50) NOT NULL,
[DepartmentId] [int] NOT NULL,
[CompanyCRN] [varchar](50) NOT NULL,
[DepartmentCRN] [varchar](50) NOT NULL,
[TitleUnit] [varchar](50) NOT NULL,
[TitleUnitId] [int] NOT NULL,
[State] [varchar](50) NOT NULL,
[County] [varchar](100) NOT NULL,
[OrderNo] [varchar](128) NOT NULL,
[OrderId] [int] NOT NULL,
[UserCompany] [varchar](50) NOT NULL,
[UserCompanyId] [int] NOT NULL,
[UserName] [varchar](50) NOT NULL,
[UserId] [int] NOT NULL,
[Source] [varchar](50) NULL,
[Event] [varchar](256) NULL,
[EventTime] [datetime] NOT NULL,
[EventValue] [varchar](150) NULL,
[PI2Status] [varchar](256) NULL,
[PI2LineCount] [varchar](256) NULL,
[PI2RequestTrigger] [varchar](256) NULL,
[PI2PrimaryRequestValue] [varchar](256) NULL,
[Comment] [varchar](4096) NULL
) ON [PRIMARY]
Here's the proc: Sorry it's kinda long, but repetitive.
USE [TitlePointReporting_QA2]
GO
/****** Object: StoredProcedure [dbo].[rpt_OrderSummary2_rob_new] Script Date: 01/17/2009 09:49:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Created By: Mike Kalousek
Updated On: 12/28/06
- 12/28/06MAK- Created based on the rpt_OrderSummary stored procedure.
This version is distinct in that results are controlled by the report being printed
and is filtered by the CRN and County inclusion tables.
- 1/16/08MAK - Moved county abbreviations to the function GetCountyAbbrev.
- Image requests that cannot be fulfilled are removed from the list of events included in the results.
- 3/4/08MAK - Fix: State and county were not being inserted into the #events table for image events.
- 3/11/08MAK - TD 15941 - Add TPX images to these reports.
- 3/17/08MAK - TD 15941 - Add new columns for TPX images.
- 3/25/08MAK - Several performance enhancements.
- 3/26/08MAK - TD 15941 - Fixed retrieval of TPX images to look in the TpxEventItems table for them.
- TD 16080 - Fixed problem with orders disappearing on subsequent runs of the reports due to services
being deleted from orders after the reports had been run.
- 12/10/2008 JRS - Modify to use GetTPEvents proc
*/
ALTER PROC [dbo].[rpt_OrderSummary2_rob_new]
@fromDate DATETIME,
@thruDate DATETIME,
@companyCRN VARCHAR(50) = NULL,
@deptCRN VARCHAR(50) = NULL,
@reportType VARCHAR(50) = 'Company Summary', -- Types: Company Summary, Order Summary
@excludeInvalidCRNs BIT = 1,
@plant VARCHAR(5) = 'ALL',
@reportID INT = NULL,
@reportTitle VARCHAR(255) = NULL
--DECLARE
--@fromDate DATETIME,
--@thruDate DATETIME,
--@companyCRN VARCHAR(50) ,
--@deptCRN VARCHAR(50) ,
--@reportType VARCHAR(50) , -- Types: Company Summary, Order Summary
--@excludeInvalidCRNs BIT,
--@plant VARCHAR(5) ,
--@reportID INT ,
--@reportTitle VARCHAR(255)
--
--set @fromDate = '11/01/2008'
--set @thruDate = '11/20/2008 11:59:00 PM'
--set @companyCRN = '00021 | Chicago Title' --'07015 | GREATER ILLINOIS TITLE'
--set @deptCRN = 'ALL'
--set @reportType = 'Company Summary' -- Types: Company Summary, Order Summary
--set @excludeInvalidCRNs = 1
--set @plant = 'TP'
--set @reportID = NULL
--set @reportTitle = 'Monthly CPEP In-County Access'
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 AND CHARINDEX('|',@companyCRN) > 0 BEGIN
SET @company = SUBSTRING(@companyCRN,CHARINDEX('|',@companyCRN) + 2,150)
SET @companyCRN = SUBSTRING(@companyCRN,1, CHARINDEX('|',@companyCRN) - 2)
END
-- Identify the report for which the results will be filtered.
DECLARE @noCRNInclusions BIT, @noFIPSInclusions BIT
IF @reportID IS NULL BEGIN
SELECT @reportID = reportID FROM reports (NOLOCK) WHERE reportTitle = @reportTitle
END
--
SET @noFIPSInclusions = 1 --temp
-- Get any CRN inclusions/exclusions
CREATE TABLE #crnInclusions (
companyCRNVARCHAR(50),
deptCRNVARCHAR(50),
includeCRNBIT
)
INSERT INTO #crnInclusions
SELECT companyCRN, departmentCRN, includeCRN FROM reportCRNInclusions (NOLOCK) WHERE reportID = @reportID
IF @@ROWCOUNT = 0 SET @noCRNInclusions = 1 ELSE SET @noCRNInclusions = 0
CREATE NONCLUSTERED INDEX ix_companyCRN ON #crninclusions(companyCRN asc)
CREATE NONCLUSTERED INDEX ix_deptCRN ON #crninclusions(deptCRN asc)
-- Get any FIPS inclusions/exclusions
CREATE TABLE #fipsInclusions (
fipsCHAR(5),
includeFIPSBIT,
countyVARCHAR(25)
)
IF @plant = 'ALL'
BEGIN
INSERT INTO #fipsInclusions
SELECT fips, includeFIPS, NULL FROM reportFIPSInclusions (NOLOCK) WHERE reportID = @reportID
END
ELSE IF @plant = 'NGTP'
BEGIN
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06025',1)-- Imperial
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06073',1)-- San Diego
END
ELSE IF @plant = 'SOL'
BEGIN
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06019',1)-- Fresno
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06029',1)-- Kern
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06037',1)-- Los Angeles
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06059',1)-- Orange
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06065',1)-- Riverside
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06071',1)-- San Bernardino
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06083',1)-- Santa Barbara
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06107',1)-- Tulare
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06111',1)-- Ventura
END
ELSE IF @plant = 'TP'
BEGIN
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06001',1)-- Alameda
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06013',1)-- Contra Costa
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06047',1)-- Merced
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06067',1)-- Sacramento
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06075',1)-- San Francisco
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06077',1)-- San Joaquin
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06095',1)-- Solano
INSERT INTO #fipsInclusions (fips, includeFIPS) VALUES ('06099',1)-- Stanislaus
END
IF @@ROWCOUNT = 0 SET @noFIPSInclusions = 1 ELSE SET @noFIPSInclusions = 0
CREATE NONCLUSTERED INDEX ix_fips ON #fipsInclusions(fips asc, county asc)
UPDATE #fipsInclusions SET county = z.county
FROM #fipsInclusions f JOIN usaZip z ON z.fips = f.fips
CREATE NONCLUSTERED INDEX pk_COMPANYCRN ON #crnInclusions(COMPANYCRN asc)
CREATE NONCLUSTERED INDEX pk_FIPS ON #fipsInclusions(COUNTY asc)
create table #events_full (
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_full
EXEC GetTPDetails_rob_new
@fromDate
,@thruDate
,@companyCRN
,@deptCRN
,@fromCoCRN
,@thruCoCRN
CREATE NONCLUSTERED INDEX pk_deptCRN ON #events_full(deptCRN asc, coCRN asc)
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)
)
IF @noFIPSInclusions = 1-- We're including all FIPS codes
BEGIN
INSERT INTO #events
SELECT e.*
FROM #events_full e
JOIN #crnInclusions c ON c.companyCRN = e.coCRN AND c.deptCRN = e.deptCRN AND c.includeCRN = 1
END
ELSE BEGIN-- We're including only some FIPS codes
INSERT INTO #events
SELECT e.*
FROM #events_full e
JOIN #crnInclusions c ON c.companyCRN = e.coCRN AND c.deptCRN = e.deptCRN AND c.includeCRN = 1
JOIN #fipsInclusions f ON f.county = UPPER(e.county)
END
--CREATE NONCLUSTERED INDEX pk_coCRN ON #events(coCRN asc)
--CREATE NONCLUSTERED INDEX pk_deptCRN ON #events(deptCRN asc)
--CREATE NONCLUSTERED INDEX __ix_order ON #events (county ASC, coCRN ASC, deptCRN ASC, orderNo ASC)
--CREATE NONCLUSTERED INDEX pk_orderNo ON #events(orderNo asc)
--CREATE NONCLUSTERED INDEX pk_logID ON #events(logID asc)
CREATE NONCLUSTERED INDEX pk_event ON #events([event] asc)
CREATE NONCLUSTERED INDEX pk_eventValue ON #events(eventValue asc)
CREATE NONCLUSTERED INDEX pk_titleUnit ON #events(titleUnit asc)
CREATE NONCLUSTERED INDEX pk_userID ON #events(userID asc)
UPDATE #events SET coID = ei.companyID, deptID = ei.departmentID, coCRN = ei.companyCRN, deptCRN = ei.departmentCRN,
state = ei.state, company = ei.company, department = ei.department, titleUnit = ei.titleUnit, county = UPPER(ei.county)
FROM #events e
JOIN tpxEventItems ei (NOLOCK) ON e.logID = ei.logID
WHERE e.event LIKE 'TPXView%'
-- 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 backPlantImage = 1
FROM #events e
JOIN eventinformation_rob i ON i.logID = e.logID WHERE i.infoName = 'IncludeBackPlant'
-- Deduce county names from image keys
UPDATE #events SET county = UPPER(LEFT(eventValue,2))
WHERE county IN ('','??')
AND (event LIKE 'Image%' OR event LIKE 'Starter%' OR event = 'ODSRequestFulfilled' OR event LIKE 'TPXView%')
AND SUBSTRING(eventValue,3,1) = ':'
UPDATE #events SET county = UPPER(LEFT(eventValue,6))
WHERE county IN ('','??')
AND (event LIKE 'Image%' OR event LIKE 'Starter%' OR event = 'ODSRequestFulfilled' OR event LIKE 'TPXView%')
AND SUBSTRING(eventValue,7,1) = ':'
-- Translate the county abbreviations.
UPDATE #events SET county = dbo.GetCountyAbbrev(state, county)
--CREATE NONCLUSTERED INDEX __ix_order ON #events (county ASC, coCRN ASC, deptCRN ASC, orderNo ASC)
-- 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.
CREATE CLUSTERED INDEX ix_order3 ON #events
(logid ASC, coCRN ASC, deptCRN ASC, county 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
*/
--Updated by Rob 1/16/09
UPDATE e
SET e.UserID = q.UserID
FROM #Events AS e
INNER JOIN (
SELECT d.coCRN,
d.deptCRN,
d.county,
d.orderNo,
d.userID
FROM (
SELECT coCRN,
deptCRN,
county,
orderNo,
userID,
ROW_NUMBER() OVER (PARTITION BY coCRN, deptCRN, county, orderNo ORDER BY logID) AS recID
FROM #Events
) AS d
WHERE d.recID = 1
) AS q ON q.coCRN = e.coCRN
AND q.deptCRN = e.deptCRN
AND q.county = e.county
AND q.orderNo = e.orderNo
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,
taxCntINT,
addressCntINT,
documentCntINT,
ownerCntINT,
corpCntINT,
corpListCntINT,
notaryCntINT,
notaryListCntINT,
partnerCntINT,
partnerListCnt INT,
ggCntINT,
recordedDocsINT,
mapsINT,
tpxRecordedDocs INT,
tpxMapsINT,
starterTicorINT,
starterOtherINT,
odiCntINT,
userCompanyVARCHAR(50),
plantOrderCreated DATETIME,
taxOrderCreated DATETIME,
extTaxInfoSearchCnt INT,
source VARCHAR(50)
)
INSERT INTO #summary
SELECT e.coCRN, e.deptCRN, e.company, e.department, e.county,
e.titleUnit, e.userID, NULL, e.orderNo, 0, 0, 0, 0, 0,
SUM(CASE WHEN e.event = 'Search-Name' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Property' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Tax' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Tax-Address' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Instrument' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Tax-Owner'THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Corp-Detail' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Corp-List'THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Notary-Detail' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Notary-List' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Partnership-Detail' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-Partnership-List' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Search-GrantorGrantee' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Image-Recorded'THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event LIKE 'Image-Map-%'THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'TPXViewImage'THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'TPXViewMap'THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Starter-Ticor'THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'Starter' OR e.event = 'Starter-Safeco' THEN 1 ELSE 0 END),
SUM(CASE WHEN e.event = 'ODSRequestFulfilled' THEN 1 ELSE 0 END),
NULL, NULL, NULL,
SUM(CASE WHEN e.extTaxInfoSearch = 1THEN 1 ELSE 0 END), e.Source
FROM #events e
--JOIN eventItems ei (NOLOCK) ON ei.logID = e.logID
GROUP BY e.coCRN, e.deptCRN, e.company, e.department, e.county, e.titleUnit, e.userID, e.orderNo, e.Source
/*******************************************/
--CREATE NONCLUSTERED INDEX ix_userName ON #summary(userName asc)
--CREATE NONCLUSTERED INDEX ix_userCompany ON #summary(userCompany asc)
--CREATE NONCLUSTERED INDEX ix_plantOrderCreated ON #summary(plantOrderCreated asc)
--CREATE NONCLUSTERED INDEX ix_CompanyCRN ON #summary(CompanyCRN asc)
--CREATE NONCLUSTERED INDEX ix_DeptCRN ON #summary(DeptCRN asc)
--CREATE NONCLUSTERED INDEX ix_OrderNo ON #summary(OrderNo asc)
--CREATE NONCLUSTERED INDEX ix_PropertyCnt ON #summary(PropertyCnt asc)
--CREATE NONCLUSTERED INDEX ix_NameCnt ON #summary(NameCnt asc)
----CREATE NONCLUSTERED INDEX ix_orderID ON #summary(orderID asc)
--CREATE NONCLUSTERED INDEX ix_TaxCnt ON #summary(TaxCnt asc)
CREATE NONCLUSTERED INDEX ix_userName ON #summary(userName asc, UserCompany asc, PlantOrderCreated asc, CompanyCRN asc, DeptCRN asc, OrderNo asc, PropertyCnt asc, NameCnt asc, TaxCnt asc)
--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 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.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) 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 + addressCnt + documentCnt + ownerCnt + ggCnt,
cnpCnt = corpCnt + notaryCnt + partnerCnt
--xrefCnt = addressCnt + documentCnt + ownerCnt,
--cnpCnt = corpCnt + corpListCnt + notaryCnt + notaryListCnt + partnerCnt + partnerListCnt
IF (LTRIM(RTRIM(@reportType)) = 'Order Summary') BEGIN
SELECT companyCRN, deptCRN, company, dept, county, titleUnit,
userName, orderNo, plantOrder, taxOrder,
inquiryCnt, cnpCnt, --xrefCnt,
recordedDocs, maps, starterTicor, starterOther, odiCnt,
taxCnt, nameCnt, propertyCnt, addressCnt, documentCnt, ownerCnt,
--corpCnt, corpListCnt, notaryCnt, notaryListCnt, partnerCnt, partnerListCnt,
corpCnt, notaryCnt, partnerCnt, ggCnt,
userCompany,plantOrderCreated,taxOrderCreated,extTaxInfoSearchCnt,source
FROM #summary s
ORDER BY Source, companyCRN, deptCRN, orderNo, county
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, tpxRecordedDocs, tpxMaps, starterTicor, starterOther, odiCnt,
taxCnt, nameCnt, propertyCnt, addressCnt, documentCnt, ownerCnt,
--corpCnt, corpListCnt, notaryCnt, notaryListCnt, partnerCnt, partnerListCnt,
corpCnt, notaryCnt, partnerCnt, ggCnt,
plantOrderCreated,taxOrderCreated,userCompany,extTaxInfoSearchCnt,source
FROM #summary s
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, tpxRecordedDocs, tpxMaps, starterTicor, starterOther, odiCnt,
taxCnt, nameCnt, propertyCnt, addressCnt, documentCnt, ownerCnt,
--corpCnt, corpListCnt, notaryCnt, notaryListCnt, partnerCnt, partnerListCnt,
corpCnt, notaryCnt, partnerCnt, ggCnt,
plantOrderCreated,taxOrderCreated,userCompany,extTaxInfoSearchCnt,source
FROM #summary s
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 #crnInclusions
--DROP TABLE #fipsInclusions
--DROP TABLE #events_full
--DROP TABLE #events
--DROP TABLE #convertedOrders
--DROP TABLE #summary
January 17, 2009 at 8:30 am
I hate to be blunt but that procedure's absolutely huge (560 lines) and I don't have the spare time to work through that size of proc.
Can you narrow down where the bad portions are? Use Statistics time and statistics IO, along with the execution plans to work out which queries in there are the slowest (according to statistics time), use the most IOs (according to statistics IO) and cost the most (according to the execution plan)
Once you've found the offending queries, post just them along with their execution plans (saved as .sqlplan files, zipped and attached), the table structures and any existing indexes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2009 at 8:35 am
Sure Gail, I understand completely.
I'm kinda new to this site, so wasn't sure if I should post something so large.
I'll work on breaking it down.
~Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply