Exploring the depths and potentials of ASP.NET RSS 2.0 or Subscribe to .BenRush by Email
 Friday, March 10, 2006

    I discovered this, probably someone else out there might find it interesting.....but, it's a bad idea to have several round-trips to the database when you want to insert many records (rows). It would be nice to build an array of values to insert with one statement. This can be done easily using ADO.Net:

                comm.CommandText = "insert into blah_lnk (asdf,asdfasdf) values (:1,:2)"
                comm.ArrayBindCount = <array_length>

                Dim id_arr(<array_length>-1) As System.Int32
                For c As System.Int32 = 0 To id_arr.Length - 1
                    id_arr(c) = CType(Session("asdfasdf"), System.Int32)
                Next

                Dim orap As New Oracle.DataAccess.Client.OracleParameter("1", OracleDbType.Varchar2)
                orap.Value = id_arr
                orap.Direction = ParameterDirection.Input
                comm.Parameters.Add(orap)

                Dim orap2 As New Oracle.DataAccess.Client.OracleParameter("2", OracleDbType.Varchar2)
                orap2.Value = arr.ToArray()
                orap2.Direction = ParameterDirection.Input
                comm.Parameters.Add(orap2)

                comm.ExecuteNonQuery()

...anyway, the idea is that you can execute the statement on the array, and allow yourself only one round-trip to the database. I'm not a DB programmer, so this is probably well known, but I didn't know it....


