Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9

    Unanswered: Possible to speed up my query?

    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 Crystal Reports XI it takes between 5 minutes and eternity, sometimes it stops responding completely.

    I'm working with 3 tables and a stored procedure.

    TABLES
    ARTICLE
    art_id
    art_artnr
    art_status

    Table contains 51000 rows. After my conditions (not all are included in above query) at the end of the query it's reduced to 2000. 64 columns.


    ARTICLE_STOCKLOCATION
    art_id
    lp_stock

    Table also contains 51000 rows. 31 columns.


    ARTICLE_EXTRA
    art_id
    ae_string_5

    Table contains 17000 rows. 15 columns.


    STORED PROCEDURE
    Myodbc.SP_Get_Transactions
    The stored procedure looks like this:

    Code:
    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. It works but it's slow:
    Code:
    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!
    Last edited by pbengtss; 11-03-16 at 05:26.

  2. #2
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    Does an index exist on the column art_id in both tables, ARTICLE_EXTRA and ARTICLE_STOCKLOCATION? If not you need them.

    "Most" times an exists will outperform an IN (subselect)

    Try:
    Code:
    WHERE exists (SELECT
            1  FROM 
            Myodbc.SP_Get_Transactions(ARTICLE.art_artnr, ARTICLE.art_id) TRANSX
        WHERE ARTICLE.art_artnr = TRANSX.artnr
         and   TRANSX.date <= CURRENT DATE 
            AND TRANSX.transtype NOT IN (2, 3)
        GROUP BY
            TRANSX.artnr
        HAVING
            SUM(TRANSX.reserved) > ARTICLE_STOCKLOCATION.lp_stock
    )
    Dave

  3. #3
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9
    Great, thanks! I'll try tomorrow.

    Yes, art_id is indexed in all tables.

    Another thing: In the stored procedure, all of the 24 sub queries use Where instead of joins. Could that have a noticeable impact?

  4. #4
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    do you mean they are using implicit join syntax rather than explicit?

    Meaning:
    Code:
    from table1
        , table2
    where table1.col = table2.col
    Instead of:
    Code:
    from table1
    inner join table2
       on table1.col = table2.col
    ? If that is what you mean, then no difference, they are syntactically the same.

  5. #5
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9
    Yes, that's what I meant.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,445
    Provided Answers: 12
    Syntactically the same; but only one of those would ever make it in to production code at this shop!
    If you're performing a JOIN, use JOIN syntax

    Going back to the original question:
    Is there anything fundamentally wrong?
    24 subqueries.
    1300 lines of code.

    These two statements smell like a problem to me.
    Both lead me to believe that things are over-complicated and you should simplify.
    Overly verbose/complicated code hints at either poor table design and/or poor query design.
    Also, you should not use the prefix of "sp_" for your procedures! First link from a search: https://sqlperformance.com/2012/10/t...ries/sp_prefix

    - I must state at this point... this sounds really harsh and for that I am sorry! That really is not the intention! -

    There's a few oddities in your code above... you can't CROSS APPLY to a stored procedure. I suspect you've edited your code and it was supposed to be a FUNCTION... Link: http://stackoverflow.com/questions/1...er-apply-block

    Okay, enough, I'll try help you solve your performance problems!

    #1 suspect: your function!

    There are 3 types of UDF:
    • Scalar
    • Inline Table-Valued
    • Multi-statement Table-Valued

    The 1st and 3rd are terrible for performance. I suspect your function is the latter of these.

    If you share the code of this; we might be able to assist in more detail.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •