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!

Edited by pbengtss - 02 Nov 2016 at 10:27pm