5 ms·

Historically at least a common use case for dynamic sql was to build up a predicate based on user-selected filters, where the user can select one or more condit

Historically at least a common use case for dynamic sql was to build up a predicate based on user-selected filters, where the user can select one or more conditions from a larger set. Handling this with a single static query requires the planner to deal gracefully with constructions like 'where @opt_param is null or some_col = @opt_param', particularly where the choice of index depends on which filters have been selected. Having not gone near such cases for a long time, I'd hope that the major dbs have become rather good at handling such cases these days. Edit: the sanest solution, then and now, is probably to select between a number of alternative static queries based on what index values have been supplied, and to use the 'is null or' construction for other filters.

magicalhippo 3y ago
Our good old CRUD app does that. We dynamically generate a WHERE clause based on the column(s) the user wants to search for, set the parameter value(s) based on the search values and rerun the query. This approach allows us to have a one-liner to enable searching in a grid, and scales well to grids with millions of rows and 50+ columns. Of course the user might issue a search on a single non-indexed column, but this hasn't been an issue in practice. Either it happens very seldom, or they've already filtered on something indexed. We also have use dynamic SQL for cases where we needed to select from different tables or views depending on some run-time condition, or similar. No direct user input there though so don't really need the safety that parameters bring.
- magicalhippo 3y ago
  Reading further I see the article goes into dynamic tables/views, and how it's normally a code smell. I agree it's not something you should do lightly, and you need to consider the cost vs the benefits. I mean clearly we could just maintain multiple near-identical static queries and just pick the right one, rather than have a single dynamic query. However, then you're stuck having to keep them in sync and you will eventually screw that up. In our case, we'll resort to that if the alternatives are too painful, which so far has just been a few places amongst our several hundred queries.
jes5199 3y ago
increasingly, search is handled by something other than SQL, so this is less relevant than it used to be
- albertopv 3y ago
  At least in Italy you may be surprised how many people use a RDBMS for almost everything. Business logic with stored procedures, FTS, queues, OLAP, data lake... It's the tool the know and they don't want to learn something else.
  - _a_a_a_ 3y ago
    If you could explain what a better tool is and why, I'd be extremely interested, TIA
    - albertopv 3y ago
      Would you use postgres to queue a billion messages a day consumed by dozens of applications? Or a message broker like kafka? Would you store dozens of TB of data in postgres or in a dedicated data lake? Would you put your business logic in Oracle stored procedures, basically denying the possibility to change DBMS? Would you put your applications log and monitor data in a RDBMS? There's no better tool generally speaking, there's the tool more suited for your use case, RDBMS are now very good, but not for everything.
      - _a_a_a_ 3y ago
        If you're as big as Google maybe not. Most companies aren't receiving 5,000+ messages a second, and I don't understand what's so special about a data like. There's nothing wrong with storing terabytes in an RDBMS. Also nothing wrong in putting your app and monitor data in one either.
sommar 3y ago
[dead]

magicalhippo 3y ago

Our good old CRUD app does that. We dynamically generate a WHERE clause based on the column(s) the user wants to search for, set the parameter value(s) based on the search values and rerun the query. This approach allows us to have a one-liner to enable searching in a grid, and scales well to grids with millions of rows and 50+ columns. Of course the user might issue a search on a single non-indexed column, but this hasn't been an issue in practice. Either it happens very seldom, or they've already filtered on something indexed. We also have use dynamic SQL for cases where we needed to select from different tables or views depending on some run-time condition, or similar. No direct user input there though so don't really need the safety that parameters bring.

magicalhippo 3y ago
Reading further I see the article goes into dynamic tables/views, and how it's normally a code smell. I agree it's not something you should do lightly, and you need to consider the cost vs the benefits. I mean clearly we could just maintain multiple near-identical static queries and just pick the right one, rather than have a single dynamic query. However, then you're stuck having to keep them in sync and you will eventually screw that up. In our case, we'll resort to that if the alternatives are too painful, which so far has just been a few places amongst our several hundred queries.

jes5199 3y ago

increasingly, search is handled by something other than SQL, so this is less relevant than it used to be

albertopv 3y ago
At least in Italy you may be surprised how many people use a RDBMS for almost everything. Business logic with stored procedures, FTS, queues, OLAP, data lake... It's the tool the know and they don't want to learn something else.
- _a_a_a_ 3y ago
  If you could explain what a better tool is and why, I'd be extremely interested, TIA
  - albertopv 3y ago
    Would you use postgres to queue a billion messages a day consumed by dozens of applications? Or a message broker like kafka? Would you store dozens of TB of data in postgres or in a dedicated data lake? Would you put your business logic in Oracle stored procedures, basically denying the possibility to change DBMS? Would you put your applications log and monitor data in a RDBMS? There's no better tool generally speaking, there's the tool more suited for your use case, RDBMS are now very good, but not for everything.
    - _a_a_a_ 3y ago
      If you're as big as Google maybe not. Most companies aren't receiving 5,000+ messages a second, and I don't understand what's so special about a data like. There's nothing wrong with storing terabytes in an RDBMS. Also nothing wrong in putting your app and monitor data in one either.

sommar 3y ago

[dead]