Slow .Net data provider.

Connectivity
Enthusiast

Slow .Net data provider.

Hi.

We have a Teradata 13.10 backend installation and a front-end installation on MS SQL 2008, using Microsoft SSAS. Due to our data model and a minor "design-glitch" in MS SSAS, we have been forced to make a MOLAP solution, generating materialized cubes on the SQL server.

However we experience that data transfer from Teradata to the SQL server is quite slow, especially when transferring large amount of data.

We use Teradata's .Net data provider, and have a feeling that this is the cause.

We have checked the network connection and hardware and have ruled that out, since collection of data from SQL database using native connection, show much better performance.

And the performance of the Teradata database should not be an issue either, since the SQL finishes within a few minutes, and then the transfer takes an hour or two...

So basically my question is:

Is there a better alternative than Teradata's .Net data provider, when you need to move large amount of data from Teradata to MS SQL database?

Looking forward for any constructive comments.

/Jakob

2 REPLIES
Teradata Employee

Re: Slow .Net data provider.

Have you measured the performance of the .NET Data Provider outside of the MS SSAS?

Here is a quick way to measure performance:

1) Create a Visual Studio console application and add the following code the default Program.cs file:

using System;
using System.Configuration;
using System.Data;
using System.Diagnostics;

using Teradata.Client.Provider;

namespace PerfTest
{
    class Program
    {
        static void Main(string[] args)
        {
            TdConnection cn = new TdConnection();

            try
            {
                Console.WriteLine(".NET Data Provider Version = {0}\r\n\r\n", typeof(TdConnection).AssemblyQualifiedName);

                String connectionStr = ConfigurationManager.AppSettings.Get("Connection_String");
                if (String.IsNullOrEmpty(connectionStr))
                {
                    Console.WriteLine("Add \"Connection_String\" to application configuration file.");
                }

                String sqlText = ConfigurationManager.AppSettings.Get("SQL_Text");
                if (String.IsNullOrEmpty(sqlText))
                {
                    Console.WriteLine("Add \"SQL_Text\" to application configuration file. ");
                }

                // Validate Connection String.
                TdConnectionStringBuilder conStrBuilder = new TdConnectionStringBuilder(connectionStr);
                Console.WriteLine("Session Character Set = {0}; Response Buffer Size = {1}", conStrBuilder.SessionCharacterSet, conStrBuilder.ResponseBufferSize);
                cn.ConnectionString = conStrBuilder.ConnectionString;

                // Open Connection.
                Stopwatch watch = new Stopwatch();
                watch.Start();
                cn.Open();
                watch.Stop();
                Console.WriteLine("Open Connection took {0}.", watch.Elapsed );
                watch.Reset();

                Console.WriteLine("\r\n\r\nPress any key to start ...");
                Console.ReadLine();
               
                // Execute and retrieve result.
                TdCommand cmd = cn.CreateCommand();
                cmd.CommandText = sqlText;
                cmd.CommandTimeout = 0;

                for (Int32 index = 1; index <= 1; index++)
                {
                    Console.WriteLine("\r\n\r\nIteration #{0}", index);

                    // Not use DBS Statement Cache.
                    cmd.CommandText = cmd.CommandText + "   ";

                    watch.Start();
                    using (TdDataReader reader = cmd.ExecuteReader())
                    {
                        watch.Stop();
                        Console.WriteLine("Query execution took {0}.", watch.Elapsed);
                        watch.Reset();

                        Console.WriteLine("Number of Rows: {0}", reader.RecordsReturned);

                        Object[] firstRowValues = new Object[reader.FieldCount];
                        Object[] rowValues = new Object[reader.FieldCount];

                        // Retrieve Data.
                        watch.Start();
                        if (reader.Read())
                        {
                            reader.GetValues(firstRowValues);
                        }

                        while (reader.Read())
                        {
                            reader.GetValues(rowValues);
                        }

                        watch.Stop();
                        Console.WriteLine("Data retrieval took {0}.", watch.Elapsed);

                        DumpRow("*** First Row ***", reader.GetSchemaTable(), firstRowValues);
                        DumpRow("*** Last Row ***", reader.GetSchemaTable(), rowValues);

                        Console.WriteLine("================================");
                        watch.Reset();
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally
            {
                cn.Close();
            }

            Console.WriteLine("Press any key to exit ...");
            Console.ReadLine();
        }

        private static void DumpRow(String msg, DataTable schema, Object[] row)
        {
            if (schema != null && row != null)
            {
                Console.WriteLine();
                Console.WriteLine(msg);

                for (Int32 index = 0; index < schema.Rows.Count; index++)
                {
                    Console.WriteLine("{0}: {1}", schema.Rows[index]["ColumnName"], row[index] );
                }
            }

            Console.WriteLine();
        }
    }
}

2) Add an App.Config file to the project; and cut and paste the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="Connection_String" value="Data Source=myTdSystem;User ID=x;Password=y;Response Buffer Size=65535;"/>
    <add key="SQL_Text" value="select * from PERF_THR2"/>
  </appSettings>

  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Teradata.Client.Provider" processorArchitecture="MSIL" publicKeyToken="76b417ee2e04956c"/>
        <publisherPolicy apply="Yes"/>         
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

3) Modify the Connection_String and SQL_TEXT in the App.Config.

4) Build and run it.

5) compare the performance to the MS SSAS scenario. I think you will see that MS SSAS has overhead when it marshals data from Managed to Native.

Teradata Employee

Re: Slow .Net data provider.

fyi; you can find the following snippet in a blog from the "Analysis Srevices and PowerPivot Team"

"It does support ADO.NET however there is a performance penalty for the Analysis Services native code to ADO.NET managed code interop."