May 14, 2002 at 3:17 am
Can anyone shed some light on this? I need to know the correct process for writing the 'FROM' part of an SQL statement when there are multiple tables and multiple joins. E.G
tab1 RIGHT OUTER JOIN tab2 RIGHT OUTER JOIN
tab3 ON tab2.fld1 = tab3.fld1
ON tab1.fld2 = tab3.fld2 RIGHT OUTER JOIN
tab4 INNER JOIN
tab5 ON tab4.fld3 = tab5.fld3
ON tab3.fld4 = tab5.fld4
I can use view design in enterprise manager, but I need to exactly how it groups and constructs the joins.
Anyone?
May 14, 2002 at 4:46 am
To watch what SQL is doing run in QA with Show execution plan on. The query engine will make some decisions which can vary based on the data itself and the statistics it has in place.
Normally based on this you will see a brach combining tab2 to tab3 and a leg for tab4 to tab5 then up a level tab45 to tab23 then up a level to tab1 to tab2345. However if it will do a hash join or other without specific hints, or an index search or scan, etc. is hard to say without knowing a lot more about your DBs structure and QAs Execution Plan view is right there to give you the visual output.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 5:08 am
Thanks for your reply......
This means that there is no hard rule for constructing the 'from' clause?
I am trying to create a basic query tool in VBA. Each query will be based on the five tables (no more, no less). All I need to do is change the joins according to a selection made by the user.
Short of creating an sql template for each join variation I am out of ideas.
Ideas?
May 14, 2002 at 5:37 am
Yeah it does become kind of hard to get very specific especially since index changes and adding of column statistics can potentially change the process. Hoever the query engine is quite gracefull in it's job and usually with good DB maintainence can make excellent decisions based on standard queries. If the joins do not vary a extreme amount then consider maybe doing work in SP which can stored their execution plans and give a bit of performance boost, other than that various things can change what you expect to happen.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 8:14 am
I would also go with the stored proc and included all available variatins of joins in the different select statements. Then using IF statement and distinct flag that was passed into proc will select what sort of join to use.
Like:
create proc _getWhatever
flag char(1)
as
If flag ='A'
begin select whatever
from tab1 RIGHT OUTER JOIN tab2
RIGHT OUTER JOIN tab3
ON tab2.fld1 = tab3.fld1
ON tab1.fld2 = tab3.fld2
end
If flag ='B'
begin select whatever
from tab1 Left OUTER JOIN tab2
RIGHT OUTER JOIN tab3
ON tab2.fld3 = tab3.fld1
ON tab1.fld2 = tab3.fld2
end
etc
May 14, 2002 at 8:46 am
I think I'll have to go with your suggestion.
Only problem with that is the potential 27+ different variations that I'll have to create....also it doesn't sit well for expansion if I need to add further tables to the five table template.
Thanks for your suggestions
🙂
May 14, 2002 at 9:10 am
Actually I think epol suggestion is great but I would take it one step further. I would create a Stored Procedure for each possiblity and a centrally called procedure following the logic epol gave. The reason is then each query can store an execution plan for itself to give you maximum bennefit from the stored procedure. Otherwise you will need to add the WITH RECOMPILE option to the Stored Procedure so you do not store an execution plan that may not support or acctually will conflict with one or more options after the first run.
Ex.
CREATE PROC ip_Test
@Opt AS int
WITH RECOMPILE
AS
IF @Opt = 1
BEGIN
SELECT .....
END
ELSE IF @Opt = 2
BEGIN
SELECT .....
END
......
Or
CREATE PROC ip_Test
@Opt AS int
AS
IF @Opt = 1
BEGIN
EXEC ip_Test2
END
ELSE IF @Opt = 2
BEGIN
EXEC ip_Test3
END
......
GO
CREATE PROC ip_Test2
AS
SELECT .....
GO
CREATE PROC ip_Test3
AS
SELECT .....
GO
The first needs to have the WITH RECOMPILE option in order to drop the previous runs execution plan. Where the second does not and since it calls a seperate stored procedure those procedures can keep execution plans which will be specific to their needs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 9:22 am
quote:
Only problem with that is the potential 27+ different variations that I'll have to create....also it doesn't sit well for expansion if I need to add further tables to the five table template.
You are wrong - there is no problem with expansion, you could change your procedure independently from application - the only thing will indicate - passing additional parameter. And as Antares suggested - create separate procs and call them from the main procedure, then any changes will affect only small part of the whole kaboom.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply