The only way to determine efficiency of SQL query is to watch it with the SQL Query Analyzer. Generally the re-indexing of large tables or cursors are the worst offenders, in most cases DNN modules behave good because DNN caches most of the datasets thus reducing the need for repeated database queries for every page load.
We have seen some modules that keep opening new sql connections for every page hit, after a while a process may have 300 to 400 active sql connections at which point the performance of entire server starts to suffer. People often complain about poor DNN performance with out any understanding of what actually makes the DNN work. DNN will never be as "fast" as the static HTML site, although if configured correctly can be almost as good in terms of speed. DNN is large dynamic ASP.NET application that consists of hundreds of assemblies and various data sources that all have to execute on the server, push the HTML and java-script output to the client, and client has to download all the HTML and media to render it all.
Furthermore, no two DNN installations are 100% identical. The number of modules installed and visible on the page, the type and complexity of the skin and media, number of database hits per page load, the availability of bandwidth on the client side, Internet routing, and multitude of other factors contribute to the overall performance of DNN.