The impact of temporary files generated during SQL queries on database queries

When an SQL statement is used for query, the query result is stored in a temporary file with the suffix tmp. When the query is performed, the file stores the query results. When the query is disabled, the temporary file is automatically deleted, the file does not exist. 1. Where does the tmp file exist for wind

When an SQL statement is used for query, the query result is stored in a temporary file with the suffix tmp. When the query is performed, the file stores the query results. When the query is disabled, the temporary file is automatically deleted, the file does not exist. 1. Where does the tmp file exist for wind

When an SQL statement is used for query, the query result is stored in a temporary file with the suffix tmp. When the query is performed, the file stores the query results. When the query is disabled, the temporary file is automatically deleted, the file does not exist.

1. Where does the tmp file exist?

For windows systems, there is a system environment variable, which can be passed through

Right-click my computer-properties-advanced-environment variables to view.

Tmp temporary files exist in the temp folder.

Ii. tmp File Format

For the query results stored in the tmp file, the storage of the query results in the tmp file is based on

The storage format is as follows:

Each column is divided into two parts:

The first part indicates the length of the column. When the character length is greater than 255, it is stored in 5 bytes.

Part 2: Data of this column. For structured data, it is converted to uniocode for storage.

Select 1, cast (1 as bigint), 'AB', getdate ()
Query the generated tmp file as (hexadecimal)
04 01 00 00 00 08 01 00 00 00 00 00 00 04 61 00 62 00 08 60 73 c2 02 7a 7b cb 08
04 01 00 00 00 is the first 1
08 01 00 00 00 00 00 00 is 1 of the second bigint type
04 61 00 62 00 is 'AB'
08 60 73 c2 02 7a 7b cb 08 is getdate ()

For example, for such a table:

The tmp File Format formed using select * from tb is:

Data Length of the id column

Id column data

Data Length of the dtcol Column

Dtcol column data

Charcol Data Length

Charcol data

Data Length of the maxcol Column

Maxcol data

Both charcol and maxcol are converted to unicode for storage.

For viewing the tmp file, you can view it through notepad, but you can only see characters. For numbers and dates, you can view the binary data of the tmp file using UE.

3. insert million data into the table

Declare @ I int @ dt datetime

Select @ I = 0, @ dt = '2017-1-1'

While @ I <1000000

Begin

Insert into testdata (dtcol, charcol, maxcol)

Values (@ dt + @ I, replicate (char (rand () * 26 + 65), 100), replicate (newid (), 100 ))

Set @ I = @ I + 1

End

Iv. query impact of tmp files

After knowing the tmp file format, we can estimate the size of the tmp file.

The following table is used as an example. The size of a row in the tmp file is: 1 + 8 + 1 + 8 + 1 + 200 + 5 + 7200 = 7424B. The data size of 7424 W is about * WB, the size of the tmp file is about 7,250,000 KB.

1. When there is a large amount of data in the table, especially when the majority of character-type data is used, pay attention to this tmp file. If the disk space of the temp folder is not rich, tmp will occupy the remaining disk space. If it is not enough, the system will prompt that the space is insufficient and the query will be terminated.

2. the disk format of the temp folder is NTFS, because the maximum file size of the FAT32 format is 4 GB. When the size of the tmp file exceeds 4 GB, if no new tmp file is generated, the system prompts that the space is insufficient and the query is terminated. (Insufficient space may not be due to insufficient disk space, but because the tmp file has reached the maximum capacity of 4 GB)

To sum up, the temp folder should be placed on a partition with sufficient disk space and ntfs format.