 | |
Inserting small tables across WAN to SQL Server (SLOW??)
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Inserting small tables across WAN to SQL Server (SLOW??) -
12-13-2010
, 04:18 PM
|
On 12/11/10 3:28 PM, Tony Toews wrote: On Sat, 11 Dec 2010 09:46:48 -0600, Ryan Mindflu... (AT) gNOSPAMPLEASEmail (DOT) com> *wrote: I had no idea Server 08 could handle multiple values in the insert. That's great (and maybe that'll be my ticket out of slowness). I'm thinking that sending 900 rows at once might be a bit much for the WAN connection to keep all correct. *I'd suggest teting by inserting 10 records at a time and then 100 records. Tony Tony, * *Could you provide some code suggestions here? *How could I easily loop through 10/100 records. *Problems I see with that (100 rec especially) is getting down to where there is less than 100/less than 10 records and the loop not completing. do until rst.eof n=1 * * * * do until n=100 * * * * insert statement here * * * * *n=n+1 * * * * loop rst.MoveNext loop for example.. would eventually get to <99 records left. I guess I could use something to tell me my current record position and total record size to help determine that last bit... |
Would this help?
n = 0
Do While (Not rst.EOF)
n = n + 1
insert statements here
if (n = 100) then Exit Do
rst.MoveNext
Loop
Imb.
#22
| |||
| |||
|
|
On Dec 13, 4:01 am, Ryan <Mindflu... (AT) gNOSPAMPLEASEmail (DOT) com> wrote: On 12/11/10 3:28 PM, Tony Toews wrote: On Sat, 11 Dec 2010 09:46:48 -0600, Ryan Mindflu... (AT) gNOSPAMPLEASEmail (DOT) com> wrote: I had no idea Server 08 could handle multiple values in the insert. That's great (and maybe that'll be my ticket out of slowness). I'm thinking that sending 900 rows at once might be a bit much for the WAN connection to keep all correct. I'd suggest teting by inserting 10 records at a time and then 100 records. Tony Tony, Could you provide some code suggestions here? How could I easily loop through 10/100 records. Problems I see with that (100 rec especially) is getting down to where there is less than 100/less than 10 records and the loop not completing. do until rst.eof n=1 do until n=100 insert statement here n=n+1 loop rst.MoveNext loop for example.. would eventually get to <99 records left. I guess I could use something to tell me my current record position and total record size to help determine that last bit... Hi Ryan, Would this help? n = 0 Do While (Not rst.EOF) n = n + 1 insert statements here if (n = 100) then Exit Do rst.MoveNext Loop Imb. |
940, the problem is looping to 100 when I get to record 900, there's
only 40 left will never loop all the way through.
I guess I could get a total record count and have a separate counter
that counts total=total+1 and then on total = rst.recordcount exit do.
#23
| |||
| |||
|
|
Could you provide some code suggestions here? How could I easily loop through 10/100 records. Problems I see with that (100 rec especially) is getting down to where there is less than 100/less than 10 records and the loop not completing. do until rst.eof n=1 do until n=100 insert statement here n=n+1 loop rst.MoveNext loop |
insert statement here
n=n+1
loop
If Not rst.EOF Then rst.MoveNext
loop
--
Marsh
#24
| |||
| |||
|
#25
| |||
| |||
|
|
wow you're a retard if you're using linked tables across a WAN. Move to Access Data Projects.. and use WITH (NOLOCK) whenever you create views.. and your life should be a lot easier. |
Thanks for the reply? I'll remember to block you from further comments.
Flaming is totally unnecessary but apparently 'your style'.
#26
| |||
| |||
|
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.