1

I have a database with following related table ( sql server enterptise edtion 2014)

each state have many citites which each city has may municpalArea and so on

   State => Cities = > MuniciplArea => CallCenter => CallCenterCode => **LandLine**

Then I have a LandLine table which is required to be searchable by the value from the above tables e.g

   select from landline where stateId =1 

The main requirements is performance

The two solutions that I have in mind are:

using relationship in landline table and making cascade delete off because of circular reference so i would have something like :

    landline (table)
        MunicipalAreaId  , CallCenterId , StateId,CityId, CallCenterCodeId

the other solution is cutting the relationship between tables so my landline table is like

    landline (table)
        MunicipalCode  , CallCenterCode , StateCode ,CityCode , CallCenterCode

both of these solutions are provided to avoid join in order of better performance.

maybe joining these tables doesn't have that much of impact on performance that I'm afraid of so how should I design tables?

should i go with either of the above solutions? ( which one)

or should i go with joining tables ? ( is performance good enough? )

is there any alternative solutions?

Update: e.g Joins that are needed to select landlines from the same state

   SELECT *   FROM     LandLines AS [Extent1]
      INNER JOIN  CallCenterCode  AS [Extent2] ON [Extent1].[SabetCallCenterCodeId] = [Extent2].[CallCenterCodeId]

      INNER JOIN [dbo].[CallCenter] AS [Extent3] ON [Extent2].[CallCenterId] = [Extent3].CallCenterId]

      LEFT OUTER JOIN [dbo].[MunicipalArea] AS [Extent4] ON [Extent3].[MunicipalAreaId] = [Extent4].[MunicipalAreaId]

      INNER JOIN [dbo].[City] AS [Extent5] ON [Extent4].[CityId] = [Extent5].[Id] WHERE 63 = [Extent5].[StateId] 
3
  • 1
    Why do you expect poor performance? Have you created a test database where you can test your different options? You should be able to generate enough data in the test database to give you a reasonable basis for the tests.
    – RLF
    Sep 3, 2014 at 2:05
  • because I need at least four joins to select from landlines which have the same state Sep 6, 2014 at 4:09
  • I don't remember the terminology, but isn’t there (at least in some DBMSs) a way to create a “saved query” that presents to SQL as a “virtual table” (maybe only read-only in this case, or at least restricting adding new records when all info to populate the relationship is not available) that can be used for queries to avoid having to repeat the join commands every time? If this exists, then shouldn’t the DBMS also automatically cache that table to minimize performance issues? Mar 8, 2021 at 13:53

1 Answer 1

1

How big is this database? How many rows are in each table? Etc?

I would say that normalized data is default state to try to obtain. It is a leaner database, rows are shorter, and indexes may be used more effectively. The short, leaner rows therefore lead to a smaller, leaner database.

One of the major accelerators of performance is memory. If you can get your 5 tables to remain cached in memory, that will be a performance accelerator for your queries since you will avoid much of the disk I/O overhead.

You identify that you are joining with IDs (which are usually integers), so your indexes may be narrow and offer relatively inexpensive joins.

If you decide to denormalize, your tables will be bigger because they are carrying more redundant data on every row. This causes a need for more memory to keep the data in cache and will require even more I/O when the cache is insufficient to buffer the data. (And your backups are bigger.)

In addition, you have taken on the task to denormalize and to maintain the denormalized data. This is an extra load of programming and on the server as well: consuming memory, I/O, and CPU.

But sometimes denormalization is the best choice. Data Warehouses, for example, are largely denormalized data. Also, you may find that in your system the benefits of denormalization may exceed the cost.

Still, you are asking a forum for an answer.

Your best answer would come by building normalized test case and seeing how it works. Even though you may not have a lot of 'real data', you should generate a fairly large data set in the millions of rows to test with.

You can try to find a tool that does it for you (RedGate has one for example, but it is not free) or generate the data yourself so that you control the complexity. There are online sources of states, cities, et cetera, and you can make up call centers, generate landline numbers and so forth.

Then try it.

If you do not like the performance, then create a denormalized table to test. And put some effort into writing the code to maintain the denormalization, since that will become integral to your process.

1
  • there are about 2 million rows in landline table which the most CRUD operations happen at the same time. and mostly searched by state in this table Sep 6, 2014 at 4:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.