Sample Header Ad - 728x90

Does it matter which database you connect to when querying across multiple databases?

2 votes
2 answers
551 views
I'm building a Sharepoint application with Nintex workflow that runs a single SQL query over multiple databases (on the same MS SQL server). Does it matter which database I specify in the connection string in terms of speed? So if my query looks like this:
col1, col2 FROM db1.table UNION ALL
col1, col2 FROM db2.table UNION ALL
col1, col2 FROM db3.table
would it make any difference if my connection string looks like this:
=***;Database=db1; Integrated Security=SSPI; Connection Timeout=900
or this
=***;Database=db2; Integrated Security=SSPI; Connection Timeout=900
or this?
=***;Database=db3; Integrated Security=SSPI; Connection Timeout=900
The table from db1 has more records than db2, which has more records than db3. EDIT: My query is actually more complex than what I wrote above, I just simplified it because I didn't know that would matter. The real queries have a
clause and a
JOIN
on a fourth database (db4). The compatibility level of db2 and db3 are
Server 2008 (100)
, for db1 and db4 it's
Server 2017 (140)
.
Asked by Teebs (121 rep)
Jan 7, 2021, 09:27 AM
Last activity: Jan 11, 2021, 08:15 AM