June 25, 2018 at 6:54 pm
Why would SSMS only use about 500kbps when going through a linked server connection vs almost full bandwith when running a query while connected directly to the server in SSMS?
The result is a query that takes 11 sec via linked server vs less than a second via a direct query.
ODBC connection is slow and also only uses about 500kbps bandwith.
*Update - My issue is a label matrix label that needs an ODBC connection to a db, which is really slow, takes about 11 sec to populate fields. As a workaround, I've created an access db with a passthrough query and pointed the label to the access db. The workaround is much faster, takes 1 sec to populate fields.
Why is performance through ODBC so slow?
June 26, 2018 at 1:43 pm
Jackie Lowery - Monday, June 25, 2018 6:54 PMWhy would SSMS only use about 500kbps when going through a linked server connection vs almost full bandwith when running a query while connected directly to the server in SSMS?
The result is a query that takes 11 sec via linked server vs less than a second via a direct query.
ODBC connection is slow and also only uses about 500kbps bandwith.*Update - My issue is a label matrix label that needs an ODBC connection to a db, which is really slow, takes about 11 sec to populate fields. As a workaround, I've created an access db with a passthrough query and pointed the label to the access db. The workaround is much faster, takes 1 sec to populate fields.
Why is performance through ODBC so slow?
Could be many different things - network, the query itself, the ODBC driver, the settings for the Linked Server, etc. There is just not enough information to provide much other than guesses for the performance.
In terms of the workaround,a passthrough query in Access is similar to using OpenQuery with the linked server. You may want to try Openquery with your linked server.
Sue
July 10, 2018 at 10:37 am
Sue_H - Tuesday, June 26, 2018 1:43 PMJackie Lowery - Monday, June 25, 2018 6:54 PMWhy would SSMS only use about 500kbps when going through a linked server connection vs almost full bandwith when running a query while connected directly to the server in SSMS?
The result is a query that takes 11 sec via linked server vs less than a second via a direct query.
ODBC connection is slow and also only uses about 500kbps bandwith.*Update - My issue is a label matrix label that needs an ODBC connection to a db, which is really slow, takes about 11 sec to populate fields. As a workaround, I've created an access db with a passthrough query and pointed the label to the access db. The workaround is much faster, takes 1 sec to populate fields.
Why is performance through ODBC so slow?
Could be many different things - network, the query itself, the ODBC driver, the settings for the Linked Server, etc. There is just not enough information to provide much other than guesses for the performance.
In terms of the workaround,a passthrough query in Access is similar to using OpenQuery with the linked server. You may want to try Openquery with your linked server.Sue
+1 to that. A passthrough query is effectively accomplishing the same thing. If you use a linked server table in a query, that ENTIRE table has to pass across the network, and not just the desired rows, as the WHERE clause is only applied AFTER that table comes across the network. By using either your passthrough query in Access or using OPENQUERY, you effectively can limit the rows that pass across the network to only those needed as opposed to the entire table, as the query is then process at the remote server, and only the results come across the network. Sounds very much like that's where the problem was. Linked servers are certainly useful tools, but you have to treat them right, so to speak.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply