Database

Speed up slow views through custom materialization

published on
SQL views are aluring as a means of abstraction; a “building block” to hide away commonly used complexity. It is no wonder then that us developers will try them out, and before you know it, your clever recursive CTE view on that hierarchy is used everywhere, by everyone, but how is it affecting overall database performance… They look like tables, can be joined on, selected from, and in some cases even updated just like tables, yet the reality is that they are not like tables. Read More...

CTE : simplify those nested sub queries

published on
This article examines how sub queries can be substituted for the far more readable common table expressions, or CTEs available in many RDBMS systems. I was motivated to write this article when a friend who is fairly new to SQL expressed difficulty in grasping queries containing nested sub queries. If you’ve never heard of CTEs before and you want to get the most out of this article, I recommend you get AdventureWorks2014 sample database and experiment a little with the queries below. Read More...

now make it fast

published on
“He began to copy one of our tables, which contained partial user information, including email IDs, hashed passwords, and last tested URL. His copy operation locked the database table, which raised alerts on our monitoring system. On receiving the alerts, we checked the logs, saw an unrecognized IP, and blocked it right away. In that time, the hacker had been able to retrieve only a portion of the data.” – From the postmortem of the Browser Stack hack of 9th November, 2014 at 23:30 GMT Since relational database management systems (RDBMS) have been used in production environments since 1970 (Micro DBMS), and the theory on which they run was developed in the preceding decade,and perfected in the three remaining decades of the previous century (long ago), it was not surprising that, as a subject, it received very little attention in our curriculum - at least where I studied. Read More...