Linked Server Performance

Does anybody know how long reads and writes should take when using Linked Tables in Navision-SQL 4.0 when connecting to an external SQL Server using the Linked Server facility? I’ve got all the data flowing properly, but performance seems to be a problem. Even with a record size of only 3-4 small fields and a small number of records (2000), a combined read/write cycle is taking 1-2 seconds. The machines have sufficient CPU power and memory, the 100BaseT network is nowhere near congested, and the keys match between all the tables on all the databases. The processing is fairly simple. I’m stepping thru a set of records filtered on one field, finding related records in another linked table, doing some internal processing, updating data in a local table, and then updating a single field in the first linked table. I’ve tested processing 10 records at a time so I’m pretty sure all the objects are cached. Does anybody know if this level of performance is normal or not? If it isn’t, then does anybody know of any programming practices to avoid that can cause such poor performance ? Thanks, Alan

Hi, I had the same experience. Linked tables are working very slow. Let me know if you will find solution.

I had the same problem, and got help to resolve it by someone on this site, I think it was RobertC, but can’t be sure. I know that it was a pretty simple setup thing to get it working.

Yes, it was Robert, here is the thread… http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=6571&SearchTerms=sql,link THe answer is in there, but I can’t rememebr what I did to fix it. unfortunatley I am on a train right now, and can’t seem to get a VPN connection to be able to connect to the server, and see what I did [V]

David, Thanks for the reply. I’ll look into Robert’s suggestions, but if you happen to recall what you changed to speed things up, let me know. Good luck with TheBook. Alan

Just an update on what I’ve found. In our situation, we’re pulling in Orders and Order Items from the external system, much like Sales Headers and Sales Lines. There’s only about 5 fields in each table amounting to about 100 bytes/record for each. 1. I wrote a report to pull in just Orders and it runs fast, about 3 milliseconds per record. If I Modify the record, access time increased dramatically to about 80 milliseconds. If I Commit immediately after Modify, time increased to about 160 milliseconds (which I have to do in our particular situation). 2. I wrote a 2nd report that pulls in just Order Items and it also ran fast at 3 milliseconds per record. 3. I wrote a 3rd report that pulls in Orders and the Order Lines for each Order and performance degraded considerably. Order access time increased slightly to 5 milliseconds per record, but Order Items increased dramatically to about 50 milliseconds per record. This was done without Modifying any records. 4. I wrote a 4th report that pulls in all the Order Items and copies them into a temporary table. The Order Items then references the temporary table rather than the real table, This report ran very fast. Access time on the Orders went back down to 3 milliseconds per record. Order Lines access time was near zero since it was accessing a temp table. The time to populate the temporary table was the 3 milliseconds per record. Conclusion: When accessing two tables at once, load data from one of the remote tables into a temporary table first. I didn’t try this, but I’d guess loading into a real local table would also be an improvement. VIEWS ===== The Navision documentation says Views should be created using a pass-through query in the FROM clause. For example: CREATE VIEW dbo.ViewName AS SELECT FieldNames FROM OPENQUERY(LinkedServerName, ‘SELECT Fields FROM TableName’) Rowset_1 This form worked very well when only reading data (3ms per record), but performed poorly when modifying a record (160ms). If you’re linking to another SQL Server, the folks at Microsoft informed me you can avoid the overhead of the Pass-through query and use a 4-Part Name directly, replacing the FROM OPENQUERY line with FROM LinkedServerName.Catalog.Schema.TableName In my test, updates took 70ms a pretty good improvement over the 160ms using the passthrough. Unfortunately, read-only queries went up to 20ms per record. Conclusion: Use 4-Part Names when you need to update records, use a Pass-Through Query when only reading records.

Did, anyone get modifications working over views on linked servers? i’m constantly getting an error re xact abort must be on - MS say its a bug in the client.

Moved to SQL forum