开发者

Correct form long time query executing myodbc syntax

I'm trying to build one SQL query for Access that links tables with myodbc connection to retrive the data from internet, but the time to finish the query is too long about five minutes, so I think the problem is with the query:

SELECT COUNT([o].[orders_id]) AS howmany_orders, 
       (SELECT SUM([op1].[products_quantity]) FROM orders_total AS ot1, orders AS o1, orders_products AS op1
        WHERE [o1].[date_purchased] >=date()-30 and [o1].[orders_id] = [op1].[orders_id] and [ot1].[orders_id] = [op1].[orders_id] and [ot1].[class]="ot_total" and [o1].[orders_status] = 1 and [op1].[products_id]=[op].[products_id]
        GROUP BY [op1].[products_id]
        ) AS pendiente,
        [op].[products_model], 
        Round((((7+1)*(howmany_orders/90))+1)-(p.stock_real- IIF(pendiente>0,pendiente,0)), 0) AS pedir,
        p.ref_id

FROM orders_total AS ot, orders AS o, orders_products AS op INNER JOIN Productos AS p ON Mid([op].[products_model],4) LIKE p.ref_id

WHERE [o].[date_purchased] >=date()-90 and [o].[orders_id] = [op].[order开发者_开发知识库s_id] and [ot].[orders_id] = [op].[orders_id] and [ot].[class]="ot_total" and [o].[orders_status] IN (7, 1) and ((p.fuera_de_stock)=False) and ((p.suspendido)=False) and  ((p.quitar_de_la_web)=False)

GROUP BY [op].[products_model], p.ref_id, p.stock_real, [op].[products_id];

At a glance I see that the "LIKE" operator could be one of the problems here:

 INNER JOIN Productos AS p ON Mid([op].[products_model],4) LIKE p.ref_id

but I have not way to substitute for an = operator

Thanks for your help!

EDITING: I have reduced the query to that but is the same time:

SELECT COUNT(o.orders_id) AS howmany_orders, (
SELECT SUM(opz.products_quantity) FROM orders AS oz, orders_products AS opz WHERE oz.date_purchased >=date()-30 and oz.orders_id = opz.orders_id and oz.orders_status = 1 and opz.products_id=op.products_id GROUP BY opz.products_id
) AS pendiente, op.products_model, Round((((7+1)*(howmany_orders/90))+1)-(p.stock_real-IIf(pendiente>0,pendiente,0)),0) AS pedir, p.ref_id
FROM orders AS o, orders_products AS op INNER JOIN Productos AS p ON op.products_model=p.cod
WHERE o.date_purchased>=date()-90 And o.orders_id=op.orders_id And o.orders_status In (7,1) And ((p.suspendido)=False) And ((p.quitar_de_la_web)=False)
GROUP BY op.products_model, p.ref_id, p.stock_real, op.products_id;


Yep there’s your problem. The database can not do the join using any indexes so it has to do a table scan. Is there anyway you could persist this data so you don’t have to do the MID statement and just join on that? i.e. in your products_model table have an extra column with the MID data stored in there with the join on that column

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