multiple calls to SP

Hi group,

I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))

All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.

The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.

When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.

Can someone give me some pointers on where to look for performance wins?

If you like I can add the SP's

Regards,

Sander

Call it one time instead of 180 times =;o)

But seriously, it does indeed look like a 'looping' symptom.
Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right.

Have you considered to - if possible - do fewer calls? Ideal would probably be just one instead of 180. It's a bit hard to come up with something tangible without knowing more. Why is it 180 calls? Are they all parts of something that is complete once 180 is done?

/Kenneth

|||"Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right."

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

|||If you could provide some details about what exactly is your SP doing, what are you calculating in general, and maybe the SP code and the caller code too, that would be nice-we could be more specific.|||Here is the source code, btw: SP_A and SP_B cannot be combined (technicly they can of course....)

This is SP_A (uspRetrieveHourlyFactor)
ALTER PROCEDURE uspRetrieveHourlyFactor
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.Normalise bit
AS
SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.RC as numeric(25,20)
DECLARE @.CurrDate as datetime
DECLARE @.WeightedAverage AS numeric(25,20)

SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType


IF @.Normalise = 0
BEGIN
--No normalisation required!
SET @.WeightedAverage = 1
END
ELSE
BEGIN
EXEC @.RC = uspCalcWeightedAverage @.StartDate2, @.EndDate2, @.InMarket, @.InProductType, 1, @.WeightedAverage OUTPUT
END

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1

CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)

SET @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END
SELECT
DBI.DATE [DATE],
[PDF].[HOUR] [HOUR],
FLAG [FLAG],
ISNULL((HHF.Factor * flag) / @.WeightedAverage,0.0) [FACTOR]
FROM ##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] - 1
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(dw, DATEPART(D,[DBI].[DATE])) = [HHF].[DayID]
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,[DBI].[DATE]) = [HHF].[Month]
WHERE PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND
(([PDF].[WD-WE] = 1 AND DATEPART(dw, [DBI].[DATE] ) <= 5) OR
([PDF].[WD-WE] = 0 AND DATEPART(dw, [DBI].[DATE] ) > 5)
)
AND HHF.MARKETID = @.InMarketID
ORDER BY DBI.DATE
DROP TABLE #DatesBetweenInterval


This is SP_B (uspCalcWeightedAverage)
ALTER PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage numeric(25,20) OUTPUT
AS

SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.CurrDate as datetime
DECLARE @.helpfloat as numeric(25,20)

--Get ID's for selected parameters
SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1
--Create temp table
CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)
Set @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END

SELECT @.WeightedAverage = (SUM(HHF.FACTOR) / COUNT(PDF.FLAG))
FROM
##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND --[PDF].[WD-WE] = @.InWeekDay
(([PDF].[WD-WE] = 1 AND DATEPART(dw, DBI.DATE ) <= 5) or
([PDF].[WD-WE] = 0 AND DATEPART(dw, DBI.DATE ) > 5)
)
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG
DROP TABLE #DatesBetweenInterval

|||

SDerix wrote:

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

Hmmmm... I'm still not convinced that you have to do 180 calls, even though I don't doubt your word on it =;o)

On the other hand, it looks more or less like the overall is grouped by year and month, so it may be doable all at once anyway.. At least in theory. Depending on the datavolume, hardware may restrain the performance if resources aren't available for the 'full' set.

It seems like the proc itself isn't really a problem, since 0.14 sec exec time seems quite acceptable? Though, 180 * 0.14 = 25.2 seconds... And that's the problem.

Would it be possible to rethink the current 'single-month-at-a-time' strategy into something that involves the entire range all at once?

Perhaps you could consider replacing the temporary date-hour table that gets created and thrown away 360 times each run, for a permanent table to join against instead?

spA ends with an order by - is that necessary?
(it would only serve it's ordering purpose if the result is sent to the client, or inserted into a table with some other ordering attribute)

In any case, I believe that the best tuning would be to lower the number of calls from 180 to some lower number, but that would probably involve some rethinking/redesigning of what these procs does....

So... why just a single month each call for a 15 year period? Would it be possible to produce the same result for all 12 months within a year? Or for all months and years in just a single call?

/Kenneth

|||

I think you can do without this temp table #DatesBetweenInterval and use a between clause for the input start and end date.

Did you try creating indexes on the global temp tables?

Multiple Calls to ServerReport.SetParameters() With Varying Numbers Of Parameters

I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.

Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);

this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;

}

}

protected void btnViewRpt_Click(object sender, EventArgs e)

{

ReportParameter[] rptParams = GetReportParameters();

this.rptViewer.ServerReport.SetParameters(rptParams);

this.rptViewer.ServerReport.Refresh();

}

private ReportParameter[] GetReportParameters()

{

int paramCount = 0;

ReportParameter[] retVal;

string emptyVal = null;

if (txtName.Text != "") paramCount++;

if (txtAddress.Text != "") paramCount++;

if (txtZip.Text != "") paramCount++;

retVal = new ReportParameter[paramCount];

paramCount = 0;

if (txtName.Text != "")

retVal[paramCount++] = new ReportParameter("Name", txtName.Text);

if (txtAddress.Text != "")

retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);

if (txtZip.Text != "")

retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);

return retVal;

}

The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.

The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.

So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.

Any suggestions?

>>

I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.

<<

Not meaning to be argumentative... just trying to figure out your situation...

What is the situation in which sending an explicit null "can cause errors"?

IAC, if you want to "render as though for the first time" (wasn't this a Madonna song <g>?) , you should probably use ReportViewer.Reset. You may need to re-specify the URL and path *after* that, though.

>L<

|||

Thanks so much for your reply! That's exactly what I was looking for. After applying ReportViewer.Reset() in our code, I got the behavior I was looking for.

Now, to address your other question (and to perhaps move away from "just get it working" toward "do it the right way"): I have a report that accepts a DateTime parameter (called ReportDate, surprisingly enough):

The parameter allows NULL values. But it also gets its set of available values from a query written for that purpose. It's also set to get the default value from the same query that provides the available values.|||

Well, that's just cr*ppy, if true. Can you share exactly the line you are using to pass the null, just in case this is supposed to work but you need to use a slightly different syntax?

>L<

|||

The code is a bit involved because we're scraping parameter values from screen controls that are automatically generated. However, I think you're on to something. I was just looking at how our parameters collection is constructed:

Code Snippet

for(int rowCount = 1; rowCount < parameterTable.Rows.Count; rowCount++)

{

TableRow tr = parameterTable.Rows[rowCount];

Control parameter = tr.Cells[1].Controls[0];

string parameterName = ((WebControl)parameter).Attributes["ParameterName"];

string parameterValue = "";

if (parameter isParameterDatePicker)

parameterValue = ((ParameterDatePicker)parameter).Text == string.Empty ? null : ((ParameterDatePicker)parameter).Text;

elseif (parameter isParameterDropDownList)

parameterValue = ((ParameterDropDownList)parameter).Value == string.Empty ? null : ((ParameterDropDownList)parameter).Value;

elseif (parameter isParameterTextBox)

parameterValue = ((ParameterTextBox)parameter).Text == string.Empty ? null : ((ParameterTextBox)parameter).Text;

else

thrownewException("Unable to determine parameter type.");

// Create the report parameter

ReportParameter reportParameter = newReportParameter();

reportParameter.Name = parameterName;

reportParameter.Values.Add(parameterValue);

reportParameters.Add(reportParameter);

}

Notice how every parameter is created with a string-type value, regardless of which type of value it represents. I'm wondering if, when actual values are specified, the code under the covers is able to successfully convert the strings into the DateTime type the report is expecting, but in the case of NULLs, the conversion/casting fails? I wonder if casting the nulls as specific data types would work?

greg

|||

Scratch that previous thought about data types. I realize now that the value(s) assigned to a ReportParameter are in fact arrays of strings. I discovered in a separate posting by Lisa that the way to create a ReportParameter with a NULL value is to simply create the parameter with the name only and not assign a value at all, like so:

Code Snippet

ReportParameter rptParam = new ReportParameter("ParamName");

or

Code Snippet

ReportParameter rptParam = new ReportParameter();

rptParam.Name = "ParamName";

But after some testing, it appears to me now that there's no difference between creating a ReportParameter object with no value assigned and simply not creating the ReportParameter object at all when you're putting together the collection you'll pass to the SetParameters() method.

So what I've determined works best here when viewing a report multiple times with optional parameters that may or may not be specified from one viewing to the next, is to simply not create any ReportParameters that don't have values specified and to use the ReportViewer.Reset() method between report renderings, as Lisa suggested, and I get the result I want. Optional parameters assume their default values as assigned in the report, and the ReportViewer object is induced to "forget" any previously-specified ReportParameter values in the current rendering of the report. If there's any downside to that I haven't discovered it yet. :-)

|||

>>

But after some testing, it appears to me now that there's no difference between creating a ReportParameter object with no value assigned and simply not creating the ReportParameter object at all when you're putting together the collection you'll pass to the SetParameters() method.

<<

Thank you for taking the time to check this out. I really thought this would be a better thing to do than Reset, if it worked. Why?

Simply because it goes against the inner grain, in terms of efficiency, to totally re-initialize if you don't have to. There are degrees of initialization in most processes that can be re-run, and one would like to use the right one <sigh>. Of course it has to *work* to be the right one. <double sigh>. There may be no downside, as you suggest, to doing extra initialization, but in some cases there is and you don't discover it until much, much later... Oh well...

Thanks again for checking this out,

>L<

Multiple BinaryWrite() calls

I have two byte[] arrays, objResult1 and objResult2, which contain data from
two separate calls to Render().
I would like to somehow write the results for both reports to the Reponse
object.
This is the code I have:
HttpResponse objResponse = System.Web.HttpContext.Current.Response;
objResponse.ClearContent();
objResponse.ClearHeaders();
string fileName = Path.GetFileName(this.ReportPath) +
".pdf";
objResponse.ContentType = this.strMimeType;
objResponse.AddHeader ("content-disposition",
"attachment; filename=\"" + fileName + "\"");
objResponse.BinaryWrite(objResult1);
objResponse.BinaryWrite(objResult2);
objResponse.Flush();
objResponse.Close();
Unfortunately when I do this, the contents of objResult1 are not visible
i.e. the 2nd call to BinaryWrite overwrites the first one.
How can i simply append the second byte array?
Any help will be much appreciated...
Thanks.You can combine two arrays by using static Array.Copy call. Just create a
new array sized as a sum of two and copy your array one by
one to the new one. Be aware, if the mime types of the webresponses are
different you'll get a garbled output.
Also, BinaryWrite should not override the previous write.
The reason of missing the first write is writing file header information
twice, thus only one will be visible, if you lucky enough, otherwise you can
get some garbage
I'd use frames or iframes, if needed to display output from two different
pages on a single one.
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:53A315A1-FFD7-48BF-99D0-84D63BD062C3@.microsoft.com...
>I have two byte[] arrays, objResult1 and objResult2, which contain data
>from
> two separate calls to Render().
> I would like to somehow write the results for both reports to the Reponse
> object.
> This is the code I have:
> HttpResponse objResponse => System.Web.HttpContext.Current.Response;
> objResponse.ClearContent();
> objResponse.ClearHeaders();
> string fileName = Path.GetFileName(this.ReportPath) +
> ".pdf";
> objResponse.ContentType = this.strMimeType;
> objResponse.AddHeader ("content-disposition",
> "attachment; filename=\"" + fileName + "\"");
> objResponse.BinaryWrite(objResult1);
> objResponse.BinaryWrite(objResult2);
> objResponse.Flush();
> objResponse.Close();
> Unfortunately when I do this, the contents of objResult1 are not visible
> i.e. the 2nd call to BinaryWrite overwrites the first one.
> How can i simply append the second byte array?
> Any help will be much appreciated...
> Thanks.