NEED HELP in CREATING NEW DATABASE

Question

Post reply

NEED HELP in CREATING NEW DATABASE

cuteprabakar

SSC Enthusiast

Points: 174
More actions
September 27, 2008 at 6:27 am

#93832

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.

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

cuteprabakar SSC Enthusiast Points: 174 More actions · Answer 6

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

steveb. SSC-Forever Points: 46733 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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