SQL Server 2014 Table/Query Optimization

Question

I have a set of tables

dbo.Store_000
dbo.Store_001
....
dbo.Store_216 

containing the sales of different stores, where relevant fields are

Username, ItemID, Description, CreatedDate, CountryID

I need to extract the latest 20 sales from all Stores and I wrote the following query:

select top 20 UserName, ItemID, Description, CreatedDate, CountryID
FROM (  
    SELECT UserName, ItemID, Description, CreatedDate, CountryID FROM dbo.Store_000 
    UNION ALL
    SELECT UserName, ItemID, Description, CreatedDate, CountryID FROM dbo.Store_001 
    UNION ALL
    SELECT UserName, ItemID, Description, CreatedDate, CountryID FROM dbo.Store_002
    .....
    ... 
    UNION ALL
    SELECT UserName, ItemID, Description, CreatedDate, CountryID FROM dbo.Store_216                   
) ii
order by ii.createdDate desc

currently tables contains some 200M records (all together)

therefore to reduce processing time I created the following index for each table:

CREATE NONCLUSTERED INDEX I2_Store000 ON dbo.Store_000
  (UserName)
INCLUDE (itemId, Description, CreatedDate, CountryID)
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]

but it still takes too much (some minutes on our machine)

can you suggest how to improve the query and or the index to reduce processing time?

should I create the index based on CreatedDate since that is the sorting field?

if it can help, HERE can find the MSSMS execution plan.

thanks

Answer 1

Just for performance, I should only select top 20 rows from each table ordered by createdDate, then select a new top 20 from the new derived table which is about 17 table * 20 = 340 rows

I hope it will be easier to deal with smaller table, I really wonder the result.

Answer 2

The first (and quicker) option would be do carry out a TOP 20 on your subqueries before you then sort in the outer select, something like this;

select top 20 UserName, ItemID, Description, CreatedDate, CountryID
FROM (  
    SELECT  TOP 20 UserName, ItemID, Description, CreatedDate, CountryID FROM dbo.Store_000 ORDER BY CreatedDate DESC
    UNION ALL
    SELECT TOP 20 UserName, ItemID, Description, CreatedDate, CountryID FROM dbo.Store_001 ORDER BY CreatedDate DESC
    UNION ALL
    SELECT TOP 20 UserName, ItemID, Description, CreatedDate, CountryID FROM dbo.Store_002ORDER BY CreatedDate DESC
    .....
    ... 
    UNION ALL
    SELECT TOP 20 UserName, ItemID, Description, CreatedDate, CountryID FROM dbo.Store_216   ORDER BY CreatedDate DESC                
) ii
order by ii.createdDate desc

The index you're going to want on each table will be like this (index name just an example);

CREATE NONCLUSTERED INDEX [IX_Store_000_CreatedDate_Desc_Incl] ON [dbo].[Store_000] ([CreatedDate] DESC)
INCLUDE ([UserName],[ItemID],[Description],[CreatedDate],[CountryID]) 

Your other option is to create an indexed view if you're going to be calling this regularly. There are upsides and downsides to an indexed view so you will have to make that call yourself, further reading below;

https://www.simple-talk.com/sql/learn-sql-server/sql-server-indexed-views-the-basics/

https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-indexed-views/

SQL Server - Creating an Indexed View

Answer 3

There are other techniques you could use that could optimize the query. Now, UNION ALL is appropriate since the values from each table should be unique.

Alex Martelli from What is the difference between JOIN and UNION?

UNION puts lines from queries after each other, while JOIN makes a cartesian product and subsets it -- completely different operations. Trivial example of UNION:

mysql> SELECT 23 AS bah
    -> UNION
    -> SELECT 45 AS bah;
+-----+  
| bah |
+-----+   
|  23 |
|  45 | 
+-----+ 2 rows in set (0.00 sec) similary trivial example of JOIN:

mysql> SELECT * FROM 
    -> (SELECT 23 AS bah) AS foo 
    -> JOIN 
    -> (SELECT 45 AS bah) AS bar
    -> ON (33=33);
+-----+-----+ 
| bah | bah |
+-----+-----+ 
|  23 |  45 | 
+-----+-----+ 1 row in set (0.01 sec)

• USE A PREDICATE IN ALL 20+ tables.

UNION ALL may be effective, but it still has to sort the ENTIRE TABLE to identify the TOP 20. Instead, assuming the stores always have more than 20 sales in a given 30-day period, use DATEADD or DATEDIFF to enable boolean comparisons that limit the number of rows SQL Server has to return with before comparing.

• USE AN INDEX...ON THE RIGHT COLUMN

Obviously, your report should use INDEXES and logical ones at that. CLUSTERED INDEXES are less expensive to build and maintain.

You know names might be unique, but SQL Server is not comparing names...it is comparing DATES. Therefore, create an INDEX on createdDate.

• USE Search ARGuments (SARGs)

Even the best laid plans can be ruined if SQL Server thinks it must go through every record every time. When a FUNCTION or variable are used on both sides of the predicate like below:

createdDATE BETWEEN DATEADD(DD, createdDate, 30) AND CAST(GETDATE() AS DATETIME2)

SQL Server has no choice but to go through the ENTIRE table or INDEX list (TABLE/INDEX SCAN) before comparing values. Instead, reorder it like this:

createdDATE >= DATEADD(DD, -30, GETDATE() )

Compare the results with this query and how an INDEX on createdDate improves the time:

SELECT UserName, ItemID, Description, CreatedDate, CountryID
FROM (  
    SELECT TOP 20 UserName, ItemID, [Description], CreatedDate, CountryID FROM dbo.Store_000 
    WHERE CreatedDate >= DATEADD(DD, -30, GETDATE())
    UNION ALL
    SELECT TOP 20 UserName, ItemID, [Description], CreatedDate, CountryID FROM dbo.Store_001 
    WHERE CreatedDate >= DATEADD(DD, -30, GETDATE())
    UNION ALL
    SELECT TOP 20 UserName, ItemID, [Description], CreatedDate, CountryID FROM dbo.Store_002 
    WHERE CreatedDate >= DATEADD(DD, -30, GETDATE())
    .....
    ... 
    UNION ALL
    SELECT TOP 20 UserName, ItemID, [Description], CreatedDate, CountryID FROM dbo.Store_216 
    WHERE CreatedDate >= DATEADD(DD, -30, GETDATE())
) ii
-- ORDER BY CreatedDate DESC    /*Unless you must, drop the ORDER BY. */