Correcting myself. I was ranting about MS SQL and Cognos interaction. I was totally wrong.
The problem, described shortly is:
-
you have 2 databases in MS SQL residing on the same physical server
-
you create a join “table A from db1 with table B from db2”
-
Cognos creates two select queries (select * from db1.A) and (select * from db2.B) and then does join locally on server. That’s terribly slow. Cognos should be generating one select query, performing the join on the db server and that’d be really fast.
The answer is simple: there’s no way Cognos can find out that these two databases are on the same physical server. But we can help old fella a bit, setting a datasource property (Content Manager Datasource) the same for both datasources. Therefore, the same datasource prefix will be generated for both tables and they’ll be regarded as residing on the same server. Query speed will increase dramatically.
Morale: always set the datasource property for MS SQL databases located on the same server.
Powered by FireStats