November 15, 2007 at 3:34 pm
We have a set of 7 tables contain customer information, that are partitioned horizontally by year. Each table has anywhere from 500 to 600 million records. We have a view on top of the tables so it looks like one table. The problem is that if you use the table and still want to go after just information in one table and you are querying and specifying only one year, it will still go after all of the tables and cause the query to run 4 to 5 times longer than if you were to go after just the one table.
I am trying to create a DTS package that outputs a couple of text files to be loaded into another system. They are done by year but I want to improve the performance by going after the specific table defined by year. I cannot just output the table because I have to join on a few others to get the correct data.
Is there any way that I can use DTS Global Variables to replace tables in a data pump out to text files? I cannot create dynamic SQL Statements because some of the queries can be over 8,000 characters long.
November 16, 2007 at 10:11 am
Can you show me the query?
Anyway I have created dynamic Sql over 8000 characters.
DECLARE @SQL1 VARCHAR(8000),
@SQL2 VARCHAR(8000),
@SQL3 VARCHAR(8000)
SET @SQL1 = .....
SET @SQL2 = ....
SET @SQL3 = .......
EXEC (@SQL1 + @SQL2 + @SQL3)
Works very well.
November 16, 2007 at 1:12 pm
Here is the query that I am trying to use with your suggestion.
--exec up_dynamic_sql_test '2004'
alter procedure up_dynamic_sql_test @year char(4) AS
SET NOCOUNT ON
DECLARE @SQL1 varchar(8000)
DECLARE @SQL2 varchar(8000)
DECLARE @SQL3 varchar(8000)
SET @SQL1 = 'SELECT
Case When b.L00_DS in (''Apr - Wtd Outstanding'', ''Ffiec - Contra Receivables'',
''Ffiec - W/O Cured Receivables'', ''Interest Rate - Wtd Outstanding'',
''Net Balance'', ''Number Of Accounts'',
''Term - Wtd Volume'', ''Term - Wtd Outstanding'',
''APR - Wtd Volume'', ''Interest Rate - Wtd Volume'')
Or a.Fact_Desc_ID <= 28 Then
''Cf '' + b.L00_DS
Else
b.L00_DS
End As Account
, e.Month_Abbr As Time
, ''Actual'' As Scenario
, ''Year '' + e.Year As Year
, Case When i.Product = ''Non R/E'' Then
Case When g.Term_Range = ''1 YEAR'' Then ''Lte 12 Mth''
When g.Term_Range = ''2 YEAR'' Then ''13 - 24 Mth''
When g.Term_Range = ''3 YEAR'' Then ''25 - 36 Mth''
When g.Term_Range = ''4 YEAR'' Then ''37 - 48 Mth''
When g.Term < 55 Then ''49 - 54 Mth''
When g.Term_Range = ''5 YEAR'' Then ''55 - 60 Mth''
When g.Term_Id > 60 Then ''61 - 179 Mth''
End
Else
Case When c.Amort_Term_Id > 0 Then l.Term_Range_2
Else
''Unknown Amort''
End
End As Amortization
, Prov_Cd As Province
, CaseWhen i.Product = ''Non R/E'' Then Cast(k.Homeowner_Ind as Varchar(20))
When i.Product = ''R/E'' Then
--Homeplan
CaseWhen Branch_Id In (3321, 3368, 3375, 3382) Then
Case When g.Term_Range = ''1 YEAR'' And ARM_or_Fixed = ''Fixed'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''1 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''1 Yr Homeplan - 2nd Mortgage''
Else ''1 Yr Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''2 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''2 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''2 Yr Homeplan - 2nd Mortgage''
Else ''2 Yr Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''3 YEAR'' And ARM_or_Fixed = ''Fixed'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''3 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''3 Yr Homeplan - 2nd Mortgage''
Else ''3 Yr Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''3 YEAR'' And ARM_or_Fixed = ''ARM'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''3 Yr ARM Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''3 Yr ARM Homeplan - 2nd Mortgage''
Else ''3 Yr ARM Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''4 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''4 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''4 Yr Homeplan - 2nd Mortgage''
Else ''4 Yr Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''5 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''5 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''5 Yr Homeplan - 2nd Mortgage''
Else ''5 Yr Homeplan - Other Mortgage Indicator''
End
Else ''Homeplan - unknown range: '' + g.Term_Range + '' : '' + Lien_Position
End
When g.Term_Range = ''1 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''1 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''1 Yr Retail RE - 2nd Mortgage''
Else ''1 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''2 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''2 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''2 Yr Retail RE - 2nd Mortgage''
Else ''2 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''3 YEAR'' And Arm_or_Fixed = ''ARM'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''3 Yr ARM Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''3 Yr ARM Retail RE - 2nd Mortgage''
Else ''3 Yr ARM Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''3 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''3 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''3 Yr Retail RE - 2nd Mortgage''
Else ''3 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''4 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''4 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''4 Yr Retail RE - 2nd Mortgage''
Else ''4 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''5 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''5 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''5 Yr Retail RE - 2nd Mortgage''
Else ''5 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''6 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''6 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''6 Yr Retail RE - 2nd Mortgage''
Else ''6 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''7 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''7 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''7 Yr Retail RE - 2nd Mortgage''
Else ''7 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''8 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''8 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''8 Yr Retail RE - 2nd Mortgage''
Else ''8 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''9 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''9 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''9 Yr Retail RE - 2nd Mortgage''
Else ''9 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''10 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''10 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''10 Yr Retail RE - 2nd Mortgage''
Else ''10 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''11 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''11 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''11 Yr Retail RE - 2nd Mortgage''
Else ''11 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''12 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''12 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''12 Yr Retail RE - 2nd Mortgage''
Else ''12 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''13 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''13 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''13 Yr Retail RE - 2nd Mortgage''
Else ''13 Yr Retail RE - Other Mortgage Indicator''
End
Else ''Retail R/E - unknown range: '' + g.Term_Range + '' : '' + Lien_Position
End
Else
''UNKNOWN PRODUCT TYPE''
EndAs Product
, h.Loan_MonthAs Origination
, Case WHEN Branch_Id < 10 Then ''00000'' + CAST(Branch_Id AS CHAR(1))
WHEN Branch_Id < 100 Then ''0000'' + CAST(Branch_Id AS CHAR(2))
WHEN Branch_Id < 1000 Then ''000'' + CAST(Branch_Id AS CHAR(3))
ELSE ''00'' + CAST(Branch_Id AS CHAR(4))
END AS Organization
,Case When Datediff(Month,f.Loan_Dt,DateAdd(Month, -1,GetDate())) < 121 Then
Cast(Datediff(Month,f.Loan_Dt,DateAdd(Month, -1,GetDate())) as varchar(3)) + '' Months''
Else
''121 Months+''
End As Age
, Sum(Fact_Amount) As Data'
set @sql2 = 'From t_st_fact_' + @year + ' a
Inner Join T_ESS_FACT_DESCRIPTION b
On a.Fact_Desc_Id = b.Fact_Desc_Id
Inner Join t_st_customer_account_history_' + @year + ' c
On a.Account_Time_Id = c.Account_Time_Id
Inner Join T_ST_INTEREST_TYPE d
On c.Interest_id = d.Interest_id
Inner Join T_ST_TRANSACTION_DATE e
On a.Transaction_Dt_Id = e.Transaction_Dt_Id
Inner Join T_ST_CUSTOMER_ACCOUNT_BOOKING f
On c.Account_Id = f.Account_Id
Inner Join T_ST_TERM g
On c.Term_Id = g.Term_Id
Inner Join T_ST_ORIGINATIONh
On f.Loan_Dt = h.Loan_Dt
Inner Join T_ST_PRODUCT i
On f.Product_Id = i.Product_Id
Inner Join T_ST_STATE_CODE j
On c.Legal_State_Code_Id = j.Legal_State_Code_Id
Inner Join T_ST_HOUSING k
On c.Housing_Id = k.Housing_Id
Left Outer Join T_ST_TERM l
On c.Amort_Term_Id = l.Term_Id
Where Product Not in (''02 AUTO'', ''SALE'')
and e.Year = ''' + @year + ''''
SET @SQL3 = 'Group by
Case When b.L00_DS in (''Apr - Wtd Outstanding'', ''Ffiec - Contra Receivables'',
''Ffiec - W/O Cured Receivables'', ''Interest Rate - Wtd Outstanding'',
''Net Balance'', ''Number Of Accounts'',
''Term - Wtd Volume'', ''Term - Wtd Outstanding'',
''APR - Wtd Volume'', ''Interest Rate - Wtd Volume'')
Or a.Fact_Desc_ID <= 28 Then
''Cf '' + b.L00_DS
Else
b.L00_DS
End
, e.Month_Abbr
, e.Year
, h.Loan_Month
, Case When i.Product = ''Non R/E'' Then
Case When g.Term_Range = ''1 YEAR'' Then ''Lte 12 Mth''
When g.Term_Range = ''2 YEAR'' Then ''13 - 24 Mth''
When g.Term_Range = ''3 YEAR'' Then ''25 - 36 Mth''
When g.Term_Range = ''4 YEAR'' Then ''37 - 48 Mth''
When g.Term < 55 Then ''49 - 54 Mth''
When g.Term_Range = ''5 YEAR'' Then ''55 - 60 Mth''
When g.Term_Id > 60 Then ''61 - 179 Mth''
End
Else
Case When c.Amort_Term_Id > 0 Then l.Term_Range_2
Else ''Unknown Amort''
End
End
, Prov_Cd
, CaseWhen i.Product = ''Non R/E'' Then
Cast(k.Homeowner_Ind as Varchar(20))
When i.Product = ''R/E'' Then
CaseWhen Branch_Id In (3321, 3368, 3375, 3382) Then
Case When g.Term_Range = ''1 YEAR'' And ARM_or_Fixed = ''Fixed'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''1 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''1 Yr Homeplan - 2nd Mortgage''
Else ''1 Yr Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''2 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''2 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''2 Yr Homeplan - 2nd Mortgage''
Else ''2 Yr Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''3 YEAR'' And ARM_or_Fixed = ''Fixed'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''3 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''3 Yr Homeplan - 2nd Mortgage''
Else ''3 Yr Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''3 YEAR'' And ARM_or_Fixed = ''ARM'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''3 Yr ARM Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''3 Yr ARM Homeplan - 2nd Mortgage''
Else ''3 Yr ARM Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''4 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''4 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''4 Yr Homeplan - 2nd Mortgage''
Else ''4 Yr Homeplan - Other Mortgage Indicator''
End
When g.Term_Range = ''5 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''5 Yr Homeplan - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''5 Yr Homeplan - 2nd Mortgage''
Else ''5 Yr Homeplan - Other Mortgage Indicator''
End
Else ''Homeplan - unknown range: '' + g.Term_Range + '' : '' + Lien_Position
End
When g.Term_Range = ''1 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''1 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''1 Yr Retail RE - 2nd Mortgage''
Else ''1 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''2 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''2 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''2 Yr Retail RE - 2nd Mortgage''
Else ''2 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''3 YEAR'' And Arm_or_Fixed = ''ARM'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''3 Yr ARM Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''3 Yr ARM Retail RE - 2nd Mortgage''
Else ''3 Yr ARM Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''3 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''3 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''3 Yr Retail RE - 2nd Mortgage''
Else ''3 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''4 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''4 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''4 Yr Retail RE - 2nd Mortgage''
Else ''4 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''5 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''5 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''5 Yr Retail RE - 2nd Mortgage''
Else ''5 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''6 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''6 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''6 Yr Retail RE - 2nd Mortgage''
Else ''6 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''7 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''7 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''7 Yr Retail RE - 2nd Mortgage''
Else ''7 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''8 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''8 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''8 Yr Retail RE - 2nd Mortgage''
Else ''8 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''9 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''9 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''9 Yr Retail RE - 2nd Mortgage''
Else ''9 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''10 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''10 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''10 Yr Retail RE - 2nd Mortgage''
Else ''10 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''11 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''11 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''11 Yr Retail RE - 2nd Mortgage''
Else ''11 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''12 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''12 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''12 Yr Retail RE - 2nd Mortgage''
Else ''12 Yr Retail RE - Other Mortgage Indicator''
End
When g.Term_Range = ''13 YEAR'' Then
Case When Lien_Position = ''1ST MORTGAGE'' Then ''13 Yr Retail RE - 1st Mortgage''
When Lien_Position = ''2ND MORTGAGE'' Then ''13 Yr Retail RE - 2nd Mortgage''
Else ''13 Yr Retail RE - Other Mortgage Indicator''
End
Else ''Retail R/E - unknown range: '' + g.Term_Range + '' : '' + Lien_Position
End
Else ''UNKNOWN PRODUCT TYPE''
End
, Case WHEN Branch_Id < 10 Then ''00000'' + CAST(Branch_Id AS CHAR(1))
WHEN Branch_Id < 100 Then ''0000'' + CAST(Branch_Id AS CHAR(2))
WHEN Branch_Id < 1000 Then ''000'' + CAST(Branch_Id AS CHAR(3))
ELSE ''00'' + CAST(Branch_Id AS CHAR(4))
END
, Case When Product = ''Non R/E'' Then
Case When g.Term_Range = ''1 YEAR'' Then ''LTE 12 MTH''
When g.Term_Range = ''2 YEAR'' Then ''13 - 24 MTH''
When g.Term_Range = ''3 YEAR'' Then ''25 - 36 MTH''
When g.Term_Range = ''4 YEAR'' Then ''37 - 48 MTH''
When g.Term_Id < 55 Then ''49 - 54 MTH''
When g.Term_Range = ''5 YEAR'' Then ''55 - 60 MTH''
When g.Term_Id > 60 Then ''61 - 179 MTH''
End
Else ''Unknown Amort''
End
, Case When Datediff(Month,f.Loan_Dt,DateAdd(Month, -1,GetDate())) < 121 Then Cast(Datediff(Month,f.Loan_Dt,DateAdd(Month, -1,GetDate())) as varchar(3)) + '' Months''
Else ''121 Months+''
End'
select @sql1
select @sql2
select @sql3
exec (@sql1 + @sql2 + @sql3)
go
I can get the query to look good when I select it from the variables but I get the error
Server: Msg 170, Level 15, State 1, Line 154
Line 154: Incorrect syntax near 't_st_fact_2004'.
when I try to execute it.
November 16, 2007 at 1:43 pm
Nevermind, I figured out what was happening. I needed to add a carrage return because otherwise it was putting two words together.
Thanks for your help.:)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply