"Andrea Caldarone" <software-livqu***@3techsrl.com> wrote in message
news:%23HC28G3SHHA.3440@TK2MSFTNGP03.phx.gbl...
> Hi all,
>
> look at this piece of routine, dsMAIN.Tables("ForeignKeys") is a
> DataTable filled with the data of all the foreignKeys in my SQL Servr
> 2005 database, I use this datatable to create the corresponding
> DataRelation object in my dataset:
>
>        'Loop into database's relation
>        For Each rRelation In dsMAIN.Tables("ForeignKeys").Rows
>
>            With rRelation
>
>                'setting relation's parameters
>                strRName = .Item("PTable") & "_" & .Item("CTable")
>                PCol =
> dsMAIN.Tables(.Item("PTable")).columns(.Item("PColumn"))
>                CCol =
> dsMAIN.Tables(.Item("CTable")).columns(.Item("CColumn"))
>
>                'adding the relation to the dataset
>                dsMAIN.Relations.Add(New DataRelation(strRName, PCol,
> CCol))
>            End With
>        Next
>
> The first time the routine loops it takes a lot of time (up to 3 seconds
> on a Celeron 2,5Ghz with 1Gb RAM) at the line:
>
>                PCol =
> dsMAIN.Tables(.Item("PTable")).columns(.Item("PColumn"))
>
> and also a lot of time at the line:
>
>                CCol =
> dsMAIN.Tables(.Item("CTable")).columns(.Item("CColumn"))
>
> the other times the routine loops trough theese lines it is very fast...
> why?
>
>

> Every time it hits one of those lines setting PCol or CCol, it has to
> search for the right table and the right column. If you have a lot of
> rows, this can significantly impact the performance.
>
> If the datacolumn or table is the same for each iteration of the
> loop, what you can do is get the ordinal index or the datacolumn
> pointer before the loop and then use those in the loop. Something
> like this:
> Dim FKTable As DataTable = dsMain.Tables("ForeignKeys")
> Dim PTableCol as DataColumn = FKTable.Columns("PTable")
> Dim CTableCol as DataColumn = FKTable.Columns("CTable")
> Dim pTable as DataTable = dsMain.Tables("PTable")
> Dim cTable as DataTable = dsMain.Tables("CTable"
>
> For Each rRelation as DataRelation in FKTable.Rows
>  with rRelation
>    strRName = .Item(PTableCol) & "_" & .Item(CTableCol)
>    PCol = pTable.Columns("PColumn")
>    CCol = cTable.Columns("CColumn")
>    dsMain.Relations.Add(new DataRelation(strRName, PCol, CCol))
> Next rRelation
>
> I'm not 100% sure I'm reading all of your relations right, but that
> should put you on the right track.  I think you could even set up a
> column for the pTable.Columns("PColumn") and the next line as well,
> but since I'm not sure I'm reading your stuff right, I didn't do
> that. But if those are fixed, you could do those,  too.
>
> Hope this makes sense.
>
> Robin S.
> Ts'i mahnu uterna ot twan ot geifur hingts uto.
> -----------------------------------------------

"Andrea Caldarone" <software-livqu***@3techsrl.com> wrote in message
news:%23MUeBEpTHHA.2256@TK2MSFTNGP02.phx.gbl...
> RobinS wrote:
>> Every time it hits one of those lines setting PCol or CCol, it has to
>> search for the right table and the right column. If you have a lot of
>> rows, this can significantly impact the performance.
>>
>> If the datacolumn or table is the same for each iteration of the
>> loop, what you can do is get the ordinal index or the datacolumn
>> pointer before the loop and then use those in the loop. Something
>> like this:
>> Dim FKTable As DataTable = dsMain.Tables("ForeignKeys")
>> Dim PTableCol as DataColumn = FKTable.Columns("PTable")
>> Dim CTableCol as DataColumn = FKTable.Columns("CTable")
>> Dim pTable as DataTable = dsMain.Tables("PTable")
>> Dim cTable as DataTable = dsMain.Tables("CTable"
>>
>> For Each rRelation as DataRelation in FKTable.Rows
>>  with rRelation
>>    strRName = .Item(PTableCol) & "_" & .Item(CTableCol)
>>    PCol = pTable.Columns("PColumn")
>>    CCol = cTable.Columns("CColumn")
>>    dsMain.Relations.Add(new DataRelation(strRName, PCol, CCol))
>> Next rRelation
>>
>> I'm not 100% sure I'm reading all of your relations right, but that
>> should put you on the right track.  I think you could even set up a
>> column for the pTable.Columns("PColumn") and the next line as well,
>> but since I'm not sure I'm reading your stuff right, I didn't do
>> that. But if those are fixed, you could do those,  too.
>>
>> Hope this makes sense.
>>
>> Robin S.
>> Ts'i mahnu uterna ot twan ot geifur hingts uto.
>> -----------------------------------------------
>
> Hi Robin,
>
> first of all thank you for your help.
>
> If you look at my piece of code, PTableCol and CTableCol are different in
> every step of the loop. But, thanks to your help, it has been sufficient
> to call the "foreign keys table" with a DataTable object previously set:
>
> Dim FKTable As DataTable = dsMain.Tables("ForeignKeys")
>
> and the loop is over ten times fastest!
>