Linked View performance issues (1 Viewer)

abenitez77

Registered User.
Local time
Today, 11:14
Joined
Apr 29, 2010
Messages
141
I am having performance issue with a DLookup on a linked view from SQL Server. I am executing a stored procedure in SQL Server that runs and updates the data on a view. I then do a DLookup to get values from the view and it takes about 5 seconds for each DLookup to retrieve the data. When I double click on the view it does take a few seconds to open...so it is the linked view that takes time to open and return records. How can Improve or do this differently to return the values I need quicker?

Code:
 sqlstr = "Exec UpdateIncludeTab05 "
 Call SQL_PassThrough(strconn, sqlstr)
                
                Me.txt_AmountDue = DLookup("AMOUNT_DUE", "Dyn_" & project_id & "_ClaimRecapSum")
                Me.txt_Billed = DLookup("BILLED", "Dyn_" & project_id & "_ClaimRecapSum")
                Me.txt_ClaimAmount = DLookup("CLAIM_AMOUNT", "Dyn_" & project_id & "_ClaimRecapSum")
                Me.txt_ClipFee = DLookup("Clip_Fee", "Dyn_" & project_id & "_ClaimRecapSum")
 

Minty

AWF VIP
Local time
Today, 15:14
Joined
Jul 26, 2013
Messages
10,354
By the looks of it you are already creating a dynamically named local table with your results? or is that a series of sql views ?

Either way that looks very inefficient. Can you give an overview of the task and what you are doing to achieve it?
 

abenitez77

Registered User.
Local time
Today, 11:14
Joined
Apr 29, 2010
Messages
141
It is 1 view that is linked. It is not being dynamically created, it already exists. The stored procedure just updates some of the tables that are being used in the view. Then the DLookup is used to retrieve the values.
 

Minty

AWF VIP
Local time
Today, 15:14
Joined
Jul 26, 2013
Messages
10,354
I just re-read you post and noticed the DLookups, there are no criteria, so is the view only returning one record? If view only has one record it's going to be a lot quicker to load that into a recordset than run 4 DLookups.

Is this view stored on locally networked server or WAN?
 

abenitez77

Registered User.
Local time
Today, 11:14
Joined
Apr 29, 2010
Messages
141
it does return 1 record. I believe it is on a LAN. I will try to put it into a recordset.
 

Users who are viewing this thread

Top Bottom