SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Qery help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

msrs
Starting Member

India
32 Posts

Posted - 06/13/2007 :  01:03:36  Show Profile  Reply with Quote
Dear all,

Edited by - msrs on 01/09/2008 03:50:19

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 06/13/2007 :  02:42:14  Show Profile  Reply with Quote
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.

Edited by - pootle_flump on 06/13/2007 02:43:17
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 06/13/2007 :  02:45:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 06/13/2007 :  06:02:39  Show Profile  Reply with Quote
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
Starting Member

India
32 Posts

Posted - 06/13/2007 :  06:02:53  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 06/13/2007 :  06:30:01  Show Profile  Reply with Quote
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
Starting Member

India
32 Posts

Posted - 06/13/2007 :  09:00:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 06/13/2007 :  09:53:12  Show Profile  Reply with Quote
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
Starting Member

India
32 Posts

Posted - 06/15/2007 :  00:49:31  Show Profile  Reply with Quote
hi pootle flump,

Edited by - msrs on 01/09/2008 03:58:42
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 06/15/2007 :  16:56:12  Show Profile  Reply with Quote
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

Edited by - pootle_flump on 06/17/2007 09:09:54
Go to Top of Page

msrs
Starting Member

India
32 Posts

Posted - 06/18/2007 :  00:11:09  Show Profile  Reply with Quote
hi

Edited by - msrs on 01/09/2008 03:59:56
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 06/18/2007 :  08:45:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 01/09/2008 :  03:58:06  Show Profile  Visit ditch's Homepage  Reply with Quote
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

Sweden
30113 Posts

Posted - 01/09/2008 :  05:29:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000