Should I use a CurrentID bit field or sort by date for each select? 2

[iwease]

I have a database that is storing historical data. It actually consists of two databases. There is a transactional database that contains all the current records, and then a historical database that contains all records over all periods of time. It is set up as follows

Transaction DB table

TableA
----------
TableAID (primary key)
col1
col2
...


Historical DB table

HIS_TableA
----------
HistoricalTableAID (primary key)
TimeStamp
TableAID
col1
col2
...

(note that there could be many HistoricalTableAID for one TableAID because of updates)


A trigger automatically inserts a new record into the historical table whenever a record is inserted or updated in the corresponding transactional table. Every table in the historical db only references other tables in the historical db and every table in the transactional db only references other tables in the transactional db. Now my problem occurs when I want to insert a record into HIS_TableB. HIS_TableB contains historical data that never changes and data is inserted directly into it rather than through a trigger (ie, there is no TableB). The problem is, I am given the ID for TableA and not the HistoricalTableAID so I need to get that.

HIS_TableB
----------
HistoricalTableBID (primary key)
TimeStamp
FK_HistoricalTableAID (foreign key)
...

So, as far as I can tell, there are two ways to do this.

Method 1) One would be to have a bit field that determines if a particular HistoricalTableAID is the current record for the TableAID.

Method 2) Another method would be to do

SELECT * FROM HIS_TableA a
INNER JOIN
(
SELECT Max(TimeStamp) as MaxTimeStamp, TableAID FROM HIS_TableA GROUP BY TableAID ) recentData
ON recentData.MaxTimeStamp = a.TimeStamp AND recentData.TableAID = a.TableAID

What do you smart people recommend. I know you have more DB experience than me so I really appreciate any advice

 

[mrdenny]

Assuming that HistoricalTableAID is an identity column why not

select max(HistoricalTableAID)
from HIS_TableA
where TableAID = [i]n[/i]

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[iwease]

Good thought. However, I forgot to mention that the databases have the ID fields as GUIDs so max(HistoricalTableAID) doesn't actually give the most recent value

 

[mrdenny]

Figures.

I'm not sure which will perform better. You'll need to try both and check out the execution plans.

I would assume that the timestamp method would be the easier method to impliment.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[iwease]

hmmm...yeah, I really should learn how to read an execution plan it seems. I was hoping one of those methods would stand out and be the obvious way to do it (I'm assume the bit method would have better performance since it doesn't need to use any subqueries) but I guess it is something that I just have to learn and test.

Neither case is obviously bad though, right?

 

[mrdenny]

I would think that the date field would be a better option because an index on that field would be more useful than an index on a bit field. Searching a bit field requires a scan every time because there are only two possible options.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[iwease]

I found another method to do this on the web using rank and partition

create table #test (testid int identity(1,1), personid int, persondate int)

insert into #test (personid, persondate) values (1, 1)
insert into #test (personid, persondate) values (1, 2)
insert into #test (personid, persondate) values (1, 3)
insert into #test (personid, persondate) values (1, 4)
insert into #test (personid, persondate) values (1, 5)
insert into #test (personid, persondate) values (2, 1)
insert into #test (personid, persondate) values (2, 2)
insert into #test (personid, persondate) values (2, 5)
insert into #test (personid, persondate) values (2, 4)
insert into #test (personid, persondate) values (2, 3)
insert into #test (personid, persondate) values (3, 1)
insert into #test (personid, persondate) values (3, 3)
insert into #test (personid, persondate) values (3, 2)
insert into #test (personid, persondate) values (4, 1)
insert into #test (personid, persondate) values (4, 2)


with HRRanks as
(
Select b.personid, b.persondate,
rank() over (partition by b.personid order by b.persondate desc) as HR_Rank
from #test b
group by b.personid, b.persondate
)
select
HRRanks.*
from
HRRanks
where
HRRanks.HR_Rank = 1

SELECT * FROM #test

 

[iwease]

Ok, so I did some testing. I set up 3 tables with the same data

Table 1 and Table 2

HIS_ID Uniqueidentifier (Primary Key)
ID Uniqueidentifier (INDEXED)
DateInserted DateTime (INDEXED)

AND

Table 3

HIS_ID Uniqueidentifier (Primary Key)
ID Uniqueidentifier (INDEXED)
DateInserted DateTime
IsCurrent BIT (INDEXED)

I filled them up with 750000 rows, each ID has between 1 and 150 items inserted (ie, different DateInserted and HIS_IDs).

I tested method 1, 2, and 3 (3 is my last post with the RANK() over) and found that for selecting the most recent HIS_ID for each ID gave

method 1) CPU Time = 300ms, Elapsed Time = 1500ms
Method 2) CPU Time = 3500ms, Elapsed Time = 4500ms
Method 3) CPU Time = 7500ms, Elapsed Time = 10500ms

And when I did the same select but added a specific ID to the where clause, it gave

method 1) CPU Time = 250ms, Elapsed Time = 700ms
Method 2) CPU Time = 500ms, Elapsed Time = 800ms
Method 3) CPU Time = 8300ms, Elapsed Time = 11000ms

So, from a performance standpoint, method 1 is the way to go. Do these results make sense?

 

[mrdenny]

Based on your results I would have to agree, method 1 is the best option. I'm curous about the execution plans. Can you post them so we can take a look?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

There's one big problem with method 1. What happens if somehow the bit column for "most recent" is not maintained properly? You get data corruption.

The kinds of triggers and constraints and table-constraint-functions you'd have to write to enforce that only one row for each series can have its "current" bit set are nightmares.

A different way to handle this is to create a HIS_TableA_LastHID table. Create a trigger on HIS_TableA that inserts, or updates the most recent HistoricalTableAID. Make the information gathering happen at insert time. You should cover all possible operations to HIS_TableA including updates and deletes, even if you think that table will never have those operations (if only to prevent those kinds of operations entirely, you need to cover that ground).

This is only a single trigger that is fairly easy to write and will give you the effective missing TableB that tells you what the most recent historical ID is.


[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[iwease]

sure

Execution Plan for method 1

<ShowPlanXML xmlns="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2004/07/showplan"[/URL] Version="1.0" Build="9.00.3042.00">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT * FROM [test_IsCurrent] WHERE [IsCurrent]=@1" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="5.2372" StatementEstRows="18940" StatementOptmLevel="FULL">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
          <QueryPlan DegreeOfParallelism="0" CachedPlanSize="9" CompileTime="2" CompileCPU="2" CompileMemory="104">
            <MissingIndexes>
              <MissingIndexGroup Impact="65.0459">
                <MissingIndex Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[IsCurrent]" ColumnId="4" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[hisid]" ColumnId="1" />
                    <Column Name="[PDate]" ColumnId="2" />
                    <Column Name="[id]" ColumnId="3" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="18940" EstimateIO="4.41201" EstimateCPU="0.825188" AvgRowSize="48" EstimatedTotalSubtreeCost="5.2372" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="hisid" />
                <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="PDate" />
                <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="id" />
                <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="IsCurrent" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="18940" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="hisid" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="PDate" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="id" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="IsCurrent" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Index="[PK_test_IsCurrent]" />
                <Predicate>
                  <ScalarOperator ScalarString="[LockTesting].[dbo].[test_IsCurrent].[IsCurrent]=(1)">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_IsCurrent]" Column="IsCurrent" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Const ConstValue="(1)" />
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Predicate>
              </IndexScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Execution plan for method 2

<ShowPlanXML xmlns="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2004/07/showplan"[/URL] Version="1.0" Build="9.00.3042.00">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT i.* FROM test_Indexed i&#xD;&#xA;INNER JOIN&#xD;&#xA;(&#xD;&#xA;&#x9;SELECT Max(Pdate) as MaxPDATE, ID FROM test_indexed GROUP BY ID&#xD;&#xA;) i2&#xD;&#xA;ON i.Pdate = i2.MaxPDate AND i.ID = i2.ID&#xD;&#xA;--WHERE i.id='B7825DAB-11B0-4B8D-8599-75E70B8CFA67'&#xD;&#xA;--*/&#xD;&#xA;&#xD;&#xA;/*&#xD;&#xA;with HRRanks as&#xD;&#xA;(&#xD;&#xA;    Select b.id, b.pdate, &#xD;&#xA;        --rank()&#xD;&#xA;&#x9;&#x9; ROW_NUMBER() over (partition by b.id order by b.pdate desc) as HR_Rank&#xD;&#xA;    from test_indexed b&#xD;&#xA;    group by b.id, b.pdate&#xD;&#xA;)&#xD;&#xA;select&#xD;&#xA;   HRRanks.*&#xD;&#xA;from&#xD;&#xA;   HRRanks&#xD;&#xA;where&#xD;&#xA;  HRRanks.HR_Rank = 1&#xD;&#xA; aND HRRanks.id='B7825DAB-11B0-4B8D-8599-75E70B8CFA67'&#xD;&#xA;--*/&#xD;&#xA;--SELECT * FROM test&#xD;&#xA;" StatementId="1" StatementCompId="4" StatementType="SELECT" StatementSubTreeCost="21.3892" StatementEstRows="18682.2" StatementOptmLevel="FULL">
          <StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
          <QueryPlan DegreeOfParallelism="0" MemoryGrant="1166" CachedPlanSize="33" CompileTime="25" CompileCPU="25" CompileMemory="280">
            <RelOp NodeId="0" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="18682.2" EstimateIO="0" EstimateCPU="5.49419" AvgRowSize="47" EstimatedTotalSubtreeCost="21.3892" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="hisid" />
                <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="pdate" />
                <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="id" />
              </OutputList>
              <MemoryFractions Input="0.843998" Output="1" />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="18940" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <Hash>
                <DefinedValues />
                <HashKeysBuild>
                  <ColumnReference Column="Expr1005" />
                  <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="id" />
                </HashKeysBuild>
                <HashKeysProbe>
                  <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="pdate" />
                  <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="id" />
                </HashKeysProbe>
                <ProbeResidual>
                  <ScalarOperator ScalarString="[LockTesting].[dbo].[test_indexed].[pdate] as [i].[pdate]=[Expr1005] AND [LockTesting].[dbo].[test_indexed].[id] as [i].[id]=[LockTesting].[dbo].[test_indexed].[id]">
                    <Logical Operation="AND">
                      <ScalarOperator>
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="pdate" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Column="Expr1005" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="id" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="id" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Logical>
                  </ScalarOperator>
                </ProbeResidual>
                <RelOp NodeId="1" PhysicalOp="Hash Match" LogicalOp="Aggregate" EstimateRows="18681.1" EstimateIO="0" EstimateCPU="5.55837" AvgRowSize="31" EstimatedTotalSubtreeCost="10.7267" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="id" />
                    <ColumnReference Column="Expr1005" />
                  </OutputList>
                  <MemoryFractions Input="1" Output="0.156002" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="18940" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Hash>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1005" />
                        <ScalarOperator ScalarString="MAX([LockTesting].[dbo].[test_indexed].[pdate])">
                          <Aggregate Distinct="0" AggType="MAX">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="pdate" />
                              </Identifier>
                            </ScalarOperator>
                          </Aggregate>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <HashKeysBuild>
                      <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="id" />
                    </HashKeysBuild>
                    <BuildResidual>
                      <ScalarOperator ScalarString="[LockTesting].[dbo].[test_indexed].[id] = [LockTesting].[dbo].[test_indexed].[id]">
                        <Compare CompareOp="IS">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="id" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="id" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </BuildResidual>
                    <RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="750028" EstimateIO="4.34312" EstimateCPU="0.825188" AvgRowSize="31" EstimatedTotalSubtreeCost="5.16831" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="pdate" />
                        <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="id" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="750028" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="pdate" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Column="id" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Index="[PK_test_indexed]" />
                      </IndexScan>
                    </RelOp>
                  </Hash>
                </RelOp>
                <RelOp NodeId="6" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="750028" EstimateIO="4.34312" EstimateCPU="0.825188" AvgRowSize="47" EstimatedTotalSubtreeCost="5.16831" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="hisid" />
                    <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="pdate" />
                    <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="id" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="750028" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="hisid" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="pdate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Alias="[i]" Column="id" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[LockTesting]" Schema="[dbo]" Table="[test_indexed]" Index="[PK_test_indexed]" Alias="[i]" />
                  </IndexScan>
                </RelOp>
              </Hash>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

 

[iwease]

You have been a really big help to me, getting me to run the tests and see what happens (I don't know why I sometimes don't think of these things myself.

Anyway, I need to update IsCurrent requently now to maintain the current record. With the index on IsCurrent, this is pretty slow. I ran some tests without the index and saw that my select time only changed to CPU Time=500ms, Elapsed Time = 1700ms. So, there is little performance gained from the IsCurrent index (as you stated). But, I am having a bit of difficulty determining how the different results compare because I don't know what the difference between CPU Time and Elapsed time. My understanding is that the CPU Time is the time needed to do the command, and the Elapsed time is the time needed plus the time spent waiting for other stuff. Am I missing something here? Why is there waiting happening for such a simple select?

 

[ESquared]

Did you see my post just above your execution plan post?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[iwease]

Wow...I am impressed. You knew that I missed you post (I'm assuming because I was doing mine at the same time).

ok, my original plan was to have a trigger on table A that does an insert into HIS_TableA each time something is inserted or updated in table A. This trigger would handle updating IsCurrent appropriately.

But your method is to have it in a separate table. That is a neat idea. I will build some test cases to try it out. It now means that I have two triggers instead of one happening on each insert/update in TableA (one on tableA and one on HIS_TableA). I think the data corruption issues would be the same for either case (in both cases, the most recent field is only being updated by a trigger so in order for data corruption to occur, a user would manually have to mess something up). Ideally, the HIS_ tables will be readonly except for that Trigger.

I have several HIS_ tables so this method could mean a lot of extra tables in the DB.

 

[ESquared]

I thought there was only one table in the DB that had the situation where you needed to know the most recent historyID. In any case, these tables would be very narrow, just two columns: TableAID, MostRecentTableAHistoryID.

And instead of having two triggers on table A, put the second trigger on HIS_TableA. There is also less possibility for data corruption because now there is only one "place" where a value can be stored.

Look:

[tt]HIS_TableA
HistoricalAID AID Current
459 1 0
460 1 0
461 1 0
462 1 1 [/tt]

This leaves ample room for corruption because there are four rows that could have a current = 1 value.

[tt]HIS_TableA_MostRecent
TableAID, MostRecentTableAHistoryID.
1 462[/tt]

This table now has only one value: 462.

I think if you analyze this from all angles you'll find the advantages outweigh the drawbacks.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[mrdenny]

I was actually hopeing for screen shots. There much easier to read. I can muddle though these however. The bigest thing which you will be waiting for is the disks.

Fire up PerfMon and monitor the disk load before, during and after your query and see how the disks get killed. That's going to be the bulk of your time. Also if other threads are taking CPU time, you may need to wait for them which can add more miliseconds.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[iwease]

Hey, you guys are really helping me think out of the box here. I think I've come up with the final solution (which is kind of similar to ESquared's idea).

In my original table, I have a reference to the current historical data id. So I have the following

Transaction DB table

TableA
----------
TableAID (primary key)
FK_HistoricalTableAID 
col1
col2
...


Historical DB table

HIS_TableA
----------
HistoricalTableAID (primary key)
TimeStamp
TableAID 
col1
col2
...

when I do an update/insert into Table A, a trigger first generates newid's for the FK_HistoricalTableAID field. The it joins TableA with the INSERTED table and inserts the new HistoricalTableAID into HIS_TableA with the rest of the data. After some initial testing, this method is faster then method 1 for inserting/updating Table A and comparable to methods 2 and 3. In terms fo selecting the current data, it is significantly faster than all the previous methods (not including ESquared's which I still haven't tested yet), CPU time of 15ms, elapsed time of 250ms. Considering that I may have to select the current record for many different HIS_ tables, this time improvement will really add up.

My only complaint is that I must now reference the HIS_ tables from my regular transaction tables. Fortunately, the timestamp will still exist in all the HIS tables so if that part of the transaction tables gets corrupted, I can always recalculate the current IDs

 

[iwease]

This idea is essentially what you suggested ESquared, except that there is not a separate table. I know that you would prefer a separate table for security/data integrity purposes and I should look into that a bit further before finalizing this but seems to be the way to go

 

[ESquared]

iwease,

The reason I suggest not using a Current bit signifier is because I tried it once in a production system and it was a nightmare, though slightly superior to always doing a Max(). I finally realized that I was putting the column in the wrong table. Instead of putting it in the history table, just put the most recent ID in the parent table (as you've done).

The reason I suggested its own table is that now you are joining to the production/live database for historical queries. HistoricalTableAID is about history. If history is in a separate database, you might have some real performance gains by keeping ALL history information there.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[iwease]

yeah, I'm not a big fan of keeping the historical data and the transactional data tied together the way I suggested in the last method. Still, in my particular case, it is not such a big deal. I really appreciate both yours and mrdenny's help. It has definately made a difference on this project.

And helped me improve my knowledge of a common situation as well which is always a plus