Hi all,
Is there a better way of writting this SQL :
SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0)
),1),19,1)FROM TABLE1
WHERE REPORT_ID = temp_ID AND RESOURCE_ID = planResID AND
IFSC_USER_ID IN (SELECT IFSC_USER_ID FROM TABLE2 WHERE BUS_CAT_CD =
lob AND INTERNAL_USER_IND IN ( internalAmex, internalNonAmex )) AND
MONTH (REPT_REQ_DT) = temp_monthID AND YEAR (REPT_REQ_DT) = reportYear
AND DELIV_STATUS_CD IN (reportReady);
This SQL is taken out from a Stored proc.
Thanks in advance... 6 3037
> MONTH (REPT_REQ_DT) = temp_monthID AND YEAR (REPT_REQ_DT) = reportYear
REPT_REQ_DT BETWEEN DATE1 AND DATE2 might perform better cs****@gmail.com (SKC) wrote in message news:<de**************************@posting.google. com>... Hi all,
Is there a better way of writting this SQL :
SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0) ),1),19,1)FROM TABLE1 WHERE REPORT_ID = temp_ID AND RESOURCE_ID = planResID AND IFSC_USER_ID IN (SELECT IFSC_USER_ID FROM TABLE2 WHERE BUS_CAT_CD = lob AND INTERNAL_USER_IND IN ( internalAmex, internalNonAmex )) AND MONTH (REPT_REQ_DT) = temp_monthID AND YEAR (REPT_REQ_DT) = reportYear AND DELIV_STATUS_CD IN (reportReady);
This may be another way. But, I don't know better or not.
SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0) ),1),19,1)
FROM TABLE1 T1
WHERE REPORT_ID = temp_ID
AND RESOURCE_ID = planResID
AND EXISTS
(SELECT *
FROM TABLE2 T2
WHERE T2.IFSC_USER_ID = T1.IFSC_USER_ID
AND BUS_CAT_CD = lob
AND INTERNAL_USER_IND
IN ( internalAmex, internalNonAmex )
)
AND MONTH (REPT_REQ_DT) = temp_monthID
AND YEAR (REPT_REQ_DT) = reportYear
AND DELIV_STATUS_CD IN (reportReady)
;
SKC wrote: Hi all,
Is there a better way of writting this SQL :
SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0) ),1),19,1)FROM TABLE1 WHERE REPORT_ID = temp_ID AND RESOURCE_ID = planResID AND IFSC_USER_ID IN (SELECT IFSC_USER_ID FROM TABLE2 WHERE BUS_CAT_CD = lob AND INTERNAL_USER_IND IN ( internalAmex, internalNonAmex )) AND MONTH (REPT_REQ_DT) = temp_monthID AND YEAR (REPT_REQ_DT) = reportYear AND DELIV_STATUS_CD IN (reportReady);
You could try if a correlated subquery is any better:
SELECT DECIMAL(ROUND(AVG(COALESCE(REPT_EXEC_TIME_NBR,0.0) ),1),19,1)
FROM TABLE1
WHERE REPORT_ID = temp_ID AND
RESOURCE_ID = planResID AND
EXISTS ( SELECT IFSC_USER_ID
FROM TABLE2
WHERE BUS_CAT_CD = lob AND
INTERNAL_USER_IND IN
( internalAmex, internalNonAmex ) AND
table2.IFSC_USER_ID = table1.IFSC_USER_ID ) AND
MONTH(REPT_REQ_DT) = temp_monthID AND
YEAR(REPT_REQ_DT) = reportYear AND
DELIV_STATUS_CD = reportReady;
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Knut,
should there ever be an index on REPT_REQ_DT, this predicate would
never utilise that index:
MONTH(REPT_REQ_DT) = temp_monthID AND
YEAR(REPT_REQ_DT) = reportYear AND
so it could run way slower than this one
REPT_REQ_DT BETWEEN ... AND ...
What do you think?
AK wrote: Knut,
should there ever be an index on REPT_REQ_DT, this predicate would never utilise that index:
MONTH(REPT_REQ_DT) = temp_monthID AND YEAR(REPT_REQ_DT) = reportYear AND
so it could run way slower than this one
REPT_REQ_DT BETWEEN ... AND ...
What do you think?
Yes, the BETWEEN has a good chance of being more efficient.
Besides that, there is another alternative: You could use 2 generated
columns, one with MONTH(rept_req_dt) and another with YEAR(rept_req_dt).
Then create indexes on those two columns.
It might be worthwhile to see what the BETWEEN (along with the index)
changes, and then evaluate the difference that might or might not come with
the rephrased query. I wouldn't change both together, though.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
> Besides that, there is another alternative: You could use 2 generated columns, one with MONTH(rept_req_dt) and another with YEAR(rept_req_dt). Then create indexes on those two columns.
in my experience the optimizer is a little bit reluctant to choose
index OR-ing plans. A single index on both generated columns might do
way better This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Dave |
last post by:
After following Microsofts admonition to reformat my system before doing a
final compilation of my app I got many warnings/errors upon compiling an rtf
file created in word. I used the Help...
|
by: Tom |
last post by:
A question for gui application programmers. . .
I 've got some GUI programs, written in Python/wxPython, and I've got
a help button and a help menu item. Also, I've got a compiled file
made with...
|
by: wukexin |
last post by:
Help me, good men. I find mang books that introduce bit "mang header
files",they talk too bit,in fact it is my too fool, I don't learn it, I have
do a test program, but I have no correct doing...
|
by: Colin J. Williams |
last post by:
Python advertises some basic service:
C:\Python24>python
Python 2.4.1 (#65, Mar 30 2005, 09:13:57) on
win32
Type "help", "copyright", "credits" or "license" for more information.
>>>
With...
|
by: Corepaul |
last post by:
Missing Help Files
When I enter "recordset" as the keyword and search the Visual Basic Help index,
I get many topics of interest in the resulting list. But there isn't any
information available...
|
by: Steve |
last post by:
I have written a help file (chm) for a DLL and referenced it using Help.ShowHelp
My expectation is that a developer using my DLL would be able to access this help file during his development time...
|
by: Mark |
last post by:
I have loaded Visual Studio .net on my home computer and my laptop, but my
home computer has an abbreviated help screen not 2% of the help on my laptop.
All the settings look the same on both...
|
by: JonathanOrlev |
last post by:
Hello everybody,
I wrote this comment in another message of mine, but decided to post it
again as a standalone message.
I think that Microsoft's Office 2003 help system is horrible, probably...
|
by: trunxnirvana007 |
last post by:
'UPGRADE_WARNING: Array has a new behavior. Click for more: 'ms-help://MS.VSCC.v80/dv_commoner/local/redirect.htm?keyword="9B7D5ADD-D8FE-4819-A36C-6DEDAF088CC7"'
'UPGRADE_WARNING: Couldn't resolve...
|
by: hitencontractor |
last post by:
I am working on .NET Version 2003 making an SDI application that calls MS Excel 2003.
I added a menu item called "MyApp Help" in the end of the menu bar to show Help-> About.
The application...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |