Wsrtjtyk

I have a SQL statement that has a weird 2nd nested SQL statement that I think is causing this query to run for 6+ min and any suggestions/help would be appreciated. I tried creating a TEMP table for the values in the nested SQL statement and just do a simple join but there is nothing to join on in the SQL code so that is why they used a 1=1 in the ON statement for the join. Here is the SQL code:

Declare @TransactionEndDate datetime;

Select @TransactionEndDate = lastmonth_end from dbo.DTE_udfCommonDates(GETDATE());



Select ''''+TreatyName as Treaty,

cast(EndOfMonth as Date) as asOfDate,

Count(Distinct ClaimSysID) as ClaimCount,

Count(Distinct FeatureSysID) as FeatureCount,

Sum(OpenReserve) as OpenReserve

From (

    Select  

       TreatyName,

       EndOfMonth,

       dbo.CMS_Claims.ClaimSysID,

       FeatureSysID,

       sum(IW_glGeneralLedger.TransactionAmount)*-1 as OpenReserve

    From  dbo.CMS_Claims 

        Inner Join dbo.CMS_Claimants 

        On dbo.CMS_Claims.ClaimSysID = dbo.CMS_Claimants.ClaimSysID

        Inner Join dbo.CMS_Features 

        On dbo.CMS_Features.ClaimantSysID = dbo.CMS_Claimants.ClaimantSysID 

        Left Join dbo.IW_glGeneralLedger 

        On IW_glGeneralLedger.FeatureID = dbo.CMS_Features.FeatureSysID 

        Left Join dbo.IW_glSubChildAccount 

        On dbo.IW_glSubChildAccount.glSubChildAccountID = dbo.IW_glGeneralLedger.glSubChildAccountSysID

        Left Join dbo.IW_glAccountGroup 

        On dbo.IW_glAccountGroup.glAccountGroupID = dbo.IW_glSubChildAccount.glAccountGroupSysID

        Left Join dbo.IW_BankRegister 

        On dbo.IW_BankRegister.BankRegisterSysID = dbo.IW_glGeneralLedger.BankRegisterID

        Left Join dbo.IW_BankRegisterStatus 

        On dbo.IW_BankRegisterStatus.BankRegisterStatusSysID = dbo.IW_BankRegister.BankRegisterStatusID

        **Left Join (Select Distinct dbo.DTE_get_month_end(dt) as EndOfMonth 

            From IW_Calendar 

            Where dt Between '3/1/2004' 

            and @TransactionEndDate) as dates 

        on 1=1**

        Left Join dbo.IW_ReinsuranceTreaty 

        On dbo.IW_ReinsuranceTreaty.TreatySysID = IW_glGeneralLedger.PolicyTreatyID     

        Where dbo.IW_glGeneralLedger.TransactionDate Between '1/1/2004 00:00:00' And EndOfMonth

        And dbo.IW_glAccountGroup.Code In ('RESERVEINDEMNITY')  

        And (

          (dbo.IW_glGeneralLedger.BankRegisterID Is Null) 

          Or (

          (IW_BankRegister.PrintedDate Between '1/1/2004 00:00:00' And EndOfMonth Or dbo.IW_glGeneralLedger.BankRegisterID = 0)

             And 

            (dbo.IW_BankRegisterStatus.EnumValue In ('Approved','Outstanding','Cleared','Void') Or dbo.IW_glGeneralLedger.BankRegisterID = 0))

        )

    Group By TreatyName, dbo.CMS_Claims.ClaimSysID, FeatureSysID, EndOfMonth

    Having sum(IW_glGeneralLedger.TransactionAmount)  <> 0

) As Data

Group By TreatyName,EndOfMonth

Order By EndOfMonth, TreatyName

This nested SQL code only provides a table of End of Month values in one column called EndOfMonth and this is what I'm trying to fix:

Select Distinct dbo.DTE_get_month_end(dt) as EndOfMonth 

        From IW_Calendar 

        Where dt Between '3/1/2004' 

        and @TransactionEndDate

Please use the below methods to increase the query performance.

1. Use temporary tables. ( load relevant data into temporary tables with necessary where conditions and then join).


2. Use clustered and non clustered indexes to your tables.


3. Create Multiple-Column Indexes.


4. Index the ORDER-BY / GROUP-BY / DISTINCT Columns for Better Response Time.


5. Use Parameterized Queries.


6. 
   

   Use query hints accordingly.

   
   
   

   NOLOCK: In the event that data is locked, this tells SQL Server to read data from the last known value available, also known as a dirty read. Since it is possible to use some old values and some new values, data sets can contain inconsistencies. Do not use this in any place in which data quality is important.

   
   
   

   RECOMPILE: Adding this to the end of a query will result in a new execution plan being generated each time this query executed. This should not be used on a query that is executed often, as the cost to optimize a query is not trivial. For infrequent reports or processes, though, this can be an effective way to avoid undesired plan reuse. This is often used as a bandage when statistics are out of date or parameter sniffing is occurring.

   
   

MERGE/HASH/LOOP: This tells the query optimizer to use a specific type of join as part of a join operation. This is super-risky as the optimal join will change as data, schema, and parameters evolve over time. While this may fix a problem right now, it will introduce an element of technical debt that will remain for as long as the hint does.

OPTIMIZE FOR: Can specify a parameter value to optimize the query for. This is often used when we want performance to be controlled for a very common use case so that outliers do not pollute the plan cache. Similar to join hints, this is fragile and when business logic changes, this hint usage may become obsolete.