January 19, 2012 at 12:22 pm
Evil Kraig F (1/19/2012)
ChrisM@home (1/19/2012)
Evil Kraig F (1/19/2012)
s.njenga (1/18/2012)
Your query is only referencing @introwno ones instead of referencing all rows in the staging table without having to loop through each row.If that's in reference to Chris' re-write, he was merely re-writing the inner query, not completely overhauling the logic, trying to make life easier.
That would be my guess too Craig.
I reckon, in your query, you might need a correlation between the cross apply and the outer query?
It's there, just well hidden. Part of what I was thinking of 'cleaning up'.
JOINStagingVolume AS sv2ONsv.BILLDATE_ID = sv2.BILLDATE_ID
Gotcha. Nice one.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 19, 2012 at 4:13 pm
I run the query below for only 100 records and it runs for 5 mins plus. I had to cancel. When I run the Select without ca.HDDSUM AS TOTHDD it runs fine. The INSERT never happens.
I've included scripts for the four tables below:
SET NOCOUNT ON;
INSERT Calendarized_Data(REGION, CONTRACT_NB, ACCT_NO, WEATHER_STATION,
[DATE],BILL_YEAR, FIN_YEAR, FIN_MONTH, FROM_DATE, TO_DATE, HDD
)
SELECT TOP 100 sv.Broker_NB, sv.Contract_NB, sv.ACCT_NO, ahd.WEATHER_STATION, ahd.[Date], sv.BILL_YEAR,
sv.FIN_YEAR, sv.FIN_MONTH, sv.FROM_DATE, sv.TO_DATE, ahd.HDD, ca.HDDSUM AS TOTHDD
FROM [NADECORP\SNjenga].StagingVolume AS sv
JOIN [NADECORP\SNjenga].ACTUAL_HDD_DAILY AS ahdON sv.Weather_station = ahd.Weather_station
CROSS APPLY
(SELECT SUM(HDD) AS HDDSUM FROM ACTUAL_HDD_DAILY AS ahd2
JOIN StagingVolume AS sv2 ON sv.BILLDATE_ID = sv2.BILLDATE_ID
JOIN EXISTING_CUST_ALPHA_BETA AS ecab ON ahd2.Weather_station = ecab.weather_station
AND ecab.account_number = sv2.acct_no
WHERE ahd2.[DATE] between sv2.FROM_DATE AND sv2.TO_DATE and ecab.Market_ID = 3
) AS ca
WHERE
ahd.Market_ID = 3
/****** Object: Table [NADECORP\SNjenga].[StagingVolume] Script Date: 01/19/2012 16:55:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [NADECORP\SNjenga].[StagingVolume](
[BillDate_ID] [int] IDENTITY(1,1) NOT NULL,
[BROKER_NB] [varchar](50) NULL,
[CONTRACT_NB] [int] NULL,
[WEATHER_STATION] [varchar](50) NULL,
[ACCT_NO] [varchar](50) NULL,
[BILL_YEAR] [int] NULL,
[BILL_MONTH] [int] NULL,
[FIN_YEAR] [int] NULL,
[FIN_MONTH] [int] NULL,
[FROM_DATE] [datetime] NULL,
[TO_DATE] [datetime] NULL,
[CONSUM_VOL_M3] [decimal](30, 15) NULL,
[CONSUM_TYPE] [varchar](50) NULL,
[PPG_RATE_PER_M3] [decimal](9, 7) NULL,
[CONSUM_DOLL] [money] NULL,
[Modified] [datetime] NOT NULL,
CONSTRAINT [PK_StagingVolume] PRIMARY KEY CLUSTERED
(
[BillDate_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [NADECORP\SNjenga].[StagingVolume] ADD CONSTRAINT [DF__StagingVo__Modif__4E5E8EA2] DEFAULT (dateadd(hour,(-1),getdate())) FOR [Modified]
GO
CREATE TABLE [NADECORP\SNjenga].[EXISTING_CUST_ALPHA_BETA](
[CUST_ID] [int] IDENTITY(1,1) NOT NULL,
[SITE_ID] [varchar](20) NOT NULL,
[ACCOUNT_NUMBER] [varchar](30) NULL,
[SITE_POSTALZIP] [varchar](7) NULL,
[SITE_LOCATION] [varchar](50) NULL,
[WEATHER_STATION] [varchar](10) NULL,
[DAILY_ALPHA] [float] NULL,
[HDD_BETA] [float] NULL,
[BILL_COUNT] [int] NULL,
[SUM_OF_SQUARED_ERRORS] [float] NULL,
[SAMPLE_COEFFICIENT_OF_DETERMINATION] [float] NULL,
[T_STATISTIC] [float] NULL,
[IS_DEFAULT_ALPHA_BETA] [bit] NOT NULL,
[WEATHER_DATA_SOURCE] [varchar](50) NULL,
[WEATHER_HDD_CUTOFF_DATA_SOURCE] [varchar](50) NULL,
[Market_ID] [int] NOT NULL,
[DPA_ID] [int] NULL,
[ForecastVersion_ID] [int] NOT NULL,
[Date_Stamp] [datetime] NOT NULL,
[Engine_ID] [int] NULL,
[Start_Date] [datetime] NULL,
[End_Date] [datetime] NULL,
[PROGRAM_TYPE] [varchar](20) NULL,
[PROGRAM_PRICE] [float] NULL,
[PRICE_CHANGE_INCREMENT] [float] NULL,
[TRANSPORT] [float] NULL,
[TIME_INTERVAL] [float] NULL,
[TRANSPORT_CURVE] [varchar](30) NULL,
[DAILY_ALPHA_SEASON_2] [float] NULL,
[DAILY_ALPHA_SEASON_3] [float] NULL,
[DAILY_ALPHA_SEASON_4] [float] NULL,
[USE_CLASS_CODE] [varchar](30) NULL,
[DIST_CODE] [varchar](50) NULL,
[FORECAST_TYPE] [varchar](10) NULL,
[Modified_Date] [datetime] NULL,
CONSTRAINT [PK_EXISTING_CUST_ALPHA_BETA] PRIMARY KEY CLUSTERED
(
[CUST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [NADECORP\SNjenga].[EXISTING_CUST_ALPHA_BETA] ADD CONSTRAINT [DF__Customer__Modif__513AFB4D] DEFAULT (dateadd(hour,(-1),getdate())) FOR [Modified_Date]
GO
CREATE TABLE [NADECORP\SNjenga].[Calendarized_Data](
[CAL_ID] [int] IDENTITY(1,1) NOT NULL,
[REGION] [varchar](50) NULL,
[CONTRACT_NB] [int] NULL,
[SITE_ID] [int] NULL,
[ACCT_NO] [varchar](50) NULL,
[UTILITY] [varchar](50) NULL,
[MARKET] [varchar](50) NULL,
[WEATHER_STATION] [varchar](50) NULL,
[CONSUM_TYPE] [varchar](50) NULL,
[CONTRACT_TYP] [varchar](50) NULL,
[DATE] [datetime] NULL,
[BILL_YEAR] [int] NULL,
[BILL_MONTH] [int] NULL,
[BILL_DATE] [datetime] NULL,
[FIN_YEAR] [int] NULL,
[FIN_MONTH] [int] NULL,
[FROM_DATE] [datetime] NULL,
[TO_DATE] [datetime] NULL,
[HDD] [decimal](30, 5) NULL,
[ZERO_HDD_COUNT] [int] NULL,
[BILL_PERIOD_HDD] [decimal](30, 5) NULL,
[DAILY_ALPHA] [decimal](30, 5) NULL,
[HDD_BETA] [decimal](30, 5) NULL,
[ALPHA_BETA] [decimal](30, 5) NULL,
[PERIOD_ALPHA_BETA] [decimal](30, 5) NULL,
[ALPHA_BETA%] [decimal](30, 5) NULL,
[BILL_PERIOD_CONSUM_VOL] [decimal](30, 5) NULL,
[CAL_CONSUM_VOL] [decimal](30, 5) NULL,
[PPG_RATE_COMM] [money] NULL,
[PPG_RATE_TRNS] [money] NULL,
[CAL_CONSUM_DOLL] [money] NULL,
[Modified] [datetime] NOT NULL,
CONSTRAINT [PK_Calendarized_Data] PRIMARY KEY CLUSTERED
(
[CAL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [NADECORP\SNjenga].[Calendarized_Data] ADD CONSTRAINT [DF__Calendari__Modif__513AFB4D] DEFAULT (dateadd(hour,(-1),getdate())) FOR [Modified]
GO
January 19, 2012 at 4:40 pm
Can you pull the estimated .sqlplan for the process? You'll find instructions if you need it in the second link on the left in my signature.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply