mohsen_zelzela00
دوشنبه 07 اسفند 1391, 17:39 عصر
با سلام خسته نباشید خدمت اساتید محترم
من یک جدول دارم با ساختار زیر
CREATE 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]
که قرار است بر روی این جدول Query زیر اجرا شود
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]'
--
--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
من ابتدا منطق ایجاد Index برای جدولم رو خدمت استاید توضیح بدم که اگر مشکلی داشته باشد اصلاح کنم
من اومدم برای هر تیکه از قسمت Where یک Index در نظر گرفتم که تبدیل به این شد
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
حال زمانی که این procedure رو به صورت زیر فراخوانی می کنم
SPSelectReport3 'and (PbsNo <> ''0'')',1,100,710101,911010
در کمتر از 8 ثانیه نتیجه رو برای من برمی گرداند
حال برای یه شرایط خاصی مجبور شدم که procedure خودم را تغییر دهم یعنی دستور زیر را به شرط اضافه کنم
Mesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) or
که کد procedure به صورت زیر است
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
حال زمانی که بخواهم procedure رو به صورت زیر اجرا کنم
SPSelectReport3 'and (PbsNo <> ''0'')',1,100,710101,911010
یه چیزی حدود 1 ساعت طول می کشد و بعد از اون از لحاظ حافظه سیتم کم می اورد و وکلاً خاموش می شود من فکر می کنم با اضافه شدن این شرط به قسمت Where لازم باشد که Index های خودم را افزایش یا اصلاح کنم ولی هر چی تست کردم نشد ممنون میشم اساتید محترم راهنمایی کنند.
ممنونم
پ . ن :اگر عنوان تاپیک صحیح نیست مدیران محترم باید ببخشید چون نمی دونستم از چه عنوانی استفاده کنم.
من یک جدول دارم با ساختار زیر
CREATE 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]
که قرار است بر روی این جدول Query زیر اجرا شود
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]'
--
--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
من ابتدا منطق ایجاد Index برای جدولم رو خدمت استاید توضیح بدم که اگر مشکلی داشته باشد اصلاح کنم
من اومدم برای هر تیکه از قسمت Where یک Index در نظر گرفتم که تبدیل به این شد
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
حال زمانی که این procedure رو به صورت زیر فراخوانی می کنم
SPSelectReport3 'and (PbsNo <> ''0'')',1,100,710101,911010
در کمتر از 8 ثانیه نتیجه رو برای من برمی گرداند
حال برای یه شرایط خاصی مجبور شدم که procedure خودم را تغییر دهم یعنی دستور زیر را به شرط اضافه کنم
Mesc in( SELECT Mesc FROM Report3 WHERE Line=''h'' '+@StringWhereParameter + ' ) or
که کد procedure به صورت زیر است
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
حال زمانی که بخواهم procedure رو به صورت زیر اجرا کنم
SPSelectReport3 'and (PbsNo <> ''0'')',1,100,710101,911010
یه چیزی حدود 1 ساعت طول می کشد و بعد از اون از لحاظ حافظه سیتم کم می اورد و وکلاً خاموش می شود من فکر می کنم با اضافه شدن این شرط به قسمت Where لازم باشد که Index های خودم را افزایش یا اصلاح کنم ولی هر چی تست کردم نشد ممنون میشم اساتید محترم راهنمایی کنند.
ممنونم
پ . ن :اگر عنوان تاپیک صحیح نیست مدیران محترم باید ببخشید چون نمی دونستم از چه عنوانی استفاده کنم.