1

I have a stored procedure of a bit over 300,000 lines, and this is my first time working on something of this size. here is an example of the code I'm writing :

IF (@type = 'commande client')
AND @etat = 'ouvert'
BEGIN
    IF (@tiersd = '')
   AND @tiersf = ''
    BEGIN
        IF (@familled = '')
       AND @famillef = ''
        BEGIN
            IF (@commerciald = '')
           AND @commercialf = ''
            BEGIN
                IF (@articled = '')
               AND @articlef = ''
                BEGIN
                    IF (@afamilled = '')
                   AND @afamillef = ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;
                    ELSE IF (@afamilled != '')
                        AND @afamillef = ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itmsgrpcod BETWEEN @afamilled AND ((SELECT MAX(itmsgrpcod)FROM oitm))
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;

                    ELSE IF (@afamilled = '')
                        AND @afamillef != ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itmsgrpcod BETWEEN ((SELECT MIN(itmsgrpcod)FROM oitm)) AND @afamillef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;

                    IF (@afamilled != '')
                   AND @afamillef != ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itmsgrpcod BETWEEN @afamilled AND @afamillef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;
                END;

                ----------------------------

                ELSE IF (@articled != '')
                    AND @articlef != ''
                BEGIN
                    IF (@afamilled = '')
                   AND @afamillef = ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itemcode BETWEEN @articled AND @articlef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;
                    IF (@afamilled != '')
                   AND @afamillef = ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itemcode BETWEEN @articled AND @articlef
                          AND OITM.itmsgrpcod BETWEEN @afamilled AND ((SELECT MAX(itmsgrpcod)FROM oitm))
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;

                    IF (@afamilled = '')
                   AND @afamillef != ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itemcode BETWEEN @articled AND @articlef
                          AND OITM.itmsgrpcod BETWEEN ((SELECT MIN(itmsgrpcod)FROM oitm)) AND @afamillef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;

                    IF (@afamilled != '')
                   AND @afamillef != ''
                    BEGIN
                        SELECT ordr.docentry,
                               OITM.itemCode,
                               rdr1.dscription,
                               rdr1.quantity,
                               rdr1.price,
                               rdr1.currency,
                               rdr1.slpCode,
                               rdr1.basedocnum,
                               rdr1.shiptocode,
                               rdr1.shiptodesc,
                               rdr1.baseprice,
                               ordr.docnum,
                               ordr.doctype,
                               ordr.docstatus,
                               ordr.docTotal,
                               ordr.docdate,
                               ordr.cardcode,
                               ordr.cardname,
                               ordr.address,
                               ordr.doccur,
                               ordr.paidtodate,
                               ordr.doctime,
                               ordr.docsubtype,
                               ordr.basetype,
                               ordr.baseEntry,
                               OITM.itemclass,
                               OITM.itemtype,
                               OITM.itemname,
                               OITM.itmsgrpcod
                        FROM ordr
                             INNER JOIN rdr1 ON ordr.docentry = rdr1.docentry
                             INNER JOIN oitm ON OITM.itemcode = rdr1.ItemCode
                        WHERE OITM.itemcode BETWEEN @articled AND @articlef
                          AND OITM.itmsgrpcod BETWEEN @afamilled AND @afamillef
                          AND ordr.DocDate BETWEEN @dated AND @datef
                          AND DocStatus = 'o';
                    END;
                END;

the problem is that the compiler seems to check inside every if statement even if the conditions are not met and that takes about half an hour so I hope to find a solution to this problem

Thom A
  • 88,727
  • 11
  • 45
  • 75
Hamza El Alamy
  • 21
  • 5
  • 1
    The compiler is doing what the name suggests - SQL is not interpereted at runtime - the values of your variables and the actual execution path is not known until runtime; the execution plan, and its validity, is determined and set in stone before execution. – Stu Jun 21 '21 at 11:29
  • The above batch isn't even valid, you have *far* more `BEGIN`s that you do `END`s. Also whitespace and line breaks are a **must** for readable code. It's literally impossible to tell what belongs inside what block in the above. – Thom A Jun 21 '21 at 11:31
  • 3
    A stored procedure of 300K lines is nothing more than a liability. Start splitting it up -- procedures can call procedures. They can also use views, functions and dynamic SQL (for more on dynamic search conditions, with and without dynamic SQL, see [this extensive writeup](https://sommarskog.se/dyn-search.html). Identify common code and make it common. This will take some effort, but one thing you can be sure of is that if time is not taken for this it's just going to get worse. – Jeroen Mostert Jun 21 '21 at 11:33
  • 3
    I suggest you look at writing a proper [Kitchen Sink Query](https://www.sentryone.com/blog/aaronbertrand/backtobasics-updated-kitchen-sink-example). 300,000 lines is always going to take a long time to compile – Charlieface Jun 21 '21 at 11:34
  • As for the question, what @Stu says is true. Despite people's beliefs T-SQL is a **compiled** language, it just compiles when you attempt to run the batch, and the *entire* batch is compiled not each statement as (or if) it's actually executed. – Thom A Jun 21 '21 at 11:34
  • Also note that there are unreachable parts in your query which you didn't need to write anyway. – Cetin Basoz Jun 21 '21 at 13:03

1 Answers1

1

Yes, as mentioned in the comments, the SQL Server's T-SQL compiler is a real compiler that tries to compile every line just as client language compilers do (some client language compilers have compiler-directives to get around this problem, T-SQL has different techniques and features to achieve the same purpose as detailed below).

If you only want some lines to be compiled then you will need to use a different approach. Since your stored procedure has 300,000 lines, this seems like a very good idea anyway. Here are some of the techniques that you might consider:

  • Break it up into a master stored procedure with all of the decisions (or the higher level ones) and many subordinate stored procedures, that have the actual SQL queries (and/or the lower level decisions)
  • Dynamic SQL for the lower levels. Dynamic SQL is not compiled until it is explicitly called at run-time (the SSMS scripter uses techniques like this, so you can look to it for examples, another example are catch-all queries)
  • Use table-driven code-generation to automatically create the proper SQL code as a pre-compiler stage, implementing one or both or the preceding techniques
  • Use table-driven code-generation to dynamically create only the SQL code that you need at run-time then dynamically execute it
  • Any or all combinations of the preceding techniques

Be forewarned, anything except the first bullet involves dynamic SQL and requires sufficient SQL expertise to understand the security consequences (SQL injection, etc.) and how to deal with them. Also, the further down this list you go, the more advanced technical skills and SQL knowledge you will need to implement these.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137