May 16, 2014 at 1:31 pm
I have a table that is used in reporting where once a month approximately 25,000 new records are added via a stored procedure. This will be cumulative over the months. Among other fields the table has numeric columns for the processing month and year. The month and year values will repeat 25,000 times while the other field values will differ. The report retrieves a single month of records (or 25,000) at a time. A 'Select' query may be: Select OrderNo, OrderDate, Color, Width, Height, Quantity from Table Where RptYear = 2014 And RptMonth = 4.
Will one of you DBAs suggest a type of indexe(s) for this table, if any?
Thank you.
May 16, 2014 at 1:42 pm
PWW (5/16/2014)
I have a table that is used in reporting where once a month approximately 25,000 new records are added via a stored procedure. This will be cumulative over the months. Among other fields the table has numeric columns for the processing month and year. The month and year values will repeat 25,000 times while the other field values will differ. The report retrieves a single month of records (or 25,000) at a time. A 'Select' query may be: Select OrderNo, OrderDate, Color, Width, Height, Quantity from Table Where RptYear = 2014 And RptMonth = 4.Will one of you DBAs suggest a type of indexe(s) for this table, if any?
Thank you.
I suspect this request is the result of some queries that are too slow? Blindly suggesting indexes is a very poor way to handle this. Can you post the table definition along with any current indexes? Are there no joins in this query? Also can you post an actual query, unless the one already posted is accurate. 25,000 new rows a months is not a whole lot of data but after a year or two it starts adding up to some performance issues if not handled correctly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2014 at 1:51 pm
Thank you for your reply, Sean. This request is not due to a slow query. Both the reporting process and table are new. There are no current indexes and there are no joins; the sample query given earlier is accurate. I realize after many months performance may suffer. That is the reason I am asking for your help.
May 16, 2014 at 1:56 pm
PWW (5/16/2014)
Thank you for your reply, Sean. This request is not due to a slow query. Both the reporting process and table are new. There are no current indexes and there are no joins; the sample query given earlier is accurate. I realize after many months performance may suffer. That is the reason I am asking for your help.
Gotcha. Can you post the ddl for the table? There are no indexes? What about a primary key?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2014 at 2:04 pm
There are no indexes; there is no primary key. I expect to always select from the table using the month and year fields. FY is the year and Period is the month.
CREATE TABLE [dbo].[CashReceipt](
[Org] [char](3) NULL,
[FY] [int] NULL,
[Period] [int] NULL,
[Company] [varchar](10) NULL,
[Account] [varchar](100) NULL,
[SVLDES] [varchar](50) NULL,
[LHDRAM] [numeric](15, 2) NULL,
[LHCRAM] [numeric](15, 2) NULL,
[HHJDAT] [datetime] NULL,
[XREVNT] [numeric](15, 0) NULL,
[XRINV] [varchar](10) NULL,
[XRHBNK] [varchar](3) NULL,
[XRINVC] [bigint] NULL,
[XRCUST] [int] NULL,
[ExecutionTime] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CSAcctCashReceipt] ADD CONSTRAINT [DF_CSAcctCashReceipt_ExecutionTime] DEFAULT (getdate()) FOR [ExecutionTime]
May 16, 2014 at 2:25 pm
PWW (5/16/2014)
There are no indexes; there is no primary key. I expect to always select from the table using the month and year fields. FY is the year and Period is the month.
CREATE TABLE [dbo].[CashReceipt]([Org] [char](3) NULL,
[FY] [int] NULL,
[Period] [int] NULL,
[Company] [varchar](10) NULL,
[Account] [varchar](100) NULL,
[SVLDES] [varchar](50) NULL,
[LHDRAM] [numeric](15, 2) NULL,
[LHCRAM] [numeric](15, 2) NULL,
[HHJDAT] [datetime] NULL,
[XREVNT] [numeric](15, 0) NULL,
[XRINV] [varchar](10) NULL,
[XRHBNK] [varchar](3) NULL,
[XRINVC] [bigint] NULL,
[XRCUST] [int] NULL,
[ExecutionTime] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CSAcctCashReceipt] ADD CONSTRAINT [DF_CSAcctCashReceipt_ExecutionTime] DEFAULT (getdate()) FOR [ExecutionTime]
OK I see where you are going here. I don't know which columns you are using in your filtering predicate but I would create a clustered index on those two columns.
I would suggest that you would be much happier in the long run if you use meaningful column names. As I look at this table I have no idea what all these very cryptic names mean. Also, the way you have this table defined you could have NULL for every single column. Surely some of these columns should be required.
You have a default value for ExecutionTime but since the column allows NULL. This means if you don't supply that column in an insert it will be populated with getdate(). However, you could update the column or provide a NULL literal and it will be NULL.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 16, 2014 at 3:03 pm
Something like this?
CREATE CLUSTERED INDEX IX_CashReceipt_FY_Period on CashReceipt (FY, Period)
I would highly recommend that you click the Stairways link over on the left of this forum. In there you will find a whole series on Indexing. Read through that because you need to understand how this works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply