How to Improve SQL Performance

Posted In Tutorials - By NitiN Kumar Jain On Thursday, December 18th, 2008 With 0 Comments






Pin It


Consider the following SQL

SELECT * FROM A-LF LEFT JOIN B-PF ON (A-LF.pkey = B-PF.pkey) 
LEFT JOIN C-PF ON (B-PF.pkey=C-PF.pkey)

Where: A-LF is the logical file created and compiled from DDS source. B-PF and C-PF are the physical files on the database

he above SQL will be slow based upon the logical file size, the reason being running the above SQL will create the copy of the file in the data library.

It is always recommended to run the complete SQL by replacing the DDS built logical A-LF with the equivalent explicit SQL or use the views instead.. This basically gave the following equivalent SQL:

SELECT * FROM (SELECT * FROM A-PF) LEFT JOIN B-PF ON (A-LF.pkey = B-PF.pkey) 
LEFT JOIN C-PF ON (B-PF.pkey=C-PF.pkey)

or

SELECT * FROM A-LV LEFT JOIN B-PF ON (A-LF.pkey = B-PF.pkey) 
LEFT JOIN C-PF ON (B-PF.pkey=C-PF.pkey)

where A-LV is created using Create View command.

The above queries will not create the copy of the file and will be a lot faster.

So in summary avoid using DDS built logicals in SQL joins.

Hope this is useful.

-NKJ