August 18, 2010 at 2:19 pm
There are 60k available reps in the database. There's going to be 60k times 8 records in the database.
August 18, 2010 at 2:32 pm
So the user at the front end has to scroll, potentially, through a list of 60,000 entries to pick the two he's looking for? Isn't there any way to limit that? By address, or state, or ...
August 18, 2010 at 2:37 pm
Once I restructure the Hierarchy table and take all of the redundant Crap out I think it will be a lot less than 60k records. Because not all of the records in that table are Reps. They have all different data records jumbled together. It looks like I am getting my data dump from some sort of OLAP Data Cube because it's not normalized at all and it has many redundancies.
I'm going to pull apart the entire table and redo.
August 19, 2010 at 9:25 am
One additional suggestion: load the data in UPPER case since you are burning sql server CPU cycles to do this on every output?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 19, 2010 at 9:59 am
gregory.perry2 (8/18/2010)
Once I restructure the Hierarchy table and take all of the redundant Crap out I think it will be a lot less than 60k records. Because not all of the records in that table are Reps. They have all different data records jumbled together. It looks like I am getting my data dump from some sort of OLAP Data Cube because it's not normalized at all and it has many redundancies.
If this is the case proposed index on PERIOD should also include other columns that would help to filter and reduce the retrieved dataset.
Still not buying the concept of an end user browsing thru 60K rows to make a selection.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 19, 2010 at 1:17 pm
I wanted to take the time to thank you all for you extensive help in reconstructing my mess of a Stored Procedure. I have done the first phase of reconstruction in re-designing the table that the data was being selected from. I have no doubt that this was my biggest problem.
I broke that one HUGE table down into several smaller tables and reduced the field sizes where I was able to do so based on the raw data values.--Here is an example of the changes that I made:
SALES_CODE TABLE:
[Period] [datetime] NOT NULL
[SALES CODE] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SALESCODE_STATUS] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
FOREIGN KEYS[REP_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
REP_TABLE:
[Period] [datetime] NOT NULL
[REP_LNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REP_FNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REP_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REP_EMAIL] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HAS_ACTING_COACH] [BIT] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
FOREIGN KEYS[COACH_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SM_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GM_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VP_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SVP_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CENTER_CODE] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
COACH_TABLE:
[Period] [datetime] NOT NULL
[COACH_LNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COACH_FNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COACH_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COACH_EMAIL] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
SM_TABLE:
[Period] [datetime] NOT NULL
[SM_LNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SM_FNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SM_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SM_EMAIL] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CENTER_TABLE:
[Period] [datetime] NOT NULL
[CENTER_CODE] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CENTER_TYPE] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CENTER_NAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
GM_TABLE:
[Period] [datetime] NOT NULL
[GM_LNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GM_FNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GM_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GM_EMAIL] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
VP_TABLE:
[Period] [datetime] NOT NULL
[VP_LNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VP_FNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VP_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VP_EMAIL] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
SVP_TABLE:
[Period] [datetime] NOT NULL
[SVP_LNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SVP_FNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SVP_ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
I'll continue working through this problem and am very greatfull for all of your help..
Greg:cool:
August 23, 2010 at 5:09 pm
Having multiple tables (COACH_TABLE, GM_TABLE,ect.) for contacts indicates a problem.
Preferably you have one table to track all your humanoid data points.
Contacts:
[Period] [datetime] NOT NULL
[LNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FNAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ATTUID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
Then track the relationship of a contact according to your logic. Probably one contact can play multiple roles. So you may wan't a table to capture these multiple roles.
ContactRoles
ATTUID [nvarchar] (10) -the contact
Role [nvarchar] (10) -the contact role. A contact with multiple roles will have an additional record(s).
The term period implies a beginning and an end, not a single point in time. Maybe you need PeriodEnd and PeriodBegin.
Keep woring with us....
August 25, 2010 at 9:32 am
Thank you for your advice regarding this Data Modeling Exercise that I’m having to do. After considering your post, I have further re-designed my table structure to look like the following.
Sales_Code Table:
[SummaryMonth] [datetime] NOT NULL
[SALES CODE] [nvarchar] (20) NULL ,
[SALESCODE_STATUS] [nvarchar] (1) NULL ,
[Rep_Attuid] [nvarchar] (10) NOT NULL
Organization Table:
[SummaryMonth] [datetime] NOT NULL
[ATTUID] [nvarchar] (10) NOT NULL,
[FName] [nvarchar] (50) NULL,
[LName] [nvarchar] (50) NULL,
[E_Mail] [nvarhcar] (50) NULL,
[Level_Indicator] [nvarchar] (10) NOT NULL
HIERARCHY Table:
[SummaryMonth] [datetime] NOT NULL
[REP_ATTUID] [nvarchar] (10) NULL,
[CM_ATTUID] [nvarchar] (10) NULL,
[SM_ATTUID] [nvarchar] (10) NULL,
[GM_ATTUID] [nvarchar] (10) NULL,
[VP_ATTUID] [nvarchar] (10) NULL
This structure I think allows for me to eliminate the Organization tables and refer to them using their Id's based on the value placed in the Level_Indicator ie: "Rep", "CM", "SM", "GM", or "VP" once this value is established I can link to the Hierarchy table to see how their org roles up.
Thanks for all the helpful suggestions, and feel free to comment on the changes that I've made so far.
August 26, 2010 at 3:01 pm
[SummaryMonth] [datetime] NOT NULL
[REP_ATTUID] [nvarchar] (10) NULL,
[CM_ATTUID] [nvarchar] (10) NULL,
[SM_ATTUID] [nvarchar] (10) NULL,
[GM_ATTUID] [nvarchar] (10) NULL,
[VP_ATTUID] [nvarchar] (10) NULL
This needs work. What are you going to do when you have a new type of relationship like Senior VP? Sure you can add a field but the need to do this indicates a problem. You probably need an ID field and a relationship type field.
[ID] [nvarchar] (10) NOT NULL,
[RelationshipType] [nvarchar] (10) NOT NULL,
So sample data might look like
1,Rep
2,VP
3,ANewRelationshipTypeDefined
This will also allow one person to have multiple relationships.
3,CM
3,RM
1CEO
3,VP
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply