community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

SQL Server read performance is slow in Designer

gtorres8
Alteryx Alumni (Retired)
Created

‎05-28-2021 03:17 PM

Environment Details


Within Designer, placing an Input Data tool that connects to SQL Server and running a simple query that pulls thousands or even millions of row can take hours to retrieve data.

 

Using other tools such as SQL Server Management Studio (SSMS) will run the same query and retrieve the same amount of data within minutes. How can we improve read speeds in Designer?

 

  • Alteryx Designer
    • All versions
  • SQL Server ODBC Drivers
    • 11
    • 13
  • SQL Server
    • All versions


Cause


An ODBC connection is being used. ODBC is typically slower as a connection interface.

ODBC architecture adds another layer into the mix when communicating SQL from a client to a database. One; between the application (the SQL query within Alteryx) and the Driver Manager > Two; then between the Driver Manager (which translates it to native query language) and the service provider interface (also known as the driver).

This translation adds a performance overhead which is worse with large amounts of data; either a large number of records or very wide tables or large objects (LOBs) data types or any combination of such.

Tools such as SSMS are not a good "apple-to-apple" comparison of reading performance mainly because SSMS does not use an ODBC interface to connect; it uses a .NET Provider interface.



Resolution


Solution A


Connect using Microsoft Windows OLE DB. OLE DB is considered the predecessor to ODBC and will have better performance.

1. From the Input Data tool, go to Data sources > Microsoft SQL Server > OleDB.



2. Select either, "Microsoft OLE DB Provider for SQL Server" or "SQL Server Native Client 11.0" as the Provider and click Next.



3. Enter the SQL Server host, credentials (authentication method), and database. Click Test connection to ensure it works.


 

Solution B


If ODBC is still needed for one reason or the other, to help lessen performance overhead, use the statement below within the PreSQL/Post SQL Statement field in the Input Data tool.
  
SET NOCOUNT ON




When SET NOCOUNT is ON, the count (which is the number of rows affected by a T-SQL statement) is not returned, to reduce network traffic.

 

Additional Resources

 

 
Labels