033 Problem with full table scan - chempkovsky/CS82ANGULAR GitHub Wiki

When the current implementation is useful

When the current implementation is useless

Suitable approach

Current implementation

Separate lookup resource as a solution

Terminology

Clarification of the requirements for the lookup resource

When the current implementation is useful

  • Sql-query, which requires full table scan is not a problem for a short tables (for tables with few rows).
  • It's not that bad for a workgroup level app, where the number of users is not so big (one hundred or so).

When the current implementation is useless

  • For tables with a huge number of rows, such a requests become time consuming. The second disadvantage is that requests require more server resources.
  • For an enterprise-level application (with a large number of users), queries that require a full table scan should be avoided altogether.

Suitable approach

  • The only suitable approach is to use primary or unique keys or indexes with high selectivity. It is known that the cost of using indexes on columns with low selectivity can be the same or even higher than with a full table scan.

Current implementation

  • For the PhoneType (007 - 018.1) we applied a full‑table scan. and It is a correct approach, since the table has a few rows.

  • For the PhbkEnterprise (020 - 025.1) we declare additional unique index. We assume that this table will have a huge number of rows. Sql queries on primary or unique key do not affect application performance. After security is implemented, we'll show you how to deny full scan requests for this table.

  • For the PhbkDivision (027-032.1) we can not applied a full‑table scan and we can not declaring additional unique indexes. We assume that this table will have a huge number of rows. For illustration purpose, please compare available filters when the component is used in along mode and in one-to-many mode. Filtering by primary key is not available in one-to-many mode.

Click to show the picture

project structure

  • Suppose, the Divisions-table has more than one foreign key and suppose, we need to implement filtering by Division Name in one-to-many mode.
    • For each possible combination of foreign keys we need to create a separate index.
    • Of course, it can not be a solution.

Separate lookup resource as a solution

  • To solve the problem mentioned above, we need a separate lookup resource for tables that have foreign keys or low selectivity columns, but these columns are required to be used for filtering. Separate lookup resource can be created for any table you like.
  • Let us formulate the requirements for such a lookup resource for the given table
    • It must be implemented as a set of separate tables (even on the separate SQL-server)
    • It accepts only queries by unique or primary key
    • It should implement preliminary queries (Typeahead or Autocomplete)
    • The final query to the lookup resource should return the list of the primary keys of the given table for which lookup resource is created
    • It should be updated automatically in async mode

Terminology

  • The requirements formulated in the Separate lookup resource as a solution -paragraph are too common. But before we dive deeper, we need to define some of the terms we are going to use below.
  • Take a look at the PhbkDivisionView-View we created for PhbkDivision-entity
Click to show the code 
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Linq.Expressions;
using System.Text.Json.Serialization;


namespace PhBkViews.PhBk {
    public class PhbkDivisionView {
        // [JsonProperty(PropertyName = "divisionId")]
        [JsonPropertyName("divisionId")]
        [Required]
        [Display(Description="Row id",Name="Id of the Division",Prompt="Enter Division Id",ShortName="Division Id")]
        public System.Int32  DivisionId { get; set; }

        // [JsonProperty(PropertyName = "divisionName")]
        [JsonPropertyName("divisionName")]
        [Required]
        [Display(Description="Name of the Enterprise Division",Name="Name of the Division",Prompt="Enter Division Name",ShortName="Division Name")]
        [StringLength(20,MinimumLength=3,ErrorMessage="Invalid")]
        public System.String  DivisionName { get; set; } = null!;

        // [JsonProperty(PropertyName = "divisionDesc")]
        [JsonPropertyName("divisionDesc")]
        [Display(Description="Description of the Enterprise Division",Name="Description of the Division",Prompt="Enter Enterprise Division Description",ShortName="Division Description")]
        [StringLength(250,ErrorMessage="Invalid")]
        public System.String ?  DivisionDesc { get; set; }

        // [JsonProperty(PropertyName = "entrprsIdRef")]
        [JsonPropertyName("entrprsIdRef")]
        [Required]
        [Display(Description="Row id",Name="Id of the Enterprise",Prompt="Enter Enterprise  Id",ShortName="Enterprise Id")]
        public System.Int32  EntrprsIdRef { get; set; }

        // [JsonProperty(PropertyName = "eEntrprsName")]
        [JsonPropertyName("eEntrprsName")]
        [Required]
        [Display(Description="Name of the Enterprise",Name="Name of the Enterprise",Prompt="Enter Enterprise Name",ShortName="Enterprise Name")]
        [StringLength(20,MinimumLength=3,ErrorMessage="Invalid")]
        public System.String  EEntrprsName { get; set; } = null!;

    }
}

  • DivisionId is mapped to the primary key of the PhbkDivision-entity.

  • EntrprsIdRef is used by the foreign key the PhbkDivision-entity

  • DivisionName, DivisionDesc are mapped to the scalar properties the PhbkDivision-entity. These properties are not used by the primary key and are not used by the foreign key.

  • EEntrprsName is mapped to the scalar property of the PhbkEnterprise-entity which is a master entity for the PhbkDivision-entity

  • For the given View created for the given Entity

    • We say that the give View's property(properties) is(are) primary key property(properties) if it(they) mapped to the primary key property of the given Entity
    • We say that the give View's property(properties) is(are) foreign key property(properties) if it(they) mapped to the foreign key property(properties) of the given Entity
    • We say that the give View's property(properties) is(are) scalar property(properties) if it(they) mapped to the scalar property(properties) of the given Entity
    • We say that the give View's property(properties) is(are) master scalar property(properties) if it(they) mapped to the scalar property(properties) of the master Entity for the given Entity

  • Suppose A-table has a foreign key that refers to B-table. Suppose B-table has a foreign key that refers to C-table.

    • We say that B-table is a direct master for the A-table
    • We say that C-table is a indirect master for the A-table
    • We say that A-table is a direct detail for the B-table
    • We say that A-table is a indirect detail for the C-table

Clarification of the requirements for the lookup resource

  • After defining the terms, we continue the formulation of requirements

  • For the given view lookup resource can include

    • scalar properties
    • foreign key properties
    • additional properties which are properties that are not mapped to any property of the given view.
      • additional properties can be used for indirect foreign keys

  • For each scalar property of the lookup resource

    • two columns table must be created
      • the first column is a primary key of the table and play the role of the row Id
      • the second column
        • must have the same name as the scalar property
        • must be of the same data type as the scalar property
        • unique key must be defined for the second column
    • we call such a two-column table a lookup dictionary

  • In addition to the lookup dictionary, lookup resource must include so called lookup refs table

    • lookup refs table must satisfy the following requirements
      • primary key of the lookup refs table must include all the columns of the given table (the table for which we create lookup resource)
      • the order of the columns in the table's primary key is important for lookup refs
        • if additional properties are included into lookup resource they must follow first in the list of the primary key of lookup refs-table
        • if foreign key properties are included into lookup resource they must follow next in the list of the primary key of lookup refs-table
          • the names of foreign key properties must match the names of the properties of the given View
          • the datatypes of foreign key properties must match the datatypes of the properties of the given View
          • for a composite foreign key, the order of the columns of foreign key properties must be the same as the order of the columns of the foreign key of given view
      • the very last group of properties of the lookup refs-table is a primary key of the given View
        • the names of last group of properties must match the names of the primary key properties of the given View
        • the datatypes of last group of properties must match the datatypes of the primary key properties of the given View
        • for a composite primary key, the order of the columns of last group of properties must be the same as the order of the columns of the primary key of given view

  • The last but very important requirements:

    • we need to prompt the Wizards to recognize the lookup resource
    • to make lookup resource recognizable to Wizards
      • for a table lookup refs we have to declare a foreign key that refers to given view
        • but such a foreign key must be a pseudo foreign key. It should only be available at design time, not at compile and run time. (The requirement is that the lookup resource should ideally reside in a separate database.)
      • if foreign key properties are included in the lookup resource, the corresponding foreign keys must also be explicitly declared for the lookup refs-table.
        • but such a foreign keys must be a pseudo foreign keys. It should only be available at design time, not at compile and run time. (The requirement is that the lookup resource should ideally reside in a separate database.)
⚠️ **GitHub.com Fallback** ⚠️