HELP OPTIMIZING 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 2000 Forums

SQL Server Development (2000)

HELP OPTIMIZING QUERY

Author

Topic

dalmada
Starting Member

12 Posts

Posted - 2006-01-11 : 12:08:57

HELLO!

I HAVE THE FOLLOWING QUERY BUT IT IS REALLY SLOW, HOW COULD I OPTIMIZE IT?

SELECT
ACCOUNT,
SUM(COLOCADO*1) COLOCADO,
SUM(EJECUTADO*1) EJECUTADO,
SUM(PENDIENTE*1) PENDIENTE,
SUM(RECIBIDO*1) RECIBIDO
FROM(
SELECT
F0006.MCMCU ACCOUNT,
SUM( CASE WHEN F0902.GBLT IN ('PA') THEN (F0902.GBAN01/100 + F0902.GBAN02/100 + F0902.GBAN03/100 + F0902.GBAN04/100 + F0902.GBAN05/100
+ F0902.GBAN06/100 + F0902.GBAN07/100 + F0902.GBAN08/100 + F0902.GBAN09/100 + F0902.GBAN10/100 + F0902.GBAN11/100 + F0902.GBAN12/100 + F0902.GBAPYC/100)
ELSE '0'
END) AS COLOCADO,
SUM( CASE WHEN F0902.GBLT IN ('AA') THEN (F0902.GBAN01/100 + F0902.GBAN02/100 + F0902.GBAN03/100 + F0902.GBAN04/100 + F0902.GBAN05/100
+ F0902.GBAN06/100 + F0902.GBAN07/100 + F0902.GBAN08/100 + F0902.GBAN09/100 + F0902.GBAN10/100 + F0902.GBAN11/100 + F0902.GBAN12/100 + F0902.GBAPYC/100)
ELSE '0'
END) AS EJECUTADO,
'0'PENDIENTE,
'0'RECIBIDO
FROM
MOMTY01.JMXPMEXDT.F0902 F0902,
MOMTY01.JMXPMEXDT.F0006 F0006
WHERE
F0902.GBMCU = F0006.MCMCU
AND F0902.GBFY=5
AND F0006.MCRP15 NOT IN ('TER','','SUS')
AND F0006.MCCT='PI'
AND F0902.GBSUB<>'99999000'
AND F0006.MCCO IN ('00100','00101','00105','00109','00113','00117','00121','00122','00123','00124',
'00125','00127','00128','00129','00140','00141','00142','00145','00146','00147','00148','00149','00150',
'00151','00152','00153','00154','00155','00156','00157','00158','00159','00160','00161','00162','00163',
'00164','00165','00166','00167','00168','00169','00170','00171','00172','00173','00175','00180','00187',
'00188','00190','00191','00199','00207','00208','00209','00224','00254','00260','00265','03002','03003',
'03004','03026','03034','03036','00174')
AND F0006.MCSTYL='JB'
GROUP BY
F0006.MCMCU
UNION
SELECT
F4311LA.PDOMCU ACCOUNT,
'0'COLOCADO,
'0'EJECUTADO,
SUM( CASE WHEN F4311LA.PDSFXO = '000' THEN (F4311LA.PDAOPN/100)
ELSE '0'
END) AS PENDIENTE,
SUM( CASE WHEN F4311LA.PDSFXO <>'000' THEN (F4311LA.PDAREC/100)
ELSE '0'
END) AS RECIBIDO
FROM
MOMTY01.JNTPUNFDT.F0101 F0101,
MOMTY01.JMXPMEXDT.F0901 F0901,
MOMTY01.JMXPMEXDT.F4311LA F4311LA,
MOMTY01.JMXPMEXDT.F1Z01 F1Z01
WHERE
F4311LA.PDDCTO = F1Z01.QTDCTO
AND F4311LA.PDDOCO = F1Z01.QTDOCO
AND F4311LA.PDOMCU = F0901.GMMCU
AND F4311LA.PDAN8 = F0101.ABAN8
AND F4311LA.PDNXTR<'790'
AND F4311LA.PDDCTO In ('AI','AO','CE','CF','CS','OA','OC','OI','ON','OS')
AND (F0901.GMPEC<>'N')
AND (F0901.GMSUB<>'99999000')
AND F4311LA.PDOBJ = F0901.GMOBJ
AND F4311LA.PDOMCU between '100000000000' and '265999999999'
AND F4311LA.PDSUB = F0901.GMSUB
GROUP BY
F4311LA.PDOMCU)
AS TOTAL
GROUP BY
ACCOUNT
ORDER BY
1

Kristen
Test

22859 Posts

Posted - 2006-01-11 : 12:38:52

1) Have a look at the query plan and see if things are using the indexes that you expect, and make sure there are no unnecessary table scans etc.

2) use JOINs instead of WHERE

3) Can you use UNION ALL instead of UNION?

4) Perhaps pre-prepare your big IN lists into temporary table(s), properly indexes, and JOIN that instead of using IN

5) Are you connecting to the MOMTY01 server when you run this? If not use OPENQUERY to do the aggregating and then pull the slimmer results from that locally for the remainder [seems like all the tables are on MOMTY01, but I haven't checked carefully)

6) Post a nicely formatted query here so I can read it easily, and then I (and I'm sure others) would be prepared to spend more time looking at it.

7) Posting some DDL for the tables / indexes etc. would help too

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-11 : 12:58:22

Start by cleaning up your code, using JOINs rather than equalities in the WHERE clause:


SELECT	ACCOUNT,
	SUM(COLOCADO*1) COLOCADO,
	SUM(EJECUTADO*1) EJECUTADO,
	SUM(PENDIENTE*1) PENDIENTE,
	SUM(RECIBIDO*1) RECIBIDO
FROM	(SELECT	F0006.MCMCU ACCOUNT,
		SUM(CASE WHEN F0902.GBLT IN ('PA')
			THEN (F0902.GBAN01/100 + F0902.GBAN02/100 + F0902.GBAN03/100 + F0902.GBAN04/100 + F0902.GBAN05/100 + F0902.GBAN06/100 + F0902.GBAN07/100 + F0902.GBAN08/100 + F0902.GBAN09/100 + F0902.GBAN10/100 + F0902.GBAN11/100 + F0902.GBAN12/100 + F0902.GBAPYC/100)
			ELSE '0' END) AS COLOCADO,
		SUM(CASE WHEN F0902.GBLT IN ('AA')
			THEN (F0902.GBAN01/100 + F0902.GBAN02/100 + F0902.GBAN03/100 + F0902.GBAN04/100 + F0902.GBAN05/100 + F0902.GBAN06/100 + F0902.GBAN07/100 + F0902.GBAN08/100 + F0902.GBAN09/100 + F0902.GBAN10/100 + F0902.GBAN11/100 + F0902.GBAN12/100 + F0902.GBAPYC/100)
			ELSE '0'END) AS EJECUTADO,
		'0'PENDIENTE,
		'0'RECIBIDO
	FROM	MOMTY01.JMXPMEXDT.F0902 F0902
		INNER JOIN MOMTY01.JMXPMEXDT.F0006 F0006 ON F0902.GBMCU = F0006.MCMCU
	WHERE	F0902.GBFY=5
		AND F0006.MCRP15 NOT IN ('TER','','SUS')
		AND F0006.MCCT='PI'
		AND F0902.GBSUB<>'99999000'
		AND F0006.MCCO IN
			('00100','00101','00105','00109','00113','00117','00121','00122','00123','00124',
			'00125','00127','00128','00129','00140','00141','00142','00145','00146','00147','00148','00149','00150',
			'00151','00152','00153','00154','00155','00156','00157','00158','00159','00160','00161','00162','00163',
			'00164','00165','00166','00167','00168','00169','00170','00171','00172','00173','00175','00180','00187',
			'00188','00190','00191','00199','00207','00208','00209','00224','00254','00260','00265','03002','03003',
			'03004','03026','03034','03036','00174')
		AND F0006.MCSTYL='JB'
	GROUP BY F0006.MCMCU
	UNION
	SELECT	F4311LA.PDOMCU ACCOUNT,
		'0'COLOCADO,
		'0'EJECUTADO,
		SUM(CASE WHEN F4311LA.PDSFXO = '000'
			THEN (F4311LA.PDAOPN/100)
			ELSE '0' END) AS PENDIENTE,
		SUM(CASE WHEN F4311LA.PDSFXO <>'000'
			THEN (F4311LA.PDAREC/100)
			ELSE '0' END) AS RECIBIDO
	FROM	MOMTY01.JMXPMEXDT.F4311LA F4311LA
		INNER JOIN MOMTY01.JNTPUNFDT.F0101 F0101 ON F4311LA.PDAN8 = F0101.ABAN8
		INNER JOIN MOMTY01.JMXPMEXDT.F0901 F0901
			ON F4311LA.PDOMCU = F0901.GMMCU
			AND F4311LA.PDOBJ = F0901.GMOBJ
			AND F4311LA.PDSUB = F0901.GMSUB
		INNER JOIN MOMTY01.JMXPMEXDT.F1Z01 F1Z01
			ON F4311LA.PDDCTO = F1Z01.QTDCTO
			AND F4311LA.PDDOCO = F1Z01.QTDOCO
	WHERE	F4311LA.PDNXTR<'790'
		AND F4311LA.PDDCTO In ('AI','AO','CE','CF','CS','OA','OC','OI','ON','OS')
		AND (F0901.GMPEC<>'N')
		AND (F0901.GMSUB<>'99999000')
		AND F4311LA.PDOMCU between '100000000000' and '265999999999'
	GROUP BY F4311LA.PDOMCU) AS TOTAL
GROUP BY ACCOUNT

Then try running your code through Query Analyzer with the Show Execution Plan option set, to see where the slow parts are and make sure you have all your tables properly indexed.
Also, consider creating tables to store lists of values such as ('00100','00101','00105','00109','00113',...'03036','00174') or ('AI','AO','CE','CF','CS','OA','OC','OI','ON','OS'). With proper indexing, a join against such a table will be much faster than an IN() function.

ORDER BY 1???

dalmada
Starting Member

12 Posts

Posted - 2006-01-11 : 13:14:43

HOW CAN I PUT MY QUERY THROUGH QUERY ANALZYER??

I CAN'T CONNECT TO SQL SINCE THE DATABASE IS IN IBM ISERIES NOT IN SQL

CAN YOU TELL ME HOW TO CONNECT TO SQL SERVVER?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-11 : 13:45:20

quote:
Originally posted by blindman
ORDER BY 1???

Yeah, it's a bit a throwback, that syntax. If you put an integer literal in an ORDER BY clause it refers to that column of the SELECT (numbering from 1). If the number's <= 0 or more than the number of columns returned, it's an error. Non-integer literals (e.g. 2.0) are not treated specially, they order the output by a single value (which is generally not a useful thing to do).

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-11 : 13:54:56

quote:
Originally posted by Arnold Fribble

quote:
Originally posted by blindman
ORDER BY 1???

Yeah, it's a bit a throwback, that syntax. If you put an integer literal in an ORDER BY clause it refers to that column of the SELECT (numbering from 1). If the number's <= 0 or more than the number of columns returned, it's an error. Non-integer literals (e.g. 2.0) are not treated specially, they order the output by a single value (which is generally not a useful thing to do).

AH, yes. I do recall seeing that before.

dalmada, if you have SQL Server installed, chances are Enterprise Manager and Query Analyzer where installed at the same time. You can also install just the client and connectivity tools on your own (PC) system and use connect to the server to test your code.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-11 : 14:06:31

Some of the string literals must be getting converted to numbers by the data type precedence rules. Replace them with real number literals. Certainly all the '0' literals fall into that category.

The bit that says F4311LA.PDNXTR<'790'. It could be right, but it looks unlikely.

I don't really see what the *1 bit is doing on the SUMs in the outer select, either.

dalmada
Starting Member

12 Posts

Posted - 2006-01-11 : 14:54:03

I have SQL server and everything installed, how can I connect to the iSeries DB from SQL Server?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-11 : 16:02:59

Open up Enterprise Manager and/or Query Analyzer. I'm sorry, but I doubt anybody on the forum is going to give you a general tutorial on these utilities when Books Online has all the information you need.

dalmada
Starting Member

12 Posts

Posted - 2006-01-11 : 16:24:53

I REALLY don't know how to do this, could someone help??

Thanks

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-01-12 : 05:23:37

If this DB is on IBM, then you're at the wrong forums...99%+ of the stuff here is MS SQL Server. You MAY get a more useful response from dbforums.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources