Optimsing an SQL Statement for IBM.Data.DB2.Iseries (asp.net)
Hi,
I currently have a program which is collates a large amount of data (residing on an iseries) to create a crystal report - basically my crystal report is using a dataset built by a backend process (vb code) which uses an sql statement connecting to an iseries using the db2.iseries .Net driver. My issue is that because of the large amount of data my sql query is taking some considerable time to run - i have included my sql statement below and was hoping someone could provide any assistance on optimizing this query (if possible). I have also inlcude the size of the tables these table sizes can vary from year to year as each yeear some of them are archived
TABLE1 -577107
TABLE2 -443379
TABLE3-1890095
TABLE4-8957
TABLE5-392816
TABLE6-2474
TABLE7-10179
I currently have a program which is collates a large amount of data (residing on an iseries) to create a crystal report - basically my crystal report is using a dataset built by a backend process (vb code) which uses an sql statement connecting to an iseries using the db2.iseries .Net driver. My issue is that because of the large amount of data my sql query is taking some considerable time to run - i have included my sql statement below and was hoping someone could provide any assistance on optimizing this query (if possible). I have also inlcude the size of the tables these table sizes can vary from year to year as each yeear some of them are archived
TABLE1 -577107
TABLE2 -443379
TABLE3-1890095
TABLE4-8957
TABLE5-392816
TABLE6-2474
TABLE7-10179
SELECT LIB.TABLE1.BLEGDT, LIB.TABLE2.BLGCOL, LIB.TABLE2.BLGLEF,
LIB.TABLE2.BLGBRK, LIB.TABLE1.BLESPL, LIB.TABLE1.BLEMCR, MIN(LIB.TABLE7.STTSTG) as STTSTG,
SUM(LIB.TABLE3.BLEALW) AS TOTALW, MIN(LIB.TABLE5.PDTUSP) AS PDTUSP, MIN(LIB.TABLE6.MITUSP) AS MITUSP,
MIN(LIB.TABLE6.MITWPD) AS MITWPD, MIN(LIB.TABLE7.STTUSP) AS STTUSP
FROM LIB.TABLE1, LIB.TABLE2, LIB.TABLE3, LIB.TABLE4, LIB.TABLE5, LIB.TABLE6,
LIB.TABLE7
WHERE LIB.TABLE1.BLESSN = LIB.TABLE2.BLGSSN AND LIB.TABLE1.BLEBIM = LIB.TABLE2.BLGBIM AND
LIB.TABLE1.BLEBGI = LIB.TABLE2.BLGBGI AND LIB.TABLE1.BLEBLE = LIB.TABLE2.BLGBLE AND
LIB.TABLE1.BLECOI = LIB.TABLE3.BLECOI AND LIB.TABLE1.BLESSN = LIB.TABLE3.BLESSN AND
LIB.TABLE1.BLEBUN = LIB.TABLE3.BLEBUN AND LIB.TABLE3.BLECOI = LIB.TABLE4.HDRCOI AND
LIB.TABLE3.BLENBR = LIB.TABLE4.HDRNBR AND LIB.TABLE3.BLECCD = LIB.TABLE4.HDRCCD AND
LIB.TABLE3.BLESSN = LIB.TABLE4.HDRSSN AND LIB.TABLE4.HDRPDT = LIB.TABLE5.PDTPAD AND
LIB.TABLE2.BLGCOL = LIB.TABLE5.PDTCOL AND LIB.TABLE2.BLGLEF = LIB.TABLE5.PDTLEF AND
LIB.TABLE2.BLGBRK = LIB.TABLE5.PDTRED AND LIB.TABLE1.BLESPL = LIB.TABLE5.PDTSPL AND
LIB.TABLE4.HDRPDT = LIB.TABLE6.MITPAD AND LIB.TABLE1.BLEMCR = LIB.TABLE6.MITMCR AND
LIB.TABLE4.HDRPDT = LIB.TABLE7.STTPAD AND LIB.TABLE1.BLESTR = LIB.TABLE7.STTSTR AND
(LIB.TABLE4.HDRCOI = 1) AND (LIB.TABLE4.HDRSSN = 108) AND (LIB.TABLE4.HDRNBR = 210015635)
GROUP BY LIB.TABLE1.BLEGDT, LIB.TABLE1.BLEMCR, LIB.TABLE1.BLESPL, LIB.TABLE1.BLESTR,
LIB.TABLE2.BLGLEF, LIB.TABLE2.BLGCOL, LIB.TABLE2.BLGBRK, LIB.TABLE4.HDREXH, STTSTG
Last Comment
Sharath S
your query is looking good.
on the iseries do the following:
run STRDBG
run STRSQL, paste your query and execute it
go to the joblog and read carefully the optimization messages. It looks like you could use a few indexes.
if you do need indexing, insist that they are created with the CREATE INDEX sql commands and NOT with the traditional logical files.
ShalomC
run STRDBG
run STRSQL, paste your query and execute it
go to the joblog and read carefully the optimization messages. It looks like you could use a few indexes.
if you do need indexing, insist that they are created with the CREATE INDEX sql commands and NOT with the traditional logical files.
ShalomC
Hi jdwan,
Try to add an "Order by" clause, most of the time SQL can find the most opitmal access patth, but somtime it doesn't work, adding the Order by will help often to determine the right access-path.
Regards,
Murph
Try to add an "Order by" clause, most of the time SQL can find the most opitmal access patth, but somtime it doesn't work, adding the Order by will help often to determine the right access-path.
Regards,
Murph
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hi Guys,
Thanks for your reponses - i have made progress on the SQL Statement (see new SQL code below), i am going to have to split the points as i tried a few things and have now got to the command (which yesterady was taking nearly 2 hours to run - for running bulk reports) down to approx 3 mins which i am very very happy with. Thanks for you help on this one.
After reviewing the SQL i realised i could take a few things out which were able to be replaced by values passed by the vb program executing the script.
Cheers
Thanks for your reponses - i have made progress on the SQL Statement (see new SQL code below), i am going to have to split the points as i tried a few things and have now got to the command (which yesterady was taking nearly 2 hours to run - for running bulk reports) down to approx 3 mins which i am very very happy with. Thanks for you help on this one.
After reviewing the SQL i realised i could take a few things out which were able to be replaced by values passed by the vb program executing the script.
Cheers
SELECT LIB.TABLE1.BLEGDT, LIB.TABLE2.BLGCOL, LIB.TABLE2.BLGLEF, LIB.TABLE2.BLGBRK,
LIB.TABLE1.BLESPL, LIB.TABLE1.BLEMCR, MIN(LIB.TABLE7.STTSTG) AS STTSTG, SUM(TABLE3.BLEALW) AS TOTALW,
MIN(LIB.TABLE5.PDTUSP) AS PDTUSP, MIN(LIB.TABLE6.MITUSP) AS MITUSP, MIN(LIB.TABLE6.MITWPD) AS MITWPD,
MIN(LIB.TABLE7.STTUSP) AS STTUSP
FROM LIB.TABLE2, LIB.TABLE1,
(SELECT BLEBUN, BLECLI, BLECCD, BLENBR, BLEULW, BLEALW, BLESSN, BLECOI
FROM LIB.TABLE3 TABLE3_1
WHERE (BLECOI = 1) AND (BLESSN = 108) AND (BLENBR = 210015635)) TABLE3, LIB.TABLE5, LIB.TABLE6,
LIB.TABLE7
WHERE LIB.TABLE2.BLGSSN = LIB.TABLE1.BLESSN AND LIB.TABLE2.BLGBIM = LIB.TABLE1.BLEBIM AND
LIB.TABLE2.BLGBGI = LIB.TABLE1.BLEBGI AND LIB.TABLE2.BLGBLE = LIB.TABLE1.BLEBLE AND
LIB.TABLE1.BLECOI = TABLE3.BLECOI AND LIB.TABLE1.BLESSN = TABLE3.BLESSN AND
LIB.TABLE1.BLEBUN = TABLE3.BLEBUN AND LIB.TABLE2.BLGCOL = LIB.TABLE5.PDTCOL AND
LIB.TABLE2.BLGLEF = LIB.TABLE5.PDTLEF AND LIB.TABLE2.BLGBRK = LIB.TABLE5.PDTRED AND
LIB.TABLE1.BLESPL = LIB.TABLE5.PDTSPL AND LIB.TABLE1.BLEMCR = LIB.TABLE6.MITMCR AND
LIB.TABLE1.BLESTR = LIB.TABLE7.STTSTR AND (LIB.TABLE5.PDTPAD = 'CODE') AND
(LIB.TABLE6.MITPAD = 'CODE') AND (LIB.TABLE7.STTPAD = 'CODE') AND (TABLE3.BLECOI = 1) AND
(TABLE3.BLESSN = 108) AND (TABLE3.BLENBR = 210015635)
GROUP BY LIB.TABLE1.BLEGDT, LIB.TABLE1.BLEMCR, LIB.TABLE1.BLESPL, LIB.TABLE7.STTSTG,
LIB.TABLE2.BLGLEF, LIB.TABLE2.BLGCOL, LIB.TABLE2.BLGBRK
Impressive results. Glad we could help.
- Gary Patterson
- Gary Patterson
ASP.NET
The successor to Active Server Pages, ASP.NET websites utilize the .NET framework to produce dynamic, data and content-driven web applications and services. ASP.NET code can be written using any .NET supported language. As of 2009, ASP.NET can also apply the Model-View-Controller (MVC) pattern to web applications
128K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
