Dynamic Join Builder question

[Bob Cergol]

Greetings, I'm back already!

I avoided DAI joins in favor of DAI views and did my joins in them, because I previously did not understand that once I created DAI views, they would become available for use by join builder! I should have known better, as a view amounts to a virtual table and of course tables can be joined.

Question: Given an invoice contains a Job number, customer ID, and sales rep ID, and that the order likewise contains the same customer and sales rep ID's does it make any difference whether I define the joins from those fields in the invoice versus the order to the customer and sales rep tables? Probably in a small database performance can't be an issue, but maybe it is in a large database and/or when many more tables are being joined? Or maybe there is just an understood "correct" way to do it even if both work?

My history with Dataflexis such that I am accustomed to mostly a single relational pathway, and it is always from the "bottom up" and children do not relate to their grandparents directly (though Dataflex permits it), rather through the parents.

Any tips or insights are appreciated.

Thanks,
Bob

[Eddy Kleinjan]

Hi Bob,

Although the question seems easy, this one is hard to answer. In database terms, you could talk about comparing the performance of a query using the snowflake model or the star model. Both have pros and cons. The best way to find out these things is to measure the performance on of the query by entering them in, for example, the Microsoft SQL Server Query Analyzer. You can ask it to proceduce an 'execution plan' that should show you how SQL Server will try to solve you query. Based on that plan, you can decide what would be the better way to choose.

Also note that the results depend on the database that you use, indexes that might or might not be there, etc. If you search for "snowflake vs star" you will encounter a lot of discussion about this theme.

Please note that Dynamic AI itself doesn't care. Simply put, it just askes the database to execute the query and return the result set. How the database achieves this result set, is completely up to the database server.

Hope this helps.

Best regards,

Data Access Europe
Eddy Kleinjan

[Bob Cergol]

Hi Eddy,

It sounds more like an arcane art than an exact science. I've never been able to find concise, simple rules-of-thumb for tuning SQL Server -- or P.SQL for that matter, though the product seems to require, and allow for, less-and-less tuning over the years.

When I look at the SQL that a DAI Join generates, if I were to create a DAI view using the same joins and using only the columns from the joined tables that I intend to use in a report, then the final SQL going to the server when running a report built on the join versus the view would not be any different. (I suppose I can verify that with the "show SQL" feature.)

However, I did learn one thing that will dictate the choice. If I want to build a form that combines fields from multiple tables into a common set, then I have to use a view. I found that building the form on a join separates the fields from each table into its own form and there seems no way in the design layout to move those fields from one sub-form to another. Interesting....

Bob

[Carsten Sørensen]

Hi Bob,

Only the floating form option is separating each joined source into it's own sub-form for display on the form. The other Form options - like the free form layout - should give you the freedom to move them around as you wish.

Best regards,
Carsten

[Bob Cergol]

Of course!
I missed that because when I was initially creating the report the only form option that appeared in the drop down was the "floating form". Now that I revisit the drop down I see the other choices.
Thanks,
Bob