kick it on DotNetKicks.com
Friday, March 10, 2006 2:14:51 AM (Central Standard Time, UTC-06:00)  #    Comments [4] - Trackback
Computing
Tracked by:
http://freewebs.com/aspxfaq/12/index.html [Pingback]
http://freewebs.com/toltom/07/sitemap6.html [Pingback]
http://freewebs.com/toltom/07/free-email-search.html [Pingback]
http://freewebs.com/toltom/04/sitemap14.html [Pingback]
http://freewebs.com/toltom/05/carlton-sheets-no-money-down.html [Pingback]
"http://fartooblog.tripod.com/69.html" (http://fartooblog.tripod.com/69.html) [Pingback]
"http://kevruublog.tripod.com/52.html" (http://kevruublog.tripod.com/52.html) [Pingback]
"http://zoedfw.org/sitemap39.html" (http://zoedfw.org/sitemap39.html) [Pingback]
"http://xhasko.org/horny-and-young.html" (http://xhasko.org/horny-and-young.html... [Pingback]
"http://topslots.nl.eu.org/10/sitemap2.html" (http://topslots.nl.eu.org/10/sitem... [Pingback]
"http://freewebs.com/amexa/31/sunroom.html" (http://freewebs.com/amexa/31/sunroo... [Pingback]
"http://freewebs.com/amexa/48/lexus-nexus.html" (http://freewebs.com/amexa/48/le... [Pingback]
"http://freewebs.com/amexa/18/debt-consolidation-companies.html" (http://freeweb... [Pingback]
"http://pinofranc.homestead.com/05/western-belt-buckles.html" (http://pinofranc.... [Pingback]
"http://pinofranc.homestead.com/00/european-river-cruises.html" (http://pinofran... [Pingback]
"http://pinofranc.homestead.com/05/washington-d-c--restaurants.html" (http://pin... [Pingback]
"http://mdq3k-xxx.com/free-porn-video.html" (http://mdq3k-xxx.com/free-porn-vide... [Pingback]
"http://z0poi-www.com/pamela-anderson-naked.html" (http://z0poi-www.com/pamela-a... [Pingback]
"http://tadguunews.netfirms.com/18.html" (http://tadguunews.netfirms.com/18.html... [Pingback]
"http://nabkoonews.tripod.com/42.html" (http://nabkoonews.tripod.com/42.html) [Pingback]
"http://pohteenews.tripod.com/158.html" (http://pohteenews.tripod.com/158.html) [Pingback]
"http://iepgq-ooo.com/green-guy-adult-links.html" (http://iepgq-ooo.com/green-gu... [Pingback]
"http://batkoonews.tripod.com/29.html" (http://batkoonews.tripod.com/29.html) [Pingback]
"http://ccc2k-hhh.com/dorm-jerk-off.html" (http://ccc2k-hhh.com/dorm-jerk-off.ht... [Pingback]
"http://nky7v-xxx.biz/naked-lady-gallery.html" (http://nky7v-xxx.biz/naked-lady-... [Pingback]
"http://naidu-www.biz/dutch-porn.html" (http://naidu-www.biz/dutch-porn.html) [Pingback]
"http://jjyfo-eee.com/shaved-cheerleader-pussy.html" (http://jjyfo-eee.com/shave... [Pingback]
"http://freewebs.com/amexa/17/ninewest-com.html" (http://freewebs.com/amexa/17/n... [Pingback]
"http://freewebs.com/lcddlp/05/map-of-north-carolina.html" (http://freewebs.com/... [Pingback]
"http://freewebs.com/bermut/00/jobs-in-san-jose-ca.html" (http://freewebs.com/be... [Pingback]
"http://freewebs.com/niret/04/morpheus.html" (http://freewebs.com/niret/04/morph... [Pingback]
"http://freewebs.com/rimoq/12/inversion-table.html" (http://freewebs.com/rimoq/1... [Pingback]
"http://p5roc-rrr.com/hustler-magazine-models.html" (http://p5roc-rrr.com/hustle... [Pingback]
"http://unistarkom.ueuo.com/00518-blog.html" (http://unistarkom.ueuo.com/00518-b... [Pingback]
"http://ramambo.nl.eu.org/07/exxon.html" (http://ramambo.nl.eu.org/07/exxon.html... [Pingback]
"http://harum.nl.eu.org/house-siding.html" (http://harum.nl.eu.org/house-siding.... [Pingback]
"http://harum.nl.eu.org/cosco-com.html" (http://harum.nl.eu.org/cosco-com.html) [Pingback]
"http://nfdi1ek.biz/native-american-nude.html" (http://nfdi1ek.biz/native-americ... [Pingback]
"http://kp7ide0.biz/www-kuryakyn-com.html" (http://kp7ide0.biz/www-kuryakyn-com.... [Pingback]
"http://yermena.nl.eu.org/mississippi-press-com.html" (http://yermena.nl.eu.org/... [Pingback]
"http://narovkom.nl.eu.org/painted-tits.html" (http://narovkom.nl.eu.org/painted... [Pingback]
"http://srykwzw.biz/gle-comhttp.html" (http://srykwzw.biz/gle-comhttp.html) [Pingback]
"http://nasferablog.netfirms.com/93.html" (http://nasferablog.netfirms.com/93.ht... [Pingback]
"http://dq8bbaq.biz/hot-studs-masturbating.html" (http://dq8bbaq.biz/hot-studs-m... [Pingback]
"http://cyisevw.com/used-golf-carts.html" (http://cyisevw.com/used-golf-carts.ht... [Pingback]
"http://mytnpg9.biz/teen-cum-shot.html" (http://mytnpg9.biz/teen-cum-shot.html) [Pingback]
"http://wbseqlc.biz/foot-erotic.html" (http://wbseqlc.biz/foot-erotic.html) [Pingback]
"http://nasferablog.netfirms.com/351.html" (http://nasferablog.netfirms.com/351.... [Pingback]
"http://vbo--blog.nl.eu.org/beyonce-knowles-nude.html" (http://vbo--blog.nl.eu.o... [Pingback]
"http://nasferablog.netfirms.com/538.html" (http://nasferablog.netfirms.com/538.... [Pingback]
"http://fto--kom.nl.eu.org/real-player-10.html" (http://fto--kom.nl.eu.org/real-... [Pingback]
"http://klo--blog.nl.eu.org/ohio-municipal-court.html" (http://klo--blog.nl.eu.o... [Pingback]
"http://gada--mene.nl.eu.org/amatuer-nude.html" (http://gada--mene.nl.eu.org/ama... [Pingback]
"http://quezyvu.biz/beautiful-young-women.html" (http://quezyvu.biz/beautiful-yo... [Pingback]
"http://nasferablog.netfirms.com/8.html" (http://nasferablog.netfirms.com/8.html... [Pingback]
"http://jmqp7tr.biz/www.varizon.com.html" (http://jmqp7tr.biz/www.varizon.com.ht... [Pingback]
"http://hjftsic.biz/cheapcarinsuranc.html" (http://hjftsic.biz/cheapcarinsuranc.... [Pingback]
"http://derfoblog.ifrance.com/sitemap1.html" (http://derfoblog.ifrance.com/sitem... [Pingback]
"http://freewebs.com/sruone/hobbs-new-mexico.html" (http://freewebs.com/sruone/h... [Pingback]
"http://freewebs.com/sruone/sitemap183.html" (http://freewebs.com/sruone/sitemap... [Pingback]
"http://lopbafrea.homestead.com/70.html" (http://lopbafrea.homestead.com/70.html... [Pingback]
"http://battxgs.info/christina-aguilera-naked.html" (http://battxgs.info/christi... [Pingback]
"http://freewebs.com/vuter/00/washington-and-lee-university.html" (http://freewe... [Pingback]
"http://vuter.homestead.com/01/murad.html" (http://vuter.homestead.com/01/murad.... [Pingback]
"http://buter.homestead.com/00/sitemap11.html" (http://buter.homestead.com/00/si... [Pingback]
"http://duter.homestead.com/01/vietnam-airlines.html" (http://duter.homestead.co... [Pingback]
"http://freewebs.com/datingblogger/1448.html" (http://freewebs.com/datingblogger... [Pingback]
"http://freewebs.com/datingblogger/538.html" (http://freewebs.com/datingblogger/... [Pingback]
"http://fasxen.netfirms.com/13.html" (http://fasxen.netfirms.com/13.html) [Pingback]
Friday, March 10, 2006 10:36:20 AM (Central Standard Time, UTC-06:00)
I have been faced with this a few times, some times I've gone for the bulk insert capability, other times I've decided to use one record at a time

how does this particular approach work if any of the inserts (say in the middle) causes an error on the SQL side?
is the entire operation aborted?
would you know which record generated the problem?

is behind the scenes really just one trip?
Friday, March 10, 2006 11:33:02 AM (Central Standard Time, UTC-06:00)
"is behind the scenes really just one trip?"

It appears to be so, through the minor tests I've run I'm seeing a slight performance improvement. I think the best way, still, is to pass an array of values to a stored procedure and have it chew on that data - I have to imagine your best performance will happen when you're running under the context of a stored procedure under the guidance of Oracle itself.
Ben
Friday, March 10, 2006 3:11:37 PM (Central Standard Time, UTC-06:00)
>>I think the best way, still, is to pass an array of values to a stored procedure and have it chew on that data

you don't mean to have the stored procedure parse some (perhaps string) data passed in, do you?
Friday, March 10, 2006 5:15:20 PM (Central Standard Time, UTC-06:00)
Nope, iterate the elements of the array and use those as arguments to the sql (not inline).

That is, indexing things via arr(1), arr(2) .... arr(n).
Ben
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):

Live Comment Preview

Computers Blogs - Blog Top Sites

Archive
<January 2009>
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
25262728293031
1234567
Blogroll
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2009
Benjamin Rush
Sign In
Statistics
Total Posts: 444
This Year: 0
This Month: 0
This Week: 0
Comments: 128
Themes
Pick a theme:
All Content © 2009, Benjamin Rush
DasBlog theme 'Business' created by Christoph De Baene (delarou)