Query slowed up
-
rs1.Open "SELECT Count(1)FROM (SELECT DISTINCT mt1.TestCaseName, mt1.TestScriptName, " & _ "mt1.FinalStatus FROM (Metrics_TestCases " & _ "AS mt1 INNER JOIN MaxScriptQuery as mt2 ON( mt1.TestCaseName = mt2.TestCaseName And " & _ mt1.TestScriptName = mt2.TestScriptName And mt1.ReleaseVersion = mt2.ReleaseVersion and " & _ "mt1.TestScriptID = mt2.maxTestScriptID) ) INNER JOIN Release_chart as tr ON mt1.ReleaseVersion = tr.ReleaseVersion) " & _ " ", oConnection, adOpenStatic, adLockReadOnly This is the query i used to get some data from access table. MaxScriptQuery is a inner query i used to join . MaxScriptQuery - SELECT DISTINCT TestCaseName, TestScriptName, ReleaseVersion, MAX(TestScriptID) AS MaxTestScriptID FROM Metrics_TestCases GROUP BY TestCaseName, TestScriptName, ReleaseVersion; This query worked fine some week before. But to my surprise now the same query with same set of data is taking too much time. I dont know why the query is suddenly slowed up. CPU usage is also very high while running this query
-
rs1.Open "SELECT Count(1)FROM (SELECT DISTINCT mt1.TestCaseName, mt1.TestScriptName, " & _ "mt1.FinalStatus FROM (Metrics_TestCases " & _ "AS mt1 INNER JOIN MaxScriptQuery as mt2 ON( mt1.TestCaseName = mt2.TestCaseName And " & _ mt1.TestScriptName = mt2.TestScriptName And mt1.ReleaseVersion = mt2.ReleaseVersion and " & _ "mt1.TestScriptID = mt2.maxTestScriptID) ) INNER JOIN Release_chart as tr ON mt1.ReleaseVersion = tr.ReleaseVersion) " & _ " ", oConnection, adOpenStatic, adLockReadOnly This is the query i used to get some data from access table. MaxScriptQuery is a inner query i used to join . MaxScriptQuery - SELECT DISTINCT TestCaseName, TestScriptName, ReleaseVersion, MAX(TestScriptID) AS MaxTestScriptID FROM Metrics_TestCases GROUP BY TestCaseName, TestScriptName, ReleaseVersion; This query worked fine some week before. But to my surprise now the same query with same set of data is taking too much time. I dont know why the query is suddenly slowed up. CPU usage is also very high while running this query
Access is a dog - I would compress & repair the database and see if it improves the response. Look into adding any required indexes.
-
Access is a dog - I would compress & repair the database and see if it improves the response. Look into adding any required indexes.
-
Thanks for your reply Holmes. But dont know how to compact and repair without exiting the application in VBA. Normally the compact and repair is used while exiting the application . How to apply it for a running code?
Hi, Don't use Distinct key word its will reduce the performance. Without distinct also given the same result because you are using group by class. So no need distinct key word. Just remove and check it.
-
Thanks for your reply Holmes. But dont know how to compact and repair without exiting the application in VBA. Normally the compact and repair is used while exiting the application . How to apply it for a running code?
Well, I posted an entry explaining this yesterday but it seems to have disappeared. Not sure what happened there. I was having a few problems accessing the site yesterday. I can't be bothered writing it all again, but basically you cannot compact a database while it is open. If you want to run the compact from code, you must run it from a different database. Then you can use DBEngine.CompactDatabase, making sure that the data file is closed and is not in use before you do the compact.