Query design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query design

Author

Topic

johnny rotten
Starting Member

10 Posts

Posted - 2005-03-30 : 15:31:09

I have recently inherited an application that uses SQL Server. Initially, there were some performance issues with the main Orders table, which was resolved by adding a CustomerID field and Clustered Index on that field (it was previously in another table and accessed via a JOIN).

Now, there is another table that is related to the Orders table named OrderMessages, which contains any correspondence between the various parites to the Order. The existing stored procedures are fine when retrieving all the OrderMessages for a particular Order, but when attempting to retrieve all unread messages for a particular customer, the entire table is scanned (actually it is a Clustered Index Scan on the Primary Key of the OrderMessage table).

Here is an abbreviated version of the query:

SELECT OrderMessages.MessageID, OrderMessages.DateTimeStamp, OrderMessages.UserID, OrderMessages.Message,
Order.OrderID,
FROM OrderMessages WITH (NOLOCK)
INNER JOIN Order WITH (NOLOCK) ON Order.OrderID = OrderMessages.OrderID
WHERE OrderMessages.ReadFlag = 0 AND Orders.CustomerID = @CustomerID
ORDER BY OrderMessages.DateTimeStamp ASC

After that overview, my question is: is there a way I can optimize the query to use the CustomerID from the Orders table to filter the OrderMessages table, or do I need to add CustomerID to the OrderMessages table?

Thanks,

John

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-30 : 16:30:39

What are the current indexes on the OrderMessages table ?
Are there any current indexes with OrderID as the 1st column of the idnex ?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-30 : 17:02:15

STOP!
Don't denormalize Your database any further, that is NOT the way to improve performance.
By denormalizing I mean : "double storing" fields in several tables.

Do You have an index on OrderId in the OrderMessages table ?

Please post the actual table structures, and what Indexes+Primary Keys are defined on them.
This will make it easier for us to help You.

rockmoose

johnny rotten
Starting Member

10 Posts

Posted - 2005-03-30 : 17:04:04

The OrderMessages table has 2 indexes: MessageId is the Primary Key (and it is Clustered), OrderID is the other.

Thanks,

John

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-30 : 17:04:53

>>Initially, there were some performance issues with the main Orders table, which was resolved by adding a CustomerID field and Clustered Index on that field (it was previously in another table and accessed via a JOIN).

Not a good way to improve performance. You are now storing redundant data in your system and your database is no longer properly normalized. Proper indexing and efficiently written SQL should be able handle any performance tweaking you need to do, assume your database is well-designed.

Always restructure your database and add index hints as a LAST resort. Check your indexes and the efficiency of your SQL statements initially before doing anything drastic.

- Jeff

johnny rotten
Starting Member

10 Posts

Posted - 2005-03-30 : 17:35:34

I appreciate all the quick responses, and I do agree that denormalizing the database is not the way to go, thus I posed the question here to learn the correct way (as I foresee this is just the beginning of numerous changes).

Here are the table structures (note: the order table field list has been truncated):
CREATE TABLE [dbo].[OrderMessages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NOT NULL ,
[DateTimeStamp] [datetime] NOT NULL ,
[UserID] [int] NULL ,
[StatusDescription] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReadFlag] [bit] NOT NULL ,
[ApprovedByCompanyUserID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [int] NOT NULL ,
[ClientID2] [int] NULL ,
[BillToID] [int] NULL ,
[SendToID] [int] NULL ,
[EnteredByID] [int] NULL ,
[BorrowerName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatusID] [smallint] NOT NULL ,
[CompanyUserID] [int] NULL ,
[ContactName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeleteFlag] [bit] NULL ,
[DateCompleted] [smalldatetime] NULL ,
[DateInvoice] [smalldatetime] NULL ,
[CompanyID] [int] NOT NULL ,
[QueueID] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderMessages] WITH NOCHECK ADD
CONSTRAINT [PK_OrderMessages] PRIMARY KEY CLUSTERED
(
[MessageID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_OrdersCustomerID] ON [dbo].[Orders]([CustomerID]) WITH STATISTICS_NORECOMPUTE ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderMessages] WITH NOCHECK ADD
CONSTRAINT [DF_OrderMessages_DateTimeStamp] DEFAULT (getdate()) FOR [DateTimeStamp],
CONSTRAINT [DF_OrderMessages_ReadFlag] DEFAULT (0) FOR [ReadFlag]
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_Orders_1] ON [dbo].[Orders]([OrderID], [ClientID], [BorrowerName], [Address1], [OrderStatusID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[OrderMessages] ADD
CONSTRAINT [FK_OrderMessages_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[Orders] (
[OrderID]
)
GO

Thanks,

John

johnny rotten
Starting Member

10 Posts

Posted - 2005-03-30 : 17:39:23

Sorry, that was an older copy of the OrderMessages table. Here is the current one:
CREATE TABLE [dbo].[OrderMessages] (
[MessageID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NOT NULL ,
[DateTimeStamp] [datetime] NOT NULL ,
[UserID] [int] NULL ,
[StatusDescription] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReadFlag] [bit] NOT NULL ,
[ApprovedByCompanyUserID] [int] NULL
) ON [PRIMARY]
GO

CREATE INDEX [IX_OrderMessages] ON [dbo].[OrderMessages]([OrderID]) ON [PRIMARY]
GO

Thanks,

John

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-30 : 17:42:22

Depending on the number of Messages per order, your index "IX_OrderMessages" on OrderID may never be used by the optimizer because it's not selective enough. What is the average number of messages per order ?

johnny rotten
Starting Member

10 Posts

Posted - 2005-03-30 : 17:47:22

The number of messages per order could be 0-n, although a quick sampling indicates 0-10 is normal.

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-30 : 17:51:33

You could try adding 1 or 2 columns to that index to see if that will make the optimizer choose it instead of the clustered index scan.

For example:

CREATE INDEX [IX_OrderMessages]
ON [dbo].[OrderMessages]([OrderID], [ReadFlag], [DateTimeStamp] ) ON [PRIMARY]
GO

If you apply that, do you see a non-clustered index seek instead of a clustered index scan in the query plan ?

[Edit] An alternative is to switch the index that is clustered. You could try clustering the index on OrderID and making the PK non-clustered, since it is a highly selective Identity column.

johnny rotten
Starting Member

10 Posts

Posted - 2005-03-31 : 06:57:22

PW: looks like putting the Clustered index on OrderID rather than the PK is the solution. I appreciate the advice.

Thanks,

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources