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
 General SQL Server Forums
 New to SQL Server Programming
 Qery help

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.
Go to Top of Page

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)?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

msrs

32 Posts

Posted - 2007-06-15 : 00:49:31
hi pootle flump,
Go to Top of Page

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_LEDGERSDATA
VFINANCEACCOUNTS
vOrganization

If 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
Go to Top of Page

msrs

32 Posts

Posted - 2007-06-18 : 00:11:09
hi
Go to Top of Page

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 be
left 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 this
FROM VRPT_FIN_LEDGERSDATA VFLED
INNER 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.
Go to Top of Page

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.
Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -