locked
Should I move data from an external EDW (Teradata) that my app uses to a local SQL server instance? RRS feed

  • Question

  • User-138270719 posted

    I have a web api / MVC 5 app that pulls fairly static data from an in-network Teradata server which is used as our EDW. The data is loaded into the EDW once daily and does not refresh or change outside of that load period.

    Instead of my app reading from the EDW directly, making a round trip for every call, does it make more sense to have an ETL process that moves a copy of the daily data the app needs to a local instance of SQL server and perform the pulls from it? I am looking for the fastest possible solution but also want to be mindful of best practices. Any advise is greatly appreciated.

    If it is best to move the data, what are some of my options for loading the data from the SQL server side?

    Friday, October 2, 2015 3:27 PM

Answers

  • User1644755831 posted

    Hello,

    Thank you Krunal but I guess what I am wanting to know is if its considered a best practice to host the end result data in a local SQL server or use Teradata ODBC drivers to access the data and perform the aggregation and then bring that data back to the app (using ado.net currently).

    In your case I think it should be ok to move data once a day. NO need to make call everytime.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 5, 2015 9:13 PM

All replies

  • User1644755831 posted

    Hi,

    If it improves your performance then. You can create a Windows Service that would pull the data and insert into the SQL server daily. Then you could then use EntityFramework or ado.net to pull that data from the SQL server into the MVC app.

    If it is best to move the data, what are some of my options for loading the data from the SQL server side?

    Please see: http://www.dotnetfunda.com/articles/show/1542/adonet-entity-framework-how-to-retrieve-data-using-stored-procedure-st

    Please see: http://www.dotnetperls.com/sqldataadapter

    Hope this helps.

    With Regards,

    Krunal Parekh

    Monday, October 5, 2015 1:00 AM
  • User-138270719 posted

    Thank you Krunal but I guess what I am wanting to know is if its considered a best practice to host the end result data in a local SQL server or use Teradata ODBC drivers to access the data and perform the aggregation and then bring that data back to the app (using ado.net currently). I am currently connecting to TD and running some fairly complex queries to achieve the desired data and then consuming the results in my app. Considering the data in Teradata is only loaded once a day I feel as if I create an ETL process that loads the results of these queries into a local SQL server instance that it would improve performance.

    Ultimately speed is of the paramount but I also want to be mindful of any best practices.

    Monday, October 5, 2015 9:21 AM
  • User1644755831 posted

    Hello,

    Thank you Krunal but I guess what I am wanting to know is if its considered a best practice to host the end result data in a local SQL server or use Teradata ODBC drivers to access the data and perform the aggregation and then bring that data back to the app (using ado.net currently).

    In your case I think it should be ok to move data once a day. NO need to make call everytime.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 5, 2015 9:13 PM