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

Image issues when Exporting to PDF

I'm using vb6 and Crystal 8.5.

I have a report that loads images from a database into OLE objects on the report ( 2 images, 2 different sections). In the format section of each section, I use the following routine to get/show the image.

Get an ID from a txt field in the header.
Feed the ID to a DLL that gets the Pic and returns it
Set oleObj.formattedpicture = pic

Works great in the report...But when I try to export to PDF. The resulting pdf will have the SAME PICTURE in every page of the report.

Help!

Thanks,Hi,
M not sure u got the answer for this question. Anyways, u need to install Crystal Reports SP3 to resolve this issue.

IIS SQL Server connection error (urgent)

I keep getting this error when I try to connect to my SQL Server DB

Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'Administrator'. Reason: Not associated with a trusted SQL Server connection.

when I installed SQL Server I set username and password to windows default which is Administrator and no password

the asp files are on IIS (localhost)

connstr = "Provider=SQLOLEDB;" &_
"Data Source=(local);" &_
"Initial Catalog=ShotWatcher;"&_
"User ID=Administrator;"&_
"Password="

shouldn't this work?

Please Help

JustinDo you have a SQL Server Standard account called Administrator? Or you're trying to use Windows account with the same name? If it's the latter, - remove User ID and Password attributes and replace them with Integrated Security=SSPI.|||And in connection string you should specify TRUSTED_CONNECTION=TRUE and try using servername instead of specifying local.

KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;306586) to more about it.|||i'll try that, thx

IIS & Sql Server authentication

When would you use scenerio one vs. scenerio two?
Scenerio one - IIS (anonymous, or anonymous and
integrated)--OLE DB Provider for SQL (integrated
security)-->SQL (mixed mode or windows
authentication)
Scenerion two - IIS (basic or integrated)--OLE DB
Provider for SQL (integrated security)--> SQL (mixed
mode or windows authentication)
Our environment has IIS and SQL Server on the same machine.Hi Michelle,
Merry Christmas and thank you for using MSDN Newsgroup! It's my pleasure to
assist you with your issue.
You are choosing the authentication way for you application run on IIS and
SQL Server with OLE DB Provider, right? From my experience, to design a
system with high efficency and security will take many aspects for
consideration, including the application environment, security, connection,
performance requirment, etc. It is hard to say that one way is better than
another. It depends on specifics. So you can refer to the following
articles from Microsoft :
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/SecNetch05.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/secnetlpMSDN.asp
Implementing a Secure Site with ASP
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/ht
ml/msdn_implement.asp
Designing Efficient Applications for Microsoft SQL Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlsg/htm
l/msdn_designeff.asp
Accessing SQL Server from a Web Application
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbconaccessingsqlserverfromwebapplication.asp
Configuring Security for Internet Information Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsentpro/ht
ml/veconConfiguringSecurityForInternetInformationServer.asp
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:
80/support/kb/articles/Q247/9/31.ASP&NoWebContent=1
Microsoft Internet Information Server Security Overview
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dniis/html/
iissecure.asp
Optimizing SQL Server and IIS Security and Connectivity
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9150
The following articles are from some other website. Microsoft does not
guarentee the correctness of it. You can still take them for reference:
Using Windows Security with IIS and SQL Server 2000
http://www.winnetmag.com/Articles/ArticleID/23035/pg/2/2.html
IIS User Authentication
http://www.adiscon.com/IIS/gen001.htm
Setting SQL Server 7.0 and IIS Security
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9002
Hope this would be helpful in solving your problem. If you still have
questions, please feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.