set index for this query in sqlserver

Rate Topic

Display Mode

Topic Options

Author

Message

mohsen.bahrzadeh

Posted Monday, February 25, 2013 11:39 AM

Forum 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

SQLRNNR

Posted Monday, February 25, 2013 11:45 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853, Visits: 12,438

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 2008

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

Sean Lange

Posted Monday, February 25, 2013 11:55 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957, Visits: 8,523

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

Post #1423729

mohsen.bahrzadeh

Posted Monday, February 25, 2013 11:57 AM

Forum Newbie

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

thanks, SSCoach
execution plan

Post #1423730

Sean Lange

Posted Monday, February 25, 2013 11:59 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957, Visits: 8,523

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

Post #1423731

SQLRNNR

Posted Monday, February 25, 2013 12:01 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853, Visits: 12,438

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.

Post #1423732

mohsen.bahrzadeh

Posted Monday, February 25, 2013 12:03 PM

Forum 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

Sean Lange

Posted Monday, February 25, 2013 12:19 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957, Visits: 8,523

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

Post #1423742

mohsen.bahrzadeh

Posted Monday, February 25, 2013 12:24 PM

Forum 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

Sean Lange

Posted Monday, February 25, 2013 12:48 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 8,957, Visits: 8,523

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

Post #1423751

« Prev Topic | Next Topic »