SQL Server Performance

DB Design and Data Retrieval from a heavy table

Discussion in 'SQL Server 2008 General Developer Questions' started by Diamond2016, Jun 1, 2012.

  1. Diamond2016 New Member

    My current design and business logic is as following:

    There can be N number of clients. CLT_Clients table stores client information.

    There can be N number of projects associated to a client. PRJ_Projects table stores project information and a link to the client.

    There can be N number of listings associated to a project. PRJ_Listings table stores listing information and a link to the project.

    There can be N number of source entities associated to a listing. ST_Entities table stores source entity information and a link to the listing.

    This source entity is the actual import that contains the InvestorID, position values, source date, active and formula status.

    The name of the import e.g. L1Entity1 is stored in ST_Entities table alongwith ID field i.e. EntityID
    InvestorID, Position, Source Date, Active and Formula values get stored in ST_Positions table

    Database Diagram


    Data need to be view as following:


    With this design I’m able to handle N number of imports because the Position, Source Date, IsActive, Formula columns get Pivoted.

    The problem that I’m facing with this design is that the system performs very slow when it has to select data for more than 10-12 source entities, and the requirement is to show about 150 source entities. Because data is not stored row wise and I need to show it column wise, hence dynamic queries are written to pivot these columns which takes long.

    Please comment/suggest on my current database design if it’s correct?
    If I need to stick to my current, what can be done to improve the performance?

Share This Page