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:
SELECT col1, col2 FROM db1.table UNION ALL
SELECT col1, col2 FROM db2.table UNION ALL
SELECT col1, col2 FROM db3.table
would it make any difference if my connection string looks like this:
Server=***;Database=db1; Integrated Security=SSPI; Connection Timeout=900
Server=***;Database=db2; Integrated Security=SSPI; Connection Timeout=900
Server=***;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
WHERE clause and a
LEFT JOIN on a fourth database (db4).
The compatibility level of db2 and db3 are
SQL Server 2008 (100), for db1 and db4 it's
SQL Server 2017 (140).