Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Re-Writting new SQL queries

Posted on 2012-04-05
1
Medium Priority
?
396 Views
Last Modified: 2012-04-13
Hi,

I have ask to re-write a sql sripts and improve performance during the run time, can some one give me an ideas on how can I re-write this query the sturcture are pretty the same and do the same thing but the destination of the table is different please see below

DECLARE @start datetime, @end datetime, @YTD datetime, @myDate datetime
--SET @start = '2011-02-01'
--SET @end = '2012-01-01'
--SET @YTD = '2012-01-31'

set @mydate =dateadd(mm,-2,DATEADD(year,-1,getdate())) --change the minus 2 to one

set @start = CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)
set @end = DATEADD(mm,11,@start)
set @YTD = CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@end))),DATEADD(mm,1,@end)),101)

SELECT
             Site
            ,Div
            ,SubDiv
            ,d.DeptCode
            ,c.NPI_Number
            ,Clarity_Provider_Id as ProvNo
            ,c.ProvName
            ,SurveyId
            ,b.Category
            ,QuestionId
            ,CASE WHEN AnsVal = 5 THEN 100
                    WHEN AnsVal = 4 THEN 75
                    WHEN AnsVal = 3 THEN 50
                    WHEN AnsVal = 2 THEN 25
                    WHEN AnsVal = 1 THEN 0 END AS 'Mean',
              ServiceDateMonth
INTO #Mean_Surveys_Convert_Mean
FROM ZZ04M_PS_Base_PatSats_PressGaney_Detail a
            INNER JOIN (select Code, category
                              from SMSSTAGING04.dbo.PS_PressGaney_Dict_Questions
                              where standard = 'Y' and Survey = 'MG'
                              )b on a.QuestionID = b.Code
            INNER JOIN GEN_0010_Base_Provider_Master c on a.Clarity_Provider_Id = c.provno
            INNER JOIN GEN_0020_Base_GLDept_Master_PAMF d on a.Clarity_Department_Id = d.Clarity_Dept_Id
WHERE ServiceDateMonth BETWEEN @start and @end
            AND AnsVal is not NULL
            AND DATEDIFF(dd,ServiceDateMonth,ReceivedDate) <=60

SELECT  
            a.DeptCode+'-'+Facility as 'Site'
            ,Division as 'Div'
            ,SubDivision as 'SubDiv'
            ,a.DeptCode
            ,NPI_Number
            ,a.provno
            ,c.ProvName
            ,SurveyId
            ,b.Category
            ,QuestionId
            ,CASE      WHEN AnsVal = 5 THEN 100
                        WHEN AnsVal = 4 THEN 75
                        WHEN AnsVal = 3 THEN 50
                        WHEN AnsVal = 2 THEN 25
                        WHEN AnsVal = 1 THEN 0 END AS 'Mean'
            ,ServiceDateMonth
INTO #Mean_Surveys_Convert_Mean_Mills
FROM ZZ04M_PS_Base_PatSats_PressGaney_Detail_Mills a
INNER JOIN (select Code, category
                  from SMSSTAGING04.dbo.PS_PressGaney_Dict_Questions
                  where standard = 'Y' and Survey = 'MG'
                  )b on a.QuestionID = b.Code
INNER JOIN GEN_0010_Base_Provider_Master_Mills c on a.provno = c.provno
WHERE ServiceDateMonth BETWEEN @start and @end
            AND AnsVal is not NULL
            AND DATEDIFF(dd,ServiceDateMonth,ReceivedDate) <=60
order by surveyid



SELECT NPI_Number, ProvName, SurveyId, Category, Avg(CAST(Mean as decimal (9,2))) AS Mean, 'Q'+datename(q,ServiceDateMonth)+'-'+datename(yy,ServiceDateMonth) as 'Quarter'
INTO #Mean_Surveys_Create_Category_Mean_Provider
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY NPI_Number, ProvName, SurveyId, Category, ServiceDateMonth
order by Category


select * from #Mean_Surveys_Create_Category_Mean_Provider
--create overall mean survey provider

SELECT NPI_Number, ProvName, SurveyId, Avg(CAST(Mean as decimal (9,2))) AS Mean, 'Q'+datename(q,ServiceDateMonth)+'-'+datename(yy,ServiceDateMonth) as 'Quarter'
INTO #Mean_Surveys_Create_Overall_Mean_Provider
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY NPI_Number, ProvName, SurveyId, ServiceDateMonth

--create category mean survey provider YTD

SELECT NPI_Number, ProvName, SurveyId, Category, Avg(CAST(Mean as decimal (9,2))) AS Mean, convert(varchar, @YTD, 101) as 'Quarter'
INTO #Mean_Surveys_Create_Category_Mean_Provider_YTD
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY NPI_Number, ProvName, SurveyId, Category

--create category mean survey provider

SELECT
       NPI_Number
      ,ProvName
      ,SurveyId
      ,Category
      ,Avg(CAST(Mean as decimal (9,2))) AS Mean
      ,'Q'+datename(q,ServiceDateMonth)+'-'+datename(yy,ServiceDateMonth) as 'Quarter'
INTO #Mean_Surveys_Create_Category_Mean_Provider
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY
            NPI_Number
            ,ProvName
            ,SurveyId
            ,Category
      ,ServiceDateMonth
order by Category


select * from #Mean_Surveys_Create_Category_Mean_Provider
--create overall mean survey provider

SELECT NPI_Number, ProvName, SurveyId, Avg(CAST(Mean as decimal (9,2))) AS Mean, 'Q'+datename(q,ServiceDateMonth)+'-'+datename(yy,ServiceDateMonth) as 'Quarter'
INTO #Mean_Surveys_Create_Overall_Mean_Provider
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY NPI_Number, ProvName, SurveyId, ServiceDateMonth

--create category mean survey provider YTD

SELECT NPI_Number, ProvName, SurveyId, Category, Avg(CAST(Mean as decimal (9,2))) AS Mean, convert(varchar, @YTD, 101) as 'Quarter'
INTO #Mean_Surveys_Create_Category_Mean_Provider_YTD
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY NPI_Number, ProvName, SurveyId, Category

--create overall mean survey provider YTD

SELECT NPI_Number, ProvName, SurveyId, Avg(CAST(Mean as decimal (9,2))) AS Mean, convert(varchar, @YTD, 101) as 'Quarter'
INTO #Mean_Surveys_Create_Overall_Mean_Provider_YTD
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY NPI_Number, ProvName, SurveyId



/**************************************
Run queries to calculate site mean score
**************************************/

--create category mean survey Site

SELECT Site, DeptCode, SurveyId, Category, Avg(CAST(Mean as decimal (9,2))) AS Mean, convert(varchar, ServiceDateMonth, 101) as 'Month'
INTO #Mean_Surveys_Create_Category_Mean_Site
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY Site,DeptCode,SurveyId, Category, ServiceDateMonth

--create overall mean survey Site

SELECT Site, DeptCode,SurveyId, Avg(CAST(Mean as decimal (9,2))) AS Mean, convert(varchar, ServiceDateMonth, 101)as 'Month'
INTO #Mean_Surveys_Create_Overall_Mean_Site
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY Site, DeptCode,SurveyId, ServiceDateMonth

--create category mean survey Site YTD

SELECT Site, DeptCode,SurveyId, Category, Avg(CAST(Mean as decimal (9,2))) AS Mean, convert(varchar, @YTD, 101) as 'Month'
INTO #Mean_Surveys_Create_Category_Mean_Site_YTD
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY Site, DeptCode,SurveyId, Category

--create overall mean survey Site YTD

SELECT Site, DeptCode,SurveyId, Avg(CAST(Mean as decimal (9,2))) AS Mean, convert(varchar, @YTD, 101) as 'Month'
INTO #Mean_Surveys_Create_Overall_Mean_Site_YTD
FROM #Mean_Surveys_Convert_Mean
WHERE category is not NULL
GROUP BY Site, DeptCode,SurveyId


There is more to it and there are at least 2000 lines of code the I need to modify, I wanted make it readable, modular, the performance is great and easy to maintain please give me an idea on how would I tackle this,  I was thinking of doing it on a udf but I am stuck please advise thank you in advance
0
Comment
Question by:apmanzojr1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 2000 total points
ID: 37815228
If the performance is already great and it's easy to maintain, why have you been asked to improve it? The SQL generally looks good, but without knowing more about the specifics of the performance, it's hard to tell where your time would be best spent reforming it. You might want to try a "SELECT GETDATE()" between each statement to see where the delays actually are. Optionally, if you're using SQL 2008 as the server, you could run the script in debug mode and step through it - it should make pretty obvious which steps are taking longer than others, and you could focus on improving those.

Without more information about your situation though, I'm not sure what you want to change here.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question