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
Last activity: Jan 11, 2021, 08:15 AM