2

I'm dealing with a three piece datapath: client application, host integration server, db server. Client application (MS Mashup Engine) is generating queries that pass through MS SQL Server to a legacy IBM iSeries DB backend.

I'm running into issues where the client is generating queries like

select * from x where numericValue = 1.46510+003

I'm checking the execution plan for these on the SQL Server and they result in a full data load with the comparison occurring on SQL Server (which is acting as the Host Integration Server).

By comparison, a human generated query

select * from x where numericValue = 1465.1

results in no scan and performance two orders of magnitude faster.

I have tried playing with the client application to force it to generate something like the human generated query, but I've had no luck.

I'm not sure if I can massage the way the query plan is generated in SQL server by playing with column data types. I.e. exposing a view over the backend DB with explicitly defined data types. Or otherwise forcing query plan generation?

Any thoughts?

Alexander Toptygin
  • 407
  • 2
  • 8
  • Dynamic SQL is more likely to have various on the fly execution plans. You could try putting your logic in a stored procedure which (in general) will have plan re-use and go from there. – sniperd Jun 15 '18 at 14:32
  • 1
    This will be problematic unless you can coerce a proper numeric literal. SQL Server will interpret the floating point literal as a real or float data type, which has a [higher data type precedence than numeric](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql). Consequently, a full scan will be required even if an index exists on the column. – Dan Guzman Jun 15 '18 at 14:39
  • @sniperd I don't believe that Power Query can target stored procedures and since it generates it's own queries dynamically it'd be problematic to create a stored procedure that can accommodate the breadth of all possible queries the application generates. – Alexander Toptygin Jun 15 '18 at 16:00
  • @DanGuzman I guessed that that is what's going on. My hope now is that I can explicitly cast the column to a numeric type to force Power Query to generate different native SQL. Without that, ... I might be out of luck – Alexander Toptygin Jun 15 '18 at 16:02
  • @AlexanderToptygin You can definitely execute a stored procedure from Power Query. I've had to do it plenty of times where I pass it parameters and it passes back a table. – Alexis Olson Aug 23 '18 at 17:00

1 Answers1

1

I'm not sure if I can massage the way the query plan is generated in SQL server by playing with column data types. I.e. exposing a view over the backend DB with explicitly defined data types. Or otherwise forcing query plan generation?

No, the answer is no using all of the following:

  • casting types prior to delivering to power query/power bi changes nothing
  • adapter properties set for DB2OLEDB or IBMDASQL ( f.ex. Decimal As Numeric=True; Derive Parameters=True; etc...) have no effect
  • creating stored procedures over the data requires parametrization in power query which is fine, but does not integrate with the ui. I.e. - using a parameter field instead of the column filters is not clean.

TBH, this has become less and less of a problem as backend performance improves and frontend caching is available.

Hope it helps, -Alex T.

Alexander Toptygin
  • 407
  • 2
  • 8