How to Work with Large Data Files

Q can handle very large files - we have tested a file with 130,000,000 cases. However, the larger the data  file the slower it will get. Generally, you should not have any noticeable problems unless working with data files containing a large:

• Number of cases (100,000+ cases)
• Number of variables (10,000+ variables and 10,000 cases). 
• File sizes (200+ MB)

Here are a few things to consider:

• When performance is slow, first restart your computer and Q and work with a minimum number of programs open.
• You will need to be running a 64-bit version of Windows, otherwise Q will be unable to use more than 2GB of your machine's RAM (this is a Windows limitation).
• If you are working with an SPSS or Excel data file and it is surprisingly large, you can often substantially reduce its size by doing the following:
  
  1. Select File > New Project.
  2. Select File > Data Sets > Add to Project > From File.
  3. In the Data Import Window: 
     • Select Use original data file structure.
     • Untick Tidy Up Variable Labels and Strip HTML from Labels.
     • Click OK.
  4. Select Tools > Save Data as SPSS/CSV File.
  5. Use File > Open to open the initial Q Project again.
  6. Select File > Data Sets > Update to update your project to the new file.
• Reduce the number of variables and/or cases in the data file:
  1. Delete cases that you don't need via the Data tab (e.g. incomplete respondents).
  2. Hide variables that you do not need.
  3. Select Tools > Save Data as SPSS/CSV File.
  4. Open the initial Q Project again.
  5. Select File > Data Sets > Update to update your project to the new file.
• You may need extra memory (RAM) in your computer. As a rough guide, multiply the number of variables in your file by the number of cases, and then divide by 100,000,000 and add 2. This is how many GB of RAM you will need.
• Some specific operations in Q, such as table calculations, can utilize extra cores in your CPU. If you find that an operation is slow, perform this test to see if using a CPU with more cores will help:
  1. Open the Windows Task Manager.
  2. Go to the Performance tab and select CPU.
  3. Right-click on the graph and select Change graph to > Logical processors (each "core" of your CPU may have one or more "logical processors", and each allows extra parallel work to be done by Q).
  4. Perform the slow operation in Q while keeping an eye on the graph.
  5. If you find that all of the logical processors are being fully utilized (near 100%), then this means the operation in Q is able to utilize the extra cores. Performing the operation on a CPU with more cores may make it faster as it is able to utilize more cores.
  6. If you find that only one or two logical processors are being fully utilized and the rest are not, then this means that the operation in Q is only using a single core. The operation will not go any faster on a CPU with more cores. It will, however, take advantage of a CPU with a higher clock speed (typically rated in GHz, gigahertz).
• Read Tables Take a Long Time to Compute and Files Take a Long Time to Open.
• Avoid unnecessary linkages between variables. That is, avoid situations where A refers to B, B refers to C, C refers to D, etc. With large projects, it is generally better to have fewer variables that contain a lot of calculations than to have a lot of variables, each containing only a few of the calculations. This is because the more variables that are included in the calculation, the greater the amount of memory on your computer that will be used up.
• If your project is still too slow and you have acted on the above suggestions then send it to our support team and we will see whether we can help. We may be able to suggest improvements to your project, or we may improve Q.

Using QDat Files 

Our QDat file format can substantially speed up the loading of a project. To convert your raw data file to this format:

1. Import the raw data file into Q 5.12.0 or later.
2. After importing, select Use original data file structure.
3. Select Tools > Save Data as SPSS/CSV File and click OK.
4. In the file save dialog (which may be slow to appear), change Save as type to QDat Files (*.QDat).
5. Click Save to save the new data file.
6. Open your original project.
7. Click File > Data Sets > Update > your file.
8. Select the new QDat file and click Open.

You can also export your current project to a QDat and use that in Q instead of your original data, but keep in mind all banners, filters, and other constructed variables will be hardcoded and will not update with new/revised data.

Next

How to Troubleshoot Tables that Take along Time to Compute

How to Deal With Files Take a Long Time to Open in Q