how to optimize this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to optimize this query

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-24 : 10:47:43

hello,
how can i make this query more simple and to work because it make my cpu over 90% for 3 minutes.

this is query:

SELECT x.EVPSOS
FROM OXDOUS x
Left Join OCIFBE c
ON x.EVPSMS=convert(varchar ,c.OCSFQO)
Left Join (SELECT o.OAPGOP,Min(d.UAJUES) As IDATE,Min
(d.UAGOUN) As ITIME
FROM OOIFBE o Join ODIFBE d On d.UAPSOP=o.OAPSOP
GROUP BY o.OAPGOP) h
On (h.OAPGOP='000'+convert(varchar,c.OCSFQO)
OR
LTRIM (h.OAPGOP)=c.OCPSOP)
WHERE
x.EVJE='OIS279R'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 11:09:11

Ofcourse, as you are converting for each match and concatenation.Check Execution plan. I am pretty sure it must doing table scan.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-24 : 11:14:42

There are functions on the JOIN columns so there goes use of indexes,plus they are joined on an OR condition, so that will really slow things down.
WHy are you self-joining?
SELECT o.OAPGOP,Min(d.UAJUES) As IDATE,Min(d.UAGOUN) As ITIME
FROM OOIFBE o Join ODIFBE d On d.UAPSOP=o.OAPSOP
GROUP BY o.OAPGOP

Jim

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-24 : 11:21:53

quote:
Originally posted by jimf

There are functions on the JOIN columns so there goes use of indexes,plus they are joined on an OR condition, so that will really slow things down.
WHy are you self-joining?
SELECT o.OAPGOP,Min(d.UAJUES) As IDATE,Min(d.UAGOUN) As ITIME
FROM OOIFBE o Join ODIFBE d On d.UAPSOP=o.OAPSOP
GROUP BY o.OAPGOP

Jim

Hi,

Where did you find self joining ?? In the above query OOIFBE table is joined with ODIFBE table. Isn't it??

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 11:23:19

quote:
Originally posted by jimf

There are functions on the JOIN columns so there goes use of indexes,plus they are joined on an OR condition, so that will really slow things down.
WHy are you self-joining?
SELECT o.OAPGOP,Min(d.UAJUES) As IDATE,Min(d.UAGOUN) As ITIME
FROM OOIFBE o Join ODIFBE d On d.UAPSOP=o.OAPSOP
GROUP BY o.OAPGOP

Jim

Also Conversion on join column doesn't use index.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-12-24 : 12:07:20

My bad eyesight about the self join, I even looked twice at it! I thought that CONVERT was a function, just like LTRIM, that's why I didn't state it explicitly here

quote:
There are functions on the JOIN columns so there goes use of indexes

Jim

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-24 : 12:49:18

Jimf and sodeep:

i need the nested query because i have some orders and i need to take the ordr with the minimum date.

can i improve the running?

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-24 : 14:03:16

can i make better the Join in the "FROM",that query will run faster?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 14:09:05

What is datatype of green portion?

SELECT X.EVPSOS
FROM OXDOUS X
LEFT JOIN OCIFBE C
ON X.EVPSMS = CONVERT(VARCHAR,C.OCSFQO) LEFT JOIN (SELECT O.OAPGOP,
MIN(D.UAJUES) AS IDATE,
MIN(D.UAGOUN) AS ITIME
FROM OOIFBE O
JOIN ODIFBE D
ON D.UAPSOP = O.OAPSOP
GROUP BY O.OAPGOP) H
ON (H.OAPGOP = '000' + CONVERT(VARCHAR,C.OCSFQO)
OR LTRIM(H.OAPGOP) = C.OCPSOP)WHERE X.EVJE = 'OIS279R'

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-24 : 14:52:14

if i remmber right(cause i am in home now)
EVPSMS -char
OAPGOP -char
OAPGOP-int

when i added the "OR" the query run slowly,without "OR" it run 30 seconds.

you think the nested query is the problem?
--------------------------------------------------------------------
do you understand why i make the nested query?

for example:
i have two tables:
TABLE1
TABLE2

TABLE1:Field1
TABLE2:Field2,DATE2

In TABLE1:10
TABLE2:10,10/10/2008
20/10/2008

TABLE1 -JOIN-TABLE2
i want to see:10,10/10/2008(Min Date From Table2)

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-25 : 01:35:41

folk i need help,i can explain again

thanks

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-25 : 05:40:51

ok,i can hanle with it,
but not to make optimazation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources