2

I am writing automated functional tests for my application. To interact with the application, I use the SQL database to retrieve data and validate data.

The main challenge that I am facing is handling the test data for my tests. I need to access the database to be able to retrieve test data, which may get updated every build. Afterwards, I use that test data for logging in, or navigating to a specific page, or for validations to make sure that what I am doing on the UI is appropriately represented in the back end.

I could hit the database multiple times throughout a test to be able to perform a specific action. The problem is that some of these queries are taking 5 minutes in order to execute, significantly slowing down my test execution and making it unnacceptable.

Therefore, I would appreciate advice on how to handle such a situation.

  • Is there an appropriate design pattern that I should use?
  • Should I execute all my queries up front and store their results somewhere and then use them for all my tests?
  • Should I execute these queries externally, build a test table, and then use that for my testing instead?
  • Or is there something better that you can suggest?

More details based on questions:

My application is allows a user to take an exam. In order for me to properly test a single question in the exam, I need to execute a query that finds this question in the DB. Afterwards, I need to find this question in an exam booklet that is assigned to a number of fake student IDs. Therefore, what I have to do is get this student ID to be able to login to the application to test this particular question. Getting this data to be able to login to the appropriate question is the time consuming part.

2

I have Test classes that create Test Data, They get deleted after the tests are complete.

Generally you need to tests Data creation methods and Deletions anyhow.

That being said, I have no idea what you are doing. How is is that your tests take longer than a user doing it? That means that your tests are not properly mimicking real use cases.. OR the performance is a real issue with your app. If either if those are true you should fix that.

  • Thanks for your suggestions. Let me answer your questions. My application is one that allows a user to take an exam. In order for me to properly test a single question in the exam, I need to execute a query that finds me this question. Afterwards, i need to find this question in an exam booklet that is assigned to a number of fake student IDs. Therefore, what I have to do is get this student ID to be able to login to the application to test this particular question. Getting this data to be able to login to the appropriate question is the time consuming part. – Nikolay Advolodkin Aug 2 '16 at 16:46
  • Agreed, if anything takes 5 minutes, optimize it. The automated tests will help with testing the optimization to guard against regressions :) – Brandon Aug 2 '16 at 21:18
  • @NikolayAdvolodkin why Not have test class that creates a Test User.. Then you use that user for your tests, then you deletes the user (also a test) – Morons Aug 2 '16 at 23:23
  • the problem is that i need to hit the db to get the appropriate test data for that user. Therefore, taking 5 minutes just to get test data for a user for every single test. I believe that the best solution is what @jose_castro_arnaud suggested. Run queries beforehand to get all my data, populate a test data table with that, then run my tests using that table. As opposed to doing this against the production DB. I might hook this into a Setup method that runs before the assembly so that it runs 1 time. Thoughts? – Nikolay Advolodkin Aug 3 '16 at 19:05
0

If possible, look at using table partitioning and/or additional indexes to help speed up your test queries. Just make sure these don't make it to production if they aren't used by your primary code. I've seen good DBAs turn a 24 hour query into a 2 hour query with appropriate use of indexes and partitioning and other database tools/tricks.

As for whether or not there is a design pattern to help with this: I don't think so. It sounds like your application makes fairly complicated updates to the database and in order to test that it does it correctly, you need to query the database.

0

Can you set up a smaller database, with just the test data? Then do so, and use that database. Should be faster than using the full-blown database.

Refresh the data once in a while, copying from the production database, to avoid testing with stale/outdated data.

Try to optimize the queries, use less queries, or get less data, if at all possible.

  • Thanks for your suggestion. I was thinking about the same thing. So you think I should run all my queries one time, set up my test data, and then use that for my tests? As more builds come, I can re-update the test data and just continue to use my tests. The queries will be faster and I will have my own table for working with test data. – Nikolay Advolodkin Aug 2 '16 at 16:50
0

If queries are taking 5 minutes you should see which ones are that slow and add indexes/ delete unnecessary data on those tables. Use a clone of production and trim the tables which cause slow queries

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.