طول کشیدن زمان اجرای Query

[mohsen_zelzela00]

با سلام خدمت اساتید محترم
من یک جدول دارم با ساختار زیر


CREATE TABLE [dbo].[Report4](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Mesc] [nvarchar](50) NULL,
[Line] [nvarchar](5) NULL,
[Unit] [nvarchar](5) NULL,
[Description] [nvarchar](500) NULL,
[ST_CODE] [nvarchar](5) NULL,
[PbsNo] [nvarchar](50) NULL,
[PbsDate] [nvarchar](10) NULL,
[PbsQty] [nvarchar](10) NULL,
[PbsQtyRec] [nvarchar](10) NULL,
[QtyConsum1] [nvarchar](10) NULL,
[QtyConsum2] [nvarchar](10) NULL,
[QtyConsum3] [nvarchar](10) NULL,
[QtyConsum4] [nvarchar](10) NULL,
[QtyConsum5] [nvarchar](10) NULL,
[Type] [nvarchar](20) NULL,
[InvQty] [nvarchar](10) NULL,
[TypeRequest] [nvarchar](50) NULL,
[HeaderId] [bigint] NULL,
[LOCATION] [nvarchar](10) NULL,
CONSTRAINT [PK_Report4] 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]
GO
CREATE NONCLUSTERED INDEX [HeaderId] ON [dbo].[Report4]
(
[HeaderId] ASC
)
INCLUDE ( [Id],
[Mesc],
[Line],
[Unit],
[Description],
[ST_CODE],
[PbsNo],
[PbsDate],
[PbsQty],
[PbsQtyRec],
[QtyConsum1],
[QtyConsum2],
[QtyConsum3],
[QtyConsum4],
[QtyConsum5],
[Type],
[InvQty],
[TypeRequest]) 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 [HeaderIdRAll] ON [dbo].[Report4]
(
[HeaderId] ASC
)
INCLUDE ( [Id],
[Mesc],
[Line],
[Unit],
[Description],
[ST_CODE],
[PbsNo],
[PbsDate],
[PbsQty],
[PbsQtyRec],
[QtyConsum1],
[QtyConsum2],
[QtyConsum3],
[QtyConsum4],
[QtyConsum5],
[Type],
[InvQty],
[TypeRequest]) 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 [LineNOIRHeaderId] ON [dbo].[Report4]
(
[Line] ASC
)
INCLUDE ( [HeaderId])
WHERE ([line]='I')
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 [LineNoRHeaderId] ON [dbo].[Report4]
(
[Line] ASC
)
INCLUDE ( [HeaderId])
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 [LineNoRMesc] ON [dbo].[Report4]
(
[Line] ASC
)
INCLUDE ( [Mesc])
WHERE ([line]='I')
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 [MescRAll] ON [dbo].[Report4]
(
[Mesc] ASC
)
INCLUDE ( [Id],
[Line],
[Unit],
[Description],
[ST_CODE],
[PbsNo],
[PbsDate],
[PbsQty],
[PbsQtyRec],
[QtyConsum1],
[QtyConsum2],
[QtyConsum3],
[QtyConsum4],
[QtyConsum5],
[Type],
[InvQty],
[TypeRequest],
[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


در این جدول بیش از 1000000 رکورد وجود دارد من Query زیر رو نوشتم

ALTER PROCEDURE [dbo].[SPSelectReport4_4] (@StringWhereParameter nvarchar(4000)=null)
AS
BEGIN

SET NOCOUNT ON;





-- َ Begin Of Transaction
begin tran


declare @Query nvarchar(max)
set @Query='
select count(id) from

((SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
,LOCATION
FROM [MyMaterialDB].[dbo].[Report4]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''H'''+ @StringWhereParameter+'))
UNION
(
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
,LOCATION
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I''' +@StringWhereParameter+'))
UNION
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
,LOCATION
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I'''+@StringWhereParameter+')
)))a'


--

exec(@Query)


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

زمان اجرایی این Query یه چیزی حدود 10 دقیقه طول می کشه . اساتید محترم ممنون میشم راهنماییم کنن.

[محمد سلیم آبادی]

اگه اشتباه می کنم بگو،
1-مگه کد بدنه کوئری به غیر از اون قسمت که در where مقدار Line با StringWhereParameter مقایسه میشه بصورت پارامتر تعیین میشه؟
اگه فقط همون قسمت متغیر هست چرا کل کوئری را داخل نقل قول قرار دادین؟

2-وقتی میشه با عملگر OR به جواب رسید دلیل خاصی داره که از عملگر UNION استفاده کردین؟ (چرا که هر سه کوئری از یک جدول تامین میشن)

این کوئری رو اجرا کنید ببینید نتیجه، مشابه نتیجه شماست و سرعتش چطوره:

SELECT COUNT(id)
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line='I' +@StringWhereParameter))
OR mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line='I'+@StringWhereParameter)
OR headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line='H'+ @StringWhereParameter);

[mohsen_zelzela00]

اگه اشتباه می کنم بگو،
1-مگه کد بدنه کوئری به غیر از اون قسمت که در where مقدار Line با StringWhereParameter مقایسه میشه بصورت پارامتر تعیین میشه؟
اگه فقط همون قسمت متغیر هست چرا کل کوئری را داخل نقل قول قرار دادین؟

2-وقتی میشه با عملگر OR به جواب رسید دلیل خاصی داره که از عملگر UNION استفاده کردین؟ (چرا که هر سه کوئری از یک جدول تامین میشن)

این کوئری رو اجرا کنید ببینید نتیجه، مشابه نتیجه شماست و سرعتش چطوره:

SELECT COUNT(id)
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line='I' +@StringWhereParameter))
OR mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line='I'+@StringWhereParameter)
OR headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line='H'+ @StringWhereParameter);

مرسی استاد دستتون درد نکنه فقط یه سوال دیگه من از این Query فقط برای گرفتن Count استفاده می کنم و برای باز گرداندن نتیجه Query از کد زیر استفاده می کنم آیا راه حلی وجود دارد که بتونم سرعت این Query رو نیز افزایش داد؟(با اینکه بر روی این Query صفحه بندی رو اعمال کردم ولی سرعت آن خیلی خیلی خیلی پایینه جوری که کاربر کلافه میشه البته ناگفته نماند که حجم دیتابیس خیلی خیلی بالاست)

--SPSelectReport4 '',1,100
ALTER PROCEDURE [dbo].[SPSelectReport4] (@StringWhereParameter nvarchar(4000)=null,@PageIndex int,@PageSize int)
AS
BEGIN

SET NOCOUNT ON;





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

declare @Query nvarchar(max)
set @Query='
select distinct
Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
,LOCATION

from (

SELECT *, ROW_NUMBER() OVER(ORDER BY Mesc,Line,unit) ROW_NUM
FROM

((SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
,LOCATION
FROM [MyMaterialDB].[dbo].[Report4]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''H'''+ @StringWhereParameter+'))
UNION
(
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
,LOCATION
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I''' +@StringWhereParameter+'))
UNION
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
,LOCATION
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I'''+@StringWhereParameter+')
)) )a)b where (b.ROW_NUM between '+CAST(@from as varchar(10))+' and '+CAST(@to as varchar(10))+') ORDER BY Mesc,Line,unit'


--

exec(@Query)


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


و آیا از لحاظ Index گذاری مشکلی ندارد؟
ممنونم استاد که راهنمایی می کنید.

[محمد سلیم آبادی]

کدتون رو ساده کردم بفرمایید:

select *
from (
SELECT *,ROW_NUMBER() OVER(ORDER BY Mesc,Line,unit) ROW_NUM
FROM [MyMaterialDB].[dbo].[Report4]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line='H'+ @StringWhereParameter)
OR mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line='I' +@StringWhereParameter)
OR mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line='I'+@StringWhereParameter)
)b
where b.ROW_NUM between @from and @to
ORDER BY ROW_NUM;

[mohsen_zelzela00]

کدتون رو ساده کردم بفرمایید:

select *
from (
SELECT *,ROW_NUMBER() OVER(ORDER BY Mesc,Line,unit) ROW_NUM
FROM [MyMaterialDB].[dbo].[Report4]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line='H'+ @StringWhereParameter)
OR mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line='I' +@StringWhereParameter)
OR mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line='I'+@StringWhereParameter)
)b
where b.ROW_NUM between @from and @to
ORDER BY ROW_NUM;


واقعاً کار من رو حل کردید نمی دونم به چه زبونی از شما تشکر کنم ممنونم استاد. فقط استاد آیا Index هایی که من تعریف کردم درست هستند یا نه؟؟؟چون من دقیقاً دو کد را اجرا کردم با سخت افزار I7,8GIG RAM اولی حدود 10 دقیقه طول کشید و دومی حدود 9 دقیقه که 1 دقیقه با هم اختلاف داشتند برای همین من میگم شاید اشکال از جای دیگر است که باعث شده زمان اجرا اینقدر طول بکشد ممنون میشم استاد باز من رو راهنمایی کنید.

[محمد سلیم آبادی]

من دقیقاً دو کد را اجرا کردم با سخت افزار I7,8GIG RAM اولی حدود 10 دقیقه طول کشید و دومی حدود 9 دقیقه که 1 دقیقه با هم اختلاف داشتندمنظور از دو کد کدومه؟ یعنی آخرین کدهایی که پست کردیم؟ یا دو تا کد خودتون؟ بالاخره مشکلتون سرعت اجرای کوئریتون حل شده یا هنوز پابرجاست؟

شاید اشکال از جای دیگر است که باعث شده زمان اجرا اینقدر طول بکشدواقعیتش من دانشی نسبت به ایندکس ندارم، ولی با شناخت اندکی که از شما پیدا کردم احتمال میدم بتونید کوئریتون رو ساده تر از این بکنید و نیاز نباشه که از سه سابکوئری استفاده کنید.
اگه مایل بودین یک نمونه از داده هایی که شرط بر اساس اونها نوشته شده رو قرار بدین، و توضیح بدین که دقیقا چه خروجی ملاکتون هست و نتیجه مطلوب چیه. اون وقت میشه نظر داد که آیا کوئری مشکل داره یا اینکه مشکل از جایی دیگه هست (مثل زیاد تعداد سطرهای جدول یا ایندکس های نامناسب).

[mohsen_zelzela00]

منظور از دو کد کدومه؟ یعنی آخرین کدهایی که پست کردیم؟ یا دو تا کد خودتون؟ بالاخره مشکلتون سرعت اجرای کوئریتون حل شده یا هنوز پابرجاست؟
واقعیتش من دانشی نسبت به ایندکس ندارم، ولی با شناخت اندکی که از شما پیدا کردم احتمال میدم بتونید کوئریتون رو ساده تر از این بکنید و نیاز نباشه که از سه سابکوئری استفاده کنید.
اگه مایل بودین یک نمونه از داده هایی که شرط بر اساس اونها نوشته شده رو قرار بدین، و توضیح بدین که دقیقا چه خروجی ملاکتون هست و نتیجه مطلوب چیه. اون وقت میشه نظر داد که آیا کوئری مشکل داره یا اینکه مشکل از جایی دیگه هست (مثل زیاد تعداد سطرهای جدول یا ایندکس های نامناسب).


با سلام من فایل Report4 (http://barnamenevis.org/188.40.250.235:888/MyMat.rar) را در یک سرور آپلود کردم چون امکان اضافه کردن آن برایم در سایت مقدور نبود(دیتاهای درون آن را برای اینکه حجم کم تر شود یه خورده کم تر کردم).

در این جدول یک سری رکورد وجود دارد با Line='H' , Line='I'.
در اینجا H به معنای هدر کالا و I به معنای آیتم ها می باشد. من می خوام داده های این جدول را به صورتی نمایش دهم که ابتدا هدرها و بعد ایتم های متعلق به آن هدرها نمایش داده شوند مانند عکس زیر


این عمل برای Search هم باید وجود داشته باشد یعنی ابتدا هدرهای و بعد آیتم های آن هدر فقط اینجا جستجو باید جوری باشد که مثلاً اگر شرط زیر را در نظر بگیریم

Description like'% pump%'

در این حالت اینجوری برخورد می کنیم که اگر ایتمی پیدا شد که Description آن حاوی pump باشد آن آیتم را به همراه تمام هدرهای آن باید آورده شود و اگر هدری پیدا شد که Description آن حاوی pump باشدتمام هدرهای آن و آیتم های مربوط به هدرها را باید واکشی کنم

[mohsen_zelzela00]

و زمانی که بخواهم جستجو رو انجام دهم دقیقاً این بلا سر سیستم من میاید(I7 cpu ,8gig Ram)


97337http://barnamenevis.org/images/misc/pencil.png

[حمیدرضاصادقیان]

شما Execution Plan رو اینجا قرار بدید تا بررسی دقیقتری بشه

[mohsen_zelzela00]

شما Execution Plan رو اینجا قرار بدید تا بررسی دقیقتری بشه

استاد چگونه می تونم Execution Plan رو استخراج کنم؟ ممنون

[mohsen_zelzela00]

من Query را انتخاب کردم و پس از آن Ctrl+L رو فشار دادم و نتیجه حاصله رو ذخیره کردم

[mohsen_zelzela00]

دوستان کسی نمی تونه من رو راهنمایی کنه؟؟ ممنون