I could really need some help here. Is there a way to redesign my query in order to improve the performance? When I run it in Excel (MS Query) it takes about 4 minutes and in CR it takes between 5 minutes and eternity, sometimes CR stops responding completely.
I'm working with 3 tables and a stored procedure.
TABLES
ARTICLE
art_id
art_artnr
art_status
ARTICLE_EXTRA
art_id
ae_string_5
ARTICLE_STOCKLOCATION
art_id
lp_stock
STORED PROCEDURE
Myodbc.SP_Get_Transactions
The stored procedure looks like this:
ALTER PROCEDURE "Myodbc"."SP_Get_Transactions"(
IN as_artnr NVARCHAR(16),
IN al_art_id INTEGER
)
RESULT (
artnr NVARCHAR(16),
date DATETIME,
transtype INTEGER,
ordered DOUBLE,
reserved DOUBLE,
stock DOUBLE
)
BEGIN
...
QUERY
This is the query that I use in CR via Add Command:
SELECT
ARTICLE.art_artnr,
Transactions.stock + SUM(Transactions.ordered-Transactions.reserved) OVER (PARTITION BY Transactions.artnr ORDER BY Transactions.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as available_stock,
Transactions.date,
Transactions.transtype,
Transactions.stock,
Transactions.ordered,
Transactions.reserved
FROM
MyDB.ARTICLE ARTICLE
LEFT OUTER JOIN MyDB.ARTICLE_EXTRA ARTICLE_EXTRA ON ARTICLE_EXTRA.art_id=ARTICLE.art_id
LEFT OUTER JOIN MyDB.ARTICLE_STOCKLOCATION ARTICLE_STOCKLOCATION ON ARTICLE_STOCKLOCATION.art_id=ARTICLE.art_id
CROSS APPLY Myodbc.SP_Get_Transactions(ARTICLE.art_artnr, ARTICLE.art_id) as Transactions
WHERE
ARTICLE.art_artnr IN (
SELECT
TRANSX.artnr
FROM
Myodbc.SP_Get_Transactions(ARTICLE.art_artnr, ARTICLE.art_id) TRANSX
WHERE
TRANSX.date <= CURRENT DATE
AND TRANSX.transtype NOT IN (2, 3)
GROUP BY
TRANSX.artnr
HAVING
SUM(TRANSX.reserved) > ARTICLE_STOCKLOCATION.lp_stock
)
AND ARTICLE.art_status BETWEEN 4 AND 6
AND Transactions.date <= CURRENT DATE
AND Transactions.transtype NOT IN (2, 3)
AND (ARTIKEL_EXTRA.ae_string_5 IS NULL OR ARTIKEL_EXTRA.ae_string_5<>'UTGÅTT')
What I'm doing:
My report should show all articles that have a higher demand than the current stock.
Is my query "OK"?
The stored procedure is by nature pretty slow, it contains I think 24 subqueries (1300+ rows of code). But other than that - is there anything fundamentally wrong? I'm not super confident with joins and CROSS APPLY...
Please let me know if you need more information!
|