Author |
Topic |
msrs
32 Posts |
Posted - 2007-06-13 : 01:03:36
|
Dear all, |
|
pootle_flump
1064 Posts |
Posted - 2007-06-13 : 02:42:14
|
Unless you can change what the query does (for example, include a where clause) you cannot really optimise the SQL much. Your case statement can be better:(CASE WHEN NOT EXISTS(SELECT NULL FROM OATABLE037 CHREG WHERE CHREG.COLUMN001=VFLED.INSTRUMENT_NO) THEN VFLED.INSTRUMENT_NO ELSE (SELECT CHREG.COLUMN005 FROM OATABLE037 CHREG WHERE CHREG.COLUMN001=VFLED.INSTRUMENT_NO) END ) INSTRUMENT_NO although personally I would do that using a standard join.Is you application filtering the data at the client?Other than that you are going to have to look at the table indexes. |
|
|
pootle_flump
1064 Posts |
Posted - 2007-06-13 : 02:45:52
|
Do you really have a table called OATABLE037 with columns COLUMN001 and COLUMN005?Oh and another thing - I just noticed you built this on views. What are they? Are they optimised for use by this query (for example not using any tables that this query does not use)? |
|
|
pootle_flump
1064 Posts |
Posted - 2007-06-13 : 06:02:39
|
quote: Originally posted by pootle_flump
(CASE WHEN NOT EXISTS(SELECT NULL FROM OATABLE037 CHREG WHERE CHREG.COLUMN001=VFLED.INSTRUMENT_NO) THEN VFLED.INSTRUMENT_NO ELSE (SELECT CHREG.COLUMN005 FROM OATABLE037 CHREG WHERE CHREG.COLUMN001=VFLED.INSTRUMENT_NO) END ) INSTRUMENT_NO
Actually that can be improved too: COALESCE((SELECT COLUMN005 FROM OATABLE037 CHREG WHERE CHREG.COLUMN001=VFLED.INSTRUMENT_NO), VFLED.INSTRUMENT_NO) INSTRUMENT_NO although I still prefer the join. |
|
|
msrs
32 Posts |
Posted - 2007-06-13 : 06:02:53
|
hi pootle flump, using above case statement in view it will not boost any performance. i have table oatable037 with columns column001 to column009.yaa i have built this as view view name(VRPT_FIN_GL_ACCOUNTSDATA) this query is for this view. |
|
|
pootle_flump
1064 Posts |
Posted - 2007-06-13 : 06:30:01
|
quote: Originally posted by msrs hi pootle flump, using above case statement in view it will not boost any performance. i have table oatable037 with columns column001 to column009.yaa i have built this as view view name(VRPT_FIN_GL_ACCOUNTSDATA) this query is for this view.
You are probably right - there can only be a one-to-one match so counting to one is no extra cost. The coalesce is an improvement though as it requires only one scan of the table.I meant VRPT_FIN_LEDGERSDATA, vOrganization and VFINANCEACCOUNTS. |
|
|
msrs
32 Posts |
Posted - 2007-06-13 : 09:00:11
|
for using coalesce also there is no improment in performance.give some other solution to improve the performance. |
|
|
pootle_flump
1064 Posts |
Posted - 2007-06-13 : 09:53:12
|
quote: Originally posted by msrs for using coalesce also there is no improment in performance.give some other solution to improve the performance.
The coalesce is more efficient though it is probably lost in other inefficiencies.You need to post the SQL of the views I outlined. |
|
|
msrs
32 Posts |
Posted - 2007-06-15 : 00:49:31
|
hi pootle flump, |
|
|
pootle_flump
1064 Posts |
Posted - 2007-06-15 : 16:56:12
|
You are still misunderstanding me. Please read the below carefully and answer each question:Are the following objects actually views?VRPT_FIN_LEDGERSDATAVFINANCEACCOUNTSvOrganizationIf they are views please could you post the SQL for these three views?Also - are these views specifically written for VRPT_FIN_GL_ACCOUNTSDATA (i.e. they reference no more columns or tables than necessary to satisfy what VRPT_FIN_GL_ACCOUNTSDATA needs), or are they generic views that you have simply used here?We can figure out the answer to question 3 based on the first 2 answers but it would save time if you can answer that one too.EDIT - clarified my clarification |
|
|
msrs
32 Posts |
Posted - 2007-06-18 : 00:11:09
|
hi |
|
|
pootle_flump
1064 Posts |
Posted - 2007-06-18 : 08:45:15
|
thank you.msrs - is this an in house database or a thrid party one you have purchases? The table and column names suggest a highly denormalised design, as do the non-key column dependencies.Ultimately there is not that much you can do. You do not have a single where clause in the whole set of code. How many rows do you return? What do you do with the data?I would remove vOrganisation from your main query and query the table GaTable028 directly. This is because you have subselects to get the address and pin data in vOrganisation but they are not used in VRPT_FIN_GL_ACCOUNTSDATA.If there is an index on GATABLE0282.column001 then left outer join GaTable028 GATABLE0282 on ACCUS.COLUMN009 = COALESCE (GATABLE0282.column001 , GATABLE0282.column001 should beleft outer join GaTable028 GATABLE0282 on ACCUS.COLUMN009 = GATABLE0282.column001 or the index cannot be used. In any event the above code makes no sense anyway.All the joins like thisFROM VRPT_FIN_LEDGERSDATA VFLEDINNER JOIN VFINANCEACCOUNTS ACMST ON ACMST.ACCOUNT_ID = VFLED.ACCOUNT_ID involving VRPT_FIN_LEDGERSDATA are destined to perform poorly since VRPT_FIN_LEDGERSDATA is actually an unfiltered union of three tables. Actually - I've just noticed that VFINANCEACCOUNTS is a union too. Blimey.I'm afraid given your design (what appears to be a denormalised design) and requirement (not one single WHERE clause) this is destined to run poorly. Given the unions I don't think there is even much to be gained examining the indexes either.Perhaps someone else could jump in with some thoughts. For me, based on the little I have seen here, it looks like you need to consider a rexamination of your database from the ground up. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2008-01-09 : 03:58:06
|
Hey msrs...... You missed deleting some code from your other posts on this thread.Duane. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-01-09 : 05:29:21
|
Or he realized SQL is not his cup of tee and decided to leave the business? E 12°55'05.25"N 56°04'39.16" |
|
|
|