May 7, 2009 at 3:13 pm
I am using SQL Server 2005. This is for an ASP.Net application. I am wondering how to boost query performance.
In the below table creation sql query, "CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED" is there. By default, the method is generated when creating a new table. That is OK. But is it a proper indexing method for boosting performance? or Do I need to create a proper index like "CREATE INDEX ----"?. I have many tables in the database. I will create indexes for all the tables if you say I need to. Thanks.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Product].[Articles]') AND type in (N'U'))
BEGIN
CREATE TABLE [Product].[Articles](
[ArticleID] [int] IDENTITY(1,1) NOT NULL,
[Article] [nvarchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ModifiedDate] [smalldatetime] NULL,
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[ArticleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
SET IDENTITY_INSERT [Product].[Articles] ON
INSERT [Product].[Articles] ([ArticleID], [Article], [ModifiedDate]) VALUES (1, N'Category article', CAST(0x9BD802D5 AS SmallDateTime))
INSERT [Product].[Articles] ([ArticleID], [Article], [ModifiedDate]) VALUES (2, N'Product item article', CAST(0x9BD802D5 AS SmallDateTime))
INSERT [Product].[Articles] ([ArticleID], [Article], [ModifiedDate]) VALUES (3, N'Collection artcle', CAST(0x9BD802D5 AS SmallDateTime))
INSERT [Product].[Articles] ([ArticleID], [Article], [ModifiedDate]) VALUES (4, N'Category article 2', CAST(0x9BD903D5 AS SmallDateTime))
INSERT [Product].[Articles] ([ArticleID], [Article], [ModifiedDate]) VALUES (5, N'Product item article 2', CAST(0x9BD903D4 AS SmallDateTime))
INSERT [Product].[Articles] ([ArticleID], [Article], [ModifiedDate]) VALUES (6, N'Collection article 2', CAST(0x9BD903D5 AS SmallDateTime))
SET IDENTITY_INSERT [Product].[Articles] OFF
May 7, 2009 at 3:17 pm
The best indexing will depend on what selects you are running, as well as what update/insert/delete activity the tables have.
For a table like this one, you really don't have a lot of choices on the index
If you'll be selecting based on the modified date column, then you'll want an index on there. It would probably include the article column. Again, that only applies if you have enough rows in the table to make it worth indexing, and enough selects with the modified date in the Where clause. If not, then the clustered index alone should be fine.
Other tables will have different indexing needs. It depends on how you use them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2009 at 3:21 pm
Hi
It depends on your select statements. If you are selecting by your "Article" column you should reconsider if it really needs NVARCHAR(1000) because this exceeds the maximal key length.
Greets
Flo
May 7, 2009 at 3:40 pm
Thanks for the replies.
I am concerned about "Select" queries. The ASP.Net website uses the database tables for retrieving the data majorly. Any common user should not get affected like slow retrieval. In company level, one user only updates / delete / insert the table data that too very rarely. That table & its data is a sample ones. But my real database is relative big. Please suggest me. Thanks.
May 8, 2009 at 1:55 am
As we wrote, it depends on the specific SELECT statements. If the database should be more optimized for queries instead of data manipulations you should use a higher FILLFACTOR.
Sorry for the indiscreet question:
Is this your first database project? It may depend on the fact that English is not your first language but I'm not sure about your SQL Server experiences.
Greets
Flo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply