Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
>|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done bu
t
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
>

Batch Updates

Can somebody from Microsoft suggest a way to do batch updates from a stored
procedure ? I was considering OPENXML but it has a lot of memory related
problems so i need something where i can update/insert multiple rows without
making round trips to the server. Thanks!
Michael,
I already have the stored procedures to update the tables. Is there anyway i
can map the xml directly to stored proc parameters using transformation ?
Thanks!
"Michael Rys [MSFT]" wrote:

> If the XML is not very big the memory issues of OpenXML should not be
> playing a big role.
> You can however do one of the following for better perf:
> 1. Use the SQLXML Bulkload object.
> 2. Write some midtier code that decomposes your XML into either one of:
> a Bulkload input file
> b. A batch of T-SQL insert and update statements.
> HTH
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...
>
>
|||You will have to write mid-tier code to do so, I am afraid...
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...[vbcol=seagreen]
> Michael,
> I already have the stored procedures to update the tables. Is there anyway
> i
> can map the xml directly to stored proc parameters using transformation ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Michael,
I know thats the easy way out but is there any other approach for sending
batch data to a stored procedure ? Looping in the application can be done but
if i need to make it in a transaction is it a better idea to have the
transaction in the sproc or the outside in the code itself ?
Thanks!
"Michael Rys [MSFT]" wrote:

> You will have to write mid-tier code to do so, I am afraid...
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:39F52718-9899-4F67-BC91-4D6CF00415E4@.microsoft.com...
>
>
|||I don't think there is much of a difference whether you send multiple
invocations of the same sproc with different parameter values in a single
batch that is sent as a transaction or a stored proc that does it inside.
Again, there may be a benefit of using OpenXML inside a stored proc to do
your update expressions set-oriented under certain conditions instead of
generating your batch on the midtier and send a large batch to the server.
The easiest is to write a sample app to test the approaches with some of the
data to be used and using the same setup (to get the client-server comm cost
right).
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:9F88DE36-35AA-4E6B-9DC5-B2EBD82CACF6@.microsoft.com...[vbcol=seagreen]
> Michael,
> I know thats the easy way out but is there any other approach for sending
> batch data to a stored procedure ? Looping in the application can be done
> but
> if i need to make it in a transaction is it a better idea to have the
> transaction in the sproc or the outside in the code itself ?
> Thanks!
> "Michael Rys [MSFT]" wrote:
|||Did you look into the general Bulkload and BCP functionalities?
Is the data originally in XML or do you just consider XML as a potential
approach?
Best regards
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
> Can somebody from Microsoft suggest a way to do batch updates from a
> stored
> procedure ? I was considering OPENXML but it has a lot of memory related
> problems so i need something where i can update/insert multiple rows
> without
> making round trips to the server. Thanks!
|||Michael,
The data is originally in xml. I am doing realtime updates to the tables and
the xml is not very big. Moreover i have to span the updates in a
transaction. If there is a different approach than xml i can transform it to
a different format.
Thanks!
"Michael Rys [MSFT]" wrote:

> Did you look into the general Bulkload and BCP functionalities?
> Is the data originally in XML or do you just consider XML as a potential
> approach?
> Best regards
> Michael
> "Vish" <Vish@.discussions.microsoft.com> wrote in message
> news:CEF7CBCC-3AEE-4062-B491-560CF56729DC@.microsoft.com...
>
>
|||If the XML is not very big the memory issues of OpenXML should not be
playing a big role.
You can however do one of the following for better perf:
1. Use the SQLXML Bulkload object.
2. Write some midtier code that decomposes your XML into either one of:
a Bulkload input file
b. A batch of T-SQL insert and update statements.
HTH
Michael
"Vish" <Vish@.discussions.microsoft.com> wrote in message
news:5818E367-1097-497F-9BD2-BACDE859450E@.microsoft.com...[vbcol=seagreen]
> Michael,
> The data is originally in xml. I am doing realtime updates to the tables
> and
> the xml is not very big. Moreover i have to span the updates in a
> transaction. If there is a different approach than xml i can transform it
> to
> a different format.
> Thanks!
> "Michael Rys [MSFT]" wrote:

Basic questions ...

We are considering using replication for some of our needs and I need a few
basic questions answered so I can let my boss know what some of the gotcha's
are.
Using transactional replication, distributor and publisher on the same
serveer, read only subscribers:
1) What happens to the subscriber when you restore a database and/or
transaction logs on a publisher?
2) Can synchronization be forced outside of the normal scheduling? I.E. if
we sync nightly but the VP of marketing gets a bright idea and says I need
the lasted data, can we force synchronization ad hoc?
3) What happens with DDL on published objects?
4) What is the meaning of life?
5) Are there any rules of thumb about additonal load on the publisher's
database server? There is obviously some overhead for running the agents and
distibution database, but if you are replicating say 10% of the
transactions, is this analogous to adding yet an additonal 10% load on the
publisher's server?
6) We will be doing this over a VPN between separate domains. We are
expecting nightly synchronization to be required, but I was wondering if
more regular syncs would be more reliable over less than perfect internet
connections (DSL and cable are likely. Dial-up in worse cse scenarios). Any
thoughts?
Thanks,
Bob Castleman
DBA Poseur
Bob,
I will leave questions 1, 2, 3, 5 & 6 to the pros (HC & PI), but as far
as #4, I received an email a while back that said...
What is the meaning of life?
The meaning of life is to give and receive love and work at something
you feel passionate about. Or not.
I hope this helps.
Larry....
|||1) when you restore a publication db you basically break replication on the
publisher. Nothing happens to the subscriber
2) Sure. You can synchronize at any time or any time interval.
3) Making DDL changes on SQL 2000 and SQL 7 server is not allowed. You can
make some schema changes (DDL) using sp_repladdcolumn and sp_repldropcolumn.
In SQL 2005 most schema changes will be replicated.
4) life is nasty, solitary, brutish and short, however it is a hauntingly
beautiful universe. Time is all we really own. Value it carefully. Spend it
with someone special. Replicate often
5) Its typically between 5 and 10%, but it can vary wildly depending on what
you are replicating, hardware, replication type, throughput etc.
6) By replicating as frequently as possible you will lessen the load on your
publisher/distributor. The longer between sync's the greater the performance
degradation.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bob Castleman" <nomail@.here> wrote in message
news:%23oB45H3aFHA.1600@.tk2msftngp13.phx.gbl...
> We are considering using replication for some of our needs and I need a
few
> basic questions answered so I can let my boss know what some of the
gotcha's
> are.
> Using transactional replication, distributor and publisher on the same
> serveer, read only subscribers:
> 1) What happens to the subscriber when you restore a database and/or
> transaction logs on a publisher?
> 2) Can synchronization be forced outside of the normal scheduling? I.E. if
> we sync nightly but the VP of marketing gets a bright idea and says I need
> the lasted data, can we force synchronization ad hoc?
> 3) What happens with DDL on published objects?
> 4) What is the meaning of life?
> 5) Are there any rules of thumb about additonal load on the publisher's
> database server? There is obviously some overhead for running the agents
and
> distibution database, but if you are replicating say 10% of the
> transactions, is this analogous to adding yet an additonal 10% load on the
> publisher's server?
> 6) We will be doing this over a VPN between separate domains. We are
> expecting nightly synchronization to be required, but I was wondering if
> more regular syncs would be more reliable over less than perfect internet
> connections (DSL and cable are likely. Dial-up in worse cse scenarios).
Any
> thoughts?
>
> Thanks,
>
> Bob Castleman
> DBA Poseur
>
|||Thanks, guys.
My own answer to #4
The meaning of life is that we indeed are alive.
"Bob Castleman" <nomail@.here> wrote in message
news:%23oB45H3aFHA.1600@.tk2msftngp13.phx.gbl...
> We are considering using replication for some of our needs and I need a
> few basic questions answered so I can let my boss know what some of the
> gotcha's are.
> Using transactional replication, distributor and publisher on the same
> serveer, read only subscribers:
> 1) What happens to the subscriber when you restore a database and/or
> transaction logs on a publisher?
> 2) Can synchronization be forced outside of the normal scheduling? I.E. if
> we sync nightly but the VP of marketing gets a bright idea and says I need
> the lasted data, can we force synchronization ad hoc?
> 3) What happens with DDL on published objects?
> 4) What is the meaning of life?
> 5) Are there any rules of thumb about additonal load on the publisher's
> database server? There is obviously some overhead for running the agents
> and distibution database, but if you are replicating say 10% of the
> transactions, is this analogous to adding yet an additonal 10% load on the
> publisher's server?
> 6) We will be doing this over a VPN between separate domains. We are
> expecting nightly synchronization to be required, but I was wondering if
> more regular syncs would be more reliable over less than perfect internet
> connections (DSL and cable are likely. Dial-up in worse cse scenarios).
> Any thoughts?
>
> Thanks,
>
> Bob Castleman
> DBA Poseur
>