Optimization of an MSSQL Database

Question

Briefly I would like to explain my project.

I have already built an eCommerce website (ASP.NET C# MSSQL) where users are asked to register before payment (in case if they are not registered).

Whenever a user register, 1 table is created automatically using C#. The purpose of this table is to store data for Cart Items. I thought this would make the logic needed for Cart Items/table easier. Recently I realize the impact of this logic. At the moment I have like 735 users, which means 735 more tables. So the total count of my tables is 785 (including 50 tables I use for the whole website). Now it seems my website is getting slow and above that sometimes, when a user register, the cart table is not created. Due to this, there arises an unexpected error. Till now there are like 8 users for whom the cart table is not created. I had to manually create those.

My assumption is that, my website is getting slow due to the number of tables in my database.

Guys, please help me understand my problem and if possible give me advice or suggestions so that I may optimize my database and website. And is there a limit, like how many tables can be created per database?

Thank you for your time and concern.

Answer 1

Firstly, the cart design is just wrong, and should be changed.

I strongly suspect that if you've gone for that "cart" design, this is indicative of (thinks how to put it nicely)... a level of experience that could have led you to make many other poor design choices.

The performance issue could be related, but without profiling there is no way to know for sure. Frankly, there are all kinds of ways to make a system slow. Consider using a tool like mini-profiler, which will help you understand what code is behaving slowly.

Answer 2

That is poor database design. You should (almost?) never have to create tables dynamically. Create one "Cart" table and add a user id field. Use that table for everybody. Modify your queries to filter by user id.

There is not set limit on the number of tables. You can have over 2 billion database objects: http://technet.microsoft.com/en-us/library/ms143432.aspx

Do invest the time to script out transferring data to the new single table... it will be worth it in the end. There is tremendous overhead with your current approach (creating objects, permissions, indexing, optimizing, etc.).

Answer 3

Use one "cart" table, storing all the cart items for all users in that table. Distinguish the different carts by making use of a cartID, that is bound to the specific user.