Solved

How to optimize Bookmark Lookup

Posted on 2003-10-22
11
1,218 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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