When Denormalizing Is Essential
Jul 20, 2005
I have a database with over 450 tables, that until a short while ago
has been strictly 3NF. In cases where complicated permutations of
data are required quickly for certain parts of the system, sometimes
it is not possible to write a query fast enough, due to the complexity
of deriving many nested levels of data.
Therefore for the few instances where this has been needed, I have
created carefully named summary tables, having one-to-one
relationships to real tables (same primary key), and containing only
the values which must be computed.
This works fine. The question now is when to compute them.
Much as I strongly dislike triggers, I have created triggers for the
tables in question, which themselves recalculate the summary values,
and populate the summary tables.
Good news, loading is much faster.
Bad news, now that I'm taking the hit on the save, the saves are far
too slow.
The only way I can see that my saves could be sped up, is to perhaps
spread the work a bit, such that maybe if I am adding fifty records to
a table, that the trigger is somehow disabled before the rows are
added, and re-enabled afterwards, to calculate the lot as a set-based
operation (rather than calling the trigger 50 times), but I don't see
a way to do that.
Any help of any nature on this matter would be greatly appreciated
Cheers
Brian McGee
View 10 Replies
ADVERTISEMENT
Jul 10, 2006
Hello,I am currently working with a data mart. The business wants a view ofthe data that shows columns from the main table along with up to 6codes from a related table (not related by the full PK of the maintable though). They don't care about the order of the codes or how theyare assigned to their columns. Here is an example, which will hopefullymake this clearer:CREATE TABLE dbo.Main_Table (my_id INT NOT NULL,line_number SMALLINT NOT NULL,some_other_columns VARCHAR(20) NULL )GOALTER TABLE dbo.Main_TableADD CONSTRAINT PK_Main_Table PRIMARY KEY CLUSTERED (my_id, line_number)GOCREATE TABLE dbo.Related_Table (my_id INT NOT NULL,my_code CHAR(6) NOT NULL )GOALTER TABLE dbo.Related_TableADD CONSTRAINT PK_Related_Table PRIMARY KEY CLUSTERED (my_id, my_code)GO-- The two tables above are related by my_idINSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 1)INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 2)INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (1, 3)INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (2, 1)INSERT INTO dbo.Main_Table (my_id, line_number) VALUES (2, 2)INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '22.63')INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '73.09')INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (1, '51.23')INSERT INTO dbo.Related_Table (my_id, my_code) VALUES (2, '26.42')GOThe results that they would want to see are:my_id line_number my_code_1 my_code_2 my_code_3 my_code_41 1 22.63 73.09 51.23 NULL1 2 22.63 73.09 51.23 NULL1 3 22.63 73.09 51.23 NULL2 1 26.42 NULL NULL NULL2 2 26.42 NULL NULL NULLI'm pretty sure that I will need to load a table with the data in thisformat (or something similar) since generating this output on the flyfor the users will not work performance-wise (the main table is apartitioned table with 6 partitions, each containting 35M+ rows, whilethe related table is a single table that contains about 2M rows. Thereis additional logic that has to be done with a similar table of 90Mrows. So, I will try to load a table with this denormalized view of theworld. I could probably add some sort of sequence number toRelated_Table if you think that might help (then it just becomes asimple series of left outer joins).Any thoughts?Thanks!-Tom.
View 8 Replies
View Related
Sep 2, 2007
I have the following:
Demograph Table (PK MemberID)
-MemberID
-FirstName
-LastName
InsuranceSegments Table (PK = all columns combined)
-MemberID
-ProgramCode
-StatusCode
-BeginDate
-EndDate
I need to get the data into this table (i'll call the table MyTest)
-MemberID
-FirstName
-LastName
-BeginDate
-EndDate
-ProgramCode
-StatusCode
The catch:
For each record in the demograph table I need only one segment and that segment is to be based on the greatest EndDate.
What I need:
Give me the last segment for each demograph record and put each into it table MyTest
In my pseodo thinking
INSERT INTO MyTest (MemberID, FirstName, LastName, BeginDate, EndDate, ProgramCode, StatusCode)
SELECT
Demograph.MemberID, Demograph.FirstName, Demograph.LastName,
InsuranceSegments.BeginDate, InsuranceSegments.EndDate, InsuranceSegments.ProgramCode, InsuranceSegments.StatusCode
FROM
Demograph
INNER JOIN
InsuranceSegments
ON
Demograph.MemberID = InsuranceSegments.MemberID
WHERE { Need help :-) }
Need help with the where clause to get the segment with the 'highest/latest' EndDate
Sorry for the wall of text. I hope this is enough information to receive help and would like to thank you in advance for your time :-)
J
View 7 Replies
View Related
Apr 19, 2007
I have two tables related many-to-many so there's an intersection table in the middle. The first table is dbo.Event. It has a list of gigs (events) my company has booked to provide services for. The second table is dbo.Employee which has a list of employees. The intersection table dbo.EventEmployee contains which employee(s) have been scheduled to work. The intersection table has some status information whether the employee asked to work, is scheduled to work, etc. The data type is TINYINT
Here's what I want to do. Create a SELECT statement that basically denormalizes these three tables so the recordset looks like this:
Event.Name, Event.StartDate, Event.Location, Ed, Mary, Tom, Steve, Sally
The idea is to get the list of employees to become columns in the result set. If an employee is scheduled for the event there is a non-null value (Status), if an employee isn't scheduled there is a NULL in their column. For example:
CDC, 2/12/07, Chicago, NULL, 0, 0, NULL, NULL
NYC, 11/04/07, New York, 0, 0, 0, 0, NULL
The first record Mary & Tom are scheduled to work the CDC. The second row everyone except Sally is scheduled to work.
I've thought of cursor based solutions but performance will be rather poor. There are multiple places I want to use this type of solution so I'm hoping there's a more elegant way to accomplish this. SQL 2000 with latest SP. Help. This is beyond my SQL abilities.
harrier
View 9 Replies
View Related
Jan 17, 2008
I am developing a web site with asp.net 2.0 and c#, using Visual Studio 2005 and Microsoft SQL Server 2005 ( I am still learning about these technologies and languages ).
I transferred my web site files to my NEW computer.
The web site worked fine when running inside Visual Studio, however when I tried to run it in its PUBLISHED format it was giving this error message:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Server Error in '/MA' Application.
Cannot open database "SiteData" requested by the login. The login failed.
Login failed for user 'CENTAURUSASPNET'.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I solved the problem by running SQL Server Management Stdio, right clicking “SiteData� database -> properties -> permissions->view server permissions then I chose “CENTAURUSASPNET� and since I DID NOT KNOW what grants are ESSENTIAL for the functioning of the database, I GRANTED EVERYTHING ( I ticked ALL the permissions).
But since, probably, a lot of GRANTS are not only unnecessary but also may compromise the security of the web site, I would like to know what are the ESSENTIAL OR MINIMUM GRANTS NECESSARY for making the database functional to the web site and what GRANTS may compromise security.
View 4 Replies
View Related
Jan 24, 2008
Please help, I've used all of my capacities...
I need to denormalize some data...
I'll give an example.
Source table: (bold €“ PK)
ID_Company Data (yyyy-mm-dd) Type Quantity
FORD 2000-01-01 A 100
FORD 2000-06-01 A 200
FORD 2000-07-01 B 150
FORD 2002-01-01 A 400
FIAT 1999-01-01 A 300
FIAT 2000-06-01 B 700
FIAT 2001-07-01 B 750
FIAT 2002-01-01 C 250
Needed result set:
ID_Company Year A B C
FORD 2000 200 150 0
FORD 2002 400 150 0
FIAT 1999 300 0 0
FIAT 2000 300 700 0
FIAT 2001 300 750 0
FIAT 2002 300 750 250
A business rules:
· if there are more records for the same year and the same type, I extract the later date.
If more explanations are needed, please ask€¦I appreciate every help I can get with this query.
View 9 Replies
View Related
Jan 17, 2008
I am developing a web site with asp.net 2.0 and c#, using Visual Studio 2005 and Microsoft SQL Server 2005 ( I am still learning about these technologies and languages ).I transferred my web site files to my NEW computer. The web site worked fine when running inside Visual Studio, however when I tried to run it in its PUBLISHED format it was giving this error message:Server Error in '/MA' Application. Cannot open database "SiteData" requested by the login. The login failed.Login failed for user 'CENTAURUSASPNET'.I solved the problem by running SQL Server Management Stdio, right clicking “SiteData� database -> properties -> permissions->view server permissions then I chose “CENTAURUSASPNET� and since I DID NOT KNOW what grants are ESSENTIAL for the functioning of the database, I GRANTED EVERYTHING ( I ticked ALL the permissions).
But since, probably, a lot of GRANTS are not only unnecessary but also may compromise the security of the web site, I would like to know what are the ESSENTIAL OR MINIMUM GRANTS NECESSARY for making the database functional to the web site and what grants may represent a security risk.
View 5 Replies
View Related
Dec 27, 2006
Live Webcast tomorrow
Essential Team System for Database Developers
1 PM Central time 12/28
https://www.clicktoattend.com/invitation.aspx?code=112602
View 2 Replies
View Related
