2

quite often while tuning queries and stored procedures I come across the situation where I have a number of different types of the same object, each type has different characteristics but all of them have similarities in the way they behave.

for example, please see the code of a view below:

SELECT  OrderNoItemSeqNoSID,
        OrderNoItemSeqNo,
        GrossDemand,
        BCGrossDemand,
        UKFSPExVAT,
        BCFullSellingPrice
--into Stage.OrderNoItemSeqNoDataForBandings
FROM    ( SELECT    o.OrderNoItemSeqNoSID,
                    o.OrderNoItemSeqNo,
                    o.GrossDemand,
                    o.BCGrossDemand,
                    o.UKFSPExVAT,
                    o.BCFullSellingPrice
          FROM      Stage.[Order] AS o
          UNION ALL
          SELECT    o.OrderNoItemSeqNoSID,
                    o.OrderNoItemSeqNo,
                    o.GrossDemand,
                    o.BCGrossDemand,
                    o.UKFSPExVAT,
                    o.BCFullSellingPrice
          FROM      Stage.[BackOrder] AS o
          UNION ALL
          SELECT    o.OrderNoItemSeqNoSID,
                    o.OrderNoItemSeqNo,
                    o.GrossDemand,
                    o.BCGrossDemand,
                    o.UKFSPExVAT,
                    o.BCFullSellingPrice
          FROM      Stage.[CancelledOrder] AS o
          UNION ALL
          SELECT    o.OrderNoItemSeqNoSID,
                    o.OrderNoItemSeqNo,
                    o.GrossDemand,
                    o.BCGrossDemand,
                    o.UKFSPExVAT,
                    o.BCFullSellingPrice
          FROM      Stage.[DespatchedOrder] AS o
          UNION ALL
          SELECT    o.OrderNoItemSeqNoSID,
                    o.OrderNoItemSeqNo,
                    o.GrossDemand,
                    o.BCGrossDemand,
                    o.UKFSPExVAT,
                    o.BCFullSellingPrice
          FROM      Stage.[ReturnedOrder] AS o
        ) AS x
GROUP BY OrderNoItemSeqNoSID,
        OrderNoItemSeqNo,
        GrossDemand,
        BCGrossDemand,
        UKFSPExVAT,
        BCFullSellingPrice

this view is just made up of different types of orders, that get different names as per the part of the process they are in.

without getting into too much detail as every business has different rules and internal processes, the way I am about to test the tuning of this view (and wherever stored procedures it is used in joints ) is:

I have created a table (see below), and added a non-unique clustered index to that table. I have been replacing the view with this new table, except for the beginning of the process when I use the view to populate the table.

IF OBJECT_ID('[Stage].[OrderNoItemSeqNoDataForBandings]') IS NOT NULL 
DROP TABLE [Stage].[OrderNoItemSeqNoDataForBandings] 
GO

CREATE TABLE [Stage].[OrderNoItemSeqNoDataForBandings] ( 
[OrderNoItemSeqNoSID]  INT                           NOT NULL,
[OrderNoItemSeqNo]     VARCHAR(30)                   NOT NULL,
[GrossDemand]          NUMERIC(18,4)                 NOT NULL,
[BCGrossDemand]        NUMERIC(18,4)                 NOT NULL,
[UKFSPExVAT]           NUMERIC(18,4)                 NOT NULL,
[BCFullSellingPrice]   NUMERIC(18,4)                 NOT NULL)


create clustered index cidx_OrderNoItemSeqNoDataForBandings
on [Stage].[OrderNoItemSeqNoDataForBandings] (OrderNoItemSeqNoSID)
GO

So far whenever I did the replace from the view to this table, I got performance gains, because I am not doing UNION ALL and GROUP BY all the time anymore.

However, I got the overhead of putting the table together at the first place, which can be an expensive operation.

Questions:

1) at the moment, I am creating the table from the view without the clustered index, then adding the clustered index. Are there other things I could consider that might make a difference to reduce the overhead of inserting about 10 to 20 million rows into this table?

I thought about putting this table on a different filegroup, but it will have to be on the same drive anyway so not sure if that would help.

are there any trace flags to improve the insert?

the database is already in simple recovery mode, with enough free space inside it, so to avoid an autogrowth.

2) as I find this type of situation not so uncommon, without changing the database design are there any other alternative that I could test against my current solution, so that I could compare both?

not sure about the number of questions inside the same question but it would be awkward to separate them all

3) what if I created a single table with all the columns of all my 5-6 tables, they have a bunch of columns that are the same for all of them, but another set of columns that are different. this table would be way bigger and wider but I could partition it according to their different types. I could use this table from the start and avoid the inserting I am doing at the moment, but then on the selects I would have a much bigger table to deal with. would this be something worth considering testing?

quite a long question but the problem is also a big chunk, accept suggestions for edits or changes cheers

  • It's not entirely clear what the problem you're trying to solve is. Are you going to have both the original (presumed) tables (Order, BackOrder, CancelledOrder, etc.) and the new table? Are you building the new table to simplify a process that runs periodically (say, once a day), where the speed-up of processing against the table looks to be worth inserting 10,000,000 rows? Or are you looking to permanently replace the individual tables? – RDFozz Feb 28 '17 at 15:39
  • alternatives to get rid of union all, the example is in the question, bonus if you could elaborate something against number 3, which is a re-structure in the whole process. I might not need to keep the original tables (It depends on a solution that I could find). simplify?? no - improve the performance. – Marcello Miorelli Feb 28 '17 at 18:06
  • Depending on the width of the unique columns in each table, and how much space they would really consume (for instance, the extra columns in CancelledOrder might all be NULL if the extra columns in DespatchedOrder were filled in), you could put everything in one table, or have tables with just the unique columns (CancelledOrderInfo, DespatchedOrderInfo, etc.). – RDFozz Feb 28 '17 at 20:21
  • All that said, you have to be wary of tuning the DB structure to accommodate what you need to do in this task, and ultimately causing performance problems in the existing parts of the application. – RDFozz Feb 28 '17 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.