March 6, 2012 at 2:10 pm
Hi,
This is a stored procedure which i am running in my OLE DB Source
ALTER PROC [dbo].[usp_CreateSMTIntervalUsageData]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MaxGroupID INT , @Mst_ID INT
SET @Mst_ID = 1
SET @MaxGroupID = (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
WHILE @Mst_ID <= @MaxGroupID
BEGIN
INSERT INTO [DE_MARKET_LIBRARY].[SMT].[IntervalData_Export]
([Service_Point_ID]
,[Account_Id]
,[Usage_Value]
,[Date]
,[Time]
,[Units]
,[Is_Estimate]
,[DayLight_Savings]
,[Hr_Id])
SELECT
ESIID AS [Service_Point_ID],
'' AS [Account_Id],
I.[0-15minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0000' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS [Hr_Id]
FROM SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE I.Mst_ID = @Mst_ID
UNION
SELECT
ESIID AS [Service_Point_ID],
'' AS [Account_Id],
I.[15-30minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0015' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS [Hr_Id]
FROM SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE I.Mst_ID = @Mst_ID
UNION
SELECT
ESIID AS [Service_Point_ID],
'' AS [Account_Id],
I.[30-45minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0030' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS [Hr_Id]
FROM SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE I.Mst_ID = @Mst_ID
UNION
SELECT
ESIID AS [Service_Point_ID],
'' AS [Account_Id],
I.[45-60minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0045' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS Hr_ID
FROM SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE I.Mst_ID = @Mst_ID
SET @Mst_ID = @Mst_ID + 1
END
SELECT
[Service_Point_ID]
,[Account_Id]
,[Usage_Value]
,[Date]
,[Time]
,[Units]
,[Is_Estimate]
,[DayLight_Savings]
,[Hr_Id]
FROM [DE_MARKET_LIBRARY].[SMT].[IntervalData_Export]
ORDER BY [Service_Point_ID],[Hr_Id],[Time]
SET NOCOUNT OFF
END
So the problem is it is taking a lot of time to execute this code is there any way that i can reduce the time. Is there a way i can optimize my query.
March 6, 2012 at 2:16 pm
have you checked the execution plan for the query to see if anything stands out
March 6, 2012 at 2:20 pm
Yes, the query takes only 0.9 sec. But when i run it in the package for pulling the data it is taking more than 20 min . Please advice me
March 6, 2012 at 2:24 pm
UNION will remove duplicates. If you know there will be no duplicates the you can use UNION ALL and increase performance.
March 6, 2012 at 2:28 pm
How is that it is taking in SSMS only 9 secs. And when i place the same query in the SSIS it is taking more time to pull the data ....is there any thing wrong with my query ..Can you please advice.
March 6, 2012 at 2:46 pm
ramchandra2cool (3/6/2012)
How is that it is taking in SSMS only 9 secs. And when i place the same query in the SSIS it is taking more time to pull the data ....is there any thing wrong with my query ..Can you please advice.
If the query runs in 0.9 seconds, the problem is not the query, but the dataflow. Possibly the options in the dataflow. Possibly the target system is slow for writes.
Please describe, in detail, the dataflow that takes 20 minutes.
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
March 6, 2012 at 2:46 pm
ramchandra2cool (3/6/2012)
How is that it is taking in SSMS only 9 secs. And when i place the same query in the SSIS it is taking more time to pull the data ....is there any thing wrong with my query ..Can you please advice.
How should we know? You have not provided the tables and indexes that are being queried. You haven't given us any details about how much data in in these tables. You have not provided us with any sample data. Please see the link in my signature by Jeff Moden so that we can better help you.
Jared
CE - Microsoft
March 6, 2012 at 2:53 pm
Sorry i am new to it . I post it according to the link you have shared ASAP
March 6, 2012 at 3:04 pm
Try simplifying your query as a troubleshooting tactic. Try it with just the select. Then try it without all the unions. Maybe one of the selects within the unions is the issue. Divide and conquer.
March 6, 2012 at 3:09 pm
I also like avoiding unions if I can by inserting into a temp table or a table variable and then selecting from that. This seems to increase performance in many cases.
Jared
CE - Microsoft
March 6, 2012 at 3:11 pm
Whats the data access mode of the OLE DB Source set to?
March 6, 2012 at 3:25 pm
Initially the data is like the first image. I used the above query and made the data like in the second image. Can you advice me how to change my query.
March 6, 2012 at 3:29 pm
If you post your data properly you are more likely to get someone to help you.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 6, 2012 at 8:09 pm
You could also rewrite your procedure. Below are two possibilities, not sure if they are totally correct as I have nothing with which to test against.
-- First rewrite of procedure:
ALTER PROC [dbo].[usp_CreateSMTIntervalUsageData]
AS
BEGIN
SET NOCOUNT ON;
--DECLARE @MaxGroupID INT , @Mst_ID INT
--SET @Mst_ID = 1
--SET @MaxGroupID = (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
--WHILE @Mst_ID <= @MaxGroupID
--BEGIN
INSERT INTO [DE_MARKET_LIBRARY].[SMT].[IntervalData_Export](
[Service_Point_ID]
,[Account_Id]
,[Usage_Value]
,[Date]
,[Time]
,[Units]
,[Is_Estimate]
,[DayLight_Savings]
,[Hr_Id])
SELECT
ESIID AS [Service_Point_ID],
' ' AS [Account_Id],
I.[0-15minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0000' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS [Hr_Id]
FROM
SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE
I.Mst_ID >= (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
UNION
SELECT
ESIID AS [Service_Point_ID],
' ' AS [Account_Id],
I.[15-30minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0015' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS [Hr_Id]
FROM
SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE
I.Mst_ID >= (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
UNION
SELECT
ESIID AS [Service_Point_ID],
' ' AS [Account_Id],
I.[30-45minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0030' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS [Hr_Id]
FROM
SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE
I.Mst_ID >= (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
UNION
SELECT
ESIID AS [Service_Point_ID],
' ' AS [Account_Id],
I.[45-60minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0045' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS Hr_ID
FROM
SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE
I.Mst_ID >= (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
--SET @Mst_ID = @Mst_ID + 1
--END
SELECT
[Service_Point_ID]
,[Account_Id]
,[Usage_Value]
,[Date]
,[Time]
,[Units]
,[Is_Estimate]
,[DayLight_Savings]
,[Hr_Id]
FROM
[DE_MARKET_LIBRARY].[SMT].[IntervalData_Export]
ORDER BY
[Service_Point_ID],[Hr_Id],[Time]
SET NOCOUNT OFF
END
--========================================================================================
-- Second rewrite of procedure:
ALTER PROC [dbo].[usp_CreateSMTIntervalUsageData]
AS
BEGIN
SET NOCOUNT ON;
SELECT
M.ESIID AS [Service_Point_ID],
' ' AS [Account_Id],
I.[0-15minInterval] AS [Usage_Value],
SUBSTRING (M.Read_Start_Date,1,8) AS [Date],
'0000' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
M.[Hr_Id] AS [Hr_Id]
FROM
SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE
I.Mst_ID >= (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
UNION
SELECT
ESIID AS [Service_Point_ID],
' ' AS [Account_Id],
I.[15-30minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0015' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS [Hr_Id]
FROM
SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE
I.Mst_ID >= (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
UNION
SELECT
ESIID AS [Service_Point_ID],
' ' AS [Account_Id],
I.[30-45minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0030' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS [Hr_Id]
FROM
SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE
I.Mst_ID >= (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
UNION
SELECT
ESIID AS [Service_Point_ID],
' ' AS [Account_Id],
I.[45-60minInterval] AS [Usage_Value],
SUBSTRING (Read_Start_Date,1,8) AS [Date],
'0045' AS [Time],
'KWH' AS [Units],
'A' AS [Is_Estimate],
'CST' AS [DayLight_Savings],
[Hr_Id] AS Hr_ID
FROM
SMT.ESIIDMasterData M
JOIN SMT.IntervalUsageData I
ON M.Mst_Id = I.Mst_ID
WHERE
I.Mst_ID >= (SELECT MAX(GroupID) FROM SMT.IntervalUsageData)
ORDER BY
[Service_Point_ID],
[Hr_Id],
[Time];
END
March 6, 2012 at 8:10 pm
And yes, if you know that there won't be duplicates you can change the UNIONs to UNION ALLs.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply