Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

set index for this query in sqlserver Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 11:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 2:25 PM
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.
Post #1423723
Posted Monday, February 25, 2013 11:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 17,609, Visits: 15,468
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1423726
Posted Monday, February 25, 2013 11:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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)
Post #1423729
Posted Monday, February 25, 2013 11:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 2:25 PM
Points: 6, Visits: 12
thanks, SSCoach
execution plan


Post #1423730
Posted Monday, February 25, 2013 11:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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)
Post #1423731
Posted Monday, February 25, 2013 12:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 17,609, Visits: 15,468
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1423732
Posted Monday, February 25, 2013 12:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 2:25 PM
Points: 6, Visits: 12
sorry you can download from this post

  Post Attachments 
plan.rar (1 view, 10.33 KB)
Post #1423733
Posted Monday, February 25, 2013 12:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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)
Post #1423742
Posted Monday, February 25, 2013 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 2:25 PM
Points: 6, Visits: 12
I did not notice much explain
Post #1423746
Posted Monday, February 25, 2013 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
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)
Post #1423751
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse