در این مقاله، قصد دارم یکی از اتفاقاتی که چند روز قبل برایم پیش آمد را برا ی دوستان نیک آموزی عزیز توضیح بدهم.
[sql]
[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 [/sql]
همانطور که مشاهده می کنید، یک SP بزرگ و شلوغ(چشم دوستان کد بد نبینه!!!، البته کلی طول کشید تا کد این SP رو مرتبش کردم تا بتونم بخونمش-همه چیز پشت سر هم نوشته شده بود، با نام گذاری ها هم کاری ندارم)






[sql] ALTER PROCEDURE [PerKar].[sp_rep_Personels2] DECLARE @vwPerEdu_1 TABLE (PersonalID int, PEducationID int, CollectDate int, EduAverage nchar(6), 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), DECLARE @tbOrgJob TABLE(PersonalID int PRIMARY KEY, JobType tinyint, Name nvarchar(50), INSERT INTO @tbTmp INSERT INTO @vwPerEdu_1 INSERT INTO @tbPersonnelEmployState INSERT INTO @PersonalLocation INSERT INTO @tbHokm_ INSERT INTO @tbOrgJob ;WITH CTE1 AS |


25 دیدگاه
مصطفي زماني
سلام
با تشکر به خاطر این مقاله خوب.
دید خوبی در رابطه با نحوه بررسی خروجی پروفایلر و روند بررسی اسکریپتهای زمانبر داد.
ali
سلام
تشکر به خاطر مقاله
مهدی شیشه بری شیشه بری
مهندس جان من به این ها کد بد نمی گویم. می گویم لانه مورچه!
حمیدقلیپور
با سلام
آقای حسینی واقعا گل کاشتین؛ مقالتون بسیار خوب بود ، واقعا باید سعی کنیم از نوشتن کدهای نامرتب (کدهای کثیف) پرهیز کنیم، اگر چند تا مقاله در رابطه با دستورات کاربردی Tsql ارائه کنید بسیار عالیه.
سرسبز باشید
سیدمحمد حسینی
سلام
امین ثریا
این اس پی فو العاده سنگین بود ، قصد توهین ندارم اما بنظر نمیرسه جداول خوب طراحی نشده باشن!
سیدمحمد حسینی
سلام
جعفر فتح الهی
بسیار عالی است.
علی موذن صفایی
خوندن این کد ها خودش یک پروژه است.
وحید
سلام آقای حسینی چرا کوری من اجرا نمیشه لطفا راهنمایی کنید .
SELECT customerID, NAME, FAMILY FROM customer WHERE Name= ‘حسین’
مسعود طاهری
SELECT customerID, NAME, FAMILY FROM customer WHERE Name= N’حسین’
سیدمحمد حسینی
سلام
وحید
سلام آقای حسینی و مهندس طاهری عزیز از بابت راهنماییتون ممنونم مشکل هم حل شد و عذر خواهی میکنم از مهندس طاهری بابات اینکه اولش متوجه نشدم ssms منظورتون SQL Server من بعد متوجه شدم فقط ای کاش یک ایمل داشتم ازتون که بتونم بعضی از مشکلات که پیش اومده اینجا نمیتونم مطرح کنم باید عکسشنو بفرستم چند روزی هستش فکرمو درگیر خودش کرده ولی به نتیجه نرسیدم ممنون میشم اگه یک ایمیل بگین تا باهاتون ارتباط داشته باشم ..بازم ازتون واقعا تشکر میکنم خسته نباشید. Management Studio
رضوان
سلام
اگر به جای استفاده از جداول در عملیات Join از select و انتخاب ستونهای مورد نیازمون استفاده میکردیم تاثیر بهتری داشت؟
سیدمحمد حسینی
سلام
سیدمحمد حسینی
...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.IsChangeByOthersFROM
PerKar.vwPersonnelEducations
AS
vwPerEdu
INNER
JOIN
(
SELECT
PersonalID,
MAX
(Degree)
AS
Degree
FROM
PerKar.vwPersonnelEducations
GROUP
BY
PersonalID)
AS
PersonnelDegrees
ON
vwPerEdu.PersonalID=
PersonnelDegrees.PersonalIDAND
vwPerEdu.Degree=
PersonnelDegrees.Degree)
AS
vwPerEdu_1
ON
PerOrg.tbPersonalSpex.PersonalID = vwPerEdu_1.PersonalID
...
از این مدل JOIN چندین بار استفاده شده که همین باعث کند شدن عملیات شده
سیدمحمد حسینی
ببخشید کل کد ریخت به هم درحای که من درستش کردم
سید مسعود موحد
بسیار هم عالی فقط خداروشکر کنید که در برابر کد اولیه دوستتون و سازمانشون اصرار فراوا ندارند چون بعضی جاها روی Script های نوشته شده خیلی غیرت دارند و کلا میگن همین کد رو با همین شکل و شمایل تا 2 روز پیش خوب بوده و ……
اسماعیل یلمه ها
سلام دوست عزیز
خسته نباشید.
سه نکته کوچک درباره کوئری جدیدی که نوشتین:
1. استفاده از Temp Table بسیار بهتر از Table Variable است.
2. بهتر از این دو استفاده از CTE است.
3. بهتر بود از Nested Select ها استفاده نمی کردین.
البته با توجه به شلوغ بودن Procedure آن را مطالعه نکردم اما این سه نکته در باز نویسی آن به نظرم رسید که بنویسم.
محمد جواد پیشوایی
سلام بر همه دوستان بخصوص استاد طاهری و حسینی .
ضمن تشکر من سوال آقای اسماعیل یلمه ها را اینگونه بپرسم که در این رویه ، استفاده از کدامیک بهتر است ؟
Temp Table , Table Variable یا CTE ؟
مسعود طاهری
واقعیت این است که سوال خیلی کلی است و با توجه به Case باید جواب داده شود اما این نکته ها را در نظر داشته باشید
Temp Table و Table Variable ها هر دو در بانک اطلاعاتی TempDB ایجاد می شوند و Worktable برای آنها در نظر گرفته می شود. توجه داشته باشید که برای CTE هم در شرایطی این اتفاق می افتاد مثلا هنگام Spool
اما در کل شما باید با توجه به شرایط این مورد را در نظر بگیرید
محمد جواد پیشوایی
تشکر میکنم . موفق باشید .
اسماعیل یلمه ها
کاملا با جناب طاهری در این زمینه که بسته به شرایط باید نظر داد موافقم.
اما یک نکته کلی، CTE بسیار سریع و هوشمند است. هوشمندی، یعنی اگر لازم نباشد بخشی از کوئری در جواب شرکت داده شود اصلا محاسبه نمی شود در صورتی که وقتی از temp table یا table variable استفاده می کنید باید خروجی را گرفته و داخل این دو قرار دهید و بعد با آن بازی کنید.
اما اگر داده زیادی دارین و به هر دلیل مجبور هستین از temp table یا table variable استفاده کنید پیشنهاد می شود که از temp table استفاده کنید.
غلامحسین عبادی
سلام دوست عزیز . ممنون بابت وقتی که برای این مطلب گذاشتید . سپاس
داوود طاهرخانی
ممنون خوب بود – اما بهتر بود از جداول موقت استفاده میکردی اگر TempDb تنظیم شده ای داشتی
چون در حجم داده های زیاد جداول متغیر Estimation Row Count متناسبی نشان نمیدهند مگر اخر هر کویری دستور Recompile رو میزاشتی.