need to performance tune by datareader

Aurovinda Shyamal

6y1kLearn iOS Programming

Hi,

Below am sending my code and I need some improvement by replacing dataadapter,dataset with data reader:

public DataSet GetEpiInputData(decimal forecastId, decimal scenarioId, decimal? lineId)

{

var result = new DataSet();

using (IndForecastingContext dbContext = new IndForecastingContext())

{

var conn = dbContext.Database.Connection;

var connectionState = conn.State;

List spParams = new List();

try

{

spParams.Add(new SqlParameter { ParameterName = "@ForecastID", Value = forecastId });

spParams.Add(new SqlParameter { ParameterName = "@ScenarioID", Value = scenarioId });

if (lineId.HasValue)

{

spParams.Add(new SqlParameter { ParameterName = "@LineID", Value = lineId });

}

if (connectionState != ConnectionState.Open)

conn.Open();

DbProviderFactory factory = DbProviderFactories.GetFactory(conn);

using (var cmd = conn.CreateCommand())

{

cmd.CommandText = "[dbo].[GetEpiInputData]";

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandTimeout = 0;

cmd.Parameters.AddRange(spParams.ToArray());

using (var adapter = factory.CreateDataAdapter())

{

adapter.SelectCommand = cmd;

adapter.Fill(result);

}

catch (Exception ex)

{

}

finally

{

if (connectionState == ConnectionState.Open)

conn.Close();

}

return result;

}

The line I marked as yellow its taking 3 to 4 mnts.when it executes datadapter fills multiple table into datasets.Is there any way to optimize .Finally any how I have to return dataset output.

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta6y ago
Try this one https://stackoverflow.com/a/11345818
Aurovinda Shyamal6y ago
@Amit the 1st step you mentioned is not improve the performance its same time taking as my previous code.
The 2nd one is you are asking to use datareader to collect datatables from recordset returne by stored proc and then fill the data set.In this stored proc we are getting 4 table I mean 4 record set.Could you please give some idea(any code) that how we collect datatable and fill data set by datareader, if it return 3 or 4 tables
.
Amit Gupta6y ago
@Aurovinda I think you forgot to read your question title. Anyway, as SP takes only 2 sec to execute whereas adapter.Fill(..) executes in minutes, as I reviewed your code and you should try this solution. 1. Do try using ADO functionality instead of EF ( try Ado's sql adapter and then fill your datatable). 2. If 1st step didn't work, then try using dataReader to load the datatable. Do let me know if you require any clarification.
Aurovinda Shyamal6y ago
I never used datareader.I asked the question how we can improve by using datareader if possible.
Here only one line i,e " adapter.Fill(result);" is taking more time.
Amit Gupta6y ago
Where did you have used DataReader? Adapter.Fill function completes when all the data were fetched from the DB whereas DataReader reads the data in forward only mode. You can also use debugging feature and watch which steps is taking time. If you able to find that then solution is in your hand :)
Salman Beg6y ago
You can check performance of your Stored Procedure in Sql Profiler and can improve also. Also you can use Execution Plan in SQL Server Management Studio. Thanks.
Aurovinda Shyamal6y ago
output of stored proc:
Aurovinda Shyamal6y ago
stored proc has no issues, it returns 4 record sets(4 tables) and if we run in ssms its complete fraction of second

-- =============================================
-- Create date: 19-04-2018
-- Description: To fetch Epi parameter data for EPI Input page
-- =============================================
CREATE PROCEDURE [dbo].[GetEpiInputData]
@ForecastID NUMERIC(18, 0),
@ScenarioID NUMERIC(18, 0),
@LineID NUMERIC(18, 0) = NULL
AS
BEGIN

SET NOCOUNT ON;

DECLARE
@SQL NVARCHAR(MAX),
@DynamicColumns NVARCHAR(MAX),
--@ParmDefinition NVARCHAR(MAX),

@ForecastStartDate DATE,
@ForecastEndDate DATE,
@Frequency NVARCHAR(30),
@savedDenomiation nvarchar(50),
@DynamicPeriodColumns NVARCHAR(MAX);
IF OBJECT_ID('tempdb..#Periods') IS NOT NULL
DROP TABLE #Periods
CREATE TABLE #Periods
(
Period NVARCHAR(50)
)

SELECT
@ForecastStartDate = StartYear,
@ForecastEndDate = EndYear,
@Frequency = DataFrequency
FROM dbo.tblForecastSettings WHERE ForecastID = @ForecastID;

select top 1 @savedDenomiation = ISNULL(Denomination, 'A') from tblforecastinput where forecastid = @ForecastID AND scenarioid = @ScenarioID and Section='EPIParameters'

--Generating forecast periods as per the settings
BEGIN

INSERT INTO #Periods
SELECT
CASE
WHEN @Frequency = 'Year' THEN DATENAME(YEAR, DATEADD(YEAR, N - 1 , @ForecastStartDate))
WHEN @Frequency = 'Quarter' THEN 'Q'+DATENAME(QUARTER, DATEADD(QUARTER, N - 1 , @ForecastStartDate)) + '-' + DATENAME(YEAR, DATEADD(QUARTER, N - 1 , @ForecastStartDate))
ELSE LEFT(DATENAME(MONTH, DATEADD(MONTH, N - 1 , @ForecastStartDate)), 3) + '-' + DATENAME(YEAR, DATEADD(MONTH, N - 1 , @ForecastStartDate))
END AS Period
FROM dbo.Tally
WHERE N <= (CASE
WHEN @Frequency = 'Year' THEN (DATEDIFF(YEAR, @ForecastStartDate, @ForecastEndDate) + 1)
WHEN @Frequency = 'Quarter' THEN (DATEDIFF(QUARTER, @ForecastStartDate, @ForecastEndDate) + 1)
ELSE (DATEDIFF(MONTH, @ForecastStartDate, @ForecastEndDate) + 1)
END);

END;

WITH template AS
(
SELECT
'' [Events],
temp.InputParameterName as [Parameters],
temp.FCPSTempID AS ParameterId,
0 as EventId,
temp.LevelID,
temp.ParamInputType as ParamInputType,
temp.FormulaSeqID,
RTRIM(temp.Operator) AS Operator,
(CASE WHEN ParentItemType ='Line' THEN 0 ELSE temp.ParentParameterID END) as SeqID,
(dbo.udf_GetIsOperatorChanged(@ForecastID,@ScenarioID,temp.FCPSTempID)) as IsOperatorChanged,
temp.DisplayOrder,
temp.ItemType as ItemType,
(CASE WHEN temp.ItemType = 'Line' THEN temp.FCPSTempID ELSE temp.LineID END) AS LineID,
temp.IsCombined,
'' as ShareTrendMethod
FROM tblForecastTemplate temp
WHERE
ForecastID=@ForecastID
AND (@LineID IS NULL OR temp.LineID = @LineID)
AND temp.ItemType in('Line', 'EPIParameters','Segment')

UNION

SELECT
'' [Events],
e.EventName as [Parameters],
e.FCPSTempID AS ParameterId,
e.EventId,
e.LevelID as LevelID,
'E' as ParamInputType,
null as FormulaSeqID,
'' as Operator,
0 as SeqID,
CONVERT(bit,0) as IsOperatorChanged,
temp.DisplayOrder,
'Event' as ItemType ,
temp.LineID,
CAST(0 AS BIT) AS IsCombined,
e.ShareTrendMethod
FROM tblEvents e
JOIN tblForecastTemplate temp on e.FCPSTempID = temp.FCPSTempID
WHERE
e.ForecastID=@ForecastID
AND e.ScenarioID=@ScenarioID
AND (@LineID IS NULL OR temp.LineID = @LineID)
AND temp.ItemType in ('Segment','EPIParameters')
)

SELECT * INTO #FinalData
FROM (
SELECT
isnull(inp.Comment,'') as Comment,
tree.*,
isnull(@savedDenomiation,'') as NumericFormat,
(CASE WHEN tree.ParamInputType != 'I' THEN '' WHEN inp.Interpolate IS NULL THEN 'No' WHEN inp.Interpolate = 0 THEN 'No' ELSE 'Yes' END) AS Interpolate,
isnull(inp.StartPeriod,'') as StartPeriod ,
isnull(inp.EndPeriod,'') as EndPeriod ,
isnull(inp.Method,'')as Method ,
isnull(inp.StartValue,'') as StartValue,
isnull(inp.EndValue,'') as EndValue ,
isnull(inp.GrowthRate,'') as GrowthRate ,
isnull(inp.Slope,'') as Slope,
isnull(inp.Constant,'') as Constant,

pr.Period,
(CASE WHEN tree.ItemType = 'Line' THEN '' ELSE ISNULL(REPLACE(REPLACE(REPLACE(inp.Periodinput,'#DIV/0!','0'),'#VALUE!','0'),'%',''), 0) END) AS PeriodInput
FROM template tree
JOIN #Periods pr ON 1 = 1
LEFT JOIN dbo.tblForecastInput inp
ON
inp.ForecastID = @ForecastID AND
inp.ScenarioID = @ScenarioID AND
inp.FCPSTempID = tree.ParameterId AND
inp.EventID = tree.EventID AND
inp.ParamInputType = tree.ParamInputType AND
inp.Section = 'EpiParameters' AND
inp.Period = pr.Period
) AS Data;

SELECT
@DynamicColumns = COALESCE(+@DynamicColumns +',', '') + 'MAX('+QUOTENAME(Period)+') as '+ '['+Period+']'
FROM #Periods;

SELECT
@DynamicPeriodColumns = COALESCE(+@DynamicPeriodColumns +',', '') + QUOTENAME(Period)
FROM #Periods;

SELECT * FROM #Periods

SELECT ParameterId, DisplayOrder FROM #FinalData WHERE ItemType = 'Line' GROUP BY ParameterId, DisplayOrder ORDER BY DisplayOrder

SET @SQL = 'SELECT
MAX(Comment) as Comment,
MAX(Events) as Events,
Parameters as Parameters,
ParameterId as ParameterId,
EventID as EventId,
LevelId as LevelID,
ParamInputType as ParamInputType,
FormulaSeqID as FormulaSeqID,
Operator as Operator,
SeqID as SeqID,
IsOperatorChanged,
DisplayOrder as DisplayOrder,
ItemType as ItemType,
LineID as LineID,
IsCombined,
MAX(NumericFormat) as NumericFormat,
MAX(Interpolate) as Interpolate,
MAX(StartPeriod) as StartPeriod,
MAX(EndPeriod) as EndPeriod,
MAX(Method) as Method,
MAX(StartValue) as StartValue,
MAX(EndValue) as EndValue,
MAX(GrowthRate) as GrowthRate,
MAX(Slope) as Slope,
MAX(Constant) as Constant,
MAX(ShareTrendMethod) as ShareTrendMethod,
'+@DynamicColumns+'
FROM (
SELECT
*
FROM #FinalData
) sl
PIVOT(
MAX(PeriodInput)
FOR Period IN ( '+ @DynamicPeriodColumns +' )
) AS Pvt
GROUP BY ParameterId, EventID, Parameters, LevelId, ParamInputType, FormulaSeqID, Operator, SeqID, IsOperatorChanged, DisplayOrder, ItemType, LineID, IsCombined
ORDER BY LineID, DisplayOrder, ParameterID, EventId';

EXECUTE sp_executesql @SQL;

--absolute operator node, extra operation with parent node
SELECT FCPSTempID,TreeID+1 as ParentOutputID,OperationWithParent FROM tblForecastTemplate WHERE ForecastID=@ForecastID AND OperationWithParent IS NOT NULL AND ParamInputType='O'

--Droping all temp tables
BEGIN

IF OBJECT_ID('tempdb..#Periods') IS NOT NULL
DROP TABLE #Periods;

IF OBJECT_ID('tempdb..#FinalData') IS NOT NULL
DROP TABLE #FinalData;
END
END
Rajanikant Hawaldar6y ago
Are tried using IdataReader instead of adapter?, is your query or procedure optimized? Can you post your procedure here
Aurovinda Shyamal6y ago
Sorry, I couldn't find any proper solutions from those links.
Rajanikant Hawaldar6y ago
This might help you just follow one by one link https://stackoverflow.com/questions/25186380/adapter-fill-takes-long https://stackoverflow.com/questions/250713/sqldataadapter-fill-method-slow https://forums.asp.net/t/1731063.aspx?SqlDataAdapter+fill+method+takes+too+long+time+for+the+first+run https://www.codeproject.com/Questions/1108950/Fill-method-of-data-adapter-operating-slow https://social.msdn.microsoft.com/Forums/en-US/ef77ae63-082b-4844-b8b0-a182f5f3c5b8/data-adapter-fill-method-is-really-really-slow?forum=Vsexpressvb