Slow interrogation of Field.Required property in TableDef (1 Viewer)

[PearlGI]

I've been trying to improve a databases performance when run across a network and have identified the following that is causing part of the under-performance issue.

This code extracts field properties from one table in the BE and adds the data to a new table in the FE (no comments re bloating please ).
The line that causes the slow performance is fld.Required (takes around 3 secs for this one line to execute).
Does anyone know why trying to interrogate the TableDef.Field.Required property takes an inordinate amount of time when FE/BE runs over a network when other TableDef.Field properties demonstrate no such issue. No such performance issue when run locally.

For Each fld In td.Fields        
    rsTemp.AddNew
    rsTemp!Field = fld.Name
    rsTemp!Type = GetType(fld.Type)
    rsTemp!Size = fld.Size
    rsTemp!Required = fld.Required       <--- this line takes 3 secs to execute
    rsTemp.Update
Next

Objects
td=TableDef
fld=Field

 

[Alisa]

What about having an empty template table and just making a copy of it? Does that run faster than creating the new table in code?

 

[PearlGI]

The code that causes the slow issue is not creating a table, it's adding a new record to a table...

 

[gemma-the-husky]

how do you know its slow to set the required field

its more likely that its the update statement thats slow (pearl has pointed this out)

rsTemp!Required = fld.Required
rsTemp.Update


is the FE database you are updating on your local PC or on the network - if its on the network, that is probably the issue

 

[PearlGI]

I know it's the .Required line as I've used Debug.Print {line identifier} & Time() before and after each line to identify where the slowness was.
All lines, including the .Update line execute instantaneously with the exception of the .Required line.

If I remove the .Required line, there is no speed issue with the code executing.

 

[DCrake]

What you could do is to place an if statement around the line

If fld.Required = True Then
   RsTemp!Required = True
Else
   RsTemp!Required = False
End If

Or if you set the default value for the required field to the most popular answer Say False. Then only use the If True statement. Then at least it should cut down the time dramatically.

David

 

[PearlGI]

I've tried your suggestions David, but this doesn't improve things as the If statement still has to read the fld.Required property for every field.

Although I have proved that the problem with the line is with the reading of fld.Required rather than an issue with writing rsTemp!Required.


Any further suggestions most welcome.

 

[Alisa]

The code that causes the slow issue is not creating a table, it's adding a new record to a table...

Sorry I misunderstood your code. If you need the required property of each field for some reason, why not store it in a table where it will be much quicker to retrieve?

 

[Banana]

Two questions.

1) I'm not clear on why you need to compare Required property for a Insert/Update operation?

I would only do this once while creating the table (and even that is questionable as Alisa earlier suggested using a template table with truncate & reload to avoid all that messy overhead with creating & deleting a temporary table), but I'm not seeing how it's relevant to a Insert or Update operation.

{EDIT: Wait, are you creating a recordset that stores information about table's properties, and the Required is actually a column within the recordset, rather than a property of a field while you're reading the Required property from the source table?}

2) How many records there are in the source table?

One of wild guess I have in my head is that it does a validation check first, reading the whole table to see if there are no nulls (which is what Required property does) before answering. Sounds unnecessary and quite odd way to do it, but that's just a thought- Do you see any speed difference if the source table is empty?

 

[PearlGI]

Thanks Banana, your EDIT is the correct reason.

{EDIT: Wait, are you creating a recordset that stores information about table's properties, and the Required is actually a column within the recordset, rather than a property of a field while you're reading the Required property from the source table?}

This code is part of a complex data interface routine. The fields of data coming in and the fields of the table where the data is going are not fixed. Indeed, new fields can be inserted into the data table by admin users or even change Type or Size and therefore the import routine needs to work out what data is required and which are optional in order to know if the data file is valid and where data should be loaded. Anyway, the routines all work, it's just a speed issue with the .Required


I'll try your 2) suggestion of an empty table, but this may take a couple of days before I can test this.
However, my initial reaction is that as I'm looking at the Fields of a TableDef rather than the Fields of a RecordSet, surely this would be unaffected by the volume of data in the table? And why does this only affect the .Required property and not any other Field properties! But hey, I'm willing to try anything - so fingers crossed.


Alisa's suggestion of maintaining a seperate table that holds all the field definitions is a possibility, but would require a significant amount of redevelopment given the dynamic nature of the underlying table structure.

 

[Alisa]

Only you know your project, but I have to say the fact that you are letting other people modify the underlying data structure makes me quite queasy. That aside, does it still take 3 seconds if you ask for the required property in the immediate window?

 

[Banana]

I do agree that it's not one of things I would normally expect and I'm more of grasping at straws only because I've observed long delays in saving table design changes due to a validation check and even though that is not the case here, wanted to rule this out.

Another thought. Consider changing the column name from "Required" to something else like "IsRequired", so it's not one of reserved words. Again, I know you've already checked that the performance is with reading the source table's Required, rather than writing to the recordset, but I want to rule out the possibility of confusion on Access due to naming ambiguity.

 

[PearlGI]

Banana, I'll give that a go. I had already thought of that and had tested by setting rsTemp!Required = True which ran with no speed issue, however it could be an ambiguity of using both !Required and .Required within the same line.

Alisa, Will try the immediate window and advise back.
But rest assured that I'm not letting users alter tables directly, this is all controlled and changes validated by routines and hence why your earlier suggestion may be the way to go, despite the redevelopment work involved.

 

[Alisa]

Good luck, I am curious to see how this one turns out.

 

[PearlGI]

Alisa / Banana

Querying fld.Required in the immediate window did not cause any delay, so I've used Banana's suggestion of renaming Required to IsRequired and speeds have dramactically improved.


So in summary the issue was not just the use of a reserved word as a fieldname, but one of using the reserved word at the same time as returning the property of that reserved word.

Thanks very much for your help.

 

[Banana]

Thanks for posting back. I'm glad it's sorted now.