اجرای دو select در یک Procedure

[spicirmkh]

سلام

فرض کنید در یک Procedure تعداد 2 تا دستور select داریم select اول از بین یک میلیون رکورد 50 انتخاب می کند

و حال می خواهیم از نتیجه Select اولی رکورد که این شرط دارد انتخاب کنیم

با تشکر

[Reza_Yarahmadi]

یا بصورت Select تودرتو بنویسید یا در صورت استفاده از SQL Server 2005 به بعد از With استفاده کنید

Select
*
From
(Select
*
From
tbl
Where
...) t
Where
t.Id > 200

With Result1 AS
(Select
*
From
tbl
Where
...
)
Select
*
From
Result1 r
Where
r.Id > 200

[spicirmkh]

با سلام

من از روش دوم استفاده کرده ام

در SP اول یک عمل Fulltext انجام می دهد زمانی کمنر 2 ثانیه انجام می شود
در SP دوم یک تایع صدا زده می شود که حاصل یک مقدار برگشتی ID است زمان انجام کمتر از ثانیه است

حال ترکیب ابن 2 تا SP به روش دوم خیلی طول می کشد یعنی 35 ثانیه
راه حل بهتری وجود دارد

با تشکر

[spicirmkh]

سلام

من 2 تا دستور Select دارم و از روش دوم استفاده کرده ام

هر select به تنهائی کمتر از 1 ثانیه عمل می کند اما ترکیب بیش از 24 ثانیه ؛ Plan آنرا ضمیمه می کنم لطف کنید مشکل برنامه از کجا است

با تشکر

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

سلام.
یک ایندکس روی جدول tblstdt روی فیلد stdt بسازید و کوئری رو دوباره اجرا کنید.plan اونو ضمیمه کنید.

[spicirmkh]

ایندکس ساختم زمان زیادتر شد plan و کد برنامه گذاشتم




With Result1 AS(select PaperID,stdT.PubID,StdFullNumber=PubTitle+Space(1) + PNo, Chg, PaperTITLE,PYear,stdT.masterFolder,FolderName, NameImage,Notes,SSort
FROM TblStdA stdA
JOIN TblStdT StdT ON stdA.PubID=StdT.PubID
WHERE 1 = 1 and Noact<>1 ANDCONTAINS(SSort,'"ASTM"')
)
Select*From Result1 r
Where r.PaperID = LisSPIC.dbo.fn_StdLicense_Ydoc(PaperID,40)
orderby r.SSort,r.Pyear desc, r.Chg

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

سلام.
میتوانید نمونه دیتابیس رو هم ارسال کنید.؟

[spicirmkh]

سلام.
میتوانید نمونه دیتابیس رو هم ارسال کنید.؟
حجم اطلاعات زیاد است سعی می کنم چند رکوردبدهم مشکلی که است روی اطلاعات کم خوب جواب می دهد

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

اشکال نداره شما با اطلاعات کم ارسال کنید که بشه این کوئری رو اجرا کرد.

[spicirmkh]

اجازه نمی دهد فایل backupشده بدهم حجم اش زیاد با اینکه حدود 1 میلیون رکورد خذف کرده ام

راهی است نمونه کمی از رکورد بدهم

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

شما فایل bACKUP رو zIP کنید و در جایی Upload کنید و لینک اونو قرار بدید

[spicirmkh]

شما فایل bACKUP رو zIP کنید و در جایی Upload کنید و لینک اونو قرار بدید

فایل zip شده کجا می توانم Upload کنم سایتی می شناسید

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

سایتهای Rapidshare,4share,megaupload,hotfile.

[spicirmkh]

سایتهای Rapidshare,4share,megaupload,hotfile.

دیتابیس اول
تعداد رکورد اصلی 321321 رکورد که این نمونه 101 رکورد
http://hotfile.com/dl/91224663/a205242/standard.rar.html

دیتا بیس دوم
تعداد رکورد اصلی 665017 رکورد که این نمونه 395 رکورد

http://hotfile.com/dl/91227199/b8892a4/License.rar.html

[AminSobati]

سلام دوست عزیزم،
من Plan رو بررسی کردم. با توجه به نداشتن شرطی که بتونه تعداد رکوردها رو کاهش بده، لذا Scan کردن جدول tblStdA اجتناب ناپذیره. همونطور که در Plan دیده میشه، 95 درصد از کل Cost صرف این Scan شده.
لطفا قبل از اجرای Query، دستور set statistics io on رو اجرا کنین و بعد Query. در قسمت Messages آمار IO بدست میاد، لطفا پست کنید

[spicirmkh]

سلام دوست عزیزم،
من Plan رو بررسی کردم. با توجه به نداشتن شرطی که بتونه تعداد رکوردها رو کاهش بده، لذا Scan کردن جدول tblStdA اجتناب ناپذیره. همونطور که در Plan دیده میشه، 95 درصد از کل Cost صرف این Scan شده.
لطفا قبل از اجرای Query، دستور set statistics io on رو اجرا کنین و بعد Query. در قسمت Messages آمار IO بدست میاد، لطفا پست کنید

در قسمت Messages :

(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblStdA'. Scan count 1, logical reads 13501, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblStdT'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)


خود Query :


With

Result1 AS(select PaperID,stdT.PubID,StdFullNumber=PubTitle+Space(1) + PNo, Chg, PaperTITLE,PYear,stdT.masterFolder,FolderName, NameImage,Notes,SSort

FROM TblStdA stdA
JOIN TblStdT StdT ON stdA.PubID=StdT.PubID
WHERE 1 = 1 and Noact<>1 ANDCONTAINS(SSort,'"ASTM"')
)
Select*From Result1 r
Where r.PaperID = LisSPIC.dbo.fn_StdLicense_Ydoc(PaperID,40)
orderby r.SSort,r.Pyear desc, r.Chg


و فایل Plan ضمیمه کردم

[spicirmkh]

سلام ، منتظر پاسخ اساتید هستم با تشکر

[AminSobati]

تعداد Read روی جدول 'tblStdA' بالاست. متن تابع fn_StdLicense_Ydoc رو میتونین پست کنین؟ ضمنا کدام قسمت کوئری رو میفرمایید سریع اجرا میشه؟

[spicirmkh]

تعداد Read روی جدول 'tblStdA' بالاست. متن تابع fn_StdLicense_Ydoc رو میتونین پست کنین؟ ضمنا کدام قسمت کوئری رو میفرمایید سریع اجرا میشه؟

متن تابع fn_StdLicense_Ydoc



CREATEFUNCTION [dbo].[fn_StdLicense_Ydoc]( @DocID int, @CustomerID int)
RETURNSINT
WITHENCRYPTION
AS
BEGIN


DECLARE @Result INT=NULL;

SELECT @Result = PaperID FROM dbo.TblOrganLicense
JOIN StdSPIC..tblStdA tblStdA ON tblStdA.PubID=LicenseOID
WHERE PaperID=@DocID AND LicenseCID=@CustomerID AND
LicenseCID>15 ANDCAST(PYear ASINT)<CAST(LicenseYear ASINT)

-- NOT FOUND IN Organization --
IF @Result ISNULL
BEGIN
SELECT @Result = LicenseID FROM dbo.TblStdLicense
WHERE LicenseID= @DocID AND LicenseCID=@CustomerID AND LicenseID>15
END

RETURN @Result
END



این مجموعه شامل 3 تا database است یکی مقالات مجلات و License و مشتریان که در بانک License دو تا پارامتر ID ذخیره می شود (ID مشتریان و مقالات )

برنامه ابتدا یک عمل FullText در مقالات مجلات انجام می دهد بعد نتیجه می خواهد را در تابع که این مقاله مشتری خریده است یا نه

این پروسیجر دارای یک دستور select است که Fulltext انجام می دهد بعد حاصل عمل select به یک تابع فراخوانی می شود ( یعنی دوتا ID عمل دستور select به تابع داده می شود داخل تابع یک عمل select است و حاصل برگشت یک ID دیگر است که با ID اصلی select مقایسه می شود )

به تنهائی یعنی Select اولی و تابع جداگانه سرعت خوبی دارند وقتی ترکیب می شوند سرعت پائین است

با تشکر فراوان

[AminSobati]

خوب مشکلاتی اینجا دیده میشه...
- تابعی که داخلش Join یا کوئری مجدد استفاده میشه، Planش با کوئری بیرونی ادغام نمیشه. به عبارت دیگه به ازای هر رکورد بیرونی، Join داخل تابع دوباره اجرا میشه
- بکار گیری تابع روی فیلد مورد جستجو مانع استفاده از ایندکس میشه. مثلا Cast روی PYear

پیشنهاد میکنم با ترفندهای TSQL کد تابع رو به شکلی تغییر بدین که بشه بعنوان یک SubQuery در کوئری اصلی استفاده کرد.

[spicirmkh]

خوب مشکلاتی اینجا دیده میشه...
- تابعی که داخلش Join یا کوئری مجدد استفاده میشه، Planش با کوئری بیرونی ادغام نمیشه. به عبارت دیگه به ازای هر رکورد بیرونی، Join داخل تابع دوباره اجرا میشه
- بکار گیری تابع روی فیلد مورد جستجو مانع استفاده از ایندکس میشه. مثلا Cast روی PYear

پیشنهاد میکنم با ترفندهای TSQL کد تابع رو به شکلی تغییر بدین که بشه بعنوان یک SubQuery در کوئری اصلی استفاده کرد.

سلام

با تشکر از جواب منطقی و زیبا شما

شما فرمودید که : پیشنهاد میکنم با ترفندهای TSQL کد تابع رو به شکلی تغییر بدین که بشه بعنوان یک SubQuery در کوئری اصلی استفاده کرد.

من چه ترفندی TSQl در این برنامه ببندم لطفا با مثال بفرمایید

[AminSobati]

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



select ISNULL(
(SELECT PaperID FROM dbo.TblOrganLicense
JOIN StdSPIC..tblStdA tblStdA ON tblStdA.PubID=LicenseOID
WHERE PaperID=@DocID AND LicenseCID=@CustomerID AND
LicenseCID>15 AND CAST(PYear AS INT)<CAST(LicenseYear AS INT))
,
(SELECT LicenseID FROM dbo.TblStdLicense
WHERE LicenseID= @DocID AND LicenseCID=@CustomerID AND LicenseID>15)
)