[AccessD] Pushing data to Azure SQL database VERY VERY slow

Ryan W wrwehler at gmail.com
Tue Oct 31 20:44:52 CDT 2017


I was using smiley coders idea he posted about looping though a recordset and writing out a big insert statement was faster for him than actually just doing “insert into” over the WAN. 

In this case it isn’t working well for me. I’m just doing it in SSMS and not using access at all. 

Sent from my iPhone

> On Oct 31, 2017, at 8:18 PM, Jim Lawrence <accessd at shaw.ca> wrote:
> 
> Why would the data need to be concatenated into a variable?
> 
> Jim
> 
> ----- Original Message -----
> From: "Ryan W" <wrwehler at gmail.com>
> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
> Sent: Tuesday, October 31, 2017 2:32:29 PM
> Subject: Re: [AccessD] Pushing data to Azure SQL database VERY VERY slow
> 
> It actually appears the slowness comes from concatenation into a variable. After a while the data stored in the variable compounds the concatenation slowing down
> 
> This is being done in pure TSQL to a linked server.... 
> 
> Sent from my iPhone
> 
>> On Oct 31, 2017, at 3:09 PM, Jim Lawrence <accessd at shaw.ca> wrote:
>> 
>> You could of course pre-translate the data before pushing it out. Would changing a null to a "" or 0 (an empty) be more efficient?
>> 
>> Please tell me you are not using pass-through queries to move the data.
>> 
>> Jim 
>> 
>> ----- Original Message -----
>> From: "Ryan W" <wrwehler at gmail.com>
>> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
>> Sent: Monday, October 30, 2017 12:54:32 PM
>> Subject: Re: [AccessD] Pushing data to Azure SQL database VERY VERY slow
>> 
>> I kind of took this idea and ran with it. I was able to get the time down
>> to 5 seconds for one table (500 records) but the larger 6000 row table is
>> giving me fits because there are nulls. So when I start wrapping colums in
>> IsNull() it slows down the processing even more.
>> 
>> I'll have to look into the XML route, perhaps.
>> 
>> 
>> On Mon, Oct 30, 2017 at 8:49 AM, The Smiley Coder <thesmileycoder at gmail.com>
>> wrote:
>> 
>>> 
>>> Doing regular inserts against a linked table would take ages. So, instead I
>>> added code in the frontend which looped the local recordsets, wrote a SQL
>>> passthrough string like so:
>>> Insert into tbl_results(ID,value,x,y) values (6,"yeah",6,7)
>>> Insert into tbl_results(ID,value,x,y) values (7,"nooo",9,7)
>>> Insert into tbl_results(ID,value,x,y) values (8,"yeah",6,7)
>>> 
>>> 
>> -- 
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>> -- 
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com



More information about the AccessD mailing list