Tuesday, May 10, 2011

Named Queries in Relational Databases

Databases don't provide a way to create named queries with runtime arguments.  Yes we have views but most databases are accessed via programming languages where we need to construct the SQL query. We do have the Prepared statements but again they are programming language constructs and not named database level constructs.

Benefits:
1) No need to parse queries. They are callable by name.
2) Prevents SQL injection attacks because of not parsing.
3) Easy integration with programming languages.  db.execute("myQuery", arg1, arg2,...)

4) Instead of trying to optimize every SQL query, every time, database will have the list of the most important queries it needs to answer. This allows databases to save time by pre-planning the execution in the short run and in the long run, databases can optimize the layout of files or creating of indexes automatically to optimize the access pattern.
5) Easy to change db logic. As long as the query returns the same fields (may be more), you can easily change the actual query logic without changing application code.

Their is a lot in the name. 

No comments:

Post a Comment