Performance Tuning - 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 2000 Forums

Import/Export (DTS) and Replication (2000)

Performance Tuning

Author

Topic

kumarpav
Starting Member

18 Posts

Posted - 2004-12-03 : 12:31:51

Can u try to tune this query?

It is taking lot of time in production....

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 in ('2026','2027','2526','2527'))
AND
INCT.INST_NO = '003'
AND
INCT.ACCT_NO = INVM.KEY_1
AND
(INVM.LST_FIN_DT >= 38172)
AND
(INCT.TRAN_TYPE = 01 OR INCT.TRAN_TYPE = 40)

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 in ('2026','2027','2526','2527'));

INCT Table Structure
===================

CREATE TABLE INCT
(INST_NO CHAR (3) NOT NULL,
ACCT_NO CHAR (16) NOT NULL,
REC_NO CHAR (09) NOT NULL,
TRAN_DATE NUMBER (9) DEFAULT (0),
POST_DATE NUMBER (9) DEFAULT (0),
POST_TIME NUMBER (9) DEFAULT (0),
JRNL_NO NUMBER (9) DEFAULT (0),
TRAN_TYPE NUMBER (2) DEFAULT (0),
DELI CHAR (1) DEFAULT (LPAD(CHR(0),001,CHR(0))),
VAR_AREA CHAR (80) DEFAULT (LPAD(CHR(0),080,CHR(0))));

INSERT INTO INCT1 VALUES('003','0000003000023261','999999999',38172,38172,0,0,40,' ','4010');
INSERT INTO INCT1 VALUES('003','0000003000023262','999999998',38172,38172,0,0,40,' ','4010');
INSERT INTO INCT1 VALUES('003','0000003000023263','999999997',38172,38172,0,0,40,' ','4010');

I am maintaining index for inct on these columns...
INST_NO,
ACCT_NO,
TRAN_TYPE,
TRAN_DATE,
REC_NO

INVM Table Structure
=====================

Create Table INVM1(
KEY_1 CHAR(16),
BRANCH_NO CHAR(5),
CURRENCY CHAR(3),
ACCT_TYPE CHAR(4),
GL_CLASS_CODE CHAR(25),
LST_FIN_DT NUMBER(9));

I am maintaining index only on KEY_1

INSERT INTO INVM1 VALUES('0000003000023261','04234','INR','2026','04234INR2048030601',38172);
INSERT INTO INVM1 VALUES('0000003000023262','04234','INR','2027','04234INR2048030601',38172);
INSERT INTO INVM1 VALUES('0000003000023263','04234','INR','2526','04234INR2048030601',38172);
INSERT INTO INVM1 VALUES('0000003000023264','04234','INR','2527','04234INR2048030601',38172);

rockmoose
SQL Natt Alfen

3279 Posts

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

Are You passing that query to another db via linked server or something ?
Because the syntax is not correct for a SQL Server database.

Execute the query with one WHERe filter at a time, to see if you can pinpoint the culprit.
Anyway, here is a SQL Server syntax version using LEFT JOIN instead of a UNION ALL operator.

SELECT 
	INVM.BRANCH_NO, 
	INVM.CURRENCY,
	INVM.GL_CLASS_CODE,
	CASE WHEN INCT.ACCT_NO IS NULL THEN 3 ELSE 1 END AS SFLAG,
	COALESCE( INCT.ACCT_NO, ' ' ) AS ACCT_NO,
	COALESCE( INCT.REC_NO, ' ' ) AS REC_NO,
	COALESCE( INCT.TRAN_DATE, 0 ) AS TRAN_DATE,
	COALESCE( INCT.POST_DATE, 0 ) AS POST_DATE,
	COALESCE( INCT.JRNL_NO, 0 ) AS JRNL_NO,
	COALESCE( INCT.TRAN_TYPE, 0 ) AS TRAN_TYPE,
	COALESCE( INCT.VAR_AREA, ' ' ) AS VAR_AREA
FROM
	INVM
	LEFT OUTER JOIN INCT ON INVM.KEY_1 = INCT.ACCT_NO
WHERE
	(INVM.ACCT_TYPE IN ('2026','2027','2526','2527'))
	AND
	INCT.INST_NO = '003'
	AND
	(INVM.LST_FIN_DT >= 38172) 
	AND
	(INCT.TRAN_TYPE = 1 OR INCT.TRAN_TYPE = 40)

rockmoose

kumarpav
Starting Member

18 Posts

Posted - 2004-12-04 : 07:19:30

What is meant by "COALESCE"?
what it will do?

Is this query will work in Oracle9i...
Here i am using Oracle 9i...

kumarpav
Starting Member

18 Posts

Posted - 2004-12-04 : 07:22:13

In my query i am using Unique(BRANCH_N0) .. Can u notice that one...Is this query will work for this thing also?
That should be necessary for my requirement..

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-04 : 09:19:02

COALESCE is ANSI SQL so Oracle should havee it too.
It returns the first NON NULL paramater passed.
Yes I noticed the UNIQUE(BRANCH_N0), perhaps I wrongly assumed that BRANCH_NO is unique in the table INVM.

Did you try the query ?, I think that would be a good way to see if it meets your requirements.

rockmoose

kumarpav
Starting Member

18 Posts

Posted - 2004-12-04 : 11:57:24

Hi,

That query is giving wrong result.it is not at all displaying that 2nd query information like spaces and zeros and 3.

Is any otherway to write this query..
This time can u try to write query with UNIQUE(BRANCH_NO) also..

Thanking u for ur time...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-05 : 16:41:47

Hi,
Yes You're right, the query would not return what You expected, I see that now.
You did not say whether BRANCH_NO is unique in table INMV ?

This query assumes that:

SELECT 
	INVM.BRANCH_NO, 
	INVM.CURRENCY,
	INVM.GL_CLASS_CODE,
	CASE WHEN INCT.ACCT_NO IS NULL THEN 3 ELSE 1 END AS SFLAG,
	COALESCE( INCT.ACCT_NO, ' ' ) AS ACCT_NO,
	COALESCE( INCT.REC_NO, ' ' ) AS REC_NO,
	COALESCE( INCT.TRAN_DATE, 0 ) AS TRAN_DATE,
	COALESCE( INCT.POST_DATE, 0 ) AS POST_DATE,
	COALESCE( INCT.JRNL_NO, 0 ) AS JRNL_NO,
	COALESCE( INCT.TRAN_TYPE, 0 ) AS TRAN_TYPE,
	COALESCE( INCT.VAR_AREA, ' ' ) AS VAR_AREA
FROM
	INVM
	LEFT OUTER JOIN INCT
		ON INVM.KEY_1 = INCT.ACCT_NO
		AND INCT.INST_NO = '003'
		AND (INCT.TRAN_TYPE = 1 OR INCT.TRAN_TYPE = 40)
WHERE
	(INVM.ACCT_TYPE IN ('2026','2027','2526','2527'))
	AND (INVM.LST_FIN_DT >= 38172)

rockmoose

kumarpav
Starting Member

18 Posts

Posted - 2004-12-27 : 12:21:44

Hi ,
Above query i am using in Pro*Cobol language(using cursors).It is giving compilation errors....

is it possible to write a query using simple outer join like like (+) .

is any otherway is there?

Whatever may be above query is reading Full INVM AND INCT tables.It is not following the indexing.

I already send u which indexes i am using for invm and inct tables.

now it is taking 6 hours in production.we r not able do any thing here..

Plz try to modify this query as per my requirement.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-02 : 16:24:51

>> Above query i am using in Pro*Cobol language(using cursors).It is giving compilation errors....
>> now it is taking 6 hours in production.we r not able do any thing here..

Is the query issued just once for the cursor ?
Is it the query itself, or the cursored cobol program that takes so much time ?

>> is it possible to write a query using simple outer join like like (+)
Oracle syntax is not my forte.

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources