April 13, 2011 at 10:32 am
kwoznica (4/13/2011)
If I do add a column as you suggest can I put a contstraint on the table to update new records? I'm basically trying to avoid having to convince the application developers to add more code.
If the default column I mention in the other response isn't what you need, let me know and we'll figure something out.
Yes we have 9 million rows which take up 20 gig. The table has 280 columns. I would love to normalize the table but application developers are consultants who keep telling me it is a hardware problem. Furthermore there are political issues because a high level manager used to work for them. Any recommendations I have made get shut down due to either price or lack of faith in my word versus theirs.
I agree with the data types as well, plus there are many columns with default values that could easily be Null or omitted from the table completely.
Hmm.. Sorry to hear you're in that situation. I seriously doubt it is a hardware problem, they likely don't know how to design a database properly. As a consultant, I assure you we're not all like that. I'm guessing they don't elaborate further? The only way I could see it being a hardware problem is that they feel the need to use all the available space and processing power so a bad design is the way to go.
Below are the index create statements. You asked for the definitions so let me know if you were referring to this.
Thanks, that's exactly what I was looking for.
CREATE UNIQUE NONCLUSTERED INDEX [$1] ON [dbo].[Live$SalesLine_37](
[Sequence No_ (History)] ASC,
[Document Type] ASC,
[Document No_] ASC,
[Line No_] ASC,
[Version No_ (History)] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
CREATE UNIQUE NONCLUSTERED INDEX [$2] ON [dbo].[Live$SalesLine_37]
(
[Changed By (History)] ASC,
[Document Type] ASC,
[Document No_] ASC,
[Line No_] ASC,
[Version No_ (History)] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
CREATE UNIQUE NONCLUSTERED INDEX [$3] ON [dbo].[Live$SalesLine_37]
(
[Changed Date (History)] ASC,
[Document Type] ASC,
[Document No_] ASC,
[Line No_] ASC,
[Version No_ (History)] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
ALTER TABLE [dbo].[Live$SalesLine_37] ADD CONSTRAINT [Live$SalesLine_37$0] PRIMARY KEY CLUSTERED
(
[Document Type] ASC,
[Document No_] ASC,
[Line No_] ASC,
[Version No_ (History)] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DataFilegroup3]
GO
Since the Primary Key is included in all other indexes on the table, you don't need to repeat the columns for each of the indexes. What are the columns in the WHERE clauses in most of the queries? That's where you want your indexes. There and for any JOIN columns. There are many people here that know more about indexes than I do and they're very good about helping, so if you have specific questions, create a new topic, post the table structure and current indexes and how you query the tables most often and people will pitch in. I'll help too, but like I said, others here are great at this.
Is that Primary Key referenced in other tables? Or are there just references to individual columns? Changing the Primary Key shouldn't break the interface, but might be a lot of work for you.
From the little I know of your system, I'd suggest that if you still want to partition your Primary Key should be (and I can't believe they use a $ in their table name...)
[Code="sql"]ALTER TABLE [dbo].[Live$SalesLine_37] ADD CONSTRAINT PK_LiveSalesLine_document_no_RecordDate PRIMARY KEY CLUSTERED (document_no, RecordDate)[/code]
Of course, you may need other columns in there to keep it unique.
The indexes as they are don't look very effective to me, but that's from a superficial understanding of the system. It is possible they're looking at the correct columns (though as I said, they're currently redundant.) If you can respond with the most common filtering criteria in your queries I can help more.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 21, 2011 at 9:12 am
Stefan,
I am going to give this a go keeping in mind all the points you brought up. If you have some time can you look at the code I am going to run to see if there are any issues with it?
First I am going to create the additional column with a date data type which will be used to partition the table and also set as the primary key.
ALTER TABLE [dbo].[Live$SalesLine_37] ADD SalesLineDate Date default GetDate();
GO
Update [dbo].[Live$SalesLine_37] Set SalesLineDate = CAST(TimeStamp AS DATE)
Go
Next I am going to change the primary key and simplify the index definitions to reduce redundancy
USE [50LIVE]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Live$SalesLine_37]') AND name = N'$1')
DROP INDEX [$1] ON [dbo].[Live$SalesLine_37] WITH ( ONLINE = OFF )
GO
USE [50LIVE]
GO
CREATE UNIQUE NONCLUSTERED INDEX [$1] ON [dbo].[Live$SalesLine_37]
(
[Document Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
USE [50LIVE]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Live$SalesLine_37]') AND name = N'$2')
DROP INDEX [$2] ON [dbo].[Live$SalesLine_37] WITH ( ONLINE = OFF )
GO
USE [50LIVE]
GO
CREATE UNIQUE NONCLUSTERED INDEX [$2] ON [dbo].[Live$SalesLine_37]
(
[Line No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
USE [50LIVE]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Live$SalesLine_37]') AND name = N'$3')
DROP INDEX [$3] ON [dbo].[Live$SalesLine_37] WITH ( ONLINE = OFF )
GO
USE [50LIVE]
GO
CREATE UNIQUE NONCLUSTERED INDEX [$3] ON [dbo].[Live$SalesLine_37]
(
[Version No_ (History)] ASC,
[Changed Date (History)] ASC,
[Changed By (History)] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
USE [50LIVE]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Live$SalesLine_37]') AND name = N'Live$SalesLine_37$0')
ALTER TABLE [dbo].[Live$SalesLine_37] DROP CONSTRAINT [Live$SalesLine_37$0]
GO
USE [50LIVE]
GO
ALTER TABLE [dbo].[Live$SalesLine_37] ADD CONSTRAINT [Live$SalesLine_37$0] PRIMARY KEY CLUSTERED
(
[SalesLineDate] ASC,
[Document No_] ASC,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DataFilegroup3]
GO
Finally I need to create the partition scripts. I'm goint to start writing those and will post back when I'm done. Thanks for your help in all of this. Its been a great learning experience. Things are getting Exciting!
April 21, 2011 at 9:17 am
kwoznica (4/21/2011)
Stefan,I am going to give this a go keeping in mind all the points you brought up. If you have some time can you look at the code I am going to run to see if there are any issues with it?
First I am going to create the additional column with a date data type which will be used to partition the table and also set as the primary key.
ALTER TABLE [dbo].[Live$SalesLine_37] ADD SalesLineDate Date default GetDate();GO
Update [dbo].[Live$SalesLine_37] Set SalesLineDate = CAST(TimeStamp AS DATE)
Go
Next I am going to change the primary key and simplify the index definitions to reduce redundancy
USE [50LIVE]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Live$SalesLine_37]') AND name = N'$1')
DROP INDEX [$1] ON [dbo].[Live$SalesLine_37] WITH ( ONLINE = OFF )
GO
USE [50LIVE]
GO
CREATE UNIQUE NONCLUSTERED INDEX [$1] ON [dbo].[Live$SalesLine_37]
(
[Document Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
USE [50LIVE]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Live$SalesLine_37]') AND name = N'$2')
DROP INDEX [$2] ON [dbo].[Live$SalesLine_37] WITH ( ONLINE = OFF )
GO
USE [50LIVE]
GO
CREATE UNIQUE NONCLUSTERED INDEX [$2] ON [dbo].[Live$SalesLine_37]
(
[Line No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
USE [50LIVE]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Live$SalesLine_37]') AND name = N'$3')
DROP INDEX [$3] ON [dbo].[Live$SalesLine_37] WITH ( ONLINE = OFF )
GO
USE [50LIVE]
GO
CREATE UNIQUE NONCLUSTERED INDEX [$3] ON [dbo].[Live$SalesLine_37]
(
[Version No_ (History)] ASC,
[Changed Date (History)] ASC,
[Changed By (History)] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
GO
USE [50LIVE]
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Live$SalesLine_37]') AND name = N'Live$SalesLine_37$0')
ALTER TABLE [dbo].[Live$SalesLine_37] DROP CONSTRAINT [Live$SalesLine_37$0]
GO
USE [50LIVE]
GO
ALTER TABLE [dbo].[Live$SalesLine_37] ADD CONSTRAINT [Live$SalesLine_37$0] PRIMARY KEY CLUSTERED
(
[SalesLineDate] ASC,
[Document No_] ASC,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DataFilegroup3]
GO
Finally I need to create the partition scripts. I'm goint to start writing those and will post back when I'm done. Thanks for your help in all of this. Its been a great learning experience. Things are getting Exciting!
That loooks pretty good to me.
Are you going to be using an archive table? What range are you going to use for your partitions? Be aware that the larger the partition, say by year as opposed to by month, the longer it will take to split the data when you're creating a new partition to move the data. The move will be incredibly quick, but the split will take more time the larger the date range is.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply