1

Background

I'm designing a table which will serve as a repository of payment information to be used for research purposes. This table will have around 4.5 million records initially and will grow at a rate of around 400k annually. The PaymentReference column would be the only unique column in the table on an on going basis meaning after the initial historical load of data which would contain nulls.

Table example:

    CREATE TABLE [dbo].[Bil_ReturnsRepository](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [PolicyNumber] [nvarchar](32) NOT NULL,
    [PaymentReference] [nvarchar](32) NULL,
    [AccountingDate] [datetime] NOT NULL,
    [ReturnDate] [datetime] NOT NULL,
    [PaymentAmount] [decimal](19, 2) NOT NULL,
    [RegionCode] [nvarchar](5) NULL,
    [PolicyStateCode] [nvarchar] (3) NULL,
    [CompanyCode] [nvarchar](4) NULL,
    [LineOfBusiness] [nvarchar](4) NULL,
    [ReturnReasonCode] [nvarchar](3) NULL,
    [PaymentType] [nvarchar](20) NULL,
    [PaymentPlan] [nvarchar](20) NULL,
    [EntryUserId] [nvarchar](20) NULL,
    [PolicyTermId] [bigint] NULL,
    [PaymentDate] [datetime] NULL,
    [DateOfFirstReturn] [datetime] NULL,
    [BankId] [nvarchar](9) NULL,
    [EquityDateOfPolicy] [datetime] NULL,
    [OrginalStartDateOfPolicy] [datetime] NULL,
    [PaymentSource] [nvarchar](20) NULL,
    [DBTransactionTimeStamp] DATETIME NOT NULL DEFAULT(GETDATE()))

Access to Data

Business users will enter search criteria on a front end UI screen. The available options to use as search criteria will be every column located on the table except ID and DBTransactionTimeStamp. Every column of the table will have an associated text box that the user will have the ability to enter the necessary value for that column to use for the search. The only field that is not optional for the search is the ReturnDate field. For example, a search could be performed with just ReturnDate and PolicyNumber. ReturnDate, PaymentReference. ReturnDate, PolicyNumber, PaymentReference, AccountingDate. ReturnDate only. etc....

A stored procedure will be created to actually perform the read of the table and will use the parameters passed by the front end UI when querying the table.

Question

Since this is a new design I'm not currently experiencing any performance issues but I would like to be proactive and prevent any of those possible issues from occurring. I would imagine that with so many records on the table and the ability to search on all fields there would be table space scans that would occur. That would result in a slow response time for the output to be returned to the front end UI. So with that being said would it be beneficial to add any indexes and or keys to this table ? If so what Keys ? What indexes ?

closed as too broad by hot2use, Erik Darling, mustaccio, RDFozz, Mr.Brownstone Nov 3 '17 at 17:52

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Why do you use unicode everywhere? Your data will be doubled in size for no reason – sepupic Nov 3 '17 at 11:14
  • I've never really considered that as it seems to be a standard practice here in my shop. – Jason Nov 3 '17 at 11:21
  • >>>So with that being said would it be beneficial to add any indexes and or keys to this table<<< Indexes are created for specific queries. If you don't post any query in your question what index advices do you expect? – sepupic Nov 3 '17 at 11:29
  • Actually, your table has no PK. Do you have any natural PK? – sepupic Nov 3 '17 at 11:31
  • You are in the business and should have some understanding of how the end-users will query the table. We are "out-of-the-loop" and have no idea, how the end-users will query your data. The recommendations will therefore vary between "index all columns" and "add some 1-n column indexes for each possible combination". It's currently a very open-ended question. Please provide additional details. – hot2use Nov 3 '17 at 11:34
1

Since the search always includes the ReturnDate column, it seems reasonable to create the clustered index on that column.

Based on your stated assumption that you'll add around 400,000 rows per year, and you have 4,000,000 rows in the table now, it seems like you might reasonably have 10 years of data, or 3650 unique ReturnDate values at the current time. (4,000,000 / 400,000 = 10)

With the clustered index on ReturnDate, selectivity on the clustered index would be on average around 1,300 rows per ReturnDate value.

If SQL Server does a seek into the clustered index to the start of the range of values fitting the desired ReturnDate, it will only need to scan approximately 1,300 rows to find the other values to fit the search criteria.

I've setup a simple test-bed to test this hypothesis:

IF OBJECT_ID(N'dbo.Bil_full', N'U') IS NOT NULL DROP TABLE dbo.Bil_full;

CREATE TABLE dbo.Bil_full
(
      PolicyNumber nvarchar(32) NOT NULL
    , PaymentReference nvarchar(32) NULL
    , AccountingDate datetime NOT NULL
    , ReturnDate datetime NOT NULL
    , PaymentAmount decimal(19, 2) NOT NULL
    , RegionCode nvarchar(5) NULL
    , PolicyStateCode nvarchar (3) NULL
    , CompanyCode nvarchar(4) NULL
    , LineOfBusiness nvarchar(4) NULL
    , ReturnReasonCode nvarchar(3) NULL
    , PaymentType nvarchar(20) NULL
    , PaymentPlan nvarchar(20) NULL
    , EntryUserId nvarchar(20) NULL
    , PolicyTermId bigint NULL
    , PaymentDate datetime NULL
    , DateOfFirstReturn datetime NULL
    , BankId nvarchar(9) NULL
    , EquityDateOfPolicy datetime NULL
    , OrginalStartDateOfPolicy datetime NULL
    , PaymentSource nvarchar(20) NULL
    , DBTransactionTimeStamp DATETIME NOT NULL 
        CONSTRAINT DF_bil_ts
        DEFAULT(GETDATE())
) ON [PRIMARY];

CREATE CLUSTERED INDEX CX_Bil
ON dbo.Bil_full(ReturnDate);

Here, I'm adding 4,000,000 rows to the table, with a variety of "made-up" data that perhaps approximates the data you might actually have.

;WITH src AS (
    SELECT num = 1.0 + v1.num 
        + (v2.num * 10) 
        + (v3.num * 100)
        + (v4.num * 1000)
        + (v5.num * 10000) 
        + (v6.num * 100000) 
        + (v7.num * 1000000) 
    FROM  (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v1(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v2(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v3(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v4(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v5(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v6(num)
        , (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v7(num)
)
INSERT TOP(4000000) INTO dbo.Bil_full (PolicyNumber, PaymentReference, AccountingDate, ReturnDate, PaymentAmount, RegionCode, PolicyStateCode, CompanyCode, LineOfBusiness, ReturnReasonCode, PaymentType, PaymentPlan, EntryUserId, PolicyTermId, PaymentDate, DateOfFirstReturn, BankId, EquityDateOfPolicy, OrginalStartDateOfPolicy, PaymentSource, DBTransactionTimeStamp)
SELECT PolicyNumber = N'PN' + RIGHT(N'000000000' + CONVERT(nvarchar(10), src.num), 10)
    , PaymentReference = N'PR' + RIGHT(N'000000000' + CONVERT(nvarchar(10), src.num - 10000), 10)
    , AccountingDate = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00')
    , ReturnDate = DATEADD(DAY, src.num % 3715, N'2012-03-01T00:00:00')
    , PaymentAmount = src.num * CONVERT(int, CRYPT_GEN_RANDOM(1)) * 0.47
    , RegionCode = CASE WHEN src.num % 3 = 1 THEN REPLICATE(char(65 + src.num % 26), 5) ELSE NULL END
    , PolicyStateCode = CASE WHEN src.num % 3 = 2 THEN REPLICATE(CHAR(65 + src.num % 26), 3) ELSE NULL END
    , CompanyCode = CASE WHEN src.num % 5 = 3 THEN REPLICATE(CHAR(65 + src.num % 26), 4) ELSE NULL END
    , LineOfBusiness = CASE WHEN src.num % 6 = 3 THEN REPLICATE(CHAR(66 + src.num % 26), 4) ELSE NULL END
    , ReturnReasonCode = CASE WHEN src.num % 3 = 2 THEN REPLICATE(CHAR(69 + src.num % 26), 3) ELSE NULL END
    , PaymentType = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , PaymentPlan = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , EntryUserId = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , PolicyTermId = CONVERT(bigint, CRYPT_GEN_RANDOM(1))
    , PaymentDate = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 27
    , DateOfFirstReturn = DATEADD(DAY, src.num % 3715, N'2012-03-01T00:00:00') - 16
    , BankId = CONVERT(varchar(8), CRYPT_GEN_RANDOM(6), 1)
    , EquityDateOfPolicy = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 365
    , OrginalStartDateOfPolicy = DATEADD(DAY, src.num % 3715, N'2012-01-01T00:00:00') + 15
    , PaymentSource = CONVERT(varchar(10), CRYPT_GEN_RANDOM(8), 1)
    , DBTransactionTimeStamp = DATEADD(SECOND, src.num, N'2013-01-01T00:00:00')
FROM src
ORDER BY src.num

The table has 1346 rows per ReturnDate from 2012-03-05 to 2022-05-02.

Assuming every query against this table does in fact include a ReportDate value, you could expect execution to consist of a single clustered index seek, which is going to be quite efficient.

For instance, the query:

SELECT *
FROM dbo.Bil_full
WHERE Bil_full.ReturnDate = N'2016-04-01 00:00:00.000'
    AND Bil_full.PolicyNumber LIKE N'%89%'
    AND Bil_full.PaymentType LIKE N'XX%'

Has this query plan:

enter image description here

Statistics I/O shows the following:

Table 'Bil_full'. Scan count 1, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You may notice I've removed the ID column from the table. If you don't need the column, why include it? If the date columns do not include times, you can save some space per row by changing the data type to DATE instead of DATETIME. The nvarchar(..) columns could be changed to just varchar(..) columns if you are not actually storing unicode data; if that is applicable those columns will become half the size they currently are.

I've not created a primary key on the table since your question makes no mention of requiring one for referential integrity or other requirements such as uniqueness. The table does have a clustered index, which optimizes the physical layout of the table on disk versus the table being a simple heap. The clustered index (i.e. the table itself) will experience a fair amount of page splits if the ReportDate value for rows being inserted are not monotonically increasing.

In the comments, you asked about performing a range-scan for the ReportDate where your client is asking for rows between two dates - as in:

SELECT *
FROM dbo.Bil_full
WHERE Bil_full.ReturnDate >= N'2016-04-01 00:00:00.000'
    AND Bil_full.ReturnDate < N'2017-04-02 00:00:00.000'
    AND Bil_full.PolicyNumber LIKE N'%89%'

Queries like this will still perform relatively well; the above query returned 19432 rows very quickly. Of course, the size of the date range will affect query performance. Keeping the ranges as small as possible, i.e. allowing a maximum range of 30 days, will ensure performance remains manageable.

  • Thank you very much for the extremely detailed explanation. – Jason Nov 3 '17 at 18:42
  • I need to add a slight change to my original post. The Search criteria will actually be 2 dates which are FromDate and ToDate. So like this: SELECT * FROM Bil_full where CONVERT(DATE, ReturnDate) >= N'2016-04-01 00:00:00.000' and CONVERT(DATE, ReturnDate) <= N'2017-11-03 00:00:00.000' AND Bil_full.PolicyNumber LIKE N'%89%' AND Bil_full.PaymentType LIKE N'XX%' – Jason Nov 3 '17 at 18:46
  • HI @Jason - you can use the edit feature to add those details to your question. – Max Vernon Nov 3 '17 at 18:58
  • @Jason - I've added a small section to the end of my answer showing those details. – Max Vernon Nov 3 '17 at 19:05
  • 1
    Max thank you again. that's very helpful. I'm confident in how I need to proceed now. – Jason Nov 6 '17 at 13:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.