0

I am new to SQL Server, though have spent sufficient time in Oracle databases. In the current application I am managing contains a lot of denormalized staging tables to receive upstream data.

Views have been created on staging tables each consisting of about 40 tables and multiple joins. These views load datamart tables of the same name as the view in another database.

These views take a lot of time to load the datamart table approx 5hrs. The logic is truncate load i.e. each day whole database is truncated and data is loaded from source system files using format files into staging area tables.

How to tune these views to make the load process faster as the truncate load process has been written on purpose?

halfer
  • 19,824
  • 17
  • 99
  • 186
Yavnica Saini
  • 33
  • 1
  • 1
  • 8
  • So, are the staging tables being truncated before running the process? The staging tables don't have indexes, right? – asemprini87 Mar 14 '17 at 18:15
  • Yes the staging tables are truncated every time they are loaded from files. We do have primary key indexes created on the staging tables. – Yavnica Saini Mar 16 '17 at 08:02
  • Delete the PK and any other index before inserting into the staging table, and create them back again at the end of the process if you need them. Inserting into a table with indexes takes a lot more time that inserting into tables with no indexes and besides they get fragmented. If you insert without the indexes it is faster and once you create them, they will have no fragmentation making them easier to read. – asemprini87 Mar 16 '17 at 15:24

1 Answers1

0

You probably need to look into the normal stuff:

  • Turn on statistics io to see which table causes most of the I/O in the query
  • Check from the leftmost node in the actual plan that your query plan creation doesn't end up into timeout (because of all the joins)
  • Look into fat arrows (=a lot of rows being handled) in the plan
  • Check any expensive operations (sorts, spools, key lookups with big row counts) in the plan
  • Check the plan for orders of magnitude difference in estimated vs actual number of rows

Don't pay that much attention to the cost percentages in the actual plan, those are just estimates and can be extremely misleading.

Without more details (create table & index clauses, actual query plan) etc. it's quite difficult to give any more detailed information.

James Z
  • 12,209
  • 10
  • 24
  • 44
  • Say suppose out of the 40 tables, a bunch of 10 are joined together to generate x columns for the view, can convert them into CTEs and then use them to make main view query. The CTEs can be tuned to better the performance? – Yavnica Saini Mar 16 '17 at 08:51
  • I would assume it depends a lot what you do in CTE (or for example in derived table or maybe in inline function) -- sometimes it seems you get better results and sometimes at least I feel it goes the wrong way :) – James Z Mar 16 '17 at 15:12