Question about creating mssql temporary tables...

[xrapidx]

you're joining across 16 databases?

 

[guest2013-1]

you're joining across 16 databases?

Yea, Dynamics GP GL tables for Budget/Movement and Period. Originally I pushed the data into the data warehouse and read from there, but with the legacy SSIS jobs it only ran every 24 hours and the FD wanted near-real time data, especially during month-end.

So I created views for each table joining all 16 tables of the same kind and then did a main view with that query. Runs 30 seconds max now (average 16 seconds) and can refresh almost every minute with a main query time of less than 1 second.

/edit guess you can include the data gathering into that and say 31 seconds, but the processes are split apart and runs independent of one another