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

[Jim Lawrence]

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