May 11, 2012 at 1:01 pm
The [lab_seq] column is updated within the code using the array index. A decision was made years ago to DELETE and INSERT as a way to update the data from the application server. Therefore, every time the data is rearranged in the array, the [lab_seq] is updated with a new number. The [document_uid] stays static. I want to determine what type of performance on the database if I add the child table. What good business justification can I provide to the client and developers to change the way the [lab_seq] is handled?
At this time, the maximum number of rows in one LAB table is 3 million growing by 20,000 monthly.
(The SQL statements are written in java not handled by stored procedures.)
Relationship: 1 to 1 (one test has one 10,000 char result)
Parent Table
CREATE TABLE [dbo].[LAB](
[document_uid] [char](16) NOT NULL,
[lab_seq] [int] NOT NULL,
[sample_id] [varchar](50) NULL,
[provider_uid] [char](16) NULL,
[specimen] [char](3) NULL,
[sample_dt] [char](8) NULL,
[receive_dt] [char](8) NULL,
[manufacturer] [varchar](2) NULL,
[clia_uid] [char](11) NULL,
[type] [varchar](3) NULL,
[loinc_cd] [varchar](7) NULL,
[result_interpretation] [varchar](100) NULL,
[result] [varchar](10) NULL,
[result_units] [varchar](4) NULL,
[result_range] [varchar](15) NULL,
[result_rpt_dt] [char](8) NULL,
[case_cd] [char](2) NULL,
[comments] [varchar](100) NULL,
[starhs_sample_id] [varchar](15) NULL,
[accession_number] [varchar](50) NULL,
[sreason] [char](1) NULL,
[facility_uid] [char](16) NULL,
CONSTRAINT [PK_LAB] PRIMARY KEY CLUSTERED
(
[document_uid] ASC,
[lab_seq] ASC
Child Table
CREATE TABLE [dbo].[L_SEQUENCE](
[document_uid] [char](16) NOT NULL,
[lab_seq] [int] NOT NULL,
[g_result] [varchar](MAX) NULL
CONSTRAINT [PK_L_SEQUENCE] PRIMARY KEY CLUSTERED
(
[document_uid] ASC,
[lab_seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [DATA]
) ON
ALTER TABLE [dbo].[L_SEQUENCE] WITH CHECK ADD CONSTRAINT [FK_SEQUENCE_LAB] FOREIGN KEY([document_uid], [lab_seq])
REFERENCES [dbo].[LAB] ([document_uid],[lab_seq])
May 11, 2012 at 1:15 pm
The [lab_seq] column is updated within the code using the array index. A decision was made years ago to DELETE and INSERT as a way to update the data from the application server. Therefore, every time the data is rearranged in the array, the [lab_seq] is updated with a new number. The [document_uid] stays static. I want to determine what type of performance on the database if I add the child table. What good business justification can I provide to the client and developers to change the way the [lab_seq] is handled?
This one is pretty simple. lab_seq is part of your primary key. Primary keys should be considered immutable. That means the value should never be changed. The primary key is how you identify a row and therefore for the entire time that row exists, the way you identify it should remain unchanged. What a nightmare that sounds like.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 1:20 pm
Not the first time I heard "experts" say drop and insert is the best way to update records.
May 11, 2012 at 1:21 pm
Thank you Sean. It is a nightmare. I posted to get a replies from reputable professionals to show my client. The client has data modeling training and designed the original database. Therefore, any change made to the model now will not receive their approval unless I can give bullet proof reasons.
Is it true performance will degrade when the FK relationship is added?
May 11, 2012 at 1:27 pm
annastassia1935 (5/11/2012)
Thank you Sean. It is a nightmare. I posted to get a replies from reputable professionals to show my client. The client has data modeling training and designed the original database. Therefore, any change made to the model now will not receive their approval unless I can give bullet proof reasons.Is it true performance will degrade when the FK relationship is added?
Well adding a FK will have an impact on performance. There is a check that must be performed on both sides. I would not pretend to go so far as to suggest it will "degrade". It will certainly have far less impact on performance than deleting and inserting instead of an update. You also have added bonus of keeping the integrity of your data. I take it there was some resistance to adding an FK?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 11, 2012 at 1:31 pm
Yes there is resistance. However, I read on this site that it may be better to store the varchar(max) field in a separate table rather than appending it to the LAB table.
May 11, 2012 at 2:47 pm
annastassia1935 (5/11/2012)
Yes there is resistance. However, I read on this site that it may be better to store the varchar(max) field in a separate table rather than appending it to the LAB table.
For VARCHAR(MAX) it can be recommended when the Version is SQL 2008 R2 or below and when the Edition is Enterprise, Evaluation or Developer, but there are no hard-and-fast rules. It will depend on whether the VARCHAR(MAX) column participates in any nonclustered indexes. It has to do with keeping the option to perform ONLINE index operations open, as there are restrictions when it comes to columns with VARCHAR(MAX) data.
The rules change slightly in SQL Server 2012 for the better, making it easier to justify keeping VARCHAR(MAX) columns in the base table (i.e. appending it to your LAB table). However, it could still be advisable to split them into a separate table depending on whether they participate in any nonclustered indexes.
Guidelines for Online Index Operations
EDIT: replace LOB with VARCHAR(MAX) to make sure things are clear
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 14, 2012 at 5:57 am
annastassia1935 (5/11/2012)
Thank you Sean. It is a nightmare. I posted to get a replies from reputable professionals to show my client. The client has data modeling training and designed the original database. Therefore, any change made to the model now will not receive their approval unless I can give bullet proof reasons.Is it true performance will degrade when the FK relationship is added?
Not necessarily. Enforced referential integrity is a part of the design of SQL Server and therefore part of the design of the query optimizer. You can see radical improvements in performance because of foreign key constraints. This blog post shows just one example.[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2012 at 12:06 pm
Thanks Grant.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply