Delete Query takes 1 hr to execute even when table contains 10 rows

[riteshsehgal]

Hi

Recently we have migrated an MS Application to Citrix Environment.
Following are few lines of code:-
'Begin code
DoCmd.DeleteObject acTable, "tblReferenceBusinessUnitRegion"

DoCmd.TransferDatabase acLink, "Microsoft Access", strRemoteUpdate & "\" & gblApplicationName & "Data.mdb", acTable, "tblReferenceBusinessUnit", "tblReferenceBusinessUnitRegion"

DoCmd.RunSQL "DELETE tblReferenceBusinessUnitRegion.* FROM tblReferenceBusinessUnitRegion WHERE tblReferenceBusinessUnitRegion.BusinessUnitID <> " & gblBusinessUnitID & ";"

'End Code

this execution of the delete query is taking an hour to complete. Even though the linked table tblReferenceBusinessUnitRegion is having 10 rows in the table.
The most weird problem is that this behavior does not occur on local environment.
Is there any know issue on Citrix environment with MS Acces?
Please help! its really very urgent...
thanks
ritesh

 

[Melagan]

Are you dealing with linked tables, or are they all local to the .mdb file? I have seen problems in the past, when a .mdb with linked tables and the backend is elsewhere on a slow WAN, where performance takes a major dive.

Also, for slightly cleaner code:

With DoCmd
  .DeleteObject acTable, "tblReferenceBusinessUnitRegion"

  .TransferDatabase acLink, "Microsoft Access", strRemoteUpdate & "\" & gblApplicationName & "Data.mdb", acTable, "tblReferenceBusinessUnit", "tblReferenceBusinessUnitRegion"

  .RunSQL "DELETE tblReferenceBusinessUnitRegion.* FROM tblReferenceBusinessUnitRegion WHERE tblReferenceBusinessUnitRegion.BusinessUnitID <> " & gblBusinessUnitID & ";"
End With

~Melagan
______
"It's never too late to become what you might have been.

 

[Ed2020]

I've no idea whether it will make a difference or not but it may also be worth trying CurrentDB.Execute to run your delete query, instead of DoCmd.RunSQL.

Even over a slow network I can't think of why it would take an hour to delete 10 records (unless maybe there were a lot of related tables and Cascade Deletes was turned on).

Is the system experiencing performance problems in any other areas?

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Also make sure tblReferenceBusinessUnitRegion.BusinessUnitID is indexed.

Ed Metcalfe.

Please do not feed the trolls.....

 

[riteshsehgal]

Thanks a lot guys.. its because of the cascade delete...
But the weird problem is that when we run the programme on the local machine, this cascade delete finishes in 2-3 minutes while on citrix environment it takes 1 hr.. what can be the possible reason?
On local machine the database file resides under c:\temp\folder1
while on citrix server it resides under \\citrixservername\folderX\username\temp\folder1
it it because of slightly deeper nesting?
is there any way we can improve this cascade delete?

thanks
ritesh

 

[Ed2020]

It may be worth turning off cascade deletes and deleting records from each of the related tables individually with a series of delete queries.

Ed Metcalfe.

Please do not feed the trolls.....

 

[riteshsehgal]

i will try that option and see. Hopefully it should resolve.
By the way since the number of tables is too large, is there any query that we can run to remove the casacde delete. or do i need to go each relationship and edit it to remove cascade delete.

ritesh