March 4, 2011 at 2:40 am
Hi
We're using a product from a vendor that has been running quite slowly. The product has gratuitous splatterings of "SELECT * FROM" pretty much everywhere. Also, there are no relationships set up between any of the tables.
I created one clustered index (that particulare table had a PK, but it wasn't clustered) and six non clustered indices on other tables and achieved a 39.1% reduction in the time taken to load a large web page.
I feel that me digging around the database trying to find out which tables are related and how, running Profiler traces to figure out which SELECT queries have been run and how many times they have been run and figuring out the relationships of tables, views and stored procedures (thank you Red Gate[/url] for SQL Dependency Tracker[/url]!) is a sub-optimal way of doing things.
I recall in my mind somewhere that there there might be a DMV that I can query that will return data on which queries have been executed and how many times times they've been executed, but I don't know which one it is.
How do you guys deal with optimising performance when "SELECT * FROM" is involved?
Thanks
March 4, 2011 at 5:31 am
Are you looking for sys.dm_exec_query_stats?
-Ki
-Ki
March 4, 2011 at 8:19 am
AndrewJacksonZA (3/4/2011)
How do you guys deal with optimising performance when "SELECT * FROM" is involved?
A "select *" is rarely a good idea but I understand you do not have access to the source code so you have to live with it.
In this scenario the quality of the predicates is what will help pin-pointing opportunities for performance improvement.
If predicates are calling for full table scans then there is little you can do.
If predicates allow for improvements based on indexing then proper indexing is the way to go.
I will start by identifying the five queries that hurt the more - try to fix them then start again and work on the top five queries then repeat this again and again at least until business is okay with performance.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply