طراحی یک کوئری پویا

[csharpcollegian]

عرض سلام و وقت به خیر
دوستان من برای پیاده سازی یک جستجوی ترکیبی رو یکی از جداول دیتابیسم، Stored Procedure زیر رو نوشتم :

CREATE PROCEDURE procPersonnelSearch
@ID int = Null,
@Name nvarchar(50) = Null,
@StartDate datetime = Null,
@EndDate datetime = Null
AS
BEGIN
SET NoCount ON
DECLARE @QUERY varchar(1000)
SELECT @QUERY = ''
IF (@ID IS NOT NULL)
BEGIN
IF (@QUERY = '')
SELECT @QUERY = @QUERY + ' WHERE'
ELSE
SELECT @QUERY = @QUERY + ' AND';
SELECT @QUERY = @QUERY + ' ID = @ID';
END
IF (@Name IS NOT NULL)
BEGIN
IF (@QUERY = '')
SELECT @QUERY = @QUERY + ' WHERE'
ELSE
SELECT @QUERY = @QUERY + ' AND';
SELECT @QUERY = @QUERY + ' Name = @Name';
END
IF (@StartDate IS NOT NULL)
BEGIN
IF (@QUERY = '')
SELECT @QUERY = @QUERY + ' WHERE'
ELSE
SELECT @QUERY = @QUERY + ' AND';
SELECT @QUERY = @QUERY + ' @StartDate = StartDate';
END
SELECT @QUERY = 'Select * From tblPersonnel' + @QUERY
EXEC (@QUERY)
SET NoCount OFF
END
GO

در حالتی که هیچ پارامتری رو مقدار نمیدم، Query درست عمل می کنه و کل رکوردهای جدول رو برمی گردونه
ولی وقتی برای یک پارامتر، مقداری ارسال میکنم خطا میده ! مثلا وقتی برای پارامتر ID@ مقداری میفرستم این خطا رو میگیره :

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@ID"
برای هر پارامتری مقدار بفرستم مشابه همین خطا رو واسه اون پارامتر میگیره...
مشکل از کجاست ؟
خیلی ممنون

[parsdarab]

سلام

متغیر ها را از توی سینگل کوتیشن بیار بیرون

مثلا بنویس
SELECT @QUERY = @QUERY + ' ID = ' + @ID;

[csharpcollegian]

سلام متغیر ها را از توی سینگل کوتیشن بیار بیرون مثلا بنویس SELECT @QUERY = @QUERY + ' ID = ' + @ID;

سلام ممنون از پاسختون ولی وقتی این کار رو میکنم خطای زیر رو میده :

Conversion failed when converting the varchar value ' ID = ' to data type int
انگار میخواد رشته رو تبدیل به integer که همون نوع ID@ هست، بکنه...

[parsdarab]

سلام

این خطا به خاطر اینکه شما می خواهید رشته را به عدد الحاق کنید

به صورت زیر عمل کنید
SELECT @QUERY = @QUERY + ' ID = ' + cast(@ID as nvarchar(10));

[Reza_Yarahmadi]

دستوری که نوشتید بدلیل استفاده از Dynamic Query سربار زیادی روی سرورتون داره
میتونید از روش زیر هم استفاده کنید
CREATE PROCEDURE procPersonnelSearch
@ID int = Null,
@Name nvarchar(50) = Null,
@StartDate datetime = Null,
@EndDate datetime = Null
AS
BEGIN
SELECT * FROM tblPersonnel
WHERE
@ID IS NULL OR ID = @ID
AND
@Name IS NULL OR Name = @Name
AND
@StartDate IS NULL OR StartDate = @StartDate
AND
@EndDate IS NULL OR EndDate = @EndDate

[csharpcollegian]

دستوری که نوشتید بدلیل استفاده از Dynamic Query سربار زیادی روی سرورتون داره
میتونید از روش زیر هم استفاده کنید
CREATE PROCEDURE procPersonnelSearch
@ID int = Null,
@Name nvarchar(50) = Null,
@StartDate datetime = Null,
@EndDate datetime = Null
AS
BEGIN
SELECT * FROM tblPersonnel
WHERE
@ID IS NULL OR ID = @ID
AND
@Name IS NULL OR Name = @Name
AND
@StartDate IS NULL OR StartDate = @StartDate
AND
@EndDate IS NULL OR EndDate = @EndDate

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

[Reza_Yarahmadi]

برای درک بهتر موضوع بهتره اول مراحل اجرای یک کوئری رو ببینیم:
1- در ابتدا کوئری ارسال شده از نظر نحوی بررسی شده تا خطا نداشته باشد.
2- دسترسی ها و شرایط امنیتی بررسی می شود.
3- قسمت بهینه سازی وارد عمل شده و کوئری را بررسی می کند تا بهترین نحوه اجرای دستورات را بیابد.
4- دستور SQL ارسال شده، به دستورات داخلی SQL Server تبدیل می شود (در اصطلاح کامپایل می شود)
5- نسخه کامپایل شده در کش قرار داده می شود.
6- دستور اجرا می شود.
7- داده های خوانده شده به درخواست کننده ارسال می شود.
بعد از اینکه اولین بار کوئری اجرا شد، دیگه مراحل 1-5 اجرا نمیشه، بلکه دستور مستقیما از کش بارگزاری ، اجرا و داده های خونده شده به درخواست کننده تحویل داده میشه.

در داینامیک کوئری هر بار همه مراحل باید انجام بشه و این سربار زیادی روی سرور داره.
چک کردن یک متغیر از نظر اینکه نال هست یا نه، نیازی به اسکن اطلاعات جداول نداره و با سرعت بالایی این کار انجام میشه.

[csharpcollegian]

برای درک بهتر موضوع بهتره اول مراحل اجرای یک کوئری رو توضیح ببینیم:
1- در ابتدا کوئری ارسال شده از نظر نحوی بررسی شده تا خطا نداشته باشد.
2- دسترسی ها و شرایط امنیتی بررسی می شود.
3- قسمت بهینه سازی وارد عمل شده و کوئری را بررسی می کند تا بهترین نحوه اجرای دستورات را بیابد.
4- دستور SQL ارسال شده، به دستورات داخلی SQL Server تبدیل می شود (در اصطلاح کامپایل می شود)
5- نسخه کامپایل شده در کش قرار داده می شود.
6- دستور اجرا می شود.
7- داده های خوانده شده به درخواست کننده ارسال می شود.
بعد از اینکه اولین بار کوئری اجرا شد، دیگه مراحل 1-5 اجرا نمیشه، بلکه دستور مستقیما از کش بارگزاری ، اجرا و داده های خونده شده به درخواست کننده تحویل داده میشه.

در داینامیک کوئری هر بار همه مراحل باید انجام بشه و این سربار زیادی روی سرور داره.
چک کردن یک متغیر از نظر اینکه نال هست یا نه، نیازی به اسکن اطلاعات جداول نداره و با سرعت بالایی این کار انجام میشه.

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

[ASKaffash]

سلام
به نظر شما مراحل 1 تا 5 بیش از یک ثانیه است ؟

برای درک بهتر موضوع بهتره اول مراحل اجرای یک کوئری رو ببینیم:
1- در ابتدا کوئری ارسال شده از نظر نحوی بررسی شده تا خطا نداشته باشد.
2- دسترسی ها و شرایط امنیتی بررسی می شود.
3- قسمت بهینه سازی وارد عمل شده و کوئری را بررسی می کند تا بهترین نحوه اجرای دستورات را بیابد.
4- دستور SQL ارسال شده، به دستورات داخلی SQL Server تبدیل می شود (در اصطلاح کامپایل می شود)
5- نسخه کامپایل شده در کش قرار داده می شود.
6- دستور اجرا می شود.
7- داده های خوانده شده به درخواست کننده ارسال می شود.
بعد از اینکه اولین بار کوئری اجرا شد، دیگه مراحل 1-5 اجرا نمیشه، بلکه دستور مستقیما از کش بارگزاری ، اجرا و داده های خونده شده به درخواست کننده تحویل داده میشه.

در داینامیک کوئری هر بار همه مراحل باید انجام بشه و این سربار زیادی روی سرور داره.
چک کردن یک متغیر از نظر اینکه نال هست یا نه، نیازی به اسکن اطلاعات جداول نداره و با سرعت بالایی این کار انجام میشه.

[ASKaffash]

سلام
دوست عزیز در نتیجه گیری عجله نکنید

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

[Iran58]

سلام
دوست عزیز در نتیجه گیری عجله نکنید
سلام
مهندس جان میشه یکمی بیشتر توضیح بدهید و مزایا ومعایب را بیان بفرمایید

[csharpcollegian]

سلام
دوست عزیز در نتیجه گیری عجله نکنید

سلام
من نتیجه گیریم رو از صحبت های جناب یاراحمدی اینجا گذاشتم تا اگر بقیه اساتید نظر دیگه ای دارن مطرح کنند
ممنون میشم اگر نظر دیگه ای دارید توضیح بدید

[ASKaffash]

سلام
من بعد از پاسخ پست 7 به شما با عدد و رقم واقعی موضوع را تشریح و مزایا و معایب را از دیدگاه خودم بیان می کنم

[ASKaffash]

سلام
این موضوع که مراحل Compile یک Dynamic SQL سربار زیادی دارد صحت ندارد و در واقع در بدترین حالت این زمان کمتر از 10 میلی ثانیه است من این موضوع را در شکل پیوست روی یک سرور با کاربران زیاد در یک شبکه محلی در یک سازمان بزرگ کشور با استفاده از ProFiler نسخه SQLServer2008 R2 به تصویر کشیده ام
واما در خصوص مزایا و معایب استفاده از Dynamic SQL می توانم به موارد ذیل اشاره کنم
معایب :
- اگر ایجاد کننده Dynamic SQL موارد پیشگیرانه را مد نظر قرار ندهد امکان حمله SQL Injection را فراهم کرده است
- ایجاد این نوع SQL بعلت عدم تسلط SQLServer به کامپایل احتمالا Syntax Error در دستور SQL را ایجاد می کند (حتما باید تست کامل شود)
- همیشه استفاده از رشته های ثابت در این حالت سخت است و کمی استفاده از کاراکتر ' و یا Char(39) مشکل ساز است
مزایا :
- ایجاد شرایط پویا برای دستور شرطی جلوی Where بسیار مطلوب است که در بسیاری موارد باعث افزایش سرعت استخراج داده ها می شود دقت شود که بکار بردن Or در دستورات شرطی در بسیاری موارد Optimizer را برای انتخاب ایندکس مناسب برای حرکت روی رکوردها دچار اشتباه می کند و خیلی موارد عمل Table Scan اتفاق می افتد و اگر Join هم وجود داشته باشد وضعیت خیلی بدتر است
(این که با Case End عمل شرط پویا را محقق کنیم و از Dynamic SQL فاصله بگیریم در واقع در چند قدمیTable Scan هستیم سایر and ها می توانند کمی کمک کنند)

[Iran58]

سلام
این موضوع که مراحل Compile یک Dynamic SQL سربار زیادی دارد صحت ندارد و در واقع در بدترین حالت این زمان کمتر از 10 میلی ثانیه است من این موضوع را در شکل پیوست روی یک سرور با کاربران زیاد در یک شبکه محلی در یک سازمان بزرگ کشور با استفاده از ProFiler نسخه SQLServer2008 R2 به تصویر کشیده ام
واما در خصوص مزایا و معایب استفاده از Dynamic SQL می توانم به موارد ذیل اشاره کنم
معایب :
- اگر ایجاد کننده Dynamic SQL موارد پیشگیرانه را مد نظر قرار ندهد امکان حمله SQL Injection را فراهم کرده است
- ایجاد این نوع SQL بعلت عدم تسلط SQLServer به کامپایل احتمالا Syntax Error در دستور SQL را ایجاد می کند (حتما باید تست کامل شود)
- همیشه استفاده از رشته های ثابت در این حالت سخت است و کمی استفاده از کاراکتر ' و یا Char(39) مشکل ساز است
مزایا :
- ایجاد شرایط پویا برای دستور شرطی جلوی Where بسیار مطلوب است که در بسیاری موارد باعث افزایش سرعت استخراج داده ها می شود دقت شود که بکار بردن Or در دستورات شرطی در بسیاری موارد Optimizer را برای انتخاب ایندکس مناسب برای حرکت روی رکوردها دچار اشتباه می کند و خیلی موارد عمل Table Scan اتفاق می افتد و اگر Join هم وجود داشته باشد وضعیت خیلی بدتر است
(این که با Case End عمل شرط پویا را محقق کنیم و از Dynamic SQL فاصله بگیریم در واقع در چند قدمیTable Scan هستیم سایر and ها می توانند کمی کمک کنند)
سلام
مهندس جان در مورد


اگر ایجاد کننده Dynamic SQL موارد پیشگیرانه را مد نظر قرار ندهد امکان حمله SQL Injection را فراهم کرده است
میشه بیشتر توضییح بدهید
باتشکر

[csharpcollegian]

سلام
این موضوع که مراحل Compile یک Dynamic SQL سربار زیادی دارد صحت ندارد و در واقع در بدترین حالت این زمان کمتر از 10 میلی ثانیه است من این موضوع را در شکل پیوست روی یک سرور با کاربران زیاد در یک شبکه محلی در یک سازمان بزرگ کشور با استفاده از ProFiler نسخه SQLServer2008 R2 به تصویر کشیده ام
واما در خصوص مزایا و معایب استفاده از Dynamic SQL می توانم به موارد ذیل اشاره کنم
معایب :
- اگر ایجاد کننده Dynamic SQL موارد پیشگیرانه را مد نظر قرار ندهد امکان حمله SQL Injection را فراهم کرده است
- ایجاد این نوع SQL بعلت عدم تسلط SQLServer به کامپایل احتمالا Syntax Error در دستور SQL را ایجاد می کند (حتما باید تست کامل شود)
- همیشه استفاده از رشته های ثابت در این حالت سخت است و کمی استفاده از کاراکتر ' و یا Char(39) مشکل ساز است
مزایا :
- ایجاد شرایط پویا برای دستور شرطی جلوی Where بسیار مطلوب است که در بسیاری موارد باعث افزایش سرعت استخراج داده ها می شود دقت شود که بکار بردن Or در دستورات شرطی در بسیاری موارد Optimizer را برای انتخاب ایندکس مناسب برای حرکت روی رکوردها دچار اشتباه می کند و خیلی موارد عمل Table Scan اتفاق می افتد و اگر Join هم وجود داشته باشد وضعیت خیلی بدتر است
(این که با Case End عمل شرط پویا را محقق کنیم و از Dynamic SQL فاصله بگیریم در واقع در چند قدمیTable Scan هستیم سایر and ها می توانند کمی کمک کنند)

سلام
دوست عزیز بابت وقتی گذاشتین ازتون ممنونم
می خواستم ازتون بخوام که اگر ممکنه نظرتون رو راجع به این تکنیک هم مطرح کنید :

CREATE PROCEDURE procPersonnelSearch
@ID int = Null,
@Name nvarchar(50) = Null,
@StartDate datetime = Null,
@EndDate datetime = Null
AS
BEGIN
SELECT * FROM tblPersonnel
WHERE
@ID IS NULL OR ID = @ID
AND
@Name IS NULL OR Name = @Name
AND
@StartDate IS NULL OR StartDate = @StartDate
AND
@EndDate IS NULL OR EndDate = @EndDate
این تکنیک چون از پارامترها استفاده می کنه در مقابل SQL Injection قوی تره، ممنون میشم اگر یک مقایسه کوچیک بین این تکنیک و تکنیک کوئری داینامیک داشته باشید

[ASKaffash]

سلام
به سه
حالت ذیل نگاه کنید :

1)
Declare@ID VarChar(5)='1'
Declare @Pass VarChar(50)='abc'
Select*
From Users
Where UserID=@ID and UserPass=@Pass

2)
Declare @ID VarChar(5)='1'
Declare @Pass VarChar(50)='abc'
Declare @Cmd VarChar(Max)=''
Set @Cmd=@Cmd+' Select *'
Set @Cmd=@Cmd+' From Users'
Set @Cmd=@Cmd+' Where UserID='+@ID+' and UserPass='+Char(39)+@Pass+Char(39)
Exec(@Cmd)

3)
Declare @ID VarChar(5)='1 --'
Declare @Pass VarChar(50)='abc'
Declare @Cmd VarChar(Max)=''
Set @Cmd=@Cmd+' Select *'
Set @Cmd=@Cmd+' From Users'
Set @Cmd=@Cmd+' Where UserID='+@ID+' and UserPass='+Char(39)+@Pass+Char(39)
Exec(@Cmd)


روش دوم در حالت معمولی مثل روش اول است ولی اگر نفوذگر به جای عدد 1 بتواند 1 -- را وارد کند توانسته حمله اینجکشن انجام دهد و با هر رمزی وارد خواهد شد (البته اگر طور دیگری نوشته شود Injection نخواهد خورد)

[ASKaffash]

سلام
دوست عزیز بابت وقتی گذاشتین ازتون ممنونم
می خواستم ازتون بخوام که اگر ممکنه نظرتون رو راجع به این تکنیک هم مطرح کنید :

CREATE PROCEDURE procPersonnelSearch
@ID int = Null,
@Name nvarchar(50) = Null,
@StartDate datetime = Null,
@EndDate datetime = Null
AS
BEGIN
SELECT * FROM tblPersonnel
WHERE
@ID IS NULL OR ID = @ID
AND
@Name IS NULL OR Name = @Name
AND
@StartDate IS NULL OR StartDate = @StartDate
AND
@EndDate IS NULL OR EndDate = @EndDate
این تکنیک چون از پارامترها استفاده می کنه در مقابل SQL Injection قوی تره، ممنون میشم اگر یک مقایسه کوچیک بین این تکنیک و تکنیک کوئری داینامیک داشته باشید

سلام
بله این دستور Injection نمی خورد ولی چون از Or استفاده کرده در حجم زیاد داده می تواند Optimizer را جهت تعیین ایندکس مناسب با شکست مواجه کند و Table Scan اتفاق افتد (من قبلا این تجربه را داشتم)

[csharpcollegian]

سلام
بله این دستور Injection نمی خورد ولی چون از Or استفاده کرده در حجم زیاد داده می تواند Optimizer را جهت تعیین ایندکس مناسب با شکست مواجه کند و Table Scan اتفاق افتد (من قبلا این تجربه را داشتم)

خیلی خیلی ممنونم دوست عزیز، ببخشید که انقدر سوال میپرسم ولی مشکل این تکنیک فقط OR هستش ؟
یعنی اگر از روش زیر استفاده بشه دیگه مشکلی نداره ؟ و از کوئری داینامیک بهینه تره ؟

CREATE PROCEDURE procPersonnelSearch
@ID int = Null,
@Name nvarchar(50) = Null,
@StartDate datetime = Null,
@EndDate datetime = Null
AS
BEGIN
SELECT * FROM tblPersonnel WHERE
ID = ISNULL(@ID, ID)
AND
Name = ISNULL(@Name, Name)
AND
StartDate = ISNULL(@StartDate, StartDate)
AND
EndDate = ISNULL(@EndDate, EndDate)
END
و در آخر ممنون میشم اگر یک منبع معرفی کنید تا در مورد مفهوم Table Scan توضیح داده باشه، من چیزی راجع بهش نمی دونم
متشکرم

[ASKaffash]

سلام
مفهوم پیمایش جدول : Table Scan هنگامی اتفاق می افتد که یک دستور SQL باعث شود که برای استخراج داده ها SQLSever مجبور شود کل جدول را پیمایش کند (فکر کنید 100 میلیون رکورد وجود دارد) ولی اگر ایندکس مناسب فراهم باشد و تنها قرار است 20 رکورد استخراج شود اختلاف زمانی فاحش است و در مورد موضوع OR تنها Or نیست که باعث Table Scan می شود خیلی موارد می تواند متناسب با دستور SQL شما این عامل را سبب شود در مورد کوئری شما اگر موارد امنیتی را رعایت کنید Dynamic SQL خیلی مناسب است و چک کنید اگر مثلا @ID مقدار ندارد در شرط جلوی Where ظاهر نشود در ضمن ایندکس ID و Name و StartDate و EndDate برای جدول شما مناسب است