speed up insert to odbc tables

[scratch]

I'm trying to insert into an odbc linked table on mysql. If the whole thing is local and access based, the insert is fast. When i try to use a linked table, it takes 15 minutes. Any way to speed this up?

 

[Banana]

What's your SQL you use to insert?

If for example, your SQL uses an Access function that ODBC cannot translate into a SQL function, Access will be forced to pull whole table from MySQL to process the selection to determine which rows should be selected for insert. Thus, we need to be sure your SQL sticks to the standard SQL so Access can hand off the query back to the MySQL and allow MySQL to perform the insertion itself.

 

[scratch]

below is the sql the query is using

INSERT INTO item_price ( item_id, price, store_id )
SELECT id, iif(cdbl(price) < cdbl(bminprice), bminprice, price), 1
FROM [SELECT id, round(iif(calc_price < bCost * (1 + STORE_min_markup), bCost * (1 + STORE_min_markup), Iif(calc_price > bCost * (1 + STORE_max_markup),bCost * (1 + STORE_max_markup),calc_price)),1)-0.06 AS price, bminprice
FROM (SELECT ID, ps.STORE_min_markup, ps.STORE_max_markup, Iif(store_qty > 0,ps.STORE_min_order_profit_instock,ps.STORE_min_order_profit) as min_order_profit, iif(bCost <= (select STORE_cost_threshold from hub_price_settings where store_id = 1),STORE_min_qty_under_cost_threshold,Nz(baipo,9)) as items_per_order,
 bCost+(min_order_profit/(Nz(items_per_order,2)+.01)) AS calc_price, bCost, bminprice
FROM 
hub_price_settings ps 
    inner join (select i.ID, Min(Nz(sf.Cost, xf.cost_per_bottle + Nz(xf.split_case_charge,0))) as bcost, Max(iif(Nz(xf.min_price,0)=0,Nz(sf.min_price,0),Nz(xf.min_price,0))) as bminprice, Max(sf.manual_price) as bmanual_price, Max(Nz(sf.qty,0) + Nz(xf.qty_avail,0)) as btqty, Sum(sf.qty) as store_qty, Avg(pa.aipo) as baipo, 1 as store_id
                     from (((item i
                    left join item_store2 si on (i.ID = si.item_id and si.store_id = 1))
                    left join STORE_store_feed sf on (si.store_sku = sf.sku and sf.qty > 0))
                    left join item_xref xf on (i.ID = xf.item_id and xf.store_id = 1))
                    left join pricing_aipo pa on i.id = pa.item_id
                    where Nz(sf.Cost, xf.cost_per_bottle + Nz(xf.split_case_charge,0)) > 0 and Nz(sf.qty,0) + Nz(xf.qty_avail,0) > 0
                    group by i.ID
                    ) b on b.store_id = ps.store_id
)  AS x
]. AS z;

 

[scratch]

i changed it so the select goes into a local table and then the new query is

insert into item_price
select * from item_price_link

it's not a complicated query now at all but it's still slow

 

[Banana]

Lot of Access functions in there.

Which tables are actually MySQL tables?

 

[scratch]

Lot of Access functions in there.

Which tables are actually MySQL tables?

i'm using this

insert into item_price
select * from item_price_link

item_price_link has the fields as item_price and are all the records i need to insesrt. nothing fancy there but it's still slow. item_price is the only mysql table

 

[Banana]

Hmm. So all tables in the item_price_link are local Access tables? And even when you insert into a local Access table instead of a MySQL, it's still slow?

To help improve the query, we will need to know more about the table itself and ultimate purpose of the query itself. Maybe we can fix up so we don't need so many subqueries.

 

[scratch]

Hmm. So all tables in the item_price_link are local Access tables? And even when you insert into a local Access table instead of a MySQL, it's still slow?

To help improve the query, we will need to know more about the table itself and ultimate purpose of the query itself. Maybe we can fix up so we don't need so many subqueries.

i created a local table called item_price_link with the same fields as item_price
so now the query is this

INSERT INTO item_price_link ( item_id, price, store_id )
SELECT id, iif(cdbl(price) < cdbl(bminprice), bminprice, price), 1
FROM [SELECT id, round(iif(calc_price < bCost * (1 + STORE_min_markup), bCost * (1 + STORE_min_markup), Iif(calc_price > bCost * (1 + STORE_max_markup),bCost * (1 + STORE_max_markup),calc_price)),1)-0.06 AS price, bminprice
FROM (SELECT ID, ps.STORE_min_markup, ps.STORE_max_markup, Iif(store_qty > 0,ps.STORE_min_order_profit_instock,ps.STORE_min_order_profit) as min_order_profit, iif(bCost <= (select STORE_cost_threshold from hub_price_settings where store_id = 1),STORE_min_qty_under_cost_threshold,Nz(baipo,9)) as items_per_order,
 bCost+(min_order_profit/(Nz(items_per_order,2)+.01)) AS calc_price, bCost, bminprice
FROM 
hub_price_settings ps 
    inner join (select i.ID, Min(Nz(sf.Cost, xf.cost_per_bottle + Nz(xf.split_case_charge,0))) as bcost, Max(iif(Nz(xf.min_price,0)=0,Nz(sf.min_price,0),Nz(xf.min_price,0))) as bminprice, Max(sf.manual_price) as bmanual_price, Max(Nz(sf.qty,0) + Nz(xf.qty_avail,0)) as btqty, Sum(sf.qty) as store_qty, Avg(pa.aipo) as baipo, 1 as store_id
                     from (((item i
                    left join item_store2 si on (i.ID = si.item_id and si.store_id = 1))
                    left join STORE_store_feed sf on (si.store_sku = sf.sku and sf.qty > 0))
                    left join item_xref xf on (i.ID = xf.item_id and xf.store_id = 1))
                    left join pricing_aipo pa on i.id = pa.item_id
                    where Nz(sf.Cost, xf.cost_per_bottle + Nz(xf.split_case_charge,0)) > 0 and Nz(sf.qty,0) + Nz(xf.qty_avail,0) > 0
                    group by i.ID
                    ) b on b.store_id = ps.store_id
)  AS x
]. AS z;

same thing except it inserts into the item_price_link table which is local and the query is fast. so now i can just do this

insert into item_price
select * from item_price_link

which should be a lot faster since it's simple but it isn't. item_price is a mysql table