NEED HELP in CREATING NEW DATABASE

  • I need help TO design DB that will contain the following tables. I am using ASP in the frontend

    USERS

    [userid] [int] NOT NULL,

    [password] [int] NOT NULL,

    [Fullname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [DOB] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [verified] [int] NOT NULL

    etcc............

    USER DATA

    [userid] [int] NOT NULL,

    [fileid] [int] NOT NULL,

    [servername] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [filename] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [Notes] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [views] [int] NOT NULL ,

    [private] [int] NOT NULL ,

    [official] [int] NOT NULL ,

    [tags] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [folderid] [int] NOT NULL

    NETWORK

    MYID [int] NOT NULL,

    YOURID [int] NOT NULL,

    FOLDERS

    Folderid Foldername Fodericonid

    There is no probelm with the above table if the rows less than 50000 rows but the table will have 5 lacs rows in another 6 to 8 months. I tested with the 5 lac dummy rows damn it took 3 mts to load the page. Afterthat I planned to split the USER DATA table to every single users, but it will cause problems with accessing in network mode. I also tried with stored procedure, views and indexes. Please give me some tips to HOW TO DESIGN TABLE.

  • A few things to consider:

    - What indexes do you have on these tables, and what is the clustered index on each table?

    - Have you performed a trace in SQL Profiler to see which query(s) are not performing well?

    - What are the WHERE clauses of the poorly performing query(s)

  • Where's the clustered index?

    What are the primary keys?

    Is that the only query that will be run on the tables?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The design is fine for storing the data, but having those LIKE queries will slow you down and guarantee that the server works hard on every query.

    You want to query intelligently. SELECT * shouldn't be done unless you really need all data in the app and I rarely see that. Usually you're looking for some data and querying all.

    You might revisit the application design and see where you can keep stuff in session from page to page or move it from page to page without requerying the db at times.

    It will help if you show more queries and explain the idea behind the queries (what you're trying to do) and we can give suggestions.

  • cuteprabakar (9/27/2008)


    Dear All, I need help TO design DB that will contain the following tables. I am using ASP in the frontend

    USERS

    [userid] [int] NOT NULL,

    [password] [int] NOT NULL,

    [Fullname] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [DOB] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [verified] [int] NOT NULL

    etcc............

    USER DATA

    [userid] [int] NOT NULL,

    [fileid] [int] NOT NULL,

    [servername] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [filename] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [Notes] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [views] [int] NOT NULL ,

    [private] [int] NOT NULL ,

    [official] [int] NOT NULL ,

    [tags] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

    [folderid] [int] NOT NULL

    NETWORK

    MYID [int] NOT NULL,

    YOURID [int] NOT NULL,

    FOLDERS

    Folderid Foldername Fodericonid

    There is no probelm with the above table if the rows less than 50000 rows but the table will have 5 lacs rows in another 6 to 8 months. I tested with the 5 lac dummy rows damn it took 3 mts to load the page. Afterthat I planned to split the USER DATA table to every single users, but it will cause problems with accessing in network mode. I also tried with stored procedure, views and indexes. Please give me some tips to HOW TO DESIGN TABLE.

    I don't see a clustered index, a primary key, any indexes... that would be good for a start, but the real problem is likely in the query you are using. 5 lacs of data is actually pretty small and shouldn't be a problem. Please post the offending query.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • cuteprabakar (9/29/2008)


    Tags??? How to setup this most of suggest set in new table???(max 10 tags with 20 characters length, previously I used space separated string)

    In a separate table.

    This will be the most used Queries:

    Distinct tags of users

    Distinct folders of users with number of files

    Select all files of user's (like command used in filename)

    Select files of users network (no like command will be used)

    Got any SQL for those? I could guess, but I could be completely wrong.

    What about primary keys? Where are the indexes currently and what type are they?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I am requesting you to help me to what is the ideal column to set the PRIMARY KEY and for indexing

  • cuteprabakar (9/30/2008)


    I am requesting you to help me to what is the ideal column to set the PRIMARY KEY and for indexing

    Primary key will should probably be UserId, FileId, FolderId and My_Id in each table respectivly.

    As for other indexes it depends on the queries that you expect to be run against these tables. But at a guess Username+Password would be a good start for the users table.

  • I can't help you with the primary key. The column must be unique and not null. From what you've posted, there's not enough info to ID the primary key.

    Post the queries that will be run please (the SQL statements) and we'll help with the indexing.

    You mentioned earlier that you have some indexes. Exactly what indexes do you currently have?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply