locked
How to avoid large number of joins when accessing data RRS feed

  • Question

  • Dear Friends,

    We are designing a multilingual web application. In our customer master table 25 fields are dropdown based fields (i.e EducationLevelCode,ResidentStatusCode,NationalityCountryCode,MaritalStatusCode,OccupationStatusCode,TaxStatusCode and so on).

    Seperate masters are designed for all these by keeping view of locale. 
    EducationLevelLocale

    EducationLevelCode      EducationLevel       LocaleCode
    ----------------------------------------------------------------------
    01                                HSC                                    en-US
    02                                Graduate                             en-US
    01                                HSC (spanish)                      en-MX
    02                                Graduate (spanish)               en-MX

    In front end first we show all the properties in view mode in label and at click of edit button user is redirected to data entry screen. At Data Entry screen we populate these dropdowns by settting selected value in the dropdowns.


    In our view screen we have to show educationlevel,nationality etc in the labels. I am doing this by adding an extra properties in customer object for each of them like EducationLevel,ResidentStatus etc and populating it in getbyid function by using joins in backend.

    Since our application is multilingual thats why we are passing localecode of user who is accessing the data in GetByID stored procedure and that parameter is used in join condition. 

    By following above mentioned approach we are able to get customer record in getbyid procedure after appling joins with these 25 tables.


    Is there any other approach like load all the dropdowns in cache at fronend and getting these property populated from there.


    I would appreciate if could suggest me some alternative approach to achieve this more efficiently.


    Regards,

    David Johnsan


     


    David Johnsan - Please remember to mark the replies as answers if they help.
    Wednesday, July 15, 2009 11:20 AM

All replies

  • Hi David,

    I would prefer to go for compactDB as compared to keeping data in cache.

    Thanks.
    Wednesday, July 15, 2009 4:30 PM
  • A join with 25 tables?  What other 25 tables are you joining to?  25 seems excessive generally, this would generally indicate a very poor database design.  Are you meaning that you join to a table for each locale?  This is the bit that looses me, perhaps you can explain differently?

    I'd say generally with relatively static text, caching is a good idea, as it avoids calls to the database, but there is a limit to how far it can be pushed, based on the amount of memory available, and the amount of data to be loaded.
    The drop-downs I would imagine are relatively static?  The education levels would also presumably be relatively static?  If this is the case, then caching would be a good idea.

    I'm not sure what GetById is meant to do - as a tip - name your methods so that they're a little more obvious what the intent is.  What gos ID in GetByID represent? 

    Please explain your problem a little further,

    Thanks,

    Martin.

    MCSD, MCTS, MCPD. Please mark my post as helpful if you find the information good!
    Friday, July 17, 2009 10:27 AM
  • Hi,

    I agree with Martin, you will have to rethink about your db design.

    Having said that you can consider Flyweight design pattern for your situiation.

    Regards,
    Vinil
    Friday, July 17, 2009 11:12 AM

  • Thanks everybody for your reply. 

    Let me explain my problem in detail. 

    To keep it simple I am taking the Branch Object 


    Following are my database Tables 

    TblCountry (CountryCode and LanguageCode is unique key.)
    -----------------------------------------
    CountryCode varchar(8)
    LanguageCode   varchar(8)
    CountryName nvarchar(256)


    TblTimeZone (TimeZoneCode and LanguageCode is unique key.
    -----------------------------------------
    TimeZoneCode varchar(8)
    LanguageCode   varchar(8)
    TimeZoneName nvarchar(256)


    TblBranch (branchID is PK)
    -------------------------------------------------
    BranchID            INT
    BranchName nvarchar(256)
    Address1 nvarchar(64)
    Address2 nvarchar(64)
    City nvarchar(64)
    State nvarchar(64)
    CountryCode varchar(8)
    TimeZoneCode  varchar(8)

    To support multiple languages in our application we had to add languageCode to each master table

    To populate CountryName and TimeZoneName in Branch ReadOnly View Screen we had to add CountryName and TimeZoneName properties in branch class.



    Following are my Business Entity, business logic and data layer 

    public class BranchEntity
          {
                     
                public int BranchId { get; set; }

                public string BranchName { get; set; }

                public string Address1{ get; set; }

                public string Address2{ get; set; }

               public string City{ get; set; }

                public string State{ get; set; }

                public string CountryCode { get; set; }
               
                public string TimeZoneCode{ get; set; }

                public string CountryName{ get; set; }

                public string TimeZoneName{ get; set; }

          }

    public class BranchManager
     {
    public BranchEntity GetBranchByID(int branchID,string LoggedInUserLanguageCode)
    {
    return BranchDB. GetBranchByID(branchID, LoggedInUserLanguageCode)
    }
     }


    public class BranchDB
     {
    public BranchEntity GetBranchByID(int branchID,string LoggedInUserLanguageCode)
    {
    // USP_GetBranchByID Stored Procedure Execution and BranchEntiry Object Population goes here

    return BranchEntity

    }
     }


    Stored Procedure:


    ALTER PROCEDURE [dbo].[USP_GetBranchByID]

    @LoggedInUserLanguageCode         VARCHAR(8),
    @BranchID                                    INT

    AS

    BEGIN

    SET NOCOUNT ON


     SELECT     dbo.Branch.BranchName, dbo.Branch.Address1, dbo.Branch.Address2, dbo.Branch.City, dbo.Branch.State,            dbo.Branch.CountryCode, 
                dbo.Branch.TimeZoneCode, dbo.TimeZoneLocale.TimeZoneName, dbo.CountryLocale.CountryName
     FROM       dbo.Branch INNER JOIN
                dbo.CountryLocale ON dbo.Branch.CountryCode = dbo.CountryLocale.CountryCode INNER JOIN
                dbo.TimeZoneLocale ON dbo.Branch.TimeZoneCode = dbo.TimeZoneLocale.TimeZoneCode
     WHERE      dbo.Branch.BranchID =@BranchID AND 
                dbo.TimeZoneLocale.LanguageCode=@LoggedInUserLanguageCode AND
                dbo.CountryLocale.LanguageCode=@LoggedInUserLanguageCode  
        
    END




    Hope this explains you everything. As you can see for getting TimeZoneName and CountryName at front end two joins are used in my select query. What will happen when I would try to fetch my 25 master columns name(EducationLevel,ResidentStatus,Nationality,.........) in my Customer Object.


    I would appreciate i you could suggest me some efficient approach of handling this.


    Regards,

    David

    David Johnsan - Please remember to mark the replies as answers if they help.
    Saturday, July 18, 2009 10:49 AM
  • Hi David,

    Go for denormalizing the database, as in this case the database is normalized to the extent.

    The ideal way to handle this scenario would be going or database Views, create a dababase view and store the column data required for each individual by UserID.

    Creating a database view will avoid joining the 25 master tables, which help in fetching the data from one source.

    Just a confirm, do see more than one language code for a country in your requirement?

    Let me know if this works for you.


    Regards,
    Vishal Mohan
    Monday, July 20, 2009 9:00 AM
  • Dear Vishal,

    Thank You For Chipping In!

    Our problem is that more than one language can be used in a country i.e some of US clients would be working in spanish and some in US-English.

    Creating a view would definitely decrease the complexity but would not provide us the efficiency.

    Should we load all our master data culturewise at front end "application" variable and populate all these master properties by using linq queries.

    How do you see this approach.

    Regards,

    David 


    David Johnsan - Please remember to mark the replies as answers if they help.
    Tuesday, July 21, 2009 5:11 AM
  • Dear David,

    Yes there can be more than one language used in a country, but its again the user who is mapped to the different languages. A User can be mapped to a language, and A Country has one language. am i right?

    Can you elobrate on the efficency part of view further.

    Regards,
    Vishal Mohan

    Tuesday, July 21, 2009 9:25 AM