Impact of "Rows per batch" on OLE DB Destination?

OLE DB Destination has a "Rows per batch" parameter.
Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?

I guess the default value for this parameter is zero.
Does that mean, internally it is implemented as:

For each row:

begin transaction
insert row
commit transaction
thanks,
Nitesh

Nitesh Ambastha wrote:

OLE DB Destination has a "Rows per batch" parameter.
Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?

I guess the default value for this parameter is zero.
Does that mean, internally it is implemented as:

For each row:

begin transaction
insert row
commit transaction
thanks,
Nitesh

Hi Nitesh.
Why not do some benchmarking for it? Find out the answers. It'd be really useful info for the community.

-Jamie|||Mmmmmhmmmmm, I'd like to see that! :)|||OK I thought I'd have a go at this. Where is this "rows per batch" parameter/property?

I can't see it. Am I being really dumb?|||

Jamie,

It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom.

Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).

Thanks
Sutha

|||

Sutha Thiru wrote:

Jamie,

It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom.

Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).

Thanks
Sutha

OK, I'm glad I'm not heading for the loony bin. I was looking at an IDW15 instance which doesn't have it. I've checked my IDW16 VM and its there.

I wonder why it (and many other properties) were removed on IWD15?

And here's another question. Why are they (Rows per batch, keep nulls, table lock, check constraints, keep ID, max insert commit size) not available via the preoperties pane?
cheers Sutha

-Jamie|||

Good question. Only reason I could think of is as they are new options, they forgot to add it into properties.

Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?

Thanks
Sutha

|||

Sutha Thiru wrote:

Good question. Only reason I could think of is as they are new options, they forgot to add it into properties.

Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?

Thanks
Sutha

As an aside...I don't think they are new properties. I'm *sure* I remember seeing them way back when. It seems to me like they disappeared for IDW15 and now they've reappeared again. Weird!!

-Jamie|||

I did test way back when on the perf of loading batches per transaction. I generally found that 2000 was a good figure. However I supsect this depends greatly on,
the wdith of the rows being inserted,
the io configuration
memory in the system

Interestingly, why is there no batch rows setting for the SQL destination?

|||I'm guessing cos it uses bulk insert which doesn't allow you to specify batch sizes (does it?)

-Jamie|||

In SQL server 2005, it is our decision to remove properties "RowsPerBatch" and "BatchSize" from SQL server destination adapter.

Reason is SQL server dest adapter is not written to utilize the functionalities the two properties provide - e.g. query plan optimization.

We will consider whether to enable them in the next version.

|||

"Rows per batch" is a Fast Load Option. In the OLE DB Destination, it is only visible (along with some other options like Keep Identity, Keep Nulls etc.) if you choose the Data Access Mode "Table or View - Fast Load". If you choose the data access mode "Table or view", they won't show up. They never disappeared from IDW15, my guess is you are looking at it with the non fast load option, and therefore are not finding it there.

They are available from the properties window, but in a different way. You can specify them in the FastLoadOptions. For example,

FastLoadOptions : TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 1000

sql

Im new to stored Procedures

I create my store proc. I want to give it a parameter AccNmbr and I want it to return to me the total of the columns SubNmbr in total variable; total is gonna be an output. So I do this in my Stored Proc:

Create Procedure dbo.totalSub
@.AccNmbr bigint,
@.total bigint out
AS
Select @.total=sum(SubNmbr) where AccNmbr=@.AccNmbr
return

Go

When I run the proc it tells me that parameter total was not provided. I also tried with return (instead of Return @.total)
it always tells me missing total parameter. But I want total to be my result, not my input. Only AccNmbr is my input.
Thanks a lot for your help.

When you create this Stored Procedure, you have 2 input parameters. Either you can specify one as output, or just have 1 input... Here's a modification that will do what it seems you were doing. You also missed what table you were pulling the results from, that is mentioned by "FROM TABLE".

Here ya go:

Create

Procedure dbo.totalSub
@.AccNmbr int,
AS
Selectsum(SubNmbr)FROMTABLEwhere AccNmbr=@.AccNmbr
Go

|||

You re right, I correct my stuff as shown bellow:

My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: I get the message : An SqlParameter with ParameterName'@.total' is not contained by this SqlParameterCollection.

Here is my stored Proc and my VB program is right below

I- Stored Proc:

CREATE PROCEDURE dbo.totalsub
@.account bigint,
@.total bigint output
AS
select total=sum(SubPhnNmbr) from tblsub whereSubAccNmbr=@.account
return
GO

II- And my pogram in VB is:

Dim totsub As Int64
Dim cm As New SqlCommand
Dim cn As New MyConnection
cn.open
'my connection is defined by me don't worry about it
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "totalsub"
cm.Connection = cn
Dim pm As SqlParameter
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Account", System.Data.SqlDbType.BigInt))
pm.Value = 100000165
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4))
pm.Direction = ParameterDirection.Output
totsub = cm.Parameters("total").Value
cm.ExecuteScalar()
totsub = cm.Parameters("total").Value

I also tried using @.total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output

No Luck, thanks a lot for help

iif/switch statement does not work in input parameter expression

Hi There,

One of the parameters that i need to pass to a stored procedure of my
report, i wish to dynamically calculate from another input default parameter

lets say parameter 'DrillDown' = "ABCDEFG"

paramenter 'MyLevel' is calculated by either of the following a switch or
iif statement:

=switch
(left(Parameters!DrillDown.Value,1)="A",1,
left(Parameters!DrillDown.Value,1)="B",2,
left(Parameters!DrillDown.Value,1)="C",3,
left(Parameters!DrillDown.Value,1)="D",4,
left(Parameters!DrillDown.Value,1)="E",5,
left(Parameters!DrillDown.Value,1)="F",6,
left(Parameters!DrillDown.Value,1)="G",7)
--or--
=iif(left(Parameters!RepLvl.Value,1) = "A",1,
iif(left(Parameters!RepLvl.Value,1) = "B",2,5))

Trouble is none of the 2 above work!

i get the following error: The value expression for the report parameter
‘MyLevel’ contains an error: [BC30201] Expression expected.

Wierd thing is if i just have '=iif(left(Parameters!RepLvl.Value,1) =
"A",1,5)' without an 'else if' it works fine !

Can any one help please!

Cheers

Dave

why not use custom code?

I have found for complicated IIF statements, it's easier and safer to create a custom code function.

|||

I would use a code function but for input parameters on start of a report you cant assign them to a 'code.' parameter. Can you? I get an error when i try this.

I got it to work , by taking out all the spaces ?!?!?

Wierdly it works with the spaces in if i have the expression elsewhere in the report, for intsance in a text box .

Anyhow thanks for the reply

Cheers

Dave

IIF and multi-valued parameters

Hi

i have a report that can show upto 8 charts, dependant on the selection(s) made in a multi-valued parameter.

basically, the report gets loads of data back from DataCube.

if the user has selected (for example) 2 categories in the multi-valued parameter (e.g. "beverages" & "confectionary") , the the first chart will show the results filtered for "beverages" and the second chart will show the results for the "confectionary" category.

but

i want to hide any charts that are not used - i.e. the user only selects 3 categories, i want to show 3 and hide 5 charts and am trying to do this with IIF in the FILTER properties of the chart

so for each chart, i am checking to see if a Category selection has been made for that chart - and if it has, filter the results and display the chart. if no selection has been made, then filter the results for "XXXXX" (no data with this category)

am doing this by trying to get the selected value or "xxxxx" if noting selected

to hide the second chart when only one category has been selected, tried

e.g. for the third chart

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

Parameters!LOOKUPITEMCategoryDescription.Label(2),

"xxxxx")

doesnt work - gives an #Error as a reult

But it works fine when there are > 2 categories selected

also

this works fine when there is 2 or >2 category selected

=iif(

Parameters!LOOKUPITEMCategoryDescription.count>2,

"more than 2",

"not more than 2")

it would appear that IIF is trying to evaluate the TRUE condition(Parameters!HydraCORELOOKUPITEMCategoryDescription.Label(2)) even though the condition is false !!!

help please

thx

IIf evaluates everything in the expression and does not employ shortcutting. This means the expression Parameters!LOOKUPITEMCategoryDescription.Label(2) will cause a problem if there are less than two items selected, because it is evaluated regardless. This means that you will need to change the way you check if a parameter option has been selected, maybe by doing an InStr() on Parameters!LOOKUPITEMCategoryDescription, or by calling a function with the parameter and looping safely through it in the function.

Also, i would think it is better to hide you chart with an expression in the Visibility|Hidden property rather than trying to use the Filters property.

|||

You have to use your report code to achieve this. Have a global variable of type object array in your code and write a function in the code which will receive an object array and store it in the global variable. Then have an invisible textbox at the top of your report and call this function from the textbox expression like Code.YourFunction(Parameters!LOOKUPITEMCategoryDescription.Value).

Then write another function in the code which will receive the index and check if the count of the items in the object array is greater than the received index and return the appropriate item from the object array. Call this function in your Filter expression of the chart.

If you need example about how to do it, please let me know.

Shyam

|||

sluugy & Shyam

thx for this - should have posted an answer as i had already realised about vb evaluating the whole expression in IIF and written the function as described

thx

m

Ignoring time stamp in my date parameter

Hi,

I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @.Startdate is fine because the timestamps is at 12:00:00AM but my @.EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.

If someone can help me out with, I would greatly appreciate it.

Thanks,

Hello,

If I understand correctly, you want to include the date that your user selects in your results. The problem is that when a parameter is used, it assumes midnight, so any values that are on that day but have a time other than midnight will not be included. In order to fix this, just add one day to your EndDate parameter.

In your SQL query, add this:

... where DateField >= @.StartDate and DateField < dateadd(d, 1, @.EndDate)

Hope this helps.

Jarret

|||

You got it! I've been adding one day when I enter the dates when I test the report. I don't know why I did not think of just adding one day in the code. I guess I was thinking too hard trying to completely ignore the timestamp.

Anyways, I think this will work with the users. THey will definitely be happy that they do not have to type in the timestamp when they run their reports.

Thank you so much!!

|||

Glad I could help!

Jarret