Re: test results

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 15:39:44 -0800

Using ADO (any version) to do bulk uploads is like asking a pizza delivery
guy on a bike to deliver a ton of coal. If you want to move a lot of rows to
the server in a hurry, use BCP or DTS. It's supported directly from ADO.NET
2.0.

hth 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"SÁRINGER Zoltán" <asdf@asdf.hu> wrote in message 
news:eO%23gGuW4EHA.2180@TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm so glad to see you so enthusiastic...:) let's do the forum:)
>
> First of all, of course I dont use SELECT *, it was only an example:) , 
> The
> user and the application are also restrict the number of columns and 
> rows..,
> but for test ".cancel" it's good.
> Windows's eventlog is clear.
>
> I'm using clientside cursor, since DataGrid supports only this (do you 
> know
> about another grid which supports serverside cursors? so the rows would be
> come as user browse the records, on demand, if I think well! I saw one for
> Delphi, any exist for VB?, tell me.)
>
> ..but I've made the tests, so here are the results:
>
> The internet connection is a kind of cable-modem.. 128 kbit is the upload
> speed of the server (512 is the downlaod, but the bottle neck is the 
> upload
> in our case..), what is not too fast, but should be enough (if calculate
> with 100 rows are 15 Kbyte, SELECT should takes for 1 seconds, am I 
> right?)
> BTW, the "Internet" connection is not "real", since the client and server 
> is
> on the same subnetwork of ISP, there is just a switch somewhere between 
> us.
> It's the test environment only!! The ping is good: max. 8000 bytes/packet
> echoes within 500 msec. So this is a continous and slow connection. The
> test:
> 1) SELECT TOP 100 * FROM table1 : takes 1 second, this is what I excepted,
> its good!
> 2) doing 100 pieces of ADO .addnew, takes for 1.5 sec!!!! very good, 
> better
> than I excepted.
>
> So I'm satisfied, and became calm, to do not have to change the almost of
> the code... I will stay at ADO, and thick client.
>
> <new thread>
> (as an curiosity: 100 pieces of ADO. addnew -1 field- takes the same time 
> as
> T-SQL INSERT...)
> This is why I'm using ADO. Easier to code, NOT much slower than T-SQL. 
> What
> about stored procedures? If they do a simple INSERT's, i think takes as
> least so long time as a pure INSERT.. There is not too much business logic
> what worthy of encapsulate into SP., Thick client: easier to mainatian,
> becouse all code in one place. I rather minimize the roundtrips to 
> server...
> and the main reason, why I stick to "thick" client, is the logging
> functionality what I developed. I didnt want make 300 triggers for the 100
> tables for update, delete, inserts,,so I do everything by an recordset
> class, where I use it's events (RecordChangeComplete), and cn's
> CommitTransComplete events...to do logging the main changes of database...
> (if required!) This is the method what I find out to avoid difficult
> synchronization problems...if two sites will not work via internet, I have
> to synchronize them... but its another topic, if you have opinion, write 
> me,
> I'm interested in !!!
> </new thread>
>
> so, what is the problem then...?! The REAL internet connection, where the
> client at another ISP, and ADSL instead of cable..., and have 6-8 routers
> between the PCs.. Then the ping is: max. 1000 bytes packet with 100 msec. 
> ,
> but the transfer rate also 15Kbyte/sec. So I think the previously tested
> stataments should take the same time. BUT NOT!
>
> 1) SELECT TOP 100 * FROM table1 : takes 10 seconds
> !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
>
> ...and the problem is just the select, maybe the solution will appliable 
> for
> ".addnew"s..
>
> The difference between the connections is the quality of speed, isnt it? 
> at
> the first case, (of same ISP) it was really continuos (as a leased line).
> The connection can be pure TCP/IP, or tuneled TCP/IP (VPN), the results 
> are
> the same.
>
> the connectionstring is:
>
> Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User
> ID=sa;Initial Catalog=provez;Data 
> Source=zoliotthon.dyndns.org,1433;Network
> Library=dbmssocn;Use Procedure for Prepare=1;Auto Translate=True;Packet
> Size=4096;Application Name=provez;Workstation ID=ZOLI;Use Encryption for
> Data=False;Tag with column collation when possible=False
>
> Hm.. maybe network packet size..????
>
> If you want, and can, we can make a realtime test, just throw me an email:
> kecskemetisrac@freemail.hu. You can login then to the database, and make
> queries...
>
> I'v read also this, but I beleive in my test results:)))
> http://www.sql-server-performance.com/visual_basic_performance.asp
>
> (I wrote lot if "little" questions in the text, please write me your 
> oinions
> also for them)
> Zoltan
>
>

Relevant Pages