Sample Header Ad - 728x90

Techniques for increasing speed of simple select query using DBI R package to MS SQL server where ASYNC_NETWORK_IO is the bottleneck

0 votes
1 answer
322 views
I am executing a simple select query similar to:
DBI::dbGetQuery(db, "SELECT date, x, y, FROM table")
where the table contains a large amount of financial data. The db is a MS SQL server that I manage. I noticed slow query times so dug into the issue more carefully. I identified that these queries were generating large network wait times, specifically ASYNC_NETWORK_IO. The reason that I'm trying to improve the performance is that I often access financial data from a database I do not manage that uses PostgreSQL. I've put together a benchmark piece of code and for the same number of rows, columns, and column types, the same query runs 25-50% faster there. I'm a new database administrator so I'm trying to understand how to improve performance to approach the performance of this other (more professionally) managed database. From reading online (e.g., https://www.sqlshack.com/reducing-sql-server-async_network_io-wait-type/) , my prior is that this is a **application-caused** bottleneck in that I'm not reading the rows from the server fast enough. However, given that a similar piece of code executes much faster with another server, I'm still not sure that it's entirely the application's fault. Finally, if it is the application's fault, I'm having trouble finding resources that **specifically explain how to "read data more quickly"**. I have tried chunking my queries but that hasn't improved performance. For example, resources often say avoid "row by agonizing row" reads or try to "use the read immediately and process afterwards method". However, in my specific situation, I'm not sure what that means or how to avoid it. Thanks in advance for helping a novice DBA.
Asked by nvt (1 rep)
Feb 9, 2024, 04:30 PM
Last activity: May 24, 2025, 02:04 AM