October 1, 2014 at 9:24 am
Hi,
We have a business analysis (BA) software that extracts data from our ERP database and processes it.
The extraction process uses cross database queries, since we don't want to change the ERP database.
We have views that join ERP tables and BA tables...
I read an article on cross database querying http://www.toadworld.com/products/toad-data-point/b/weblog/archive/2012/11/21/how-to-write-a-cross-database-query.aspx.
The part that most concerns me is joining the same database tables on subqueries before joining with other database tables... Is this a "fact" on SQL Server 2012?
And passing the filter to all subqueries ?
Thanks,
Pedro
October 1, 2014 at 12:03 pm
It looks like the article you linked to applies specifically to how Toad Intelligence Central works and not how SQL Server works.
As long as permissions are correct running cross-database queries against SQL Server usually does not cause a performance problem. Where you could get performance issues is if the columns you are joining on are of different data types and there have to be implicit conversions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 1, 2014 at 1:21 pm
Jack Corbett (10/1/2014)
Where you could get performance issues is if the columns you are joining on are of different data types and there have to be implicit conversions.
Unfortunately we have joins on char columns with COLLATION specified since the databases have different ones...
Right now I'm not very worried about performance since this is a DW with a staging database... The ETL process clears all the data on the staging database, processes all of the ERP data and inserts it in the staging and when it finishes it switches the DW with the staging...
But there are some good practices that still I'd like to go with....
Some views have scalar functions that only do "math stuff"...
On of the views returns 450.000 records and takes 1min... I changed the scalar function to a CASE statement on the view and it ran for 13 secs...
How does SQL handle cross database statistics? Would it be better instead of using directly the tables on other databases use a synonym or that doesn't make any difference?
Thanks,
Pedro
October 1, 2014 at 2:25 pm
PiMané (10/1/2014)
How does SQL handle cross database statistics? Would it be better instead of using directly the tables on other databases use a synonym or that doesn't make any difference?
To the best of my knowledge it doesn't matter. As long as permissions are correct statistics are handled the same. The only thing you miss out on is the optimization you can get with foreign keys, but that would be there even with synonyms.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply