2

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
or this
Server=***;Database=db2; Integrated Security=SSPI; Connection Timeout=900
or this?
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).

Teebs
  • 121
  • 2

2 Answers2

4

In your example there is no difference.

But there can be difference if your query is more complicated, has parameters and the databases have different compatibility level/parameterization options.

Compatibility level of the database where the query is executed will affect cardinality estimations, so if your predicates contain AND OR conditions the estimated number of rows will be calculated according to different optimizator's rules, there are cases when someone migrates to a new server (say 2014) but leaves user databases compatibility level 100, in this case in he executes the query in the master's context new 2014 optimizator will do calculations, but if he executes the same query in user database the legacy optimizator will do estimations and they will differ.

Also forced parameterization can affect the plan.

If your query contain joins and constants in where filter, these constants will be preserved when estimation is done if database has simple parameterization, so the estimation will use statistics for filtering field. But if the same query is executed in the context of database with forced parameterization the query will be parameterized and the plan will be built by using unknown value for the filter.

sepupic
  • 11,267
  • 18
  • 27
2

Assuming the databases has the same compatibility level, then the performance shuuld be the same.

However, compat level also determine performance related stuff (the CE, whether you have access to new features in the optimizer etc) and with this in mind I believe that the source database could affect performance. However, doing straight SELECT * without a WHERE clause, there isn't much the optimizer can do. Possibly batch mode on rowstore if you are on 2019.

Tibor Karaszi
  • 18,269
  • 2
  • 17
  • 30