Solved

How to optimize Bookmark Lookup

Posted on 2003-10-22
11
1,212 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Your are right....this query is pretty fast...and I'm not having much luck optimizing it further...

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now