Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


set index for this query in sqlserver


set index for this query in sqlserver

Author
Message
mohsen.bahrzadeh
mohsen.bahrzadeh
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
i'm starter in sqlserver indexing. i have one table with this Structure

REATE TABLE [dbo].[Report3](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Mesc] [nvarchar](50) NULL,
[Line] [nvarchar](5) NULL,
[Unit] [nvarchar](5) NULL,
[Discription] [nvarchar](500) NULL,
[InvQty] [bigint] NULL,
[LastDateNil] [nvarchar](10) NULL,
[ST_CODE] [nvarchar](5) NULL,
[PlanCode] [nvarchar](10) NULL,
[Min] [bigint] NULL,
[Max] [bigint] NULL,
[PbsNo] [nvarchar](50) NULL,
[PbsDate] [nvarchar](10) NULL,
[PbsQty] [nvarchar](10) NULL,
[PbsQtyRec] [nvarchar](30) NULL,
[DateDelay] [nvarchar](10) NULL,
[PartNo] [nvarchar](50) NULL,
[TranQty] [int] NULL,
[TypeRequest] [nvarchar](1) NULL,
[HeaderId] [bigint] NULL,
[LOCATION] [nvarchar](10) NULL,
[Search_date] [int] NULL,
[TranType] [int] NULL,
[QtyDate] [int] NULL,
[PriceMesc] [bigint] NULL,
[TotalPrice] [bigint] NULL,
[CountItem] [int] NULL,
[CountALL] [int] NULL,
CONSTRAINT [PK_Report3] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


i want run this procedure on the Report3 table

CREATE PROCEDURE [dbo].[SPSelectReport3] (@StringWhereParameter nvarchar(4000)=null,@PageIndex int,@PageSize int,@fromDate int,@todate int)
AS
BEGIN

SET NOCOUNT ON;

-- َ Begin Of Transaction
begin tran
declare @from int=(@PageSize*(@PageIndex-1))+1
declare @to int=(@PageIndex*@PageSize)

declare @Query2 nvarchar(max)


print @from
print @to
set @Query2='
select Distinct
[Id]
,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[ST_CODE]
,[PlanCode]
,[Min]
,[Max]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[PartNo]
,[TranQty]
,[TypeRequest]
,[HeaderId]
,[LOCATION]
,0 as Search_date
,QtyDate
,PriceMesc

,TotalPrice
,CountItem
,CountALL

from (
SELECT
[Id]
,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[ST_CODE]
,[PlanCode]
,[Min]
,[Max]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[PartNo]
, 0 as TranQty
,[TypeRequest]
,[HeaderId]
,[LOCATION]
,Search_date
, isnull((select sum(cast( TranQty as int)) from report3 where mesc=r2.mesc and Search_date between '+ cast(@fromDate as varchar(10)) +'and '+ cast(@todate as varchar(10))+'),0) as QtyDate
,PriceMesc,

(select sum (PriceMesc) from Report3
WHERE
HeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
HeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or

Mesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) or
Mesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' )

) as TotalPrice



,(select count (aa.mesc) from ( select Distinct mesc,line from Report3
WHERE
HeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
HeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or


Mesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) or
Mesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' )

)aa where aa.line=''i'') as CountItem

,(select count (aa.mesc) from ( select Distinct mesc,line from Report3
WHERE
HeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
HeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or

Mesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) or
Mesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' )
)aa ) as CountALL

, dense_rank() OVER(ORDER BY Mesc,Line,unit ) ROW_NUM
FROM Report3 r2
WHERE


HeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
HeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or



Mesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) or
Mesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' ) )a where ROW_NUM between '+CAST(@from as varchar(10))+' and '+CAST(@to as varchar(10))
+' order by [Mesc]
,[Line]
,[Unit]'





exec (@Query2)

if @@error = 0
Commit Tran
Else
rollback tran
End

i first explain how to create index for this table:
i look in where stracture and for any part i create one index
and index this table

CREATE NONCLUSTERED INDEX [HeaderId_R_All] ON [dbo].[Report3]
(
[HeaderId] ASC
)
INCLUDE ( [Id],
[Mesc],
[Line],
[Unit],
[Discription],
[InvQty],
[LastDateNil],
[ST_CODE],
[PlanCode],
[Min],
[Max],
[PbsNo],
[PbsDate],
[PbsQty],
[PbsQtyRec],
[DateDelay],
[PartNo],
[TranQty],
[TypeRequest],
[LOCATION],
[Search_date],
[TranType],
[QtyDate],
[CountItem],
[CountALL]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [HeaderIdIndex] ON [dbo].[Report3]
(
[HeaderId] ASC
)
INCLUDE ( [Id],
[Mesc],
[Line],
[Unit],
[Discription],
[InvQty],
[LastDateNil],
[ST_CODE],
[PlanCode],
[Min],
[Max],
[PbsNo],
[PbsDate],
[PbsQty],
[PbsQtyRec],
[DateDelay],
[PartNo],
[TranQty]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Report3] ON [dbo].[Report3]
(
[Mesc] ASC,
[Line] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [LineH_R_HeaderId] ON [dbo].[Report3]
(
[Line] ASC
)
INCLUDE ( [HeaderId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [LineH_R_MESC] ON [dbo].[Report3]
(
[Line] ASC
)
INCLUDE ( [Mesc])
WHERE ([line]='h')
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [LineI_R_Mesc] ON [dbo].[Report3]
(
[Line] ASC
)
INCLUDE ( [Mesc]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [LineNoHeaderId] ON [dbo].[Report3]
(
[Line] ASC
)
INCLUDE ( [HeaderId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [LinI_R_HeaderId] ON [dbo].[Report3]
(
[Line] ASC
)
INCLUDE ( [HeaderId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [LinNoHeaderIdIndex] ON [dbo].[Report3]
(
[Line] ASC
)
INCLUDE ( [HeaderId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [LinNoMeseIndex] ON [dbo].[Report3]
(
[Line] ASC
)
INCLUDE ( [Mesc]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [MESC_R_All] ON [dbo].[Report3]
(
[Mesc] ASC
)
INCLUDE ( [Id],
[Line],
[Unit],
[Discription],
[InvQty],
[LastDateNil],
[ST_CODE],
[PlanCode],
[Min],
[Max],
[PbsNo],
[PbsDate],
[PbsQty],
[PbsQtyRec],
[DateDelay],
[PartNo],
[TranQty],
[TypeRequest],
[HeaderId],
[LOCATION],
[Search_date],
[TranType],
[QtyDate],
[CountItem],
[CountALL]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [MescIndex] ON [dbo].[Report3]
(
[Mesc] ASC
)
INCLUDE ( [Id],
[Line],
[Unit],
[Discription],
[InvQty],
[LastDateNil],
[ST_CODE],
[PlanCode],
[Min],
[Max],
[PbsNo],
[PbsDate],
[PbsQty],
[PbsQtyRec],
[DateDelay],
[PartNo],
[TranQty],
[HeaderId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

now i run this proc like this

SPSelectReport3 'and (PbsNo <> ''0'')',1,100,710101,911010

after 8 sec i get result. now i want add this code to where part

Mesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) or

finally all proc code like this

CREATE PROCEDURE [dbo].[SPSelectReport3] (@StringWhereParameter nvarchar(4000)=null,@PageIndex int,@PageSize int,@fromDate int,@todate int)
AS
BEGIN

SET NOCOUNT ON;

-- َ Begin Of Transaction
begin tran
declare @from int=(@PageSize*(@PageIndex-1))+1
declare @to int=(@PageIndex*@PageSize)

declare @Query2 nvarchar(max)


print @from
print @to
set @Query2='
select Distinct
[Id]
,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[ST_CODE]
,[PlanCode]
,[Min]
,[Max]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[PartNo]
,[TranQty]
,[TypeRequest]
,[HeaderId]
,[LOCATION]
,0 as Search_date
,QtyDate
,PriceMesc

,TotalPrice
,CountItem
,CountALL

from (
SELECT
[Id]
,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[ST_CODE]
,[PlanCode]
,[Min]
,[Max]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[PartNo]
, 0 as TranQty
,[TypeRequest]
,[HeaderId]
,[LOCATION]
,Search_date
, isnull((select sum(cast( TranQty as int)) from report3 where mesc=r2.mesc and Search_date between '+ cast(@fromDate as varchar(10)) +'and '+ cast(@todate as varchar(10))+'),0) as QtyDate
,PriceMesc,

(select sum (PriceMesc) from Report3
WHERE
HeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
HeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
Mesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) or
Mesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) or
Mesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' )

) as TotalPrice



,(select count (aa.mesc) from ( select Distinct mesc,line from Report3
WHERE
HeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
HeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or

Mesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) or
Mesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) or
Mesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' )

)aa where aa.line=''i'') as CountItem

,(select count (aa.mesc) from ( select Distinct mesc,line from Report3
WHERE
HeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
HeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
Mesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) or
Mesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) or
Mesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' )
)aa ) as CountALL

, dense_rank() OVER(ORDER BY Mesc,Line,unit ) ROW_NUM
FROM Report3 r2
WHERE


HeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or
HeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) or

Mesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) or

Mesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) or
Mesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' ) )a where ROW_NUM between '+CAST(@from as varchar(10))+' and '+CAST(@to as varchar(10))
+' order by [Mesc]
,[Line]
,[Unit]'


--

--isnull((select sum(cast( TranQty as int)) from report3 where mesc=r2.mesc and Search_date between '+ cast(@fromDate as varchar(10)) +'and '+ cast(@todate as varchar(10))+'),0) as QtyDate
exec (@Query2)
--print @query2
if @@error = 0
Commit Tran
Else
rollback tran
End

when run this query after 2
hour i get result. I think the problem is in Index please help me, thanks all.
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
The issue is less the index and more the code. My first recommendation would be to rewrite your query.

Please provide the execution plan, sample data, and expected results sample.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16545 Visits: 16999
I agree with Jason that your code is the biggest issue here. That being said your indexes are kind of all over the place too. This is a catch all type query and the way you receive code as a parameter and then execute renders your code vulnerable to sql injection. You should probably take a look at Gail's blog post about catch all queries here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

This could really use some divide and conquer type techniques as well so you don't keep running the same sub query over and over and over. Some properly formatted and parameterized dynamic sql along with liberal usage of some temp tables would help whip this into shape fairly quickly.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mohsen.bahrzadeh
mohsen.bahrzadeh
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
thanks, SSCoach
execution plan
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16545 Visits: 16999
mohsen.bahrzadeh (2/25/2013)
thanks, SSCoach
execution plan




I can't read that and it appears to require a login. Can you just post the file as an attachment here?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Sean Lange (2/25/2013)
mohsen.bahrzadeh (2/25/2013)
thanks, SSCoach
execution plan




I can't read that and it appears to require a login. Can you just post the file as an attachment here?


Yes, please just post the attachment for the actual execution plan here.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

mohsen.bahrzadeh
mohsen.bahrzadeh
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
sorry you can download from this post
Attachments
plan.rar (1 view, 10.00 KB)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16545 Visits: 16999
mohsen.bahrzadeh (2/25/2013)
sorry you can download from this post


Can you post the actual execution plan instead of the estimated one?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
mohsen.bahrzadeh
mohsen.bahrzadeh
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
I did not notice much explain
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16545 Visits: 16999
mohsen.bahrzadeh (2/25/2013)
I did not notice much explain


Not totally sure what you mean here but to get the actual execution plan you need to select that choice in SSMS. It is about 3 buttons to the right of the one you chose when you got the estimated plan.

Honestly ddl, sample data and desired output would be far more helpful. I don't really need to see your execution plan to know that it is going to be suboptimal. There are so many easy targets for improvement.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search