Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is it a proper indexing method for boosting performance? Expand / Collapse
Author
Message
Posted Thursday, May 07, 2009 3:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 18, 2009 8:21 PM
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
Post #712464
Posted Thursday, May 07, 2009 3:17 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #712469
Posted Thursday, May 07, 2009 3:21 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #712474
Posted Thursday, May 07, 2009 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 18, 2009 8:21 PM
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.
Post #712488
Posted Friday, May 08, 2009 1:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #712692
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse