DBI insert vs update question

Hello everyone,
I want someone to share his expertise on the following:
Suppose we have a table with some data that periodically must be synced 
with an external source, which provides only partial information (e.g. 
it might provide all the fields for a new record, but might provide 
only a single field difference for an already existing record). This 
obviously will involve a series of UPDATE and INSERT statements, mostly 
in random order. As most RDBMS can hold only one prepared statement at a 
time (including MySQL) I have 2 ways to do this that look somewhat 
efficient:

1. Sweep the pre-existing table into a hash, DELETE all the records from 
it and INSERT the new data, using the hash as a source for missing 
fields.

2. Create 3 connections to the database, have the first one hold a 
SELECT statement that will replace the hash above, have the second 
connection be an INSERT for nonexisting records and the third connection 
be an UPDATE for existing ones

From reading on DBI I understood that moving lots of data with do()
statements is pretty slow (e.g. constantly re-preparing the same 
statement), so I think scenario 2 with a single connection is even 
worse.

The reason I am elaborating on this is that I am afraid the dataset will 
stop fitting in memory at some point of time if I go with scenario 1 
(which is easier and cleaner to implement). Also I have no idea how 
resource intensive scenario 2 would be, although I have proper 
indexing and stuff so the UPDATES should be fast enough...

Thanks

Peter
0
rabbit
7/10/2005 3:09:04 PM
perl.beginners 29382 articles. 4 followers. Follow

6 Replies
296 Views

Similar Articles

[PageSpeed] 31

Can't you use the replace sql query?

Use it like you use "insert". It will insert new rows where there are no
rows, and do an update where there are rows...

Teddy


----- Original Message ----- 
From: "Peter Rabbitson" <rabbit@rabbit.us>
To: <beginners@perl.org>
Sent: Sunday, July 10, 2005 18:09 PM
Subject: DBI insert vs update question


> Hello everyone,
> I want someone to share his expertise on the following:
> Suppose we have a table with some data that periodically must be synced
> with an external source, which provides only partial information (e.g.
> it might provide all the fields for a new record, but might provide
> only a single field difference for an already existing record). This
> obviously will involve a series of UPDATE and INSERT statements, mostly
> in random order. As most RDBMS can hold only one prepared statement at a
> time (including MySQL) I have 2 ways to do this that look somewhat
> efficient:
>
> 1. Sweep the pre-existing table into a hash, DELETE all the records from
> it and INSERT the new data, using the hash as a source for missing
> fields.
>
> 2. Create 3 connections to the database, have the first one hold a
> SELECT statement that will replace the hash above, have the second
> connection be an INSERT for nonexisting records and the third connection
> be an UPDATE for existing ones
>
> >From reading on DBI I understood that moving lots of data with do()
> statements is pretty slow (e.g. constantly re-preparing the same
> statement), so I think scenario 2 with a single connection is even
> worse.
>
> The reason I am elaborating on this is that I am afraid the dataset will
> stop fitting in memory at some point of time if I go with scenario 1
> (which is easier and cleaner to implement). Also I have no idea how
> resource intensive scenario 2 would be, although I have proper
> indexing and stuff so the UPDATES should be fast enough...
>
> Thanks
>
> Peter
>
> -- 
> To unsubscribe, e-mail: beginners-unsubscribe@perl.org
> For additional commands, e-mail: beginners-help@perl.org
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>
>

0
orasnita
7/10/2005 3:23:19 PM
On Sun, Jul 10, 2005 at 06:23:19PM +0300, Octavian Rasnita wrote:
> Can't you use the replace sql query?
> 
> Use it like you use "insert". It will insert new rows where there are no
> rows, and do an update where there are rows...
> 

Negative. REPLACE is just a shortcut for DELETE FROM... INESERT INTO (at 
least in MySQL) with some pretty neat gimmicks to find out what exactly 
to DELETE. Performance is the same but you have less control.

Peter
0
rabbit
7/10/2005 3:33:21 PM
> I'm sure others will give you more informed answers..  But why can't
> you create multiple statement handlers under the same connection?
> 

Because you can't. One connection holds only one prepared statement (at 
least in MySQL). If you prepare $statement2 on the same $dbh, 
$statement1 automatically gets invalidated. Clinically proven :)

Peter

0
rabbit
7/10/2005 3:35:05 PM
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hey Peter,

Are you sure about that?
As far as I understand, what you say you can't do, is to have two
prepares  for example:

my $dbh = DBI->connect("dbi:mysql:database","user","pass");
my $foo = $dbh->prepare("SELECT * FROM .....");
my $bar = $dbh->prepare("INSERT INTO .....");
$foo->execute();
$foo->finish();
$bar->execute();
$bar->finish();

At least that works for me.

Sorry if I misunderstood you.


Mads

Peter Rabbitson wrote:
|>I'm sure others will give you more informed answers..  But why can't
|>you create multiple statement handlers under the same connection?
|>
|
|
| Because you can't. One connection holds only one prepared statement (at
| least in MySQL). If you prepare $statement2 on the same $dbh,
| $statement1 automatically gets invalidated. Clinically proven :)
|
| Peter
|
|

- --
Mads N. Vestergaard - http://rwxr-xr-x.dk
Interested in Open Source, and web application development
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFC0Ukt7WOaPMd53OMRAvcCAJ0Y/kCn1Oh67D3AUrCfpq7ihBOu8QCeO/SC
JMvYjVK+8naZfgReXn13AQU=
=psBJ
-----END PGP SIGNATURE-----
0
mnv
7/10/2005 4:13:36 PM
Below is taken from: http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm

Also look at: http://dev.mysql.com/tech-resources/articles/4.1/prepared-sta=
tements.html


Prepared statement support (server side prepare)

    To use server side prepared statements, all you need to do is set
the variable mysql_server_prepare in the connect:

    $dbh =3D DBI->connect(
"DBI:mysql:database=3Dtest;host=3Dlocalhost:mysql_server_prepare=3D1", "",
"", { RaiseError =3D> 1, AutoCommit =3D> 1 } );

    To make sure that the 'make test' step tests whether server
prepare works, you just need to export the env variable
MYSQL_SERVER_PREPARE:

    export MYSQL_SERVER_PREPARE=3D1

    Test first without server side prepare, then with.


On 7/10/05, Mads N. Vestergaard <mnv@timmy.dk> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>=20
> Hey Peter,
>=20
> Are you sure about that?
> As far as I understand, what you say you can't do, is to have two
> prepares  for example:
>=20
> my $dbh =3D DBI->connect("dbi:mysql:database","user","pass");
> my $foo =3D $dbh->prepare("SELECT * FROM .....");
> my $bar =3D $dbh->prepare("INSERT INTO .....");
> $foo->execute();
> $foo->finish();
> $bar->execute();
> $bar->finish();
>=20
> At least that works for me.
>=20
> Sorry if I misunderstood you.
>=20
>=20
> Mads
>=20
> Peter Rabbitson wrote:
> |>I'm sure others will give you more informed answers..  But why can't
> |>you create multiple statement handlers under the same connection?
> |>
> |
> |
> | Because you can't. One connection holds only one prepared statement (at
> | least in MySQL). If you prepare $statement2 on the same $dbh,
> | $statement1 automatically gets invalidated. Clinically proven :)
> |
> | Peter
> |
> |
>=20
> - --
> Mads N. Vestergaard - http://rwxr-xr-x.dk
> Interested in Open Source, and web application development
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>=20
> iD8DBQFC0Ukt7WOaPMd53OMRAvcCAJ0Y/kCn1Oh67D3AUrCfpq7ihBOu8QCeO/SC
> JMvYjVK+8naZfgReXn13AQU=3D
> =3DpsBJ
> -----END PGP SIGNATURE-----
>=20
> --
> To unsubscribe, e-mail: beginners-unsubscribe@perl.org
> For additional commands, e-mail: beginners-help@perl.org
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>=20
>=20
>
0
davevg
7/10/2005 5:31:46 PM
Peter Rabbitson wrote:
> Hello everyone,
> I want someone to share his expertise on the following:
> Suppose we have a table with some data that periodically must be synced 
> with an external source, which provides only partial information (e.g. 
> it might provide all the fields for a new record, but might provide 
> only a single field difference for an already existing record). This 
> obviously will involve a series of UPDATE and INSERT statements, mostly 
> in random order. As most RDBMS can hold only one prepared statement at a 
> time (including MySQL) I have 2 ways to do this that look somewhat 
> efficient:
>

What version of MySQL? Generally this would be handled with a
transaction, but only newer MySQLs support them. And I suspect Randal
(if he is listening) will pop in with a comment about using PostgreSQL
right about now :-). And now, having used both I would agree with him.

> 1. Sweep the pre-existing table into a hash, DELETE all the records from 
> it and INSERT the new data, using the hash as a source for missing 
> fields.
> 

Yeh this sounds like a nightmare.

> 2. Create 3 connections to the database, have the first one hold a 
> SELECT statement that will replace the hash above, have the second 
> connection be an INSERT for nonexisting records and the third connection 
> be an UPDATE for existing ones
> 

As the other posters said, and to my knowledge, you should double check
that you can't prepare multiple statements.  From the DBI docs:

http://search.cpan.org/~timb/DBI-1.48/DBI.pm#prepare

"Portable applications should not assume that a new statement can be
prepared and/or executed while still fetching results from a previous
statement."

That is a "should not assume" rather than a can't do it, and that is
specifically geared towards "portable applications" which generally
means when dealing with multiple different db backends. You might also
want to have a read through the 'prepare_cached' section following the
above. It appears to be a way in DBI to handle the same if the backend
doesn't support it.

>>From reading on DBI I understood that moving lots of data with do()
> statements is pretty slow (e.g. constantly re-preparing the same 
> statement), so I think scenario 2 with a single connection is even 
> worse.
> 

Slow is still only meaningful in your context until you benchmark
whether it really is too slow you won't know.

> The reason I am elaborating on this is that I am afraid the dataset will 
> stop fitting in memory at some point of time if I go with scenario 1 
> (which is easier and cleaner to implement). Also I have no idea how 
> resource intensive scenario 2 would be, although I have proper 
> indexing and stuff so the UPDATES should be fast enough...
>

Easier I would agree with, cleaner I definitely wouldn't, at least not
if we are talking about enterprise level stuff. There are all kinds of
issues you can run into when trying to do a DELETE/INSERT instead of the
more appropriate UPDATE, especially when there is an intermediary (your
Perl hash) involved.

> Thanks
> 
> Peter
> 

HTH some,

http://danconia.org
0
wiggins
7/10/2005 5:39:14 PM
Reply:

Similar Artilces:

INSERT vs. UPDATE question
I have a Sql2000 database located on a remote server that contains a table named tblLinkStore which has a columns named ID_L and L_Rank, both of type Int.  I also have a dynamically generated GridView which also contains columns named ID_L and L_Rank.   When I generate the GridView it recalculates the L_Rank value.  I want to write that newly generated L_Rank value into the remote Sql table WHERE tblLinkStore.ID_L = GridView's ID_L . Do I need to look at doing an UPDATE or an INSERT?  I thought INSERT was only to add new records.&nb...

Insert/Update trigger question--how to know if insert or update
Is there an easy way to tell in a single trigger for both insert/update whether it was an insert or update. I know how to do this in transact sql (for each statement) , but the for each row thing is messing me up. in transact sql I can just count the number of deleted rows (if 0 then its an insert) but in the sql anywhere syntax its kinda strange-- Thanks Dave Stienessen davids@xata.com Couldn't you check if there's an 'old' row? If there is it's an update, otherwise it's an insert. David. Dave Stienessen wrote: > Is there an easy way to ...

Insert/Update question
Hi, I am building an e-commerce site for a college project and in my database design I am using autonumbers/Identity for primary keys for things like order numbers. I am using SQL Server 2005 and have the keys as int's and with identitys that start and 1 and increment by one.  I know that when I do an insert the table will automatically put a number in the id field. My question is if I have to do an insert on multiple tables, lets say and order table and order history table.  How do I get the generated id number into the foriegn key on the other table?  Is there someway...

Beginners question about INSERT
Hello! I've been trying to figure out how to make an insert when getting the values from normal asp:TextBox. I read something about parameters but don't know how to use them? Thanks in advance!Martin LundbergSweden Check if this article helps. The article has sample code Updating a table. You can easily change it to INSERT. The rest of the code should be pretty much the same. The values provided to the parameters have been hard coded. You can use the value from the textbox instead.***********************Dinakar NethiLife is short. Enjoy it.***********************...

Beginner VS 2008 question
This must be a very easy questoin but I have to ask after several hours of trying. I installed MVC Preview 3 on my VS TS 2008 but now I don't have "view in browser". How can I view the website on browser? Thank you. Hiya,  Take a look at this blog for information on MVC http://weblogs.asp.net/scottgu/archive/2008/05/27/asp-net-mvc-preview-3-release.aspx and the setup of some demo.  Cheers Jorus--Please put "Mark as Answer" if I help you solve your problem, Thanks. Also, there is a ASP.NET MVC forum here: http://forums.asp.net/1146.aspx Richa...

How can i do a multiple insert or multiple updates or inserts and updates to the same table..
Hi...  I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...   this is my sproc...  ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowS...

[perl5-dbi/dbi] 573e96: Always insert *ALL* fields on INSERT
----==_mimepart_51a75a8ec8d2d_68e29c3de41136f Date: Thu, 30 May 2013 06:56:30 -0700 Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-ID: <51a75a8ecacaf_68e29c3de411435@hookshot-fe6-pe1-prd.aws.github.net.mail> Branch: refs/heads/master Home: https://github.com/perl5-dbi/dbi Commit: 573e96824bb08bb411788e9e2354d0916fb1f589 https://github.com/perl5-dbi/dbi/commit/573e96824bb08bb411788e9e2354d0916fb1f589 Author: Jens Rehsack <sno@netbsd.org> Date: 2013-05-30 (Thu, 30 May 2013) Changed paths: ...

PackageKit vs Zypper (or 950 updates vs 0 updates)
Hi all! Well I started my Susy today and I noticed this little green update button that says that 950(!) updates are available. Now: 950 updates are a lots of, right. In the IRC-channel someone (lejo) told me to try this-> zypper ref and then zypper lu. The result: no(!) updates are available, with this method. Now: That makes me wonder. Why does Zypper say this and PackagKit says that...does someone know how to focalize this problem? ANY hints are welcomed. Yours. S PS: not to sure, if it is important to know that I installed OSuse while it still was a RC. (->I have a...

DELETE & INSERT vs. UPDATE
Hello, When you specify the updated properties for a datawindow what is the best option to choose for Key Modification. I'm using PB 6.5 and don't know whether to choose DELETE & INSERT or UPDATE. Could someone shed some light on this topic? Also, I heard somewhere that if you use DELETE & INSERT there is a possibility of losing some rows in you table. Is this true? Thanks in advance. Shenn Sellers waste.ssellers@co.riverside.ca.us In my experience, this seems to depend a lot on whether or not your database will allow you to do an UPDATE when the key is m...

Few questions regarding INSERT and UPDATE statements.
Hi, I have some questions regarding code below. (using embedded C and static statements). // first i create some tables EXEC SQL CREATE TABLE Island( islandno integer NOT NULL DEFAULT AUTOINCREMENT, islandarea integer NOT NULL, noofregions integer NOT NULL, primary key(islandno)); EXEC SQL CREATE TABLE Region( regionno integer NOT NULL DEFAULT AUTOINCREMENT, islandno integer NOT NULL, posx integer NOT NULL, posy integer NOT NULL, primary key(regionno,islandno), FOREIGN KEY rolename(islandno) REFERENCES Island(islandno)); // then i will insert one row at...

embedded SQL vs INSERT/UPDATE
I'm trying to do some embedded SQL statement. I'm using PB 6.5 with SQL Anywhere. Most of them work properly but I have problem with two of them. I want to do insert/update of more than one row at a time in a table from a temporay one. My statements look like this: =============== INSERT statement: INSERT INTO "tcar_inventory" ( "assgn_id", "car_init", "car_numb", "car_kind", "load_empty", "stn_abbr", "trk_numb", "moved" ) SELECT "tcar_inventory_t...

Details View Insert/update question
When I insert a record I would like dateadded column to be equal to the current insert datetime.                     1. How do I assign current datetime to to this coulmn? Also when a record is updated in the detailsview the dateaddedfield is erased.                       2. How do I make it so that the update does not affect the dateadded value? &nbs...

Update vs. Insert (���Urgent Please!!!)
Hi! I'm working with PowerSite + Jaguar + PowerDynamo and I'm using a DW (created with dwCurriculum = java.CreateComponent("DataWindow/HTMLGenerator", "iiop://mse_inet:9000", "...", ""); dwCurriculum.SetDWObject ("C:\\....PBL", "dw_curriculum"); dwCurriculum.SetBrowser(document.GetServerVariable("HTTP_USER_AGENT")); dwCurriculum.SetHTMLObjectName ("dwCurriculum"); dwCurriculum.SetTrans ("ODBC", "ConnectString='DSN=...;UID=...;PWD=...'", "", "", &q...

Help Linksys BEFVP41 v1 VS v2 VS BEFSX41 v2, questions questions
Cross post to grc.securitynow. Followup to grc.securitynow. See below for 2 BUGS in the BEFSX41 v2. Hello all. The last two weeks have been exasperating for me. I listened to Steve's shows about VPN's a few weeks and got all excited about the technology. I wanted to learn about VPN's just because I could. For the moment, all I want to do is VPN into my network and browse back out on to the internet through my cable modem. I've been posting messages here and reading dozens, if not hundreds over on dslreports.com. I still have many questions. Sorry this ...

Web resources about - DBI insert vs update question - perl.beginners

West Lothian question - Wikipedia, the free encyclopedia
He illustrated his point by pointing out the absurdity of a Member of Parliament for West Lothian being able to vote on matters affecting the ...

Seven Questions With Spotify On Beatles Twitter Campaign
To keep excitement alive about Beatles songs finally being added to its streaming platform, Spotify launched a social media campaign with customized ...

A question of faith - with a lower case 'f'
Essay: What happens when you leave your Faith with an upper case 'F' to pursue faith with a lower case 'f'?

Question of the Day: Most heinous act of badge engineering?
Filed under: Chrysler , Dodge , Misc. Automakers , Mitsubishi , Nissan , Suzuki , Automotive History When GM sold the Isuzu Faster pickup as ...

SunEdison Drops 7%: Merrill Lauds Disclosure, UBS Questions Cost of Funding
Shares of solar energy system installation firm SunEdison ( SUNE ) today closed down 43 cents, over 7%, at $5.49, amidst mixed coverage from ...

Put on the spot by a question at a town hall, Hillary Clinton calls what is happening to Christians in ...
A member of the audience asked (and I'm assuming this wasn't a plant): "Will you join those leaders, faith leaders and secular leaders and political ...

7 Questions for Those Who Claim Islam is "Just Another Religion"
Offered up by our long-suffering Summer Intern @BiffSpackle : Democrats, progressives, and other miscreants: please feel free to answer any or ...

No More GOP? The Democratic Party Will Welcome You, No Questions Asked
Tuesday morning I woke up at 4am. It was still dark so I reached for the channel changer and switched the TV on. It was Eugene Robinson, one ...

8 burning questions everyone has about 'Star Wars: The Force Awakens' and the best fan theory answers ...
Warning: There are massive spoilers ahead. Do not read on if you have not seen "Star Wars: The Force Awakens." Hopefully by now you've seen ...

Question #9 for 2016: What will happen with house prices in 2016?
Over the weekend, I posted some questions for next year: Ten Economic Questions for 2016 . I'll try to add some thoughts, and maybe some predictions ...

Resources last updated: 12/31/2015 2:30:21 PM