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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-02 : 07:20:58
|
| Pavan kumar writes "Hi,Here i am sending a query .In that query i am using 2 union all.1)In 1st query i am getting data from 2 tables (INVM AND INCT)2)In 2nd query i am getting data from another 2 tables(GECT AND GLDM)3)In 3rd query i am getting data from only 1 table (INVM,the same table i am using in 1st query). Note: 1)In 3rd query from 4 th column onwards i am using dummy variables (Can u notice that thing ) 2)For all queries i am using 4th column SFLAG as 1,2 and 3 (I need this thing also in my query) This query is executing more than 2 hours in real scenario.Can u write a simple query to reduce time. Can u plz just modify this query and send to me .. i will try here with your query.My query is:============ SELECT INVM.BRANCH_NO, INVM.CURRENCY, INVM.GL_CLASS_CODE, 1 SFLAG, INCT.ACCT_NO, INCT.REC_NO, INCT.TRAN_DATE, INCT.POST_DATE, INCT.JRNL_NO, INCT.TRAN_TYPE, INCT.VAR_AREA FROM INVM,INCT WHERE (INVM.ACCT_TYPE = '2026' OR INVM.ACCT_TYPE = '2027' OR INVM.ACCT_TYPE = '2526' OR INVM.ACCT_TYPE = '2527') AND INCT.ACCT_NO = SUBSTR(INVM.KEY_1,4,16) AND (LST_FIN_DT >= :WA-PREVIOUS-BINARY-DATE) AND (INCT.TRAN_TYPE = 01 OR INCT.TRAN_TYPE = 40) UNION ALL SELECT GLDM.BRANCH, GLDM.CURRENCY, GLDM.GL_CLASS_CODE, 2 SFLAG, GECT.ACCT_NO, GECT.REC_NO, GECT.TRAN_DATE, GECT.POST_DATE, GECT.JRNL_NO, GECT.TRAN_TYPE, GECT.VAR_AREA FROM GLDM,GECT WHERE ((SUBSTR(GLDM.KEY_1,4,16) = GECT.ACCT_NO) AND (GECT.POST_DATE = :WA-TODAY-BINARY-DATE) AND ((SUBSTR(GECT.ACCT_NO,1,11) LIKE '%98845') OR (SUBSTR(GECT.ACCT_NO,1,11) LIKE '%98847')) AND (GLDM.BRANCH = SUBSTR(GECT.ACCT_NO,12,5)) AND (GECT.TRAN_TYPE = 01)) UNION ALL SELECT UNIQUE(INVM.BRANCH_NO), INVM.CURRENCY, INVM.GL_CLASS_CODE, 3 SFLAG, ' ' DUMYACCTNO, ' ' DUMYRECNO, 0 DUMYTRANDATE, 0 DUMYPOSTDATE, 0 DUMYJRNLNO, 0 DUMYTRANTYPE, ' ' DUMYVARAREA FROM INVM WHERE (INVM.ACCT_TYPE = '2026' OR INVM.ACCT_TYPE = '2027' OR INVM.ACCT_TYPE = '2526' OR INVM.ACCT_TYPE = '2527') ORDER BY GL_CLASS_CODE ASC , ACCT_NO ASC, POST_DATE ASC I need this thing urgent.Plz Can u help me out plese.it is taking more than 2 hours in production.Can u just modify the query,i will test here myself. Thanking u for ur time. Regards and Thanks pavan kumar.k |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-11-03 : 06:32:20
|
| Please post (relevent) DDL for the tables involved in the above query.....ie you can leave columns not referenced....but do include indices.and if possible post the expected/actual execution plan.1 or both of these will advance a solution much faster. |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-11-03 : 07:01:46
|
quote:
Can u write a simple query to reduce time.
Don't know what those bits with colons at the start are, but I've never used Oracle or whatever it is -- you didn't say. But I would guess that many of your problems are in the join conditions that look like: INCT.ACCT_NO = SUBSTR(INVM.KEY_1,4,16)or SUBSTR(GLDM.KEY_1,4,16) = GECT.ACCT_NO |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-03 : 10:46:50
|
| I agree, Arnold -- most likely those joins are causing problems.Also -- the nice thing about a union is that it is trivial to divide this SQL up into parts. if the whole thing performs poorly, just take one part of the union at a time and test it and optimize it. you should be able to indentify extremely easy which portion is the bottleneck (if it is not all of them, of course).- Jeff |
|
|
|
|
|
|
|
|
Performance Tuning