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

***************************
LVL 1
TARJrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DexstarCommented:
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?
0
TARJrAuthor Commented:
How can I tell what the Bookmark Lookup is?

I can't cut and paste the popup window in SQL analyzer...
0
PaulBarbinCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DexstarCommented:
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*
0
TARJrAuthor Commented:
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)
0
DexstarCommented:
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*
0
TARJrAuthor Commented:
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

0
xenon_jeCommented:
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
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Without any data, I got only clustered index seeks (4 x 200%) ??? :-)

Now, you should know that if the query takes 100% of the total cost, all 100% will need to be used by some parts of your query. You cannot have all the parts with 1% totalling 10% for example...

If the query takes less than 1 second, don't try to optimize it (unless that query runs million of times per day). Only if it takes > 1 second, you should start investigating.

Cheers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xenon_jeCommented:
PS: try to update statistics...this may be a cause that your query does not uses some indexes.... but alo as angelIII pinted out above....I hope this query takes a lot of time, that's why you're trying to optimize it...and not because in the execution plan that bookmark lookup takes so long..:))

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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.