Solved

How to optimize Bookmark Lookup

Posted on 2003-10-22
11
1,219 Views
Last Modified: 2007-12-19

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

***************************
0
Comment
Question by:TARJr
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 19

Expert Comment

by:Dexstar
ID: 9602462
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
 
LVL 1

Author Comment

by:TARJr
ID: 9602744
How can I tell what the Bookmark Lookup is?

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

Expert Comment

by:PaulBarbin
ID: 9602807
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 19

Expert Comment

by:Dexstar
ID: 9602835
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
 
LVL 1

Author Comment

by:TARJr
ID: 9603244
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
 
LVL 19

Expert Comment

by:Dexstar
ID: 9603418
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
 
LVL 1

Author Comment

by:TARJr
ID: 9603500
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
 
LVL 9

Expert Comment

by:xenon_je
ID: 9603974
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 9604994
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
 
LVL 9

Assisted Solution

by:xenon_je
xenon_je earned 150 total points
ID: 9605747
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
 
LVL 1

Author Comment

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

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question