InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 13:35:51 GMT20http://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspx[quote][b]mohsen.bahrzadeh (2/25/2013)[/b][hr]I did not notice much explain[/quote]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.Mon, 25 Feb 2013 12:48:58 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspxI did not notice much explainMon, 25 Feb 2013 12:24:30 GMTmohsen.bahrzadehhttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspx[quote][b]mohsen.bahrzadeh (2/25/2013)[/b][hr]sorry you can download from this post[/quote]Can you post the actual execution plan instead of the estimated one?Mon, 25 Feb 2013 12:19:30 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspxsorry you can download from this postMon, 25 Feb 2013 12:03:20 GMTmohsen.bahrzadehhttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspx[quote][b]Sean Lange (2/25/2013)[/b][hr][quote][b]mohsen.bahrzadeh (2/25/2013)[/b][hr]thanks, SSCoach [url=http://barnamenevis.org/attachment.php?attachmentid=100548&d=1361812860]execution plan[/url][/quote]I can't read that and it appears to require a login. Can you just post the file as an attachment here?[/quote]Yes, please just post the attachment for the actual execution plan here.Mon, 25 Feb 2013 12:01:53 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspx[quote][b]mohsen.bahrzadeh (2/25/2013)[/b][hr]thanks, SSCoach [url=http://barnamenevis.org/attachment.php?attachmentid=100548&d=1361812860]execution plan[/url][/quote]I can't read that and it appears to require a login. Can you just post the file as an attachment here?Mon, 25 Feb 2013 11:59:56 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspxthanks, SSCoach [url=http://barnamenevis.org/attachment.php?attachmentid=100548&d=1361812860]execution plan[/url]Mon, 25 Feb 2013 11:57:49 GMTmohsen.bahrzadehhttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspxI 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. [url=http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]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.Mon, 25 Feb 2013 11:55:38 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspxThe 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.Mon, 25 Feb 2013 11:45:06 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1423723-149-1.aspxi'm starter in sqlserver indexing. i have one table with this Structure[quote]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][/quote]i want run this procedure on the Report3 table[quote]CREATE PROCEDURE [dbo].[SPSelectReport3] (@StringWhereParameter nvarchar(4000)=null,@PageIndex int,@PageSize int,@fromDate int,@todate int)ASBEGIN SET NOCOUNT ON;-- َ Begin Of Transactionbegin trandeclare @from int=(@PageSize*(@PageIndex-1))+1declare @to int=(@PageIndex*@PageSize)declare @Query2 nvarchar(max)print @fromprint @toset @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 WHEREHeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orHeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) orMesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' ) ) as TotalPrice ,(select count (aa.mesc) from ( select Distinct mesc,line from Report3 WHEREHeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orHeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) orMesc 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 WHEREHeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orHeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) orMesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' ) )aa ) as CountALL , dense_rank() OVER(ORDER BY Mesc,Line,unit ) ROW_NUM FROM Report3 r2WHEREHeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orHeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) orMesc 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 tranEnd[/quote]i first explain how to create index for this table: i look in where stracture and for any part i create one indexand index this table [quote]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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOCREATE 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[/quote]now i run this proc like this [quote]SPSelectReport3 'and (PbsNo <> ''0'')',1,100,710101,911010[/quote]after 8 sec i get result. now i want add this code to where part[quote]Mesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) or[/quote]finally all proc code like this[quote]CREATE PROCEDURE [dbo].[SPSelectReport3] (@StringWhereParameter nvarchar(4000)=null,@PageIndex int,@PageSize int,@fromDate int,@todate int)ASBEGIN SET NOCOUNT ON;-- َ Begin Of Transactionbegin trandeclare @from int=(@PageSize*(@PageIndex-1))+1declare @to int=(@PageIndex*@PageSize)declare @Query2 nvarchar(max)print @fromprint @toset @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 WHEREHeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orHeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) orMesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' ) ) as TotalPrice ,(select count (aa.mesc) from ( select Distinct mesc,line from Report3 WHEREHeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orHeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) orMesc 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 WHEREHeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orHeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) orMesc in(SELECT HeaderId FROM Report3 WHERE Line=''i'' '+@StringWhereParameter+ ' ) )aa ) as CountALL , dense_rank() OVER(ORDER BY Mesc,Line,unit ) ROW_NUM FROM Report3 r2WHEREHeaderID IN (SELECT headerID FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orHeaderID IN (SELECT mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter +' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) orMesc in( SELECT Mesc FROM Report3 WHERE Line=''i'' '+@StringWhereParameter + ' ) orMesc 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 QtyDateexec (@Query2)--print @query2 if @@error = 0 Commit Tran Else rollback tranEnd[/quote]when run this query after 2hour i get result. I think the problem is in Index please help me, thanks all.Mon, 25 Feb 2013 11:39:02 GMTmohsen.bahrzadeh