| Author |
Topic |
|
osupratt
Posting Yak Master
238 Posts |
 Posted - 2014-06-20 : 15:52:42
|
| i have two Functions. one that pulls in dates from another function and cross apply's to a table that gives me Equipment and the Equipments location for a Date Range parameter. the other function gathers data for work done and the revenue for the same Equipment. So Function A gives the availability of the Equipment and Function B gives us the production of the Equipment. I query these two Functions doing a LEFT OUTER JOIN on A to B. The results pull in as expected where for example on 1/1/2014 it shows Equipment 1 and if gives the info from Function A and if the Equipment worked it will show the production info from Function B on the same row. If the Equipmet didn't work then there will be NULLS from Function B. This is normal and correct.The issue is that Function A is on one server and Function B is called across another server. I can filter on one of my companies (there are nine companies) and pick a date range of 1/1/2014 - 3/31/2014 and it will return results in about six minutes. If i filter on two companies with same date range it takes over 12 minutes returning 2000 plus rows. If i run this for all companies it runs for over several hours before i cancel it. on the execution plan it shows 'Remote Query' as for most of the costs.My question is how would i go about approaching a solution? Is there a way to dump the results from Function A into a table and then do a LEFT OUTER JOIN out of that to Function B? Would this make a difference. Can i run a SSIS job from the second server that puts my tables that i build Function B with up on my first server nightly and then query against that? Would this help?I am just stuck on how to approach this. it seems that optimization is not the answer and adding an index somewhere is not going to help to the degree that i need. This needs to run from a dashboard and is not the kind of report that someone is going to sit around and wait 8 minutes to process or anything.If anyone can maybe point me in a direction one that i haven't thought of ........anything; that would be great. Thanks ahead of time for your help. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-20 : 15:57:49
|
| If you are doing cross server calls/joins/etc. you are already at a disadvantage. Ideally, you could ETL the data from one or both servers to a common server and run reports off of that (the SSIS job you mentioned). Given what little I know of your ecosystem, that seems like a reasonable solution. |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-06-20 : 17:59:33
|
| You likely need to rewrite "Function B" to fully process the table as efficiently as possible, pull that result to an indexed temp table on other server, then do the join. |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2014-06-20 : 18:03:06
|
| So how would i pull a Functions result into an indexed temp table? Sorry as i have never tried ths before. i will research but is there some basic ways of proceeding? Thanks. |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2014-06-21 : 13:57:38
|
| Both functions are on one server. Function B however is calling across servers for the results. So is a solution to create a temp table on where both Functions are located to pull the Function B results in and then join Function A to this temp table? Again i have not done this so i am just looking for workable solutions. Thanks for your help. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-06-21 : 15:45:21
|
| Nope..the best option would be to bring all data from various servers onto a single server and then query from there. Thats reason why Lamprey suggested doing ETL to bring data to common area in consistent format and report out of it. You can use SSIS ETL tool of SQL Server for getting data onto common server.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2014-06-22 : 23:22:20
|
| Thanks. That makes perfect sense to me. |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2014-06-24 : 15:54:49
|
| So just to be sure; and this might be a dumb queston to a whole lot of you. Ok so i have functions on Server A that query Server A. I have functions on Server A that query Views on Server A that query tables on Server B. So querying a View does not make a difference correct? i mean what i need to do is have the data (SSIS job or whatever) on Server A instead of Server B querying from Server A? I just want to be sure before researching and requesting ETL of data. Thanks. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-24 : 16:27:04
|
| In general, you cannot tell what the optimizer is going to do when it comes to cross server calls. So, if your view references tables on a different server, you run the risk that SQL will pull ALL the data into the local TEMPDB from the remote server and use it from there. Given that, I'd avoid any cross server "anything" if performance is a concern. |
|
|
|
|
UDF into temp table help