asked on

How to optimize Bookmark Lookup

I am trying to optimize a query by using the Execution Plan and it shows me a cost of 89% at the
Bookmark Lookup....

How can I fix this in order to speed up this particular query?

I have included the query, the tables, and indexes, etc.

***************

CREATE PROCEDURE [dbo].[usp_getNoteByDataGroup2]
      @DataID int,
      @groupID int
AS

      SELECT TOP 75 LEFT(dbo.Note.TheNote, 251) AS theNote, dbo.[User].Alias, dbo.Note.DataID, dbo.Note.[Date] AS theDate, dbo.Note.NoteID, dbo.Note.Price,
dbo.Note.isShared, dbo.Note.UserID AS AuthorID, DATALENGTH(dbo.Note.AttachedChart) AS AttachedChart, dbo.Note.NoteRating,
dbo.GroupNotes.DiscussionCount, dbo.Note.HotCount, '1' AS type, dbo.Note.postTime, dbo.[Group].GroupName,dbo.[Group].GroupID
FROM dbo.Note INNER JOIN
dbo.[User] ON dbo.Note.UserID = dbo.[User].UserID INNER JOIN
dbo.GroupNotes ON dbo.Note.NoteID = dbo.GroupNotes.NoteID INNER JOIN
dbo.[Group] ON dbo.GroupNotes.GroupID = dbo.[Group].GroupID
where DataID = @DataID AND (groupNotes.GroupID = @groupID) order by date desc

GO

***** Table [Note] ***********

CREATE TABLE [dbo].[Note] (
      [NoteID] [int] IDENTITY (110, 1) NOT FOR REPLICATION NOT NULL ,
      [UserID] [int] NULL ,
      [TheNote] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Date] [datetime] NULL ,
      [DataID] [int] NULL ,
      [AttachedChart] [varchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
      [postTime] [datetime] NULL ,
      [Symbol] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Price] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [isShared] [bit] NULL ,
      [LastEditMK] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [isDeleted] [bit] NULL ,
      [isPublic] [bit] NULL ,
      [PublicDate] [datetime] NULL ,
      [NoteRating] [float] NULL ,
      [isGroupShared] [bit] NULL ,
      [DiscussionCount] [int] NULL ,
      [LastDiscussion] [datetime] NULL ,
      [HotCount] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Note] WITH NOCHECK ADD
      CONSTRAINT [PK_Note] PRIMARY KEY CLUSTERED
      (
            [NoteID]
      ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Note] ADD
      CONSTRAINT [DF__Note__rowguid__18EBB532] DEFAULT (newid()) FOR [rowguid],
      CONSTRAINT [DF_Note_postTime] DEFAULT (getdate()) FOR [postTime],
      CONSTRAINT [DF_Note_isShared] DEFAULT (0) FOR [isShared],
      CONSTRAINT [DF_Note_isDeleted] DEFAULT (0) FOR [isDeleted],
      CONSTRAINT [DF_Note_isPublic] DEFAULT (0) FOR [isPublic],
      CONSTRAINT [DF_Note_isGroupShared] DEFAULT (0) FOR [isGroupShared],
      CONSTRAINT [DF_Note_DiscussionCount] DEFAULT (0) FOR [DiscussionCount],
      CONSTRAINT [DF_Note_HotCount] DEFAULT (0) FOR [HotCount],
      CONSTRAINT [repl_identity_range_sub_97500F70_9FA8_4259_96C3_16D490A1A1F9] CHECK NOT FOR REPLICATION ([NoteID] > 53000000 and [NoteID] < 54000000)
GO

CREATE UNIQUE INDEX [index_1285579618] ON [dbo].[Note]([rowguid]) ON [PRIMARY]
GO

CREATE INDEX [IX_Note] ON [dbo].[Note]([UserID]) ON [PRIMARY]
GO

CREATE INDEX [IX_Note_1] ON [dbo].[Note]([DataID]) ON [PRIMARY]
GO

****Table [User] *******

CREATE TABLE [dbo].[User] (
      [UserID] [int] IDENTITY (110, 1) NOT FOR REPLICATION NOT NULL ,
      [UserNo] [int] NULL ,
      [Alias] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
      [CreateDate] [datetime] NULL ,
      [LastAccessed] [datetime] NULL ,
      [LastLoginIP] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [LastMailCheck] [datetime] NULL ,
      [IRCNicked] [bit] NULL ,
      [MarDate] [datetime] NULL ,
      [RoyaltyGroupID] [int] NULL ,
      [isPaid] [bit] NULL ,
      [TicketID] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [MK] [bigint] NULL ,
      [ClientBuild] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [isPublisher] [bit] NULL ,
      [DefaultPublishGroup] [int] NULL ,
      [EUASigned] [bit] NULL ,
      [EUASignedDate] [datetime] NULL ,
      [LastShareChange] [datetime] NULL ,
      [LastDiscussionChange] [datetime] NULL ,
      [NoteCount] [int] NULL ,
      [SharedCount] [int] NULL ,
      [hadFirstLogin] [bit] NULL ,
      [UnreadMailCount] [int] NULL ,
      [msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User] WITH NOCHECK ADD
      CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
      (
            [UserID]
      ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User] ADD
      CONSTRAINT [DF__User__rowguid__07C12930] DEFAULT (newid()) FOR [rowguid],
      CONSTRAINT [DF_User_CreateDate] DEFAULT (getdate()) FOR [CreateDate],
      CONSTRAINT [DF_User_LastAccessed] DEFAULT (getdate()) FOR [LastAccessed],
      CONSTRAINT [DF_User_IRCNicked] DEFAULT (0) FOR [IRCNicked],
      CONSTRAINT [DF_User_isPublisher] DEFAULT (0) FOR [isPublisher],
      CONSTRAINT [DF_User_DefaultPublishGroup] DEFAULT (2049) FOR [DefaultPublishGroup],
      CONSTRAINT [DF_User_EUASigned] DEFAULT (0) FOR [EUASigned],
      CONSTRAINT [DF_User_hadFirstLogin] DEFAULT (0) FOR [hadFirstLogin],
      CONSTRAINT [DF__User__msrepl_tra__7C255952] DEFAULT (newid()) FOR [msrepl_tran_version],
      CONSTRAINT [repl_identity_range_sub_D73054E9_BFA4_4739_8626_54B595101991] CHECK NOT FOR REPLICATION ([UserID] > 270000 and [UserID] < 275000)
GO

CREATE UNIQUE INDEX [index_1333579789] ON [dbo].[User]([rowguid]) ON [PRIMARY]
GO

CREATE INDEX [IX_User] ON [dbo].[User]([UserNo]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User] ADD
      CONSTRAINT [FK_User_Group] FOREIGN KEY
      (
            [DefaultPublishGroup]
      ) REFERENCES [dbo].[Group] (
            [GroupID]
      )
GO

****Table [Group] ********

CREATE TABLE [dbo].[Group] (
      [GroupID] [int] IDENTITY (100, 1) NOT FOR REPLICATION NOT NULL ,
      [GroupName] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [GroupDescription] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [GroupPassword] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [GroupOwner] [int] NULL ,
      [isPublic] [bit] NULL ,
      [ContactEmail] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [isDeleted] [bit] NULL ,
      [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
      [isEveryoneAuthor] [bit] NULL ,
      [isListed] [bit] NULL ,
      [isChannelCreated] [bit] NULL ,
      [isPublisher] [bit] NULL ,
      [ParentClub] [int] NULL ,
      [HasChat] [bit] NULL ,
      [CreateDate] [datetime] NULL ,
      [ContactInfo] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [LastReportDate] [datetime] NULL ,
      [TemplateID] [int] NULL ,
      [clubLogo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [clubLogoMini] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [msrepl_tran_version] [uniqueidentifier] NOT NULL ,
      [ChatRank] [int] NULL ,
      [NotesRank] [int] NULL ,
      [OverallRank] [int] NULL ,
      [Rating] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Group] WITH NOCHECK ADD
      CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED
      (
            [GroupID]
      ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Group] ADD
      CONSTRAINT [DF_Group_isPublic] DEFAULT (0) FOR [isPublic],
      CONSTRAINT [DF_Group_isDeleted] DEFAULT (0) FOR [isDeleted],
      CONSTRAINT [DF__Group__rowguid__0D7A0286] DEFAULT (newid()) FOR [rowguid],
      CONSTRAINT [DF_Group_AuthDefaultOn] DEFAULT (1) FOR [isEveryoneAuthor],
      CONSTRAINT [DF_Group_isListed] DEFAULT (0) FOR [isListed],
      CONSTRAINT [DF_Group_isChannelCreated] DEFAULT (0) FOR [isChannelCreated],
      CONSTRAINT [DF_Group_isPublisher] DEFAULT (0) FOR [isPublisher],
      CONSTRAINT [DF_Group_HasChat] DEFAULT (0) FOR [HasChat],
      CONSTRAINT [DF_Group_CreateDate] DEFAULT (getdate()) FOR [CreateDate],
      CONSTRAINT [DF_Group_LastReportDate] DEFAULT (getdate()) FOR [LastReportDate],
      CONSTRAINT [DF_Group_TemplateID] DEFAULT (1) FOR [TemplateID],
      CONSTRAINT [DF__Group__msrepl_tr__67B44C51] DEFAULT (newid()) FOR [msrepl_tran_version],
      CONSTRAINT [repl_identity_range_sub_434A23BD_59EF_4CCF_832F_D2B24900BD21] CHECK NOT FOR REPLICATION ([GroupID] > 7700 and [GroupID] < 7800)
GO

CREATE UNIQUE INDEX [index_1205579333] ON [dbo].[Group]([rowguid]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Group] ADD
      CONSTRAINT [FK_Group_GroupLayout] FOREIGN KEY
      (
            [TemplateID]
      ) REFERENCES [dbo].[GroupLayout] (
            [TemplateID]
      )
GO

*****Table [GroupNotes] ********

CREATE TABLE [dbo].[GroupNotes] (
      [GroupID] [int] NOT NULL ,
      [NoteID] [int] NOT NULL ,
      [DiscussionCount] [int] NULL ,
      [LastDiscussion] [datetime] NULL ,
      [isLocked] [bit] NULL ,
      [shareTime] [datetime] NULL ,
      [SharedByID] [int] NULL ,
      [MaxNoteDate] [datetime] NULL ,
      [msrepl_tran_version] [uniqueidentifier] NOT NULL ,
      [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupNotes] WITH NOCHECK ADD
      CONSTRAINT [PK_GroupNotes] PRIMARY KEY CLUSTERED
      (
            [GroupID],
            [NoteID]
      ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupNotes] ADD
      CONSTRAINT [DF_GroupNotes_DiscussionCount] DEFAULT (0) FOR [DiscussionCount],
      CONSTRAINT [DF_GroupNotes_isLocked] DEFAULT (0) FOR [isLocked],
      CONSTRAINT [DF_GroupNotes_shareTime] DEFAULT (getdate()) FOR [shareTime],
      CONSTRAINT [DF__GroupNote__msrep__3434A84B] DEFAULT (newid()) FOR [msrepl_tran_version],
      CONSTRAINT [DF__GroupNote__rowgu__4A39C35A] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_373576369] ON [dbo].[GroupNotes]([rowguid]) ON [PRIMARY]
GO

CREATE INDEX [IX_GroupNotes_1] ON [dbo].[GroupNotes]([NoteID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[GroupNotes] ADD
      CONSTRAINT [FK_GroupNotes_Note] FOREIGN KEY
      (
            [NoteID]
      ) REFERENCES [dbo].[Note] (
            [NoteID]
      ) ON DELETE CASCADE
GO

***************************

Dexstar

Can you post more information about the Execution plan. I tried to recreate it, and I didn't find any step that had a bookmark lookup... But I don't have data in my tables either, so that isn't much help.

What is the bookmark lookup that is taking so long?

TARJr

ASKER

How can I tell what the Bookmark Lookup is?

I can't cut and paste the popup window in SQL analyzer...

PaulBarbin

Lets start with the definition of Bookmark lookup. What happens is the optimizer decides to use a nonclustered index to find (and possibly filter) the data you want, then once its got the nonclustered index, it has to find the data row to get all the columns to bring back. It does this by using a the clustered index value (also called a bookmark) that brings it to the specific data row to get the columns you requested in your select. That specific process could only be optimized by faster drives or by having that table in memory. Its just a matter of IO at that point.

To answer your other question, if you look at the popup window, it will tell you the table that the bookmark is being performed on.

How long is the query taking?

Paul

Dexstar

Put this in front of your query in the QA, and then paste the results

SET SHOWPLAN_TEXT ON
GO

That should give us the information we need.

Dex*

TARJr

ASKER

Here it is:

|--Top(75)
|--Compute Scalar(DEFINE:([Expr1008]=substring([Note].[TheNote], 1, 251), [Expr1009]=datalength([Note].[AttachedChart])))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Note].[NoteID]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES:([Note].[UserID]) WITH PREFETCH)
| |--Nested Loops(Inner Join)
| | |--Clustered Index Seek(OBJECT:([Notes].[dbo].[Group].[PK_Group]), SEEK:([Group].[GroupID]=2049) ORDERED FORWARD)
| | |--Sort(ORDER BY:([Note].[Date] DESC))
| | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Notes].[dbo].[Note]))
| | |--Index Seek(OBJECT:([Notes].[dbo].[Note].[IX_Note_1]), SEEK:([Note].[DataID]=2662) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([Notes].[dbo].[User].[PK_User]), SEEK:([User].[UserID]=[Note].[UserID]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Notes].[dbo].[GroupNotes].[PK_GroupNotes]), SEEK:([GroupNotes].[GroupID]=2049 AND [GroupNotes].[NoteID]=[Note].[NoteID]) ORDERED FORWARD)

Dexstar

Try removing the "ORDER BY Date DESC", and see if that eliminates that costly bookmark lookup. If it does, then try adding an index on the Date field, like this:

      CREATE INDEX [IX_Note_2] ON [dbo].[Note]([Date]) ON [PRIMARY]
      GO

If that doesn't help, then post the execution plan again, but use this command instead to get more information:

      SET SHOWPLAN_ALL ON
      GO

Hope that helps,
Dex*

TARJr

ASKER

Didn't seem to work...
I did SHOWPLAN_ALL ON:

--select count(*) from SymbIDLookup --select SymbolID, ChannelCount from vChatByStockID SELECT TOP 75 LEFT(dbo.Note.TheNote, 251) AS theNote, dbo.[User].Alias, dbo.Note.StockID, dbo.Note.[Date] AS theDate, dbo.Note.NoteID, dbo.Note.Price,       14      1      0      NULL      NULL      1      NULL      75.0      NULL      NULL      NULL      0.345346      NULL      NULL      SELECT      0      NULL
|--Top(75)      14      2      1      Top      Top      NULL      NULL      75.0      0.0      7.4999998E-6      1723      0.345346      [Note].[StockID], [Note].[Date], [Note].[NoteID], [Note].[Price], [Note].[isShared], [Note].[UserID], [Note].[NoteRating], [Note].[HotCount], [Note].[postTime], [User].[Alias], [GroupNotes].[DiscussionCount], [Group].[GroupName], [Group].[GroupID], [Expr10      NULL      PLAN_ROW      0      1.0
|--Compute Scalar(DEFINE:([Expr1008]=substring([Note].[TheNote], 1, 251), [Expr1009]=datalength([Note].[AttachedChart])))      14      3      2      Compute Scalar      Compute Scalar      DEFINE:([Expr1008]=substring([Note].[TheNote], 1, 251), [Expr1009]=datalength([Note].[AttachedChart]))      [Expr1008]=substring([Note].[TheNote], 1, 251), [Expr1009]=datalength([Note].[AttachedChart])      75.0      0.0      9.603571E-6      1723      0.34533849      [Note].[StockID], [Note].[Date], [Note].[NoteID], [Note].[Price], [Note].[isShared], [Note].[UserID], [Note].[NoteRating], [Note].[HotCount], [Note].[postTime], [User].[Alias], [GroupNotes].[DiscussionCount], [Group].[GroupName], [Group].[GroupID], [Expr10      NULL      PLAN_ROW      0      1.0
|--Nested Loops(Inner Join, OUTER REFERENCES:([Note].[NoteID]) WITH PREFETCH)      14      4      3      Nested Loops      Inner Join      OUTER REFERENCES:([Note].[NoteID]) WITH PREFETCH      NULL      75.0      0.0      4.0142928E-4      7251      0.34533098      [Note].[TheNote], [Note].[StockID], [Note].[Date], [Note].[NoteID], [Note].[Price], [Note].[isShared], [Note].[UserID], [Note].[AttachedChart], [Note].[NoteRating], [Note].[HotCount], [Note].[postTime], [User].[Alias], [GroupNotes].[DiscussionCount], [Grou      NULL      PLAN_ROW      0      1.0
|--Nested Loops(Inner Join, OUTER REFERENCES:([Note].[UserID]) WITH PREFETCH)      14      6      4      Nested Loops      Inner Join      OUTER REFERENCES:([Note].[UserID]) WITH PREFETCH      NULL      75.0      0.0      4.0142928E-4      7196      0.33049485      [Note].[TheNote], [Note].[StockID], [Note].[Date], [Note].[NoteID], [Note].[Price], [Note].[isShared], [Note].[UserID], [Note].[AttachedChart], [Note].[NoteRating], [Note].[HotCount], [Note].[postTime], [User].[Alias], [Group].[GroupName], [Group].[GroupID      NULL      PLAN_ROW      0      1.0
| |--Nested Loops(Inner Join)      14      8      6      Nested Loops      Inner Join      NULL      NULL      75.0      0.0      4.0142928E-4      7104      0.31630948      [Note].[TheNote], [Note].[StockID], [Note].[Date], [Note].[NoteID], [Note].[Price], [Note].[isShared], [Note].[UserID], [Note].[AttachedChart], [Note].[NoteRating], [Note].[HotCount], [Note].[postTime], [Group].[GroupName], [Group].[GroupID]      NULL      PLAN_ROW      0      1.0
| | |--Clustered Index Seek(OBJECT:([Notes].[dbo].[Group].[PK_Group]), SEEK:([Group].[GroupID]=2049) ORDERED FORWARD)      14      9      8      Clustered Index Seek      Clustered Index Seek      OBJECT:([Notes].[dbo].[Group].[PK_Group]), SEEK:([Group].[GroupID]=2049) ORDERED FORWARD      [Group].[GroupName], [Group].[GroupID]      1.0      3.2034011E-3      7.9603E-5      1056      3.283004E-3      [Group].[GroupName], [Group].[GroupID]      NULL      PLAN_ROW      0      1.0
| | |--Sort(ORDER BY:([Note].[Date] DESC))      14      10      8      Sort      Sort      ORDER BY:([Note].[Date] DESC)      NULL      75.0      1.1261261E-2      1.0926683E-3      6057      0.312713      [Note].[TheNote], [Note].[StockID], [Note].[Date], [Note].[NoteID], [Note].[Price], [Note].[isShared], [Note].[UserID], [Note].[AttachedChart], [Note].[NoteRating], [Note].[HotCount], [Note].[postTime]      NULL      PLAN_ROW      0      1.0
| | |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Notes].[dbo].[Note]))      14      11      10      Bookmark Lookup      Bookmark Lookup      BOOKMARK:([Bmk1000]), OBJECT:([Notes].[dbo].[Note])      [Note].[TheNote], [Note].[StockID], [Note].[Date], [Note].[NoteID], [Note].[Price], [Note].[isShared], [Note].[UserID], [Note].[AttachedChart], [Note].[NoteRating], [Note].[HotCount], [Note].[postTime]      96.035713      0.29686558      1.0563929E-4      6096      0.30035907      [Note].[TheNote], [Note].[StockID], [Note].[Date], [Note].[NoteID], [Note].[Price], [Note].[isShared], [Note].[UserID], [Note].[AttachedChart], [Note].[NoteRating], [Note].[HotCount], [Note].[postTime]      NULL      PLAN_ROW      0      1.0
| | |--Index Seek(OBJECT:([Notes].[dbo].[Note].[IX_Note_1]), SEEK:([Note].[StockID]=2662) ORDERED FORWARD)      14      13      11      Index Seek      Index Seek      OBJECT:([Notes].[dbo].[Note].[IX_Note_1]), SEEK:([Note].[StockID]=2662) ORDERED FORWARD      [Bmk1000]      96.035713      3.2034011E-3      1.8442739E-4      33      3.3878284E-3      [Bmk1000]      NULL      PLAN_ROW      0      1.0
| |--Clustered Index Seek(OBJECT:([Notes].[dbo].[User].[PK_User]), SEEK:([User].[UserID]=[Note].[UserID]) ORDERED FORWARD)      14      14      6      Clustered Index Seek      Clustered Index Seek      OBJECT:([Notes].[dbo].[User].[PK_User]), SEEK:([User].[UserID]=[Note].[UserID]) ORDERED FORWARD      [User].[Alias]      1.0      3.2034011E-3      7.9603E-5      101      1.4112654E-2      [User].[Alias]      NULL      PLAN_ROW      0      75.972107
|--Clustered Index Seek(OBJECT:([Notes].[dbo].[GroupNotes].[PK_GroupNotes]), SEEK:([GroupNotes].[GroupID]=2049 AND [GroupNotes].[NoteID]=[Note].[NoteID]) ORDERED FORWARD)      14      15      4      Clustered Index Seek      Clustered Index Seek      OBJECT:([Notes].[dbo].[GroupNotes].[PK_GroupNotes]), SEEK:([GroupNotes].[GroupID]=2049 AND [GroupNotes].[NoteID]=[Note].[NoteID]) ORDERED FORWARD      [GroupNotes].[DiscussionCount]      1.0      3.2034011E-3      7.9607002E-5      63      0.0146458      [GroupNotes].[DiscussionCount]      NULL      PLAN_ROW      0      75.957558

xenon_je

Ok I took a look at your select....

SELECT
TOP 75 LEFT(dbo.Note.TheNote, 251) AS theNote, dbo.[User].Alias, dbo.Note.DataID, dbo.Note.[Date] AS theDate, dbo.Note.NoteID, dbo.Note.Price,
dbo.Note.isShared, dbo.Note.UserID AS AuthorID, DATALENGTH(dbo.Note.AttachedChart) AS AttachedChart, dbo.Note.NoteRating,
dbo.GroupNotes.DiscussionCount, dbo.Note.HotCount, '1' AS type, dbo.Note.postTime, dbo.[Group].GroupName,dbo.[Group].GroupID
FROM
dbo.Note INNER JOIN
dbo.[User] ON dbo.Note.UserID = dbo.[User].UserID INNER JOIN
dbo.GroupNotes ON dbo.Note.NoteID = dbo.GroupNotes.NoteID INNER JOIN
dbo.[Group] ON dbo.GroupNotes.GroupID = dbo.[Group].GroupID and dbo.[Group].GroupID = @groupID
where DataID = @DataID order by date desc

hope this small change will improve your select....

good luck,
xenon

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

xenon_je

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TARJr

ASKER

Your are right....this query is pretty fast...and I'm not having much luck optimizing it further...