Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Inner Join Vs Database view Vs for all entries

Former Member
0 Kudos
1,203

Hi I know how to use and create inner join ( outer join) ,databse view and for all entries ....

I want to know since all of these three are used for the same purpose...which one is better....

and projection view ( why do we really create it ) ...is it faster ..because no correpsoding table or view is created in databe for the projection view ...then why do we really create it ...is it more optimized ???

Once database view is created then does the data in databse view is selected suring the select query ie at thr runtime or data is permanently stored in int in database....as far as i know .....data is selected at runtime in databse view..then why to create database view..instead I can use for all entries .........beacuse though using databse view we have one select query and using for all entries we have 2 select queries but internally when data is selected form a databse view.....database has to trigger two select queries....

In nutsheel i wan to understand the functionality ( efficcincy wise and how it works in background

) for all these three

1 ACCEPTED SOLUTION

Former Member
0 Kudos
188

>

> Hi I know how to use and create inner join ( outer join) ,databse view and for all entries ....

> I want to know since all of these three are used for the same purpose...which one is better....

Neither of them is always evil nor always good. It depends.

FAE and Joins there are discussed here:

You will find some useful information (SAP Notes) where you can find how FAE is implemented by SAP and

you can decide for yourself when to use and wich common pitfalls you can step into.

> and projection view ( why do we really create it ) ...is it faster ..because no correpsoding table or view is created in databe for the projection view ...then why do we really create it ...is it more optimized ???

it's a common definition of queries that you can put aside and can be used by different developers

with a well known interface. It makes your live a little bit easier and hides some complex querie constructs from the developer. So it has nothing to do with optimization - only your query definition and underlying data model dictates the optimization.

> Once database view is created then does the data in databse view is selected suring the select query ie at thr runtime or data is permanently stored in int in database....as far as i know .....data is selected at runtime in databse view..

right

then why to create database view..instead I can use for all entries .........beacuse though using databse view we have one select query and using for all entries we have 2 select queries but internally when data is selected form a databse view.....database has to trigger two select queries....

If you are talking about joins wrapped by a view: Databases are much smarter than you think.There

is a piece of software called the Optimizer who generates execution plans for queries.

More than often you "scan" twice or more in FAE while using only one join query you have only one "scan" by the database. "Scan" can mean full table or index, index lookups, sort-merge / hash/ nested loops joins

(depends on database vendor).

> In nutsheel i wan to understand the functionality ( efficcincy wise and how it works in background

> ) for all these three

You might consider reading some books about these topics because using them efficiently

assumes that you have a good basic knowledge. Knowledge in a nutshell will not help you to survive in big data volumes or high transaction environments

You will find a lot here in SDN .

bye

yk

4 REPLIES 4

Former Member
0 Kudos
188

Hi Rajesh,

Welcome to SDN.

Please sreach in SDN forum before you post a query as there are lot of threads regarding ur

query.

http://help.sap.com/saphelp_47x200/helpdata/en/6a/082f38ffccf905e10000009b38f8cf/frameset.htm

u can have all stuff in this site

Regards

Former Member
0 Kudos
189

>

> Hi I know how to use and create inner join ( outer join) ,databse view and for all entries ....

> I want to know since all of these three are used for the same purpose...which one is better....

Neither of them is always evil nor always good. It depends.

FAE and Joins there are discussed here:

You will find some useful information (SAP Notes) where you can find how FAE is implemented by SAP and

you can decide for yourself when to use and wich common pitfalls you can step into.

> and projection view ( why do we really create it ) ...is it faster ..because no correpsoding table or view is created in databe for the projection view ...then why do we really create it ...is it more optimized ???

it's a common definition of queries that you can put aside and can be used by different developers

with a well known interface. It makes your live a little bit easier and hides some complex querie constructs from the developer. So it has nothing to do with optimization - only your query definition and underlying data model dictates the optimization.

> Once database view is created then does the data in databse view is selected suring the select query ie at thr runtime or data is permanently stored in int in database....as far as i know .....data is selected at runtime in databse view..

right

then why to create database view..instead I can use for all entries .........beacuse though using databse view we have one select query and using for all entries we have 2 select queries but internally when data is selected form a databse view.....database has to trigger two select queries....

If you are talking about joins wrapped by a view: Databases are much smarter than you think.There

is a piece of software called the Optimizer who generates execution plans for queries.

More than often you "scan" twice or more in FAE while using only one join query you have only one "scan" by the database. "Scan" can mean full table or index, index lookups, sort-merge / hash/ nested loops joins

(depends on database vendor).

> In nutsheel i wan to understand the functionality ( efficcincy wise and how it works in background

> ) for all these three

You might consider reading some books about these topics because using them efficiently

assumes that you have a good basic knowledge. Knowledge in a nutshell will not help you to survive in big data volumes or high transaction environments

You will find a lot here in SDN .

bye

yk

0 Kudos
188

YukonKid

points awarded ...thanx a lot

0 Kudos
188

ok,

only 4 from a range of good books that I have at hand at my desk:

SQL in General:

  • Stephane Faroult: "The Art of SQL" O'Reilly 2006

working with SAP on ORACLE:

  • Guy Harrison: "ORACLE SQL - High performance tuning" Prentice-Hall 2001

  • Jonathan Lewis: "Cost-based ORACLE: Fundamentals Vol.1" Apress 2005

last but not least...

  • Thomas Schneider: SAP Performanceoptimization Galileo Press 2007