لطفا کوئری select بهینه کنید

[Mrs.Net]

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



SELECT gcode, (select top 1 amn1-amn2 from factorrow where code=gcode and stack=gstack and date<={0} order by stack desc, id desc ) as amount
FROM (select code as gcode , stack as gstack from factorrow where date<={0} group by code, stack )

یک جدول دارم factorrow که فیلدهای id, date, code, stack داره
برای قبل از یک date مشخص باید جمع آخرین مقادیر فیلدهای (amn1-amn2) برای هر code, stack بدست بیارم.
یعنی کوئری بالا رکوردهای حاوی amount (یا همون amn1-amn2) برای من بدست میاره و من اونهارو جمع میکنم.
حالا میخوام بهینه تر بشه
یعنی اگه میشد کاری کرد که group گرفتن و استفاده از Top 1 برای انتخاب آخرین رکورد باهم انجام میشد کار تموم بود.

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

سلام.

متاسفانه نفرمودید که از چه نسخه SQL Server استفاده می کنید 2000 یا 2005 به بالا.

بهتر بود چند سطر از جدولتان را برای نمونه ارسال می کردید.


SELECT code,
stack,
amn1-amn2,
Row_num=ROW_NUMBER() OVER(PARTITION BY code, stack ORDER BY date DESC)
FROM (SELECT * FROM factorrow WHERE date<={0}) D
WHERE Row_num=1;

[Mrs.Net]

2005 استفاده میکنم اما باید به شکلی باشه که با اکسس هم جواب بده چون بعضی مشتریهام اکسس
برای جدول زیر مقدار برای date کوچکتر از28 بدست بیارید
که در نهایت آیدیهای روبرو انتخاب میشه: 1, 8, 7, 3, 4
http://tinypic.info/files/dlxs2s3lcpraon91o3td.jpg

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

اگر راه حل خوب و بهتری می خواهید که در SQL server 2005 جواب بدهد من به شما این روش را پیشنهاد می دهم.
ولی باز هم سعی می کنم یک راه حلی پیدا کنم که بدون استفاده از توابع و دستورات خاصی باشد.


SELECT id, dif FROM
(
SELECT id,
code,
stack,
Dif=ABS(amn1-amn2),
Row_num=ROW_NUMBER() OVER(PARTITION BY code, stack ORDER BY date DESC)
FROM (SELECT * FROM factorrow WHERE date<=28) D
) AS D
WHERE Row_num=1





id date code stack amn1 amn2
----------- ----------- ----------- ----------- ----------- -----------
1 24 1 1 1000 50
2 25 1 2 1000 50
3 23 2 2 1111 222
4 27 3 1 3333 4444
5 22 2 1 5555 666
6 20 3 1 4444 222
7 25 2 1 4444 22
8 26 1 2 444 222
9 29 2 1 4444 22
10 22 3 1 5555 55

OUTPUT:
id dif
----------- -----------
1 950
8 222
7 4422
3 889
4 1111

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

یک روش بهتر.
امید وارم این روش در access هم جواب بدهد.



SELECT id,
dif=ABS(amn1-amn2)
FROM
(
SELECT code ,
stack,
Date=MAX(case when date<=28 then date end)
FROM factorrow
group by code ,stack
)D
INNER JOIN factorrow p ON D.code=P.code AND D.stack=P.stack AND D.date=P.date






OUTPUT

id dif
----------- -----------
1 950
8 222
7 4422
3 889
4 1111

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

این روش واقعا باید نیازمندی های شما را بطور کامل پوشش دهد.



SELECT id,
dif=ABS(amn1-amn2)
FROM
(
SELECT code,
stack,
date=max(date)
FROM(
SELECT *
FROM factorrow
WHERE date<=28
) DD
GROUP BY code, stack
) D
INNER JOIN factorrow p ON D.code=P.code AND D.stack=P.stack AND D.date=P.date





id dif
----------- -----------
4 1111
3 889
7 4422
8 222
1 950

(5 row(s) affected)

[Mrs.Net]

ممنون که دارید وقت میگزارید
باید کدهارو امتحان کنم ببینم کدون بهینه تر هست
کدی که تو پست اول دادم برای 4402 رکورد حدود 2:45 دقیقه طول میکشه
و درضمن همیشه باید id چک بشه چون رکوردها تاریخ مشابه هم دارند (گرچه تو مثالم نیست)

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

با استفاده از Actual Execution Plan و SQL Profiler عملکرد چهار Query موجود را با یکدیگر مقایسه کنید و نتیجه را پست کنید تا بدانیم که کدام روش بهترین است از این پس از آن روش استفاده کنیم.

البته به نظر می رسد که روی ستون های code , stack باید یک INDEX گذاشته شود.
به هر حال استفاده از INDEX در سرعت اجرای یک Query واقعا تاثیر گذار می باشد.

[Mrs.Net]

متاسفانه شرط date و id باید باهم باشند

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

من زیاد متوجه منظورتان نمی شوم.
الان این query هایی که برایتان نوشتم جواب مورد نظر را نمی دهند؟
حد اقل می دانم که با داده های نمونه جواب مورد نظر را می دهند!

فکر کنم منظورتان را از "باید id هم چک کند ممکن است date تکراری هم داشته باشیم" این باشد که ممکن است چند سطر، فیلد های code, date, stack برابری داشته باشند، درست است؟

[Mrs.Net]

با داده های نمونه جواب میدن اما مشکلی که هست اینه که
ممکنه دو تا رکورد برای کد 1 و انبار 1 و تاریخ 25 باشه اونموقع باید رکوردی که id بزرگتر داره انتخاب کرد.
اینو تو مثالم نگذاشتم.

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

درست است حق با شما است، پس همان طوری بود که فکر می کردم (پست شماره 10 ویراست آخر)
باشد کمی روی query ها کار می کنم و سعی می کنم اصلاحشان کنم.

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

فعلا یکی از روش ها را تصحیح کردم.


SELECT id, dif FROM
(
SELECT id,
code,
stack,
Dif=ABS(amn1-amn2),
Row_num=ROW_NUMBER() OVER(PARTITION BY code, stack ORDER BY date DESC, id DESC)
FROM (SELECT * FROM factorrow WHERE date<=28) D
) AS D
WHERE Row_num=1

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

یکی دیگم اصلاح کردم.
ولی چه اصلاحی !!


SELECT ddd.id,
dif=ABS(amn1-amn2)
FROM
(
SELECT id=MAX(id)
FROM
(
SELECT code,
stack,
date=MAX(date)
FROM(
SELECT *
FROM factorrow
WHERE date<=28
) DD
GROUP BY code, stack
) D
INNER JOIN factorrow p ON D.code=P.code AND D.stack=P.stack AND D.date=P.date
GROUP BY d.code, d.stack, d.date
) AS DDD
INNER JOIN factorrow p ON DDD.id=p.id






id date code stack amn1 amn2
----------- ----------- ----------- ----------- ----------- -----------
1 24 1 1 1000 50
2 25 1 2 1000 50
3 23 2 2 1111 222
4 27 3 1 3333 4444
5 22 2 1 5555 666
6 20 3 1 4444 222
7 25 2 1 4444 22
8 26 1 2 444 222
9 29 2 1 4444 22
10 22 3 1 5555 55
11 25 2 1 NULL NULL
12 25 2 1 NULL NULL

(12 row(s) affected)

خروجی



id dif
----------- -----------
1 950
8 222
12 NULL
3 889
4 1111

(5 row(s) affected)

[Mrs.Net]

اصلاح میکنم: حالا متوجه شدم که کوئری درسته

------------------------------------
فکر کنم این آخری درست نباشه
چون اول تاریخ ماکس انتخاب میشه و بعد آیدی ماکس که تاثیری در انتخاب نداره!

SELECT id=MAX(id)
FROM
(
SELECT code,
stack,
date=MAX(date)
FROM(
SELECT *
FROM factorrow
WHERE date<=28
) DD
GROUP BY code, stack
) D

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

حالا بهتر نیست که کوئری پست شماره 14 را با کوئری پست شماره 1 مقایسه کنید؟
حد اقل با استفاده از ابزار Actual Execution Plan.

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

کدی که تو پست اول دادم برای 4402 رکورد حدود 2:45 دقیقه طول میکشه


واقعا! دو دقیقه طول می کشد؟
اولش که گذرا به جمله شما نگاه کردم احساس کردم دو ثانیه کمی که بیشتر توجه کردم دیدم نوشته اید دو دقیقه واقعا عجیب است.

لطفا از کوئری پیشنهادی بنده هم استفاده کندید (پست 14) و ببنید آیا به ثانیه می رسد یا خیر. و نتیجه اجرای آن را به اطلاع بنده توسط همین تاپیک برسانید.