آیا با اصلاح کد TSQL، می توان کارایی کوئری را افزایش داد!؟

در این مقاله، قصد دارم یکی از اتفاقاتی که چند روز قبل برایم پیش آمد را برا ی دوستان نیک آموزی عزیز توضیح بدهم.

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

[sql] ALTER PROCEDURE [PerKar].[sp_rep_Personels]
(
@CurrentDate varchar(15),
@EmployStateCode nvarchar(10)
)
AS
;WITH CTE1 AS
(
SELECT PerOrg.tbPersonalSpex.PersonalID, PerOrg.tbPersonalSpex.PersonalNum,
PerOrg.tbPersonalSpex.FName,
PerOrg.tbPersonalSpex.LName, PerKar.tbConstantItems.ItemID AS GenreCode,
PerKar.tbConstantItems.Name AS GenreName, PerOrg.tbPersonalSpex.BCNo,
PerOrg.tbPersonalSpex.NationalCode,
PerKar.fn_ConvertIntToDate(PerOrg.tbPersonalSpex.BirthDate) AS SBirthDate,
PerOrg.tbPersonalSpex.BirthDate,
tbConstantItems_1.ItemID AS MaritalCode,
tbConstantItems_1.Name AS MaritalName, vwPerEdu_1.Degree,
vwPerEdu_1.DegreeName, vwPerEdu_1.EduBranchID, vwPerEdu_1.EduEvidenceDetails,
PerKar.fn_GetJebheStates(PerOrg.tbPersonalSpex.PersonalID) AS JebheState,
tbPersonnelEmployState.ItemID AS EmployStateCode,
tbPersonnelEmployState.Name AS EmployStateName,
PerKar.fn_ConvertIntToDate(PerOrg.tbPersonalSpex.EmployDate)
AS SEmployDate, PerOrg.tbPersonalSpex.EmployDate,
PerOrg.tbPersonalSpex.AccountNo,
(SELECT Title FROM PerKar.tb_EmploymentAllocation
WHERE Code=LEFT(PerKar.vw_PersonelAllocation.RasteCode,1)) AS RasteAsli,
PerKar.vw_PersonelAllocation.RasteCode,
PerKar.vw_PersonelAllocation.Raste,
PerKar.vw_PersonelAllocation.ReshteCode,
PerKar.vw_PersonelAllocation.Reshte,
PersonalLocation.LocationID AS MKhedmatID,
PersonalLocation.LocationName AS MKhedmat, tbHokm_.tbHokmID,
tbHokm_.Rotbeh,tbHokm_.Tabaghe, tbHokm_.FatherName,
tbHokm_.IssuePlace,tbHokm_.BirthPlace,
tbHokm_.SanavateKhedmateRasmi,tbHokm_.OrgJobUnitTitle,tbHokm_.Childs,
PerKar.fn_FindEmploymentYears(PerOrg.tbPersonalSpex.PersonalID,
dbo.fn_ConvertHijriDateToDays(@CurrentDate)) AS SanavatKhedmat
,tbOrgJob.JobType AS JobTypeID, tbOrgJob.Name AS JobTypeName,
tbOrgJob.OrgJobID, tbOrgJob.OrgJobTitle
FROM PerOrg.tbPersonalSpex
INNER JOIN (SELECT POS.PersonalID
FROM [PerKar].[tbPersonnelOfficeStates] AS POS
INNER JOIN (SELECT PersonalID, MAX(StartDate) AS StartDate
FROM [PerKar].[tbPersonnelOfficeStates] AS POS
GROUP BY PersonalID) AS tbTmp1
ON POS.PersonalID=tbTmp1.PersonalID AND
POS.StartDate=tbTmp1.StartDate
WHERE POS.OfficeStateType<>43) AS tbTmp
ON PerOrg.tbPersonalSpex.PersonalID=tbTmp.PersonalID
INNER JOIN PerKar.tbConstantItems
ON PerOrg.tbPersonalSpex.Genre = PerKar.tbConstantItems.ItemID AND
PerKar.tbConstantItems.ConstantID = 43
INNER JOIN PerKar.tbConstantItems AS tbConstantItems_1
ON PerOrg.tbPersonalSpex.MaritalState = tbConstantItems_1.ItemID AND
tbConstantItems_1.ConstantID = 5
INNER JOIN (SELECT vwPerEdu.PersonalID, vwPerEdu.PEducationID,
vwPerEdu.CollectDate, vwPerEdu.EduAverage,
vwPerEdu.Degree, vwPerEdu.DegreeName,
vwPerEdu.EduEvidenceID, vwPerEdu.University,
vwPerEdu.Comments, vwPerEdu.EduBranchID,
vwPerEdu.EduEvidenceDetails,
vwPerEdu.EduDegree, vwPerEdu.EBName,
vwPerEdu.IsChangeByOthers
FROM PerKar.vwPersonnelEducations AS vwPerEdu
INNER JOIN (SELECT PersonalID,
MAX(Degree)
AS Degree FROM PerKar.vwPersonnelEducations
GROUP BY PersonalID) AS PersonnelDegrees
ON vwPerEdu.PersonalID =
PersonnelDegrees.PersonalID
AND vwPerEdu.Degree =
PersonnelDegrees.Degree) AS vwPerEdu_1
ON PerOrg.tbPersonalSpex.PersonalID = vwPerEdu_1.PersonalID
INNER JOIN PerKar.vw_PersonelAllocation
ON PerOrg.tbPersonalSpex.PersonalID =
PerKar.vw_PersonelAllocation.PersonalID
INNER JOIN (SELECT DISTINCT X.PersonalID,
tbConstantItems_2.ItemID, tbConstantItems_2.Name
FROM (SELECT PersonalID, MAX(StartEmployDate) AS StartEmployDate,
(SELECT EmployType FROM PerKar.tbPersonnelEmployState AS PES1
WHERE (PersonalID = PES.PersonalID)
AND (StartEmployDate =
MAX(PES.StartEmployDate))) AS EmployType
FROM PerKar.tbPersonnelEmployState AS PES
GROUP BY PersonalID) AS X
INNER JOIN PerKar.tbConstantItems AS tbConstantItems_2
ON X.EmployType =
tbConstantItems_2.ItemID) AS tbPersonnelEmployState
ON tbPersonnelEmployState.PersonalID = PerOrg.tbPersonalSpex.PersonalID
INNER JOIN (SELECT tbOrgJobToPersonel.PersonalID,
PerKar.tbLocations.LocationID, PerKar.tbLocations.LocationName
FROM PerKar.tbLocations
INNER JOIN PerOrg.tbOrganizationUnit
ON PerKar.tbLocations.LocationID =
PerOrg.tbOrganizationUnit.LocationID
INNER JOIN PerOrg.tbOrganizationJob
ON PerOrg.tbOrganizationUnit.tbUnitID =
PerOrg.tbOrganizationJob.tbUnitID
INNER JOIN (SELECT PersonalID, MAX(StartDate) AS StartDate,
(SELECT TOP 1 tbOrgJobID
FROM PerKar.tbOrgJobToPersonel AS tbOrgJobToPersonel_1
WHERE (PersonalID = jtp.PersonalID)
AND (StartDate = MAX(jtp.StartDate))) AS tbOrgJobID
FROM PerKar.tbOrgJobToPersonel AS jtp
GROUP BY PersonalID) AS tbOrgJobToPersonel
ON PerOrg.tbOrganizationJob.tbOrgJobID =
tbOrgJobToPersonel.tbOrgJobID) AS PersonalLocation
ON PersonalLocation.PersonalID = PerOrg.tbPersonalSpex.PersonalID
INNER JOIN (SELECT h.tbHokmID,h.PersonalID,h.Rotbeh,h.Tabaghe,
h.FatherName,h.IssuePlace,h.BirthPlace,
h.SanavateKhedmateRasmi,h.OrgJobUnitTitle,h.Childs
FROM [PerKar].[tbHokm_] AS h
INNER JOIN (SELECT PersonalID,MAX(tbHokmID) AS tbHokmID,
MAX(ExecutionDate) AS ExecutionDate
FROM [PerKar].[tbHokm_]
GROUP BY PersonalID) AS t2
ON h.tbHokmID=t2.tbHokmID) AS tbHokm_
ON tbHokm_.PersonalID = PerOrg.tbPersonalSpex.PersonalID
INNER JOIN (SELECT ojp.PersonalID, oj.JobType, t.Name,
oj.OrgJobID, oj.OrgJobTitle
FROM [PerOrg].[tbOrganizationJob] AS oj
INNER JOIN [PerKar].[tbOrgJobToPersonel] AS ojp
ON oj.tbOrgJobID=ojp.tbOrgJobID AND ojp.EndDate IS NULL
INNER JOIN (SELECT ci.ItemID, ci.Name
FROM [PerKar].[tbConstants] AS c
INNER JOIN [PerKar].[tbConstantItems] AS ci
ON c.ConstantID=ci.ConstantID
WHERE c.Name=N'JobType') AS t
ON t.ItemID=oj.JobType) AS tbOrgJob
ON tbOrgJob.PersonalID = PerOrg.tbPersonalSpex.PersonalID
)
SELECT ROW_NUMBER() OVER (ORDER BY PersonalID) AS RowNo, *
FROM CTE1
WHERE EmployStateCode=@EmployStateCode

همانطور که مشاهده می کنید، یک SP بزرگ و شلوغ(چشم دوستان کد بد نبینه!!!، البته کلی طول کشید تا کد این SP رو مرتبش کردم تا بتونم بخونمش-همه چیز پشت سر هم نوشته شده بود، با نام گذاری ها هم کاری ندارم)

البته قبل از اینکه بخوام برم سراغ ایندکس ها و موارد این چنین، با اجرای این SP(پارامترها را از طریق برنامه Profiler به دست آوردم) و بررسی IO، متوجه شدم که فقط از جداول “_tbHokm” و “tbLocations” و “tbOrganizationUnit” به ترتیب هر یک حدود ۹۵۶۰۰۰۰ و ۴۵۰۰۰ و ۴۵۰۰۰ عملیات Logical Read دارند.

و این درحالی بود که این جداول اصلا جداول بزرگی نبوند.

و اجرای این SP نیز در کل حدود ۹۹۰۰۰۰۰ عملیات Logical Read داشت و فقط ۲۰۳ رکورد در خروجی نمایش داده شده ظاهر می شد.

البته Execution Plan این SP را هم بررسی کردم، ولی اینقدر بزرگ بود که نتوانستم تصویر مناسبی از آن را در اینجا نمایش دهم.

در ابتدا خیلی تعجب کردم. جداول دیگر رو هم که بررسی کردم، متوجه شدم که هیچ جدول بزرگی وجود ندارد و بزرگترین جدول حدود ۳۰۰۰ رکورد دارد. این مسئله خیلی ذهنم رو درگیر کرد.

کاری که من کردم، این بود که بجای اینکه برم سراغ ایندکس ها و بررسی آنها، اول کد این SP را شروع کردم با دقت بررسی کردن و متوجه شدم که در این کد، از زیر پرسوجوهای زیادی استفاده شده و همچنین اینکه مثلا از یک جدول چندین بار در بخش های مختلف استفاده شده است.

به همین علت، برای اولین کار خواستم که این زیر پرسوجوها را کمی مرتب کنم و میزان استفاده SQL Server از یک جدول را تا جایی که می شود کم کنم. زیرا با توجه به ماهیت و نوع زیر پرسوجو(Self-Contained یا Correlated) و نوع خروجی زیر پرسوجو(Single Value، Multiple Value یا Table Result) و مکان استفاده از زیر پرسوجو، برخورد SQL Server با زیر پرسوجو متفاوت خواهد بود.

با بررسی این زیر پرسوجوها، متوجه شدم که ۶ زیر پرسوجوی کاملا مستقل(Self-Contained) وجود دارد که تعداد رکوردهای خروجی آنها نیز خیلی زیاد نیست و اینکه می توان اجرای این زیر پرسوجوها را خارج از پرسوجوی اصلی انجام داد، به همین منظور کد SP را به صورت زیر تغییر دادم و به یک نتیجه بسیار جالب رسیدم(البته به طور موقت و برای انجام مقایسه، SP مورد نظر را تحت نام جدیدی ذخیره کردم). در حقیقت کاری که انجام دادم، خارج کردن اجرای زیر پرسوجوها از اجرای پرسوجوی اصلی بود و در پرسوجوی اصلی از جداول موقت ایجاد شده بجای زیر پرسوجوها در عملیات JOIN استفاده کردم.

 ALTER PROCEDURE [PerKar].[sp_rep_Personels2]
(
	@CurrentDate		varchar(15),
	@EmployStateCode	nvarchar(10)
)
AS
DECLARE @tbTmp TABLE (PersonalID int PRIMARY KEY);

DECLARE @vwPerEdu_1 TABLE (PersonalID int, PEducationID int, CollectDate int, EduAverage nchar(6),
							Degree tinyint, DegreeName nvarchar(80), EduEvidenceID int, University nvarchar(100),
							Comments nvarchar(480), EduBranchID int, EduEvidenceDetails nvarchar(100),
							EduDegree tinyint, EBName nvarchar(100), IsChangeByOthers int);

DECLARE @tbPersonnelEmployState TABLE(PersonalID int PRIMARY KEY, ItemID int,	Name nvarchar(50));

DECLARE @PersonalLocation TABLE (PersonalID int PRIMARY KEY, LocationID int, LocationName nvarchar(80));

DECLARE @tbHokm_ TABLE (tbHokmID int PRIMARY KEY, PersonalID int, Rotbeh tinyint, Tabaghe int, FatherName nvarchar(30),
						IssuePlace nvarchar(250), BirthPlace nvarchar(40), SanavateKhedmateRasmi nvarchar(250),
						OrgJobUnitTitle nvarchar(100), Childs tinyint);

DECLARE @tbOrgJob TABLE(PersonalID int PRIMARY KEY, JobType tinyint, Name nvarchar(50),
						OrgJobID nvarchar(11), OrgJobTitle nvarchar(200));

INSERT INTO @tbTmp
	SELECT DISTINCT POS.PersonalID
		FROM [PerKar].[tbPersonnelOfficeStates] AS POS
			INNER JOIN (SELECT PersonalID, MAX(StartDate) AS StartDate
							FROM [PerKar].[tbPersonnelOfficeStates] AS POS
							GROUP BY PersonalID) AS tbTmp1
				ON POS.PersonalID=tbTmp1.PersonalID AND POS.StartDate=tbTmp1.StartDate
		WHERE POS.OfficeStateType<>43;

INSERT INTO @vwPerEdu_1
	SELECT	vwPerEdu.PersonalID, vwPerEdu.PEducationID, vwPerEdu.CollectDate, vwPerEdu.EduAverage,
			vwPerEdu.Degree, vwPerEdu.DegreeName, vwPerEdu.EduEvidenceID, vwPerEdu.University,
			vwPerEdu.Comments, vwPerEdu.EduBranchID, vwPerEdu.EduEvidenceDetails, vwPerEdu.EduDegree,
			vwPerEdu.EBName, vwPerEdu.IsChangeByOthers
		FROM PerKar.vwPersonnelEducations AS vwPerEdu
			INNER JOIN (SELECT PersonalID, MAX(Degree) AS Degree   FROM PerKar.vwPersonnelEducations
						GROUP BY PersonalID) AS PersonnelDegrees
				ON vwPerEdu.PersonalID = PersonnelDegrees.PersonalID AND vwPerEdu.Degree = PersonnelDegrees.Degree;

INSERT INTO @tbPersonnelEmployState
	SELECT  DISTINCT X.PersonalID, tbConstantItems_2.ItemID, tbConstantItems_2.Name
		FROM (SELECT PersonalID, MAX(StartEmployDate) AS StartEmployDate,
					(SELECT EmployType FROM PerKar.tbPersonnelEmployState AS PES1
						WHERE (PersonalID = PES.PersonalID)
								AND (StartEmployDate = MAX(PES.StartEmployDate))) AS EmployType
				FROM PerKar.tbPersonnelEmployState AS PES
				GROUP BY PersonalID) AS X
			INNER JOIN PerKar.tbConstantItems AS tbConstantItems_2
				ON X.EmployType = tbConstantItems_2.ItemID AND tbConstantItems_2.ConstantID=9;

INSERT INTO @PersonalLocation
	SELECT	tbOrgJobToPersonel.PersonalID, PerKar.tbLocations.LocationID, PerKar.tbLocations.LocationName
		FROM PerKar.tbLocations
			INNER JOIN PerOrg.tbOrganizationUnit
				ON PerKar.tbLocations.LocationID = PerOrg.tbOrganizationUnit.LocationID
			INNER JOIN PerOrg.tbOrganizationJob
				ON PerOrg.tbOrganizationUnit.tbUnitID = PerOrg.tbOrganizationJob.tbUnitID
			INNER JOIN (SELECT PersonalID, MAX(StartDate) AS StartDate,
								(SELECT TOP 1 tbOrgJobID
									FROM PerKar.tbOrgJobToPersonel AS tbOrgJobToPersonel_1
									WHERE (PersonalID = jtp.PersonalID)
											AND (StartDate = MAX(jtp.StartDate))) AS tbOrgJobID
							FROM PerKar.tbOrgJobToPersonel AS jtp
							GROUP BY PersonalID) AS tbOrgJobToPersonel
				ON PerOrg.tbOrganizationJob.tbOrgJobID = tbOrgJobToPersonel.tbOrgJobID;

INSERT INTO @tbHokm_
	SELECT	h.tbHokmID,h.PersonalID,h.Rotbeh,h.Tabaghe,h.FatherName,
			h.IssuePlace,h.BirthPlace,h.SanavateKhedmateRasmi,h.OrgJobUnitTitle,h.Childs
		FROM [PerKar].[tbHokm_] AS h
			INNER JOIN (SELECT PersonalID,MAX(tbHokmID) AS tbHokmID,
								MAX(ExecutionDate) AS ExecutionDate
							FROM [PerKar].[tbHokm_]
							GROUP BY PersonalID) AS t2
				ON h.tbHokmID=t2.tbHokmID;

INSERT INTO @tbOrgJob
	SELECT	ojp.PersonalID, oj.JobType, t.Name, oj.OrgJobID, oj.OrgJobTitle
		FROM [PerOrg].[tbOrganizationJob] AS oj
			INNER JOIN [PerKar].[tbOrgJobToPersonel] AS ojp
				ON oj.tbOrgJobID=ojp.tbOrgJobID AND ojp.EndDate IS NULL
			INNER JOIN (SELECT	ci.ItemID, ci.Name
							FROM [PerKar].[tbConstants] AS c
								INNER JOIN [PerKar].[tbConstantItems] AS ci
									ON c.ConstantID=ci.ConstantID
							WHERE c.Name=N'JobType') AS t
				ON t.ItemID=oj.JobType;

;WITH CTE1 AS
(
	SELECT	PerOrg.tbPersonalSpex.PersonalID, PerOrg.tbPersonalSpex.PersonalNum, PerOrg.tbPersonalSpex.FName,
			PerOrg.tbPersonalSpex.LName, PerKar.tbConstantItems.ItemID AS GenreCode,
			PerKar.tbConstantItems.Name AS GenreName, PerOrg.tbPersonalSpex.BCNo, PerOrg.tbPersonalSpex.NationalCode,
			PerKar.fn_ConvertIntToDate(PerOrg.tbPersonalSpex.BirthDate) AS SBirthDate, PerOrg.tbPersonalSpex.BirthDate,
			tbConstantItems_1.ItemID AS MaritalCode, tbConstantItems_1.Name AS MaritalName, vwPerEdu_1.Degree,
			vwPerEdu_1.DegreeName, vwPerEdu_1.EduBranchID, vwPerEdu_1.EduEvidenceDetails,
			PerKar.fn_GetJebheStates(PerOrg.tbPersonalSpex.PersonalID) AS JebheState,
			tbPersonnelEmployState.ItemID AS EmployStateCode, tbPersonnelEmployState.Name AS EmployStateName,
			PerKar.fn_ConvertIntToDate(PerOrg.tbPersonalSpex.EmployDate) AS SEmployDate, PerOrg.tbPersonalSpex.EmployDate,
			PerOrg.tbPersonalSpex.AccountNo,
			(SELECT Title FROM PerKar.tb_EmploymentAllocation
				WHERE Code=LEFT(PerKar.vw_PersonelAllocation.RasteCode,1)) AS RasteAsli,
			PerKar.vw_PersonelAllocation.RasteCode, PerKar.vw_PersonelAllocation.Raste,
			PerKar.vw_PersonelAllocation.ReshteCode, PerKar.vw_PersonelAllocation.Reshte,
			PersonalLocation.LocationID AS MKhedmatID, PersonalLocation.LocationName AS MKhedmat, tbHokm_.tbHokmID,
			tbHokm_.Rotbeh,tbHokm_.Tabaghe, tbHokm_.FatherName,tbHokm_.IssuePlace,tbHokm_.BirthPlace,
			tbHokm_.SanavateKhedmateRasmi,tbHokm_.OrgJobUnitTitle,tbHokm_.Childs,
			PerKar.fn_FindEmploymentYears(PerOrg.tbPersonalSpex.PersonalID, dbo.fn_ConvertHijriDateToDays(@CurrentDate)) AS SanavatKhedmat
			,tbOrgJob.JobType AS JobTypeID, tbOrgJob.Name AS JobTypeName, tbOrgJob.OrgJobID, tbOrgJob.OrgJobTitle
		FROM PerOrg.tbPersonalSpex
			INNER JOIN @tbTmp AS tbTmp
				ON PerOrg.tbPersonalSpex.PersonalID=tbTmp.PersonalID
			INNER JOIN PerKar.tbConstantItems
				ON PerOrg.tbPersonalSpex.Genre = PerKar.tbConstantItems.ItemID AND PerKar.tbConstantItems.ConstantID = 43
			INNER JOIN PerKar.tbConstantItems AS tbConstantItems_1
				ON PerOrg.tbPersonalSpex.MaritalState = tbConstantItems_1.ItemID AND tbConstantItems_1.ConstantID = 5
			INNER JOIN @vwPerEdu_1 AS vwPerEdu_1
				ON PerOrg.tbPersonalSpex.PersonalID = vwPerEdu_1.PersonalID
			INNER JOIN PerKar.vw_PersonelAllocation
				ON PerOrg.tbPersonalSpex.PersonalID = PerKar.vw_PersonelAllocation.PersonalID
			INNER JOIN @tbPersonnelEmployState AS tbPersonnelEmployState
				ON tbPersonnelEmployState.PersonalID = PerOrg.tbPersonalSpex.PersonalID
			INNER JOIN @PersonalLocation AS PersonalLocation
				ON PersonalLocation.PersonalID = PerOrg.tbPersonalSpex.PersonalID
			INNER JOIN @tbHokm_ AS tbHokm_
				ON tbHokm_.PersonalID = PerOrg.tbPersonalSpex.PersonalID
			INNER JOIN @tbOrgJob AS tbOrgJob
				ON tbOrgJob.PersonalID = PerOrg.tbPersonalSpex.PersonalID
)
SELECT	ROW_NUMBER() OVER (ORDER BY PersonalID) AS RowNo, *
	FROM CTE1
	WHERE EmployStateCode=@EmployStateCode;

نتیجه به دست آمده از اجرای این SP بسیار قابل توجه بود، زمان اجرا از ۲۴ ثانیه به کمتر از ۱ ثانیه کاهش پیدا کرد و همچنین تعداد Logical Readها نیز از حدود ۹۹۰۰۰۰۰ عدد به حدود ۴۳۰۰۰ عدد کاهش یافت.

با همین تغییر به ظاهر ساده، می توان مشاهده نمود که زمان اجرا حدود ۹۵ درصد و تعداد Logical Readها نیز حدود ۹۹٫۵ درصد بهبود یافته است.

البته باید توجه داشته باشیم که این بهبودی بخاطر استفاده از ایندکس مناسب نیست(زیرا من اصلا ایندکس ها را بررسی نکردم) بلکه فقط بخاطر تغییر در کد TSQL و چگونگی نوشتن و بکارگیری دستورات TSQL این بهبودی به دست آمد. در واقع در بسیاری از مواقع با درست نوشتن کد TSQL مورد نیاز می توان تا حد زیادی از بروز چنین مشکلاتی جلوگیری کرد.

بابت وقتی که گذاشتید تشکر می کنم

امیدوارم که این مقاله برای دوستان مفید باشد

سیدمحمد حسینی

برچسب ها: Performance SQL Server آموزش SQL آموزش اس کیو ال افزایش سرعت کوئری

25 Comments

مصطفي زماني
۱۳۹۵-۰۴-۲۶ at ۱۱:۱۲ ق.ظ
سلام

با تشكر به خاطر اين مقاله خوب.

ديد خوبي در رابطه با نحوه بررسي خروجي پروفايلر و روند بررسي اسكريپت‌هاي زمان‌بر داد.

پاسخ دادن
ali
۱۳۹۵-۰۴-۲۷ at ۹:۳۳ ب.ظ
سلام

تشكر به خاطر مقاله

پاسخ دادن
مهدی شیشه بری شیشه بری
۱۳۹۵-۰۴-۲۸ at ۱۰:۱۸ ب.ظ
مهندس جان من به این ها کد بد نمی گویم. می گویم لانه مورچه!

پاسخ دادن
حمیدقلیپور
۱۳۹۵-۰۴-۳۰ at ۳:۳۶ ب.ظ
با سلام
آقای حسینی واقعا گل کاشتین؛ مقالتون بسیار خوب بود ، واقعا باید سعی کنیم از نوشتن کدهای نامرتب (کدهای کثیف) پرهیز کنیم، اگر چند تا مقاله در رابطه با دستورات کاربردی Tsql ارائه کنید بسیار عالیه.
سرسبز باشید

پاسخ دادن
1. سیدمحمد حسینی
  ۱۳۹۵-۰۵-۰۲ at ۱۱:۳۴ ب.ظ
  سلام
  
  ممنونم که وقت گذاشتید و خوشحالم که مورد استفاده واقع شد
  
  پاسخ دادن
امین ثریا
۱۳۹۵-۰۴-۳۰ at ۴:۱۸ ب.ظ
این اس پی فو العاده سنگین بود ، قصد توهین ندارم اما بنظر نمیرسه جداول خوب طراحی نشده باشن!

خوشبختانه آقای طاهری tips & triks های جالب و اموزشها و نکات عالی رو در این مورد اراعه دادن که یکی از اونها استفاده از native sp هست البنه نکته ای که شما فرمودید بنظر کاربردی میرسه اما باز ظاهرا یه جای کار میلنگه!

پاسخ دادن
1. سیدمحمد حسینی
  ۱۳۹۵-۰۵-۰۲ at ۱۱:۴۴ ب.ظ
  سلام
  
  ممنونم که وقت گذاشتید
  
  در این که جداول خوب طراحی نشده اند که شکی نیست
  
  در متن عرض شد که این کد مربوط به یکی از دوستان بنده بود
  
  البته این کد خیلی خوب بود(نسبت با سایر کدهایی که بنده دیدم در اون سیستم)
  
  به عنوان مثال یک SP دیگر وجود داشت که اتفاقا از SP های اصلی برنامه بود و در تمامی فرمهای برنامه نیز فراخوانی می شد
  
  نکته جالب این بود که اجرای این SP حدود ۱۲۰ الی ۱۸۰ ثانیه(با توجه به ورودی ها) طول می کشید و برخی از کارمندان اداراتی که با این سیستم کار می کردند می گفتند که “ما تا یک چایی بخوریم فلان فرم باز می شود”(این دوستان دیگه چقدر صبور بودند)
  
  بنده این SP رو که بسیار هم کد طولانی داشت، بررسی و تصحیح کردم(هم با تصحیح کد و هم با استفاده از ایندکس های مناسب و…) و زمان اجرای آن را به کمتر از ۱ ثانیه رساندم
  
  البته بدون استفاده از Native SP
  
  باز هم ممنون که توجه فرمودید
  
  پاسخ دادن
جعفر فتح الهی
۱۳۹۵-۰۵-۰۲ at ۵:۲۴ ب.ظ
بسیار عالی است.

پاسخ دادن
علی موذن صفایی
۱۳۹۵-۰۵-۰۴ at ۱۰:۳۷ ق.ظ
خوندن این کد ها خودش یک پروژه است.

پاسخ دادن
وحید
۱۳۹۵-۰۵-۰۶ at ۱۱:۱۴ ق.ظ
سلام آقای حسینی چرا کوری من اجرا نمیشه لطفا راهنمایی کنید .

SELECT customerID, NAME, FAMILY FROM customer WHERE Name= ‘حسین’

پاسخ دادن
1. مسعود طاهری
  ۱۳۹۵-۰۵-۰۶ at ۶:۰۹ ب.ظ
  سلام کوئری زیر را کپی کنید و در SSMS اجرا کنید به تگ N هم توجه کنید
  
  SELECT customerID, NAME, FAMILY FROM customer WHERE Name= N’حسین’
  
  پاسخ دادن
  1. سیدمحمد حسینی
    ۱۳۹۵-۰۵-۰۶ at ۶:۵۴ ب.ظ
    سلام
    
    با اجازه استاد طاهری عزیز و عذرخواهی از ایشان
    
    ستون Name شما از نوع یونیکد است(NVARCHAR)
    
    به خاطر همین هم هنگامی که در شرط می نویسید ‘حسینی’، این کلمه به صورت غیر یونیکد تفسیر میشود،
    
    برای اصلاح این مشکل همانطور که استاد عزیز فرمودند باید از ‘حسین’N استفاده کنید که در این صورت کلمه مورد نظر شما به صورت یونیکد تفسیر میشود.
وحید
۱۳۹۵-۰۵-۰۸ at ۱:۰۴ ب.ظ
سلام آقای حسینی و مهندس طاهری عزیز از بابت راهنماییتون ممنونم مشکل هم حل شد و عذر خواهی میکنم از مهندس طاهری بابات اینکه اولش متوجه نشدم ssms منظورتون SQL Server من بعد متوجه شدم فقط ای کاش یک ایمل داشتم ازتون که بتونم بعضی از مشکلات که پیش اومده اینجا نمیتونم مطرح کنم باید عکسشنو بفرستم چند روزی هستش فکرمو درگیر خودش کرده ولی به نتیجه نرسیدم ممنون میشم اگه یک ایمیل بگین تا باهاتون ارتباط داشته باشم ..بازم ازتون واقعا تشکر میکنم خسته نباشید. Management Studio

پاسخ دادن
رضوان
۱۳۹۵-۰۵-۱۲ at ۱۱:۵۰ ق.ظ
سلام
اگر به جای استفاده از جداول در عملیات Join از select و انتخاب ستونهای مورد نیازمون استفاده میکردیم تاثیر بهتری داشت؟

پاسخ دادن
1. سیدمحمد حسینی
  ۱۳۹۵-۰۵-۱۸ at ۵:۴۱ ب.ظ
  سلام
  
  تا جایی که از سوال شما متوجه شدم
  
  اگر شما به متن کوئری اصلی، دقت کنید(البته میدونم که خیلی بد نوشته شده و شلوغه)، JOINها همه به صورتی هست که شما میفرمایید، و به همین علت هم کند شده
  
  پاسخ دادن
  1. سیدمحمد حسینی
    ۱۳۹۵-۰۵-۱۸ at ۵:۵۵ ب.ظ
    
    این یه نمونه از کد هست که از وسط کوئری آوردم
    
    ...INNER JOIN (SELECT vwPerEdu.PersonalID, vwPerEdu.PEducationID,vwPerEdu.CollectDate, vwPerEdu.EduAverage,
    
    vwPerEdu.Degree, vwPerEdu.DegreeName,vwPerEdu.EduEvidenceID, vwPerEdu.University,
    
    vwPerEdu.Comments, vwPerEdu.EduBranchID, vwPerEdu.EduEvidenceDetails,
    
    vwPerEdu.EduDegree, vwPerEdu.EBName,vwPerEdu.IsChangeByOthers
    
    FROM PerKar.vwPersonnelEducations AS vwPerEdu
    
    INNER JOIN (SELECT PersonalID,MAX(Degree) AS Degree
    
    FROM PerKar.vwPersonnelEducations
    
    GROUP BY PersonalID) AS PersonnelDegrees
    
    ON vwPerEdu.PersonalID=PersonnelDegrees.PersonalID
    
    AND vwPerEdu.Degree=PersonnelDegrees.Degree) AS vwPerEdu_1
    
    ON PerOrg.tbPersonalSpex.PersonalID = vwPerEdu_1.PersonalID
    
    ...
    
    از این مدل JOIN چندین بار استفاده شده که همین باعث کند شدن عملیات شده
  2. سیدمحمد حسینی
    ۱۳۹۵-۰۵-۱۸ at ۶:۰۰ ب.ظ
    ببخشید کل کد ریخت به هم درحای که من درستش کردم
    
    اگر دقت بفرمایید، فقط در این تکه کد از ویوو PerKar.vwPersonnelEducations دو بار استفاده شده که خودش با خودش JOIN شده(این ویوو خودش شامل حدود ۱۰ جدول هست)
    
    مشابه این مورد زیاد استفاده شده
سید مسعود موحد موحد
۱۳۹۵-۰۵-۲۳ at ۸:۳۵ ق.ظ
بسیار هم عالی فقط خداروشکر کنید که در برابر کد اولیه دوستتون و سازمانشون اصرار فراوا ندارند چون بعضی جاها روی Script های نوشته شده خیلی غیرت دارند و کلا میگن همین کد رو با همین شکل و شمایل تا ۲ روز پیش خوب بوده و ……

پاسخ دادن
اسماعیل یلمه ها
۱۳۹۵-۰۶-۰۱ at ۸:۳۶ ب.ظ
سلام دوست عزیز

خسته نباشید.

سه نکته کوچک درباره کوئری جدیدی که نوشتین:
۱٫ استفاده از Temp Table بسیار بهتر از Table Variable است.
۲٫ بهتر از این دو استفاده از CTE است.
۳٫ بهتر بود از Nested Select ها استفاده نمی کردین.

البته با توجه به شلوغ بودن Procedure آن را مطالعه نکردم اما این سه نکته در باز نویسی آن به نظرم رسید که بنویسم.

پاسخ دادن
محمد جواد پیشوایی
۱۳۹۵-۰۶-۱۳ at ۸:۳۱ ق.ظ
سلام بر همه دوستان بخصوص استاد طاهری و حسینی .
ضمن تشکر من سوال آقای اسماعیل یلمه ها را اینگونه بپرسم که در این رویه ، استفاده از کدامیک بهتر است ؟
Temp Table , Table Variable یا CTE ؟

پاسخ دادن
1. مسعود طاهری
  ۱۳۹۵-۰۶-۱۴ at ۷:۰۱ ق.ظ
  واقعیت این است که سوال خیلی کلی است و با توجه به Case باید جواب داده شود اما این نکته ها را در نظر داشته باشید
  Temp Table و Table Variable ها هر دو در بانک اطلاعاتی TempDB ایجاد می شوند و Worktable برای آنها در نظر گرفته می شود. توجه داشته باشید که برای CTE هم در شرایطی این اتفاق می افتاد مثلا هنگام Spool
  اما در کل شما باید با توجه به شرایط این مورد را در نظر بگیرید
  
  پاسخ دادن
  1. محمد جواد پیشوایی
    ۱۳۹۵-۰۶-۱۴ at ۹:۴۱ ق.ظ
    تشکر میکنم . موفق باشید .
اسماعیل یلمه ها
۱۳۹۵-۰۶-۱۷ at ۵:۰۷ ب.ظ
کاملا با جناب طاهری در این زمینه که بسته به شرایط باید نظر داد موافقم.

اما یک نکته کلی، CTE بسیار سریع و هوشمند است. هوشمندی، یعنی اگر لازم نباشد بخشی از کوئری در جواب شرکت داده شود اصلا محاسبه نمی شود در صورتی که وقتی از temp table یا table variable استفاده می کنید باید خروجی را گرفته و داخل این دو قرار دهید و بعد با آن بازی کنید.

اما اگر داده زیادی دارین و به هر دلیل مجبور هستین از temp table یا table variable استفاده کنید پیشنهاد می شود که از temp table استفاده کنید.

پاسخ دادن
غلامحسین عبادی
۱۳۹۵-۰۶-۲۰ at ۱۲:۳۰ ب.ظ
سلام دوست عزیز . ممنون بابت وقتی که برای این مطلب گذاشتید . سپاس

پاسخ دادن
داوود طاهرخانی طاهرخانی
۱۳۹۷-۰۹-۲۸ at ۸:۴۷ ب.ظ
ممنون خوب بود – اما بهتر بود از جداول موقت استفاده میکردی اگر TempDb تنظیم شده ای داشتی
چون در حجم داده های زیاد جداول متغیر Estimation Row Count متناسبی نشان نمیدهند مگر اخر هر کویری دستور Recompile رو میزاشتی.

پاسخ دادن

درباره نویسنده

سیدمحمد حسینی

25 Comments

مصطفي زماني

ali

مهدی شیشه بری شیشه بری

حمیدقلیپور

سیدمحمد حسینی

امین ثریا

سیدمحمد حسینی

جعفر فتح الهی

علی موذن صفایی

وحید

مسعود طاهری

سیدمحمد حسینی

وحید

رضوان

سیدمحمد حسینی

سیدمحمد حسینی

سیدمحمد حسینی

سید مسعود موحد موحد

اسماعیل یلمه ها

محمد جواد پیشوایی

مسعود طاهری

محمد جواد پیشوایی

اسماعیل یلمه ها

غلامحسین عبادی

داوود طاهرخانی طاهرخانی

ارسال نظر لغو پاسخ

دوره‌های در حال ثبت نام و اجرا

خدمات حرفه‌ای نیک آموز

پُرفروش‌ترین محصولات

Service Broker چیست؟

رادیو نیک آموز: تجربیات ما در مورد Backup/Restore

۷ قابلیت‌ جدید در SQL Server 2016

رادیو نیک آموز: چگونه یک نرم افزار را شبکه کنیم

رادیو نیک آموز: تغییرات Always-on در SQL Server 2016

نیک‌آموز TV [قسمت پانزدهم] نوشتن یک Mobile App با چهار زبان برنامه نویسی