FerencMantfeld
10th February 2010, 04:50 PM
We found a situation that when connecting to Sql Server 2005 and above, if you run a parameterized query in Sql Query Analyzer, it runs lightning fast but with BI Plus, if you use parameters with bind variables, as in :
where column = <<PARAMETER>>, the performance slows down.
If you replace this with curly braces, as in {{PARAMETER}}, it signals BI Plus not to use Bind variables and to use hard-coded values. In my opinion, thi sis actually a database design flaw, but since we're not trying to rewrite Sql Server kernel, but rather optimize the way we use it, if you find that parameters and master keys <<MASTER_KEY>> start to slow down Sql Server, try replacing these with {{PARAMETER}} and {{MASTER_KEY}}. We got about a 10X performance boost on one particular instance using Sql Server 2005.
Full credit for this discovery must go to Hugo Morales of City Software, a brilliant consultant who has implemented a complex consolidated financial reporting system for a multi-corp SAP Business 1 customer that has need for consolidated financial reporting across the group. Hugo was able to implement this using standard BI Plus functionality and the power of the ETL functionality found in our Professional Edition. Well done and thank you, Hugo!
where column = <<PARAMETER>>, the performance slows down.
If you replace this with curly braces, as in {{PARAMETER}}, it signals BI Plus not to use Bind variables and to use hard-coded values. In my opinion, thi sis actually a database design flaw, but since we're not trying to rewrite Sql Server kernel, but rather optimize the way we use it, if you find that parameters and master keys <<MASTER_KEY>> start to slow down Sql Server, try replacing these with {{PARAMETER}} and {{MASTER_KEY}}. We got about a 10X performance boost on one particular instance using Sql Server 2005.
Full credit for this discovery must go to Hugo Morales of City Software, a brilliant consultant who has implemented a complex consolidated financial reporting system for a multi-corp SAP Business 1 customer that has need for consolidated financial reporting across the group. Hugo was able to implement this using standard BI Plus functionality and the power of the ETL functionality found in our Professional Edition. Well done and thank you, Hugo!