Anyone producing a large number of reports per day?

I'm evaluting Reporting Services for my client. They need to produce 5000+
reports per day, mainly output in PDF format. The PDF's are 6-8 pages long.
Does anyone have any experience of getting this kind of performance out of
SQL-RS?
They have an 8-processor 2.8Ghz server running SQL Server that will be the
Reporting Services server. It has 4gig of memory. Will it be up to the job?
Thanks,
Andy.I am able to render about 70reports (PDF format) in less than 10mins. Each
report has about 200 pages.
"Andy Smith" wrote:
> I'm evaluting Reporting Services for my client. They need to produce 5000+
> reports per day, mainly output in PDF format. The PDF's are 6-8 pages long.
> Does anyone have any experience of getting this kind of performance out of
> SQL-RS?
> They have an 8-processor 2.8Ghz server running SQL Server that will be the
> Reporting Services server. It has 4gig of memory. Will it be up to the job?
> Thanks,
> Andy.|||Don't forget to set boot.ini to use /3GB switch in order to take advantage
of the memory.
To verify RS can handle this load, consider using a test tool like
Application Center Test (ACT), part of Visual Studio, to run a Proof Of
Concept. Below is sample ACT script you might use to run such a test:
' --
CONSTANTS --
const ENABLE_DELAYS = True
const REQUESTBUFFERSIZE = 15000
const REPORTSERVERNAME = "localhost"
' --
' -- Think time variables - examples shows between 2-3 seconds
' --
const MIN_SLEEP_MSEC = 2000
const MAX_SLEEP_MSEC = 3000
' --
' -- Think time logic if desired
' --
Function RandomSleep()
if (NOT ENABLE_DELAYS) then
RandomSleep = 0
return
end if
Dim lMinSleep, lMaxSleep, lSleep
lMaxSleep = MAX_SLEEP_MSEC
lMinSleep = MIN_SLEEP_MSEC
' create a random int within our range
Call Randomize()
lSleep = Int((lMaxSleep - lMinSleep + 1) * Rnd(1) + lMinSleep)
' Test.Trace "Sleeping: " + Cstr(lSleep)
Call Test.Sleep(lSleep)
' return the delay time
RandomSleep = lSleep
End Function
Function SendGetRequest(reportServerUrl)
Dim oConnection, oRequest, oResponse, oHeaders, statusCode
Set oConnection = Test.CreateConnection(REPORTSERVERNAME, 80, false)
If (oConnection is Nothing) Then
Test.Trace "Error: Unable to create connection to server"
Else
' TODO: uncomment this after debugging
' Test.Trace(reportServerUrl)
Set oRequest = Test.CreateRequest
oRequest.ResponseBufferSize = REQUESTBUFFERSIZE
oRequest.Path = reportServerUrl
oRequest.Verb = "GET"
oRequest.HTTPVersion = "HTTP/1.1"
set oHeaders = oRequest.Headers
oHeaders.RemoveAll
oHeaders.Add "Accept", "image/gif, image/x-xbitmap, image/jpeg,
image/pjpeg, application/vnd.ms-excel, application/vnd.ms-powerpoint,
application/msword, */*"
oHeaders.Add "Accept-Language", "en-us"
oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
Windows NT 5.1; .NET CLR 1.0.3512; .NET CLR 1.1.4322)"
oHeaders.Add "Host", "(automatic)"
oHeaders.Add "Cookie", "(automatic)"
Set oResponse = oConnection.Send(oRequest)
If (oResponse is Nothing) Then
Test.Trace "Error: Failed to receive response for URL to " +
reportServerUrl
Else
statusCode = oResponse.ResultCode
'DEBUG
Test.Trace "Received: " + CStr(statusCode) + " for: " + REPORTSERVERNAME
+ reportServerUrl
' delay in case of errors - to avoid the snowball effect
' Test.Trace "Response code of: " + CStr(statusCode) + "
recieved for " + reportServerUrl
if (statusCode = 503) Then
Test.Trace "Server too busy error: " + CStr(statusCode) + "
recieved for " + reportServerUrl + " - Process Put To Sleep"
End If
End If
oConnection.Close
End If
End Function
Sub Main()
Dim Url
'--
'-- URL of report to call for test. This one calls report named "Simple"
located in dir /Benchmark
'-- passing in a parm of &RowNumber '--
Url ="/ReportServer?/Benchmark/Simple&RowNumber=20&rs:Command=Render&rc:Toolbar=false&rs:Format=PDF"
SendGetRequest(Url)
RandomSleep()
End Sub
Main
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Raj Chandra" <RajChandra@.discussions.microsoft.com> wrote in message
news:C23DE604-F4E0-4F3E-BD7B-1C4516C1987C@.microsoft.com...
>I am able to render about 70reports (PDF format) in less than 10mins. Each
> report has about 200 pages.
> "Andy Smith" wrote:
>> I'm evaluting Reporting Services for my client. They need to produce
>> 5000+
>> reports per day, mainly output in PDF format. The PDF's are 6-8 pages
>> long.
>> Does anyone have any experience of getting this kind of performance out
>> of
>> SQL-RS?
>> They have an 8-processor 2.8Ghz server running SQL Server that will be
>> the
>> Reporting Services server. It has 4gig of memory. Will it be up to the
>> job?
>> Thanks,
>> Andy.sql

anyone have a snazzy way to count substrings?

Hi All,

I have a varchar that contains a comma-delimited list of integers, such as "12,34,56,78,123,1,123455".

I need a way to count the number of numbers in the string (or, perhaps, better stated as "I need a count of substrings") ;)

I'm thinking there must be a number of ways to calculate the number of numbers in my list of numbers, but I can only seem to come up with looping through the string/varchar and counting the number of commas, and then adding one to that final count.

Anybody know of a "cooler" way to do this? Everything I can think of involves stepping through a character at a time...

any (printable) thoughts?
THanks!I think it was Brett who came up with this method:

select (len(YourString) - len(replace(YourString, ',', ''))) + 1|||Outstanding!!! I thought about using "replace" but didn't come up with near as nice an idea - I guess that's why you guys get paid the big bucks! :D

I know you were just free-handin' it, but the actual syntax is thus:
select (len(@.YourString) - len(replace(@.YourString,',', '')) + 1)

Exactly what I was looking for though...THANKS!!!

You guys never let me down...someday I hope to bring a little something to the party besides questions! :D|||I think it was Brett who came up with this method:

select (len(YourString) - len(replace(YourString, ',', ''))) + 1

Thanks...not hardly...I think it was Nigel who showed me...

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578

Although if you think about it's perfectly logical...

Once I saw that one, everytime I see a "complicated" problem, I step back and look for an easy answer...

Doesn't always work for me...

The theta join stuff still gives me pause...|||The theta join stuff still gives me pause...
piece o' cake

it's just a cross join with a loose condition

in fact, an inner join is just a cross join with a more restrictive condition

here's a perfect example of a theta join --

http://www.dbforums.com/showthread.php?p=3671683#post3671683|||The only way it works if you give something back. I use to be that way. Only asking question and no even trying to help someone else out there. Just remember that. Even if you take one question a week. I try to do at least one a day during the week.|||Gotcha GarryDawkins,

I am a member in quite a few forums related to the restoration of classic cars ;) so I know the importance of contributing. Otherwise, if everyone asks the questions without answering any, ummm...well...the forums would be quite a bit more boring *LOL*

I've got about 15 years of SQL experience, though mostly on Tandem Nonstop and Oracle, but have just recently started out with SQL Server, so am sure I can contribute once I've gotten past the initial curve.

Meanwhile, I'll ask questions and appreciate the knowledge and willingness to help of everyone else! and the help IS appreciated!

In fact, I did run across (on another forum, I think) a GREAT example of the reverse of this thread, which is how to build a comma-delimited string for use in, for example, a "dynamic" IN... clause...(the lead-in and example are modified to my application) - I posted this in an internal forum at my company).

I needed a way to do both, and found a way to do it easily with a function I didn't even know existed before yesterday! *L*:

Another, more efficient alternative is to use the COALESCE function,
which is much more efficient than the use of the cursor option, and also
shortens the cursor-supporting code block to a single select statement:

DECLARE @.PortfolioList varchar(100)

SELECT @.PortfolioList = COALESCE(@.PortfolioList + ',', '') +
CAST(PortfolioID AS varchar(5))
FROM Portfolio
ORDER BY PortfolioID

SELECT @.PortfolioList as CSVList

which results in the following output:
CSV_List
----------------------
11,67,90,100,105,110,115,120,125,130,135,140,145,1 50,155

(1 row(s) affected)

The COALESCE function performs the magic here. When @.PortfolioList is NULL
(the first row processed), it returns an empty string. On subsequent rows, it
concatenates the @.PortfolioList value with a comma and the current
PortfolioID value.|||Damn...that just looks sooooooooooo familiar...

Using COALESCE to Build Comma-Delimited String (http://www.sqlteam.com/item.asp?ItemID=2368)|||In fact, I did run across (on another forum, I think) a GREAT example of the reverse of this thread,

and quite interestingly so, eh? ;) In case anyone misinterpreted my comment and is thinking about wacking me with a plagarism stick, I also reworded my "example is mine" to read, correctly, that "the lead-in and example are modified to my application"

I have no interest in taking credit for the creativity of others, though I with great frequency use it with great glee and bastardize it without remorse or hindsight to be of use within my own evil empire. BwahahahahaHAHAHA!|||I have no interest in taking credit for the creativity of others, though I with great frequency use it with great glee and bastardize it without remorse or hindsight to be of use within my own evil empire. BwahahahahaHAHAHA!
good developers steal

great developers steal and pass it on to others

;)|||good developers steel

great developers steel and pass it on to others

;)

Or they can copper, iron, nickel or gold

:D|||Or they can copper, iron, nickel or gold

:D

I once tried to make some little spheres of tin and copper, but it got too hot and mixed together somehow, and all I ended up with was a HUGE mess and a pair of bronze balls. *sigh*

...but I digress...

hey, if you jack your own thread, is it still a thread-jacking? :D|||i've had my balls bronzed, too|||Was that before or after the Leiderhosen picture? It's hard to tell, 'cause the image is so small...|||Was that before or after the Leiderhosen picture? It's hard to tell, 'cause the image is so small...

Ouch, that's gonna leave a scar... :D|||Was that before or after the Leiderhosen picture? It's hard to tell, 'cause the image is so small...

wanna see the big version? http://rudy.ca/quatsch.cfm|||Ah. Definitely after... :o

Any tips for using large parameter lists

Our parts table has 5k records. I want to use part number as a parameter for one of my reports. Is there a way to do this and have the report generate in a reasonable amount of time?

Thanks

Create a new dataset on the Data tab with the query as "SELECT part_number from Parts" and name the dataset as, say DataSet2.

Go to Report (menu) -> Report Parameters

and add a new parameter and give the name, type and prompt.

Then under Available Values, select "From Query" radio button

select DataSet2 under Dataset, part_number under Value field

Shyam

|||Thats the problem.
It takes five minutes for the param list to render.|||

Maybe you can reduce the query time on SQL server by adding index (clustered preferably) to part_number column. This is the only solution to reduce the load time.

Shyam

Any limit on number of characters for FLATFILE connection ?

Any one knows for sure if there is any limit on the number of characters/letters that a FLATFILE connection manager can maximally have?

Is the following name (36 letters) valid ?

Code Snippet

<DTS:Property DTS:Name="ObjectName">Load Ready Output Connection Manager</DTS:Property>

Why do you ask?|||

my observation is that FLATFILE connection manager often failed on those long-name connections. I wonder if this is the reason causing packages run unstably. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860426&SiteID=1

On another observation -- we have some PCs that have only one processor/CPU. SSIS packages run always successfully on those PCs. The unstable issue occur only on dual-processor or 4-processor PCs. I wonder if threading on >1 CPU causing any issue (although not theoretically). This again seems weird enough.

Any limit of the length of the characters in table name ?

Hi,
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too long.
Maximum length is 128.
> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538
> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345

Any help solidifying the following?

I am trying to create a table structure that would allow users to define custom formulas for computing 1 or more values for any given number of variables.

Table Structure

FORMULA
ID | Descr
-----
1 | Rectangle

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
------------
1 | Side A | X |
1 | Size B | Y |
1 | Height | Z |
1 | Area | A | X * Y
1 | Volume | V | A * V

FORMULA_VARIABLE_VALUE
fkID | Variable | Value
-------
1 | X | 10
1 | Y | 10
1 | Z | 2

In the above, notice on FORMULA_VARIABLE the field named expression. If this is NULL, this will be a parameter that must be specified by the user, else, this value from the expression must be evaluated using the variable values.

Notice the Volume expression uses the expression for the variable A. This is where the fun begins...

I have produced the following using the code below. If you could please look at it and let me know if there are any ways to make this more effective, efficient, and stable. That would be greatly appreciated.

The code is a first time run-through!

USE Northwind
GO

CREATE TABLE FORMULA
(
FormulaID int NOT NULL,
Descr char(30)
)
GO

CREATE TABLE FORMULA_VARIABLE
(
fkFormulaID int NOT NULL,
Dimension char(30),
Variable char(10),
Units char(5),
Expression char(255)
)
GO

CREATE TABLE FORMULA_VARIABLE_VALUE
(
fkFormulaID int,
Variable char(10),
Value float
)
GO

INSERT INTO FORMULA (FormulaID, Descr)
SELECT 1, 'Rectangular'
GO

INSERT INTO FORMULA_VARIABLE (fkFormulaID, Dimension, Variable, Units, Expression)
SELECT 1, 'Side 1', 'X', 'IN', NULL UNION ALL
SELECT 1, 'Side 2', 'Y', 'IN', NULL UNION ALL
SELECT 1, 'Height', 'Z', 'IN', NULL UNION ALL
SELECT 1, 'Area', 'A', 'SI', 'X * Y' UNION ALL
SELECT 1, 'Volume', 'V', 'I3', 'A * Z'GO
GO

INSERT INTO FORMULA_VARIABLE_VALUE (fkFormulaID, Variable, Value)
SELECT 1, 'X', 10 UNION ALL
SELECT 1, 'Y', 10 UNION ALL
SELECT 1, 'Z', 2
GO

CREATE PROCEDURE usp_BuildExpressions
@.iFormula int
AS

CREATE TABLE #TempFormulaResults
(
Dimension char(30),
Value float
)

DECLARE @.cDimension char(30), @.cOldExp varchar(255), @.cNewExp varchar(4000), @.cVariable char(5)

DECLARE OldExpCursor CURSOR FOR
SELECT Dimension, Expression FROM FORMULA_VARIABLE WHERE fkFormulaID = 1 AND Expression IS NOT NULL
OPEN OldExpCursor

FETCH NEXT FROM OldExpCursor INTO @.cDimension, @.cOldExp
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Iterate through expresions, build new cursor where dimension is not equal
-- Replace expression variable with expressions
DECLARE NewExpCursor CURSOR FOR
SELECT Variable, Expression FROM FORMULA_VARIABLE
WHERE fkFormulaID = @.iFormula AND Expression IS NOT NULL AND NOT (Dimension = @.cDimension)

OPEN NewExpCursor
FETCH NEXT FROM NewExpCursor INTO @.cVariable, @.cNewExp
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cOldExp = REPLACE(RTRIM(@.cOldExp), RTRIM(@.cVariable), RTRIM(@.cNewExp))
FETCH NEXT FROM NewExpCursor INTO @.cVariable, @.cNewExp
END
CLOSE NewExpCursor
DEALLOCATE NewExpCursor

-- Get the variable values, replace values in expression and calcluate result
DECLARE @.fValue float, @.cVarName char(5)
DECLARE ValueCursor CURSOR FOR
SELECT Variable, Value FROM FORMULA_VARIABLE_VALUE
WHERE fkFormulaID = @.iFormula

OPEN ValueCursor
FETCH NEXT FROM ValueCursor INTO @.cVarName, @.fValue
WHILE(@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.cOldExp = REPLACE(@.cOldExp, RTRIM(@.cVarName), CONVERT(VARCHAR, @.fValue))
FETCH NEXT FROM ValueCursor INTO @.cVarName, @.fValue
END

DECLARE @.cSelect nvarchar(4000), @.param nvarchar(4000), @.Eval float

SET @.cSelect = 'SET @.fResult = ' + @.cOldExp
SET @.Param = '@.fResult float OUTPUT'

EXEC sp_executesql @.cSelect, @.Param, @.Eval OUT
INSERT INTO #TempFormulaResults (Dimension, Value) VALUES (@.cDimension, @.Eval)
FETCH NEXT FROM OldExpCursor INTO @.cDimension, @.cOldExp
CLOSE ValueCursor
DEALLOCATE ValueCursor
END

CLOSE OldExpCursor
DEALLOCATE OldExpCursor

SELECT * FROM #TempFormulaResults
DROP TABLE #TempFormulaResults
GO

EXEC usp_BuildExpressions 1

DROP TABLE FORMULA
GO
DROP TABLE FORMULA_VARIABLE
GO
DROP TABLE FORMULA_VARIABLE_VALUE
GO
DROP PROCEDURE usp_BuildExpressions

Any thoughts?

Mike BOn flaw I see, but I am not sure how to fix is if the "nested" variables are more then 2 deep. What if I used Volume from above in a different formula?

FORMULA_VARIABLE
fkID | Dimension | Variable | Expression
------------
1 | Side A | X |
1 | Size B | Y |
1 | Height | Z |
1 | Area | A | X * Y
1 | Volume | V | A * Z
1 | 1/2 Vol | v | V / 2

Now after executing the stored proc usp_BuildExpressions, the expression for 1/2 Vol would not work and end up looking like
A * 2 / 2.

It will stop short of replacing all the variables with the appropriated nested expressions.

Any ideas?

Mike B