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).

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.

2
  • Thanks, that already cleared it up a bit. 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). So would it be best to connect to db1 then, since it has the highest compatibility level?
    – Teebs
    Jan 11 at 8:16
  • You should test it by executing it from db with c.l. 100 and c.l. 140 and comparing the actual execution plans. Some queries can be faster with the legacy estimations, some with newest, and there can be no difference at all, it's impossible to guess without comparing the actual plans
    – sepupic
    Jan 11 at 8:28
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.