Indexing tables by name is very slow

[Andrea Caldarone]

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?

 

[RobinS]

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]

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!

 

[RobinS]

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!

I *thought* the P and C stuff were different, but got tired of staring at
it, and figured you could apply my info as needed. I'm glad that helped. I
learned about that from Dave Sceppa's ADO.Net Core Reference; he said it's
something like 30% faster. Blazin'!

Robin S.