Is there any option within LLBL Gen Pro v5.6 that allows for foreign key indexes to be created when generating DDL Sql Create script?

I haven't seen anything, and it seems like this would be a very important feature to have for database usage.

Thank you.

No LLBLGen Pro doesn't generate index DDL SQL for FK fields. We don't do that as indexes aren't part of an entity model but actually part of the query behavior and the data in the tables: if you insert more than you query, an index will hurt performance so you should add indexes at places where they're needed, not 'just because it's an FK field', hence we don't do that.

Sure, they are related to query behavior, just like FK's are. Indexes are also a fundamental aspect of the storage model schema.

1. Since automatically generating indexes for FKs is better than not in our environment, how would you recommend we enable that behavior?
2. If configuring that behavior is not possible, what workflow do you recommend for maintenance of model-related indexes, that allows LLBL-generated storage model compatibility with an RCS environment, including build-time schema checking?

Side note, on the thrust of your initial reply that it's not an LLBL concern because it's a query concern. Conceptually, the purpose of the ORM is to present queries as OO interactions. The ORM is building the queries, and best understands the shape of the queries it will build on navigation properties, and best the indexes that are necessary to support the queries the ORM itself will build.

The point is, we are looking to migrate to your product from EF. This is a feature we appreciated in EF and desired to keep. We would like the feature. I understand your suggestion that we shouldn't do it that way, but it's an opinionated topic.

If we don't keep the feature, it would help us to understand and appreciate your option if you gave us more information. A hint as to the alternate workflow you use in your solutions could be a powerful persuasive tool. Maybe another approach would work great for us, and ease our concerns about this feature we lost.

Just a thought. In future versions of the product, you could allow the user to select "Index this navigation property" in the LLBL designer. You could also allow the user to decide if the option was enabled or disabled by default on new navigation properties.

shannonm wrote:

Sure, they are related to query behavior, just like FK's are. Indexes are also a fundamental aspect of the storage model schema.

FKs are part of the model, as they represent relationships between entities, indexes are purely an optimization technique, like there are others too, for queries. Please don't see them as the same thing. See below.

1. Since automatically generating indexes for FKs is better than not in our environment, how would you recommend we enable that behavior?

This could be done with a template which generates a DDL SQL file for the FKs in the project. You can also add the index creation DDL SQL to the CreateForeignKeyConstraintInclude.lpt template which creates an FK Constraint to a table in the DDL SQL script generation. This should be the ideal spot as you know at that location what the FK constraint is and thus which fields participate in it. It's easy to edit this template and run it instead of the vanilla one every time you export a DDL SQL script.

This is however problematic for a reason: you don't know any fill factors nor the specifics about an index at the model level. So how are you going to generate that? E.g. are locks required, or not allowed? It's essential for indexes to function properly that they're setup/tuned correctly. This isn't 'set and forget', you need to keep track on them (see: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index?view=sql-server-2017 )

1. If configuring that behavior is not possible, what workflow do you recommend for maintenance of model-related indexes, that allows LLBL-generated storage model compatibility with an RCS environment, including build-time schema checking?

What's an 'RCS environment', I'm not familiar with the term...

I'd monitor which queries need optimization in the database and if an index is the right way to optimize them, make sure the index is setup properly. At runtime (and only then) you know the fill factor needs for your index and other characteristics, and which index will properly solve the query needs. For instance if you have a compound PK-FK pair, it might be most queries use 1 field in the FK and thus an index is needed on that field, but not on the compound FK together (as querying on 1 field in an FK where the complete FK is covered by an index won't use the index)

shannonm wrote:

Side note, on the thrust of your initial reply that it's not an LLBL concern because it's a query concern. Conceptually, the purpose of the ORM is to present queries as OO interactions. The ORM is building the queries, and best understands the shape of the queries it will build on navigation properties, and best the indexes that are necessary to support the queries the ORM itself will build.

An ORM won't touch any indexes by itself: that's left to the DB to deal with. The ORM also can't even emit hints by itself for this if it wanted to because it has no statistics to optimize the query on, the DB does. (Hints for 'use this index' is discouraged by RDBMS-s anyway)

I really don't see why an index should be the concern of the ORM: it doesn't have any information to act on: the statistics used to optimize SQL queries are in the DB, the RDBMS knows these and optimizes the query.

Which index to use, is depending on the query and it's up to the RDBMS during the SQL optimization stage which index is used, if any. E.g. maybe it turns out a partitioned view is more efficient for a particular use case than the live query with a couple of indexes...

shannonm wrote:

The point is, we are looking to migrate to your product from EF. This is a feature we appreciated in EF and desired to keep. We would like the feature. I understand your suggestion that we shouldn't do it that way, but it's an opinionated topic.

No it really isn't opinionated, sorry. An entity model doesn't contain index information. Based on FKs you can guess where indexes might be needed, but that's not a given, nor does the FK tell you in all cases which queries are going to be performed a lot and therefore need an index.

E.g. on SQL Server, by default a PK gets a clustered index. But perhaps a table doesn't need it on a PK and another field needs a clustered index. Perhaps you need a fillfactor X now and a fillfactor Y later on because the dataset grows. This means you need to monitor your queries, see where bottlenecks are prominent and check execution plans and only then check what you can do differently. Maybe an index will solve it, but maybe a different query altogether is better, or executing 2 queries, or removing a select n+1 bottleneck, who knows?

Adding an index to every FK and be done with it isn't going to work: the index might not be sufficient (compound keys) and there might be other indexes needed more for the queries which are slow(er). Indexes are a vital part of a database used in an application, but not part of the model, they're part of the runtime aspect of the model and depend on how much data is in the tables and which queries are run on that data.

If we don't keep the feature, it would help us to understand and appreciate your option if you gave us more information. A hint as to the alternate workflow you use in your solutions could be a powerful persuasive tool. Maybe another approach would work great for us, and ease our concerns about this feature we lost.

We give away our profiler for free for licensees so developers can use it when they write queries in their application, monitor what their code is really doing. It's far more valuable to know what's really going on when e.g. a webpage is opened in your web app, than to simply add an index on every FK and be done with it. Because ... that's not the case: indexes need tuning over time: they depend on the amount of data, and like I said above an index over a compound fk is useless if you query a lot over a part of it.

So it's more valuable to see what queries are actually executed by your application and then check what the execution plans are, if there are concerns of performance. Adding an index might introduce performance problems where before you didn't have them, especially in cases with a lot of inserts/updates and not frequent select queries.

shannonm wrote:

Just a thought. In future versions of the product, you could allow the user to select "Index this navigation property" in the LLBL designer. You could also allow the user to decide if the option was enabled or disabled by default on new navigation properties.

Sure. This was already on our future items to work on (Index advisor). But like the index advisor in SQL Server Management Studio, it won't be 100% airtight: it can guess and it might have a lot of it right but only monitoring at runtime of the app you're building/running will tell you where you really need indexes (and thus should add them too )

Our system comes with a code generator so it's not that hard to add this yourself, e.g. by adjusting the DDL SQL templates used to generate create/update DDL SQL scripts, and if you need help with that, we can assist you in that too, give you a head start if you want.

In v5.6 (released next week, if everything goes to plan) we ship a new feature called adhoc templates which allows you to quickly add a template to a code generation cycle, so this e.g. should be ideal if you want to run a template which generates index creation statements in DDL SQL.

In v5.5 and earlier you can add your own templates as well btw, but it's a bit more complex (you have to edit template bindings files and presets, it's not rocket science but it's also not a simple click of a button)