Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

re-writting SQL scripts

Posted on 2012-04-05
2
Medium Priority
?
222 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
  • 2
2 Comments
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 2000 total points
ID: 37813233
At the end you said the performance is great and it was easy to maintain.  did you mean the current version or the new one you were told to create?  I ask because performance tuning for me does not usually involve looking at a script and deciding to rewite it. I will usually fire up the profiler while the process runs and then evaluate where my most costly operations are and where I think I can get the most bang for my buck.  Most times adding a few strategic indexes or dropping them prior to a bulk load and rebuilding them afterwards can have a big impact on performance.

But if management objects to the 2000 lines of code and wants a parameter driven solution, that is another story and has nothing to do with performance tuning....  it might make it more flexible, but not faster. UDFs are not known for their speed (my opinion).  

And I do not know how you collect and process the data.    Or whether you have DASD available to preprocess and keep data from one iteration to the next so that you only process new data.  Or whether you have the CPU power to just split out the final computations to run in parallel to finish quicker rather than running everything serially.  I did not notice you making use of the with (nolock) clause on the from clauses.  Perhaps I am missing something on this type of table, but you might reduce overhead with this clause a small amount.
0
 
LVL 6

Assisted Solution

by:SJCFL-Admin
SJCFL-Admin earned 2000 total points
ID: 37813261
I'd also have the activity monitor up while it was running (but not while the profiler was active) and look at what my high waits were....  But then I expect you have already gone through these steps...
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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