Greetings,
I have a query that takes about 28 seconds to run. If I open the query, run it, then save the query, it will run in less than 3 seconds. I have found a couple of postings that mention this method to improve performance, but would like to know if anyone knows if there is a way to automate this process.
We have a VB process that preps the database for deployment (compact & repair, sets options, etc) and I would like to be able to optimize certain queries before making the MDE. As there are several hundred queries -- many requiring parameters -- it would be nice to avoid a large manual effort.
I am including two references below (but know of another that I can't find at the moment).
Thank you very much!
Wayne
Ref: Microsoft Knowledge Base Article 209126 (“If you add a significant number of records to your database, you must open and then save your queries to recompile the queries. For example, if you design and then test a query by using a small set of sample data, you must re-compile the query after additional records are added to the database.”
Experts Exchange: “After compacting the database, run each query to compile it using the updated table statistics.”
I have a query that takes about 28 seconds to run. If I open the query, run it, then save the query, it will run in less than 3 seconds. I have found a couple of postings that mention this method to improve performance, but would like to know if anyone knows if there is a way to automate this process.
We have a VB process that preps the database for deployment (compact & repair, sets options, etc) and I would like to be able to optimize certain queries before making the MDE. As there are several hundred queries -- many requiring parameters -- it would be nice to avoid a large manual effort.
I am including two references below (but know of another that I can't find at the moment).
Thank you very much!
Wayne
Ref: Microsoft Knowledge Base Article 209126 (“If you add a significant number of records to your database, you must open and then save your queries to recompile the queries. For example, if you design and then test a query by using a small set of sample data, you must re-compile the query after additional records are added to the database.”
Experts Exchange: “After compacting the database, run each query to compile it using the updated table statistics.”
