SQL Performance

[Saeed.Elmi]

سلام.
من لازم دازم یک Query بنویسم که پارامتر های آن پویا باشد. این Query را به دو گونه زیر نوشتم و می خواستم از لحاظ Performance بدانم که کدام کارایی بالاتری دارند
چون referenceی برای ساختار و ساختمان SQLServer2005 در دسترس نداشتم.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [Users].[Select_UserInfo]
@ID uniqueidentifier ,
@UserType tinyint ,
@RelationID uniqueidentifier ,
@Name nvarchar(50) ,
@Family nvarchar(50) ,
@SelectType TinyInt
AS
BEGIN
SET NOCOUNT ON;
Begin Try
Declare @SQL nvarchar(Max), @SQLWhere nvarchar(Max)
Set @SQLWhere='';
Set @SQL='Select '
if (@SelectType=1)
Set @SQL='
Users_UserInfo.ID,
Users_UserInfo.UserName ,
Users_UserInfo.UserType ,
Users_UserInfo.[Name] As Users_UserInfo_Name,
Users_UserInfo.Family ,
Users_UserInfo.Phone ,
Users_UserInfo.MobileNo ,
Users_UserInfo.Email ,
Users_UserInfo.AreaID ,
BaseInfo_Places_AreaID.[Name] As BaseInfo_Places_AreaID_Name,
Users_UserInfo.StreetID ,
BaseInfo_Places_StreetID.[Name] As BaseInfo_Places_StreetID_Name,
Users_UserInfo.SubStreetID ,
BaseInfo_Places_SubStreetID.[Name] As BaseInfo_Places_SubStreetID_Name,
Users_UserInfo.Number ,
Users_UserInfo.Active ,
Users_UserInfo.ExpirationDate'
Else
Set @SQL='
Users_UserInfo.ID
, Users_UserInfo.Name
, Users_UserInfo.Family
, Users_UserInfo.FatherName
, Users_UserInfo.IDNo
, Users_UserInfo.IDCode
, Users_UserInfo.PostalCode
, Users_UserInfo.BirthPlace
, Users_UserInfo.Job
, Users_UserInfo.Phone
, Users_UserInfo.MobileNo
, Users_UserInfo.Email
, Users_UserInfo.Web
, Users_UserInfo.MarriageStatus
, Users_UserInfo.AreaID
, BaseInfo_Places_AreaID.[Name] As BaseInfo_Places_AreaID_Name
, Users_UserInfo.StreetID
, BaseInfo_Places_StreetID.[Name] As BaseInfo_Places_StreetID_Name
, Users_UserInfo.SubStreetID
, BaseInfo_Places_SubStreetID.[Name] As BaseInfo_Places_SubStreetID_Name
, Users_UserInfo.Number
, Users_UserInfo.Gender'
Set @SQL='
From
Users.UserInfo Users_UserInfo
inner join BaseInfo.Places BaseInfo_Places_AreaID On BaseInfo_Places_AreaID.ID= Users_UserInfo.AreaID
inner join BaseInfo.Places BaseInfo_Places_StreetID On BaseInfo_Places_StreetID.ID=Users_UserInfo.StreetI D
inner join BaseInfo.Places BaseInfo_Places_SubStreetID On BaseInfo_Places_SubStreetID.ID=Users_UserInfo.SubS treetID'

if(@ID is Not Null)
Set @SQLWhere=@SQLWhere+' And Users_UserInfo.ID='+Cast(@ID as nvarchar(36))

if(@UserType is Not Null)
Set @SQLWhere=@SQLWhere+' And Users_UserInfo.UserType='+Cast(@UserType as nvarchar(3))

if(@RelationID is Not Null)
Set @SQLWhere=@SQLWhere+' And Users_UserInfo.RelationID='+Cast(@RelationID as nvarchar(36))

if(@Name is Not Null)
Set @SQLWhere=@SQLWhere+' And User_Userinfo.Name Like ''%'+@Name'%''';

if(@Family Is Not Null)
Set @SQLWhere=@SQLWhere+' And User_Userinfo.Family ''%'+@Family+'%''';

if @SQLWhere<>''
Set @SQL=@SQL+' Where '+SubString(@SQLWhere,5,Len(@SQLWhere)-4)

Execute SP_ExecuteSQL @SQL
End Try
Begin Catch
RaisError('00001',16,1)
RollBack TransAction
End Catch
END

و کد دوم:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [Users].[Select_UserInfo]
@ID uniqueidentifier ,
@UserType tinyint ,
@RelationID uniqueidentifier ,
@Name nvarchar(50) ,
@Family nvarchar(50) ,
@SelectType TinyInt
AS
BEGIN
SET NOCOUNT ON;
Begin Try
if (@SelectType=1)
Select
Users_UserInfo.ID,
Users_UserInfo.UserName ,
Users_UserInfo.UserType ,
Users_UserInfo.[Name] As Users_UserInfo_Name,
Users_UserInfo.Family ,
Users_UserInfo.Phone ,
Users_UserInfo.MobileNo ,
Users_UserInfo.Email ,
Users_UserInfo.AreaID ,
BaseInfo_Places_AreaID.[Name] As BaseInfo_Places_AreaID_Name,
Users_UserInfo.StreetID ,
BaseInfo_Places_StreetID.[Name] As BaseInfo_Places_StreetID_Name,
Users_UserInfo.SubStreetID ,
BaseInfo_Places_SubStreetID.[Name] As BaseInfo_Places_SubStreetID_Name,
Users_UserInfo.Number ,
Users_UserInfo.Active ,
Users_UserInfo.ExpirationDate
From
Users.UserInfo Users_UserInfo
inner join BaseInfo.Places BaseInfo_Places_AreaID On BaseInfo_Places_AreaID.ID= Users_UserInfo.AreaID
inner join BaseInfo.Places BaseInfo_Places_StreetID On BaseInfo_Places_StreetID.ID=Users_UserInfo.StreetI D
inner join BaseInfo.Places BaseInfo_Places_SubStreetID On BaseInfo_Places_SubStreetID.ID=Users_UserInfo.SubS treetID'
Where Cast(Users_UserInfo.ID as nvarchar(36)) Like '%'+Cast(@ID as nvarcharf(36))
And Cast(Users_UserInfo.UserType as nvarchar(3)) Like '%'+Cast(@UserType as nvarchar(3))
And Cast(Users_UserInfo.RelationID as nvarchar(36)) Like '%'+Cast(@RelationID as nvarchar(36))
And ...
else
Select
Users_UserInfo.ID
, Users_UserInfo.Name
, Users_UserInfo.Family
, Users_UserInfo.FatherName
, Users_UserInfo.IDNo
, Users_UserInfo.IDCode
, Users_UserInfo.PostalCode
, Users_UserInfo.BirthPlace
, Users_UserInfo.Job
, Users_UserInfo.Phone
, Users_UserInfo.MobileNo
, Users_UserInfo.Email
, Users_UserInfo.Web
, Users_UserInfo.MarriageStatus
, Users_UserInfo.AreaID
, BaseInfo_Places_AreaID.[Name] As BaseInfo_Places_AreaID_Name
, Users_UserInfo.StreetID
, BaseInfo_Places_StreetID.[Name] As BaseInfo_Places_StreetID_Name
, Users_UserInfo.SubStreetID
, BaseInfo_Places_SubStreetID.[Name] As BaseInfo_Places_SubStreetID_Name
, Users_UserInfo.Number
, Users_UserInfo.Gender'
From
Users.UserInfo Users_UserInfo
inner join BaseInfo.Places BaseInfo_Places_AreaID On BaseInfo_Places_AreaID.ID= Users_UserInfo.AreaID
inner join BaseInfo.Places BaseInfo_Places_StreetID On BaseInfo_Places_StreetID.ID=Users_UserInfo.StreetI D
inner join BaseInfo.Places BaseInfo_Places_SubStreetID On BaseInfo_Places_SubStreetID.ID=Users_UserInfo.SubS treetID'
Where Cast(Users_UserInfo.ID as nvarchar(36)) Like '%'+Cast(@ID as nvarcharf(36))
And Cast(Users_UserInfo.UserType as nvarchar(3)) Like '%'+Cast(@UserType as nvarchar(3))
And Cast(Users_UserInfo.RelationID as nvarchar(36)) Like '%'+Cast(@RelationID as nvarchar(36))
And ...
End Try
Begin Catch
RaisError('00001',16,1)
RollBack TransAction
End Catch
END


لطفا به این نکته توجه کنید که SP ها کامپایل شده ذخیره می شوند (مثل کد دوم) ولی کد دوم چون تمام دستورات در قالب یک رشته ایجاد شده اند، در زمان اجرا کامپایل می شوند، البته این برداشت من بوده است!!!

اگر این کد ها بررسی کنید خیلی ممنوم می شوم.!

[Saeed.Elmi]

سلام.
نمیدونم سوال من اینقدر سطح پایین بود که حتی یک نفر هم حاضر نیست به دادم برسه!!!!
خواهش می کنم!!:گریه:

[AminSobati]

دوست عزیزم بهترین کار اینه که خودتون Query رو اصطلاحا Parameterize کنین چون جلوی Recompileهای اضافی رو میگیره. در روشی که لینکش رو براتون ارسال کردم، وقتی کاربر فیلدهای مشخصی رو برای جستجو انتخاب کنه، براش یک Exec Plan بدست میاد و اگر کاربر دیگه ای همون فیلدها رو انتخاب کنه، این Plan در حقیقت Resue میشه و Recompile اتفاق نمیافته
http://www.sommarskog.se/dyn-search.html

[Saeed.Elmi]

آقای ثباتی بسیار ممنون و از زحمتی که کشیدید متشکر هستم
ولی Linkی که زحمت کشیده بودین و فرستاده بودین در نهایت هیچ مقایسه صریحی نکرده بود و بر طبق متن همین paper :

Conclusion
You have now seen several ways to implement this kind of searches, both in dynamic SQL and static SQL. You have seen that with dynamic SQL you can get the best performance, while still keeping your code maintainable. With static SQL you have to make compromises to get something which performs decently, and still is maintainable. You have also seen that for static SQL, you need to be creative, and see which tricks that are best for the search you are about to implement. And you have seen how it is possible to combine static SQL with dynamic SQL to almost get the full powers of dynamic SQL, at the cost of violating what is normally good practice.

And let me stress once more that, no matter whether you go for dynamic or static SQL, you should test your procedure for all in input parameters and preferably some combinations too, both for correctness and for performance. And to test the performance, you need data which resembles production data. If you expect to have ten million orders and 50.000 customers in production, you cannot play with a toy database at the size of Northwind or even Northgale.


در ضمن در روش CLR اشتباه کرده بود چون این روش که معرفی کرده بود از تکنولژی CLR استفاده نکرده بود.!!
باز هم خیلی ممنون و اگر reference دیگری برایم بگذارد خیلی خوشحال می شوم.

[AminSobati]

سعید جان تکلیف روشنه! دلیلی برای سر در گم شدن بین روشهای مختلف وجود نداره.
ببینین: شما تصور کنین که اصلا کاربر همیشه با تعداد فیلدهای ثابتی SP رو فراخوانی میکنه و Query شما کاملا از پیش نوشته شده (Static) هستش. پس شکی نیست که مسئله بهینه کردن ایندکسها و خود Query همچنان به قوت خودش باقیه.
از طرفی، در بین روشهای موجود در اون مقاله، چیزی که بیشتر از هر چیزی شما رو به Query ثابت و Static نزدیک میکنه، روش جمع کردن "متغیر حاوی Query" با "شرطهای جدید" هستش. یعنی چیزی که خودتون در روش اول نوشتین رو من انتخاب میکنم. اما آخر کار رو درست انجام ندادین. یعنی این دو روش از نظر Compile شدن دقیقا مثل هم عمل میکنند:


EXECUTE SP_ExecuteSQL @SQL

EXEC (@SQL)

تنها چیزی که Code شما کم داره، Parameterize کردنی هست که در اون مقاله توضیح داده شده ولی شما انجام ندادین. یعنی اگر اون رو هم انجام بدین، شک نکنین که بهترین روش رو در پیش گرفتین!

[Saeed.Elmi]

خیلی خیلی ممنون جناب آقای ثباتی،
حتما اعمال می کنم و نتیجه را گزارش می دهم.

[Saeed.Elmi]

سلام
در paperی که شما ارسال کرده بودید که روش فوق العاده جالب معرفی کرده بود به شرح زیر:

x = @x AND @x IS NOT NULL

جناب ثباتی با اینکه شما قبلا فرموده بودید ولی من فکر می کنم این روش خیلی به Static Query نزدیک تر هست! ولی اگه لطف کنید و یک جواب نهایی به من بدهید که کدام یک از روش های که فرموده بودین دارای performance بهتری است و من نیازی به maintainability ندارم هرچند که فکر می کنم پیچیده تر می شود.
لازم به ذکر است در زمان استفاده از روش ذکر شده تمامی نیازهایم بر طرف شد.
خیلی ممنونم اگه لطف کنید و در زمان کوتاه تری پاسخ دهید.
باز هم تشکر

[Saeed.Elmi]

خواهش می کنم کمکم کنید