راهنمایی برای نوشتن کوئری بهینه

[nafisehk]

سلام

چطور این کوئری و به شکل بهینه بنویسم؟؟

SELECT ui.UsrID, ui.UsrName, ui.UsrType, ui.UsrFirstName, ui.UsrLastName, ui.UsrAbbreviation, ui.UsrEmail, ui.UsrTelNumbers, ui.UsrAddress, '' AS 'RolName',

'' AS 'a'

FROM UserInfo ui

WHERE ui.UsrID NOT IN

(SELECT ui.UsrID

FROM dbo.UserInfo AS ui LEFT OUTER JOIN

dbo.UserRole AS ur ON ui.UsrID = ur.UserID LEFT OUTER JOIN

dbo.RoleInfo AS ri ON ur.RoleID = ri.RolID

WHERE (ui.UsrType = 'WN' OR

ui.UsrType = 'MN') AND (ur.RecordDeleted IS NULL OR

ur.RecordDeleted = 0) AND (ui.RecordDeleted <> 1)) AND (ui.UsrType = 'WN' OR

ui.UsrType = 'MN') AND ui.RecordDeleted <> 1



UNION


SELECT ui.UsrID, ui.UsrName, ui.UsrType, ui.UsrFirstName, ui.UsrLastName, ui.UsrAbbreviation, ui.UsrEmail, ui.UsrTelNumbers, ui.UsrAddress, ri.RolName,

ur.RecordDeleted AS a

FROM dbo.UserInfo AS ui LEFT OUTER JOIN

dbo.UserRole AS ur ON ui.UsrID = ur.UserID LEFT OUTER JOIN

dbo.RoleInfo AS ri ON ur.RoleID = ri.RolID

WHERE (ui.UsrType = 'WN' OR

ui.UsrType = 'MN') AND (ur.RecordDeleted IS NULL OR

ur.RecordDeleted = 0) AND (ui.RecordDeleted <> 1)

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

سلام.
یکی از راههای Tune کردن یک Query دیدن Plan و statistics اون Query هست.
برای اینکار قبل از اجرای کوئری دستور Set statistics io on رو اجرا کنید و Execution Planر و انتخاب کنید واون اطلاعات رو اینجا قرار بدید.

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

اگر اشتباه نکرده باشم باید کوئری که در ادامه قرار داده شده معادل کوئری که قرار دادین باشه با این تفاوت که بهینه تر هست.
پیشنهاد میکنم کمی راجب Outer Join و مبحث Missing Values مطالعه داشته باشید.


SELECT ui.UsrID, ui.UsrName, ui.UsrType, ui.UsrFirstName,
ui.UsrLastName, ui.UsrAbbreviation, ui.UsrEmail,
ui.UsrTelNumbers, ui.UsrAddress,
CASE WHEN ur.useris is null then '' else ri.RolName end AS 'RolName',
case when ur.userid is null then '' else ur.RecordDeleted end AS 'a'
FROM UserInfo ui
LEFT OUTER JOIN
dbo.UserRole AS ur ON ui.UserID = ur.UserID LEFT OUTER JOIN
dbo.RoleInfo AS ri ON ur.RoleID = ri.RolID
WHERE
ui.UsrType IN ('WN', 'MN')
AND ui.RecordDeleted <> 1
AND (ur.RecordDeleted IS NULL OR ur.RecordDeleted = 0);

[nafisehk]

اگر اشتباه نکرده باشم باید کوئری که در ادامه قرار داده شده معادل کوئری که قرار دادین باشه با این تفاوت که بهینه تر هست.
پیشنهاد میکنم کمی راجب Outer Join و مبحث Missing Values مطالعه داشته باشید.


SELECT ui.UsrID, ui.UsrName, ui.UsrType, ui.UsrFirstName,
ui.UsrLastName, ui.UsrAbbreviation, ui.UsrEmail,
ui.UsrTelNumbers, ui.UsrAddress,
CASE WHEN ur.useris is null then '' else ri.RolName end AS 'RolName',
case when ur.userid is null then '' else ur.RecordDeleted end AS 'a'
FROM UserInfo ui
LEFT OUTER JOIN
dbo.UserRole AS ur ON ui.UserID = ur.UserID LEFT OUTER JOIN
dbo.RoleInfo AS ri ON ur.RoleID = ri.RolID
WHERE
ui.UsrType IN ('WN', 'MN')
AND ui.RecordDeleted <> 1
AND (ur.RecordDeleted IS NULL OR ur.RecordDeleted = 0);

سلام
این کوئری که شما نوشتید همون بخش دوم کوئری خودم هستش
تو این کوئری این کاربرهارو نمیاره
1- کاربری تو جدول واسط(userrole) رکوردش recordeleted خورده باشه
2-کاربری که Role نداشته باشه


من تو کوئری که تو پست اول گذاشتم از دو بخش مشابه استفاده کردم که میخوام بهینش کنم

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

عرض کردم برای بهینه کردن یک Query به اون مواردی که گفتم نیاز هست.تا اونها نباشه با تغییر کد تغییر آنچنانی در بازدهی شما صورت نمیگیره.
چون شما نمیدونید SQL Server‌چه رفتاری داره از خودش نشون میده.

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

سلام
این کوئری که شما نوشتید همون بخش دوم کوئری خودم هستش
تو این کوئری این کاربرهارو نمیاره
1- کاربری تو جدول واسط(userrole) رکوردش recordeleted خورده باشه
2-کاربری که Role نداشته باشه


من تو کوئری که تو پست اول گذاشتم از دو بخش مشابه استفاده کردم که میخوام بهینش کنم

چیزی که من از کوئری مورد نظر برداشت کردم این بود که شما سعی در این داشتید که هم سطرهایی که در گزاره ی شما Match نمی شوند (قسمت NOT IN) را بدست آورید و هم آنهایی که Match شده اند با این تفاوت که برای سطرهای از نوع اول، مقدار آن دو ستون مذکور را برابر با تهی ('') بگذارید. آیا غیر از این است؟
به همین دلیل بوده که دو کوئری مجزا و سوا از هم نوشتید و آن دو را با یکدیگر توسط Union اجتماع کردید.

در حالی که پیشنهاد من این بود که بر اساس برداشتی که دارم نیاز به دو کوئری مجزا و سوا از هم نیست (چون Left Outer Join برای همین کار ارائه شده است) و از طرفی منظور شما را از استفاده از Left Outer Join در جای جای کوئری تان را متوجه نمی شوم. ضمن اینکه در اولین کوئری، در subquery که بعد از NOT IN آمده مجدد جدول UserInfo آمده است که در ظاهر نیاز به ارجاع مجدد آن نیست.

[nafisehk]

آنهایی که Match شده اند

--> رکوردایی که تو جدول واسط ایدشون نیست و یا تو جدول واسط هست اما فیلد recorddeleted اون true هستش(role که قبلا داشتند Delete شده) نمیاره


سطرهایی که در گزاره ی شما Match نمی شوند
برای بدست اوردن این سطرها -->همه سطرها رو منهای match شده ها کردم(macht نشده ها بدست میاد) حالا با Match شده ها جمعش کردم و چون Rol ندارند مقدارشون رو خالی در نظر گرفتم

[nafisehk]

عرض کردم برای بهینه کردن یک Query به اون مواردی که گفتم نیاز هست.تا اونها نباشه با تغییر کد تغییر آنچنانی در بازدهی شما صورت نمیگیره.
چون شما نمیدونید SQL Server‌چه رفتاری داره از خودش نشون میده.

سلام فایل و عکس و attach نمیشه :افسرده:

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

برای attach کردن اونها رو rar کنید بعد ارسال کنید.

[nafisehk]

برای attach کردن اونها رو rar کنید بعد ارسال کنید.

executaation paln

113040

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

Index زیر رو ایجاد کنید.
در ضمن گفتم قبل از اجرای Query دستور Set statistics io on رو بنویسید و اینجا بذارید و بعد از اینکه Index رو نیز ایجاد کردید هم Plan رو بذارید هم نتیجه اون دستور رو. که بشه وضعیت قبل و بعد رو تست کرد.

Create NonClustered Index Ix_UserRole_Uid_RecDel_RoleId on UserRole(UserId,RoleId,RecordDeleted)

در Plan شما وقتی Clustered Index Scan دارید یعنی از Index مناسبی استفاده نمیشه و کل Page های Index شما جستجو میشه. اگر از ایندکس درستی استفاده میشه اینها باید به Index Seek تبدیل بشه.

حالا قبل از اجرای این دستور حتما نتیجه statistics رو بذارید بعد ایندکس رو ایجاد کنید هم Plan و هم Statistics رو قرار بدید.