June 27, 2014 at 8:35 am
I was wondering if anyone knows of a way to speed this stored procedure up some, there are no pk or fk in the tables, here's my code I'm rather new to sql development. So go easy on the critism, I already know the query is "UGLY", but this is how I was told to do it for now, but I can't accept the performance.
USE [AA_Helper]
GO
/****** Object: StoredProcedure [dbo].[sp_TraceCallReport] Script Date: 6/26/2014 9:08:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_TraceCallReport]
@startdate as date,
@endDate as date,
@ext as nvarchar(50)
AS
BEGIN
WITH AcctJoin(rnk,Phone, CompanyName,AL_Contact_Name,cCallID)
AS (
SELECT *, rnk = RANK() OVER(PARTITION BY companyname ORDER BY companyname)
FROM (
SELECT a.Contact_Name as AL_Contact_Name,c.AR_COMPANY_NAME AS CompanyName --00 is the default list number
,REPLACE(REPLACE(REPLACE(REPLACE(a.CONTACT_PHONE, '(', ''), ')', ''), ' ', ''), '-', '') AS phone
,rnk = RANK() OVER(PARTITION BY c.AR_COMPANY_NAME ORDER BY c.AR_COMPANY_NAME)
FROM table1 AS a
left JOIN table2 AS b ON a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER
left JOIN table3 AS c ON b.AR_NUMBER = c.AR_NUMBER
WHERE RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(a.CONTACT_PHONE, '(', ''), ')', ''), ' ', ''), '-', '')) <> ''
AND a.Default_list_no = '00'
AND LEN(REPLACE(REPLACE(REPLACE(REPLACE(a.CONTACT_PHONE, '(', ''), ')', ''), ' ', ''), '-', '')) > 6
AND REPLACE(REPLACE(REPLACE(REPLACE(a.CONTACT_PHONE, '(', ''), ')', ''), ' ', ''), '-', '') NOT LIKE '0%'
AND REPLACE(REPLACE(REPLACE(REPLACE(a.CONTACT_PHONE, '(', ''), ')', ''), ' ', ''), '-', '') NOT LIKE '1111%'
UNION ALL
SELECT d.FIRST_NAME + ' ' + d.LAST_NAME as ar_CONTACT_NAME,e.AR_COMPANY_NAME --001 is the default list number
,REPLACE(REPLACE(REPLACE(REPLACE(d.PHONE_NUMBER, '(', ''), ')', ''), ' ', ''), '-', '') AS arPhone
,rnk = RANK() OVER(PARTITION BY e.AR_COMPANY_NAME ORDER BY e.AR_COMPANY_NAME)
FROM table4 AS d
INNER JOIN table5 AS e ON d.AR_NUMBER = e.AR_NUMBER
WHERE (RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(d.PHONE_NUMBER, '(', ''), ')', ''), ' ', ''), '-', '')) <> '')
AND d.list_number = '0001'
AND LEN(REPLACE(REPLACE(REPLACE(REPLACE(d.PHONE_NUMBER, '(', ''), ')', ''), ' ', ''), '-', '')) > 6
AND REPLACE(REPLACE(REPLACE(REPLACE(d.PHONE_NUMBER, '(', ''), ')', ''), ' ', ''), '-', '') NOT LIKE '0%'
AND REPLACE(REPLACE(REPLACE(REPLACE(d.PHONE_NUMBER, '(', ''), ')', ''), ' ', ''), '-', '') NOT LIKE '11111%'
) AS CompanyPhone
WHERE rnk = 1
)
SELECT cStaging.ID
,cStaging.cDate
,cStaging.cStartTime
,cStaging.cDuration
,cStaging.cTimetoAnswer
,cStaging.callingparty
,cStaging.cDigitsDialed
,cStaging.cOrigCall
,cStaging.Cdestination
,cStaging.cTransfer1
,cStaging.cTransfer2
,cStaging.cCustPhone
,cStaging.cDirection
,cStaging.calledparty
,cStaging.cSystemID
,cStaging.cANI
,cStaging.cDNIS
,cStaging.cCallID
,cStaging.cCallIDSeq
,(
CASE
WHEN aj1.CompanyName IS NULL
THEN aj2.CompanyName
ELSE aj1.CompanyName
END
) AS CompanyName, aj1.*,aj2.*
FROM CallTrace AS cStaging
LEFT JOIN AcctJoin AS aj1 ON LTRIM(RTRIM(aj1.phone)) = cStaging.Cdestination
LEFT JOIN AcctJoin AS aj2 ON LTRIM(RTRIM(aj2.phone)) = cStaging.callingparty
WHERE (CONVERT(DATE, cStaging.cDate) BETWEEN @startdate AND @endDate) AND (CONVERT(DATE, cStaging.cDate) <> '')AND (cStaging.cOrigCall IN (SELECT NewParameterFROM dbo.fnSplit(@ext, ',') AS fnSplit_1))
END
GO
June 27, 2014 at 8:39 am
What does your execution plan look like?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 8:39 am
I suspect not that much can be done while all those REPLACE and TRIM functions are needed in the WHERE clause. Is the data really that messy?
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
June 27, 2014 at 8:50 am
It is even messier than that, I created a ETL package that cleaned it up even more, but after importing all of the files I am processing, found there was even more things that needed to be done to the data. There is no data validation on any of the data entry screens because, the users weren't trained properly and didn't know how to move forward in the data entry screens, so Management allowed the data validation to be removed, and now this has caused extremely messy data as you can imagine, so another developer and myself are looking at creating a datamart to do our reporting off, but as you can imagine, they want everything now, but don't understand the nightmare they have created. "Oh forgot to mention this is 2012 SQL."
June 27, 2014 at 8:57 am
cbrammer1219 (6/27/2014)
It is even messier than that, I created a ETL package that cleaned it up even more, but after importing all of the files I am processing, found there was even more things that needed to be done to the data. There is no data validation on any of the data entry screens because, the users weren't trained properly and didn't know how to move forward in the data entry screens, so Management allowed the data validation to be removed, and now this has caused extremely messy data as you can imagine, so another developer and myself are looking at creating a datamart to do our reporting off, but as you can imagine, they want everything now, but don't understand the nightmare they have created. "Oh forgot to mention this is 2012 SQL."
Sigh, who needs good clean accurate data? It's just customer management afterall.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 9:10 am
We've showed them that they are losing 10's of thousands of dollars yearly because of this bad data, they are giving free service to customers because they can't track all accounts, I mean we only have phone numbers to match customer and account to Accounting, and they allow data entry to be anything example..."1111111", "XXXXXXX" it's insane...
June 27, 2014 at 9:28 am
Must have money to burn.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 9:34 am
1) Don't name stored procedures "sp_..."
2) consider updating the data in a single pass (possibly during load since this is part of a staging process?!?) so you can greatly simply your WHERE clause
3) speaking of WHERE, if you wrap columns in functions you are dead meat. BAD stuff happens, from poor estimates leading to suboptimal plans to CPU burn, scans/hashes, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 27, 2014 at 11:48 am
I have attached my execution plan, I ran it last night and it was taki9ng approx. 15-20 minutes to run, today it is running for 30 minutes and still not returning results, why would this be? I thought after a while it runs executes a little faster due to caching.
June 27, 2014 at 11:59 am
A quick glance shows that you are having some implicit conversion problems. There are also some cardinality estimate warnings.
And then there are multiple pieces coming in across a linked server. This could be a huge performance issue for real-time queries.
Can you eliminate the queries through the linked servers?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 12:01 pm
Nope that is the way they have things setup, these are all linked servers..
June 27, 2014 at 12:07 pm
Then the next best thing for the time being is to start fixing data types and fixing those implicit conversions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 12:10 pm
I am not seeing where it is saying conversion problem, I do see the remote query problems, which I told my lead that this was going to be a HUGE problem not just because of querying from a remote server, but because of the poor db structures. I am demanding we fix the data before moving forward with any other projects, even if they have to contract a DBA to set things up, I'm no DBA and neither is my Lead. We work with what we have, but making a company successful has to have at least a proper foundation to build off of, I am all for learning but, it makes it difficult when things are as bad as they are and they want things yesterday.
June 27, 2014 at 12:14 pm
It is sometimes easier to see those conversions when looking at the XML directly.
Open up the Plan XML and search for CONVERT_IMPLICIT.
There is also that warning on the select operator that shows some conversion issues and cardinality issues.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2014 at 12:20 pm
So let me ask what is the best approach for setting up a Data mart as it looks like I am going to be the one to do this, and I want to do it right??
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply