Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is it a proper indexing method for boosting performance?


Is it a proper indexing method for boosting performance?

Author
Message
nzkks1
nzkks1
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 27
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
Florian Reischl
Florian Reischl
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 3934
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


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
nzkks1
nzkks1
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 27
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.
Florian Reischl
Florian Reischl
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1939 Visits: 3934
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


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search