Using extern is only of relevance when the program you're building consists of multiple source files linked together, where some of the variables defined, for example, in source file file1.c need to be referenced in other source files, such as file2.c.

It is important to understand the difference between defining a variable and declaring a variable:

• A variable is declared when the compiler is informed that a variable exists (and this is its type); it does not allocate the storage for the variable at that point.

• A variable is defined when the compiler allocates the storage for the variable.

You may declare a variable multiple times (though once is sufficient); you may only define it once within a given scope. A variable definition is also a declaration, but not all variable declarations are definitions.

Best way to declare and define global variables

The clean, reliable way to declare and define global variables is to use a header file to contain an extern declaration of the variable.

The header is included by the one source file that defines the variable and by all the source files that reference the variable. For each program, one source file (and only one source file) defines the variable. Similarly, one header file (and only one header file) should declare the variable. The header file is crucial; it enables cross-checking between independent TUs (translation units — think source files) and ensures consistency.

Although there are other ways of doing it, this method is simple and reliable. It is demonstrated by file3.h, file1.c and file2.c:

file3.h

extern int global_variable;  /* Declaration of the variable */

file1.c

#include "file3.h"  /* Declaration made available here */
#include "prog1.h"  /* Function declarations */

/* Variable defined here */
int global_variable = 37;    /* Definition checked against declaration */

int increment(void) { return global_variable++; }

file2.c

#include "file3.h"
#include "prog1.h"
#include <stdio.h>

void use_it(void)
{
    printf("Global variable: %d\n", global_variable++);
}

That's the best way to declare and define global variables.

---

The next two files complete the source for prog1:

The complete programs shown use functions, so function declarations have crept in. Both C99 and C11 require functions to be declared or defined before they are used (whereas C90 did not, for good reasons). I use the keyword extern in front of function declarations in headers for consistency — to match the extern in front of variable declarations in headers. Many people prefer not to use extern in front of function declarations; the compiler doesn't care — and ultimately, neither do I as long as you're consistent, at least within a source file.

prog1.h

extern void use_it(void);
extern int increment(void);

prog1.c

#include "file3.h"
#include "prog1.h"
#include <stdio.h>

int main(void)
{
    use_it();
    global_variable += 19;
    use_it();
    printf("Increment: %d\n", increment());
    return 0;
}

• prog1 uses prog1.c, file1.c, file2.c, file3.h and prog1.h.

The file prog1.mk is a makefile for prog1 only. It will work with most versions of make produced since about the turn of the millennium. It is not tied specifically to GNU Make.

prog1.mk

# Minimal makefile for prog1

PROGRAM = prog1
FILES.c = prog1.c file1.c file2.c
FILES.h = prog1.h file3.h
FILES.o = ${FILES.c:.c=.o}

CC      = gcc
SFLAGS  = -std=c11
GFLAGS  = -g
OFLAGS  = -O3
WFLAG1  = -Wall
WFLAG2  = -Wextra
WFLAG3  = -Werror
WFLAG4  = -Wstrict-prototypes
WFLAG5  = -Wmissing-prototypes
WFLAGS  = ${WFLAG1} ${WFLAG2} ${WFLAG3} ${WFLAG4} ${WFLAG5}
UFLAGS  = # Set on command line only

CFLAGS  = ${SFLAGS} ${GFLAGS} ${OFLAGS} ${WFLAGS} ${UFLAGS}
LDFLAGS =
LDLIBS  =

all:    ${PROGRAM}

${PROGRAM}: ${FILES.o}
    ${CC} -o $@ ${CFLAGS} ${FILES.o} ${LDFLAGS} ${LDLIBS}

prog1.o: ${FILES.h}
file1.o: ${FILES.h}
file2.o: ${FILES.h}

# If it exists, prog1.dSYM is a directory on macOS
DEBRIS = a.out core *~ *.dSYM
RM_FR  = rm -fr

clean:
    ${RM_FR} ${FILES.o} ${PROGRAM} ${DEBRIS}

---

Guidelines

Rules to be broken by experts only, and only with good reason:

• A header file only contains extern declarations of variables — never static or unqualified variable definitions.

• For any given variable, only one header file declares it (SPOT — Single Point of Truth).

• A source file never contains extern declarations of variables — source files always include the (sole) header that declares them.

• For any given variable, exactly one source file defines the variable, preferably initializing it too. (Although there is no need to initialize explicitly to zero, it does no harm and can do some good, because there can be only one initialized definition of a particular global variable in a program).

• The source file that defines the variable also includes the header to ensure that the definition and the declaration are consistent.

• A function should never need to declare a variable using extern.

• Avoid global variables whenever possible — use functions instead.

^{The source code and text of this answer are available in my SOQ (Stack Overflow Questions) repository on GitHub in the src/so-0143-3204 sub-directory.}

^{If you're not an experienced C programmer, you could (and perhaps should) stop reading here.}

Not so good way to define global variables

With some (indeed, many) C compilers, you can get away with what's called a 'common' definition of a variable too. 'Common', here, refers to a technique used in Fortran for sharing variables between source files, using a (possibly named) COMMON block. What happens here is that each of a number of files provides a tentative definition of the variable. As long as no more than one file provides an initialized definition, then the various files end up sharing a common single definition of the variable:

file10.c

#include "prog2.h"

long l;   /* Do not do this in portable code */

void inc(void) { l++; }

file11.c

#include "prog2.h"

long l;   /* Do not do this in portable code */

void dec(void) { l--; }

file12.c

#include "prog2.h"
#include <stdio.h>

long l = 9;   /* Do not do this in portable code */

void put(void) { printf("l = %ld\n", l); }

This technique does not conform to the letter of the C standard and the 'one definition rule' — it is officially undefined behaviour:

J.2 Undefined behavior

An identifier with external linkage is used, but in the program there does not exist exactly one external definition for the identifier, or the identifier is not used and there exist multiple external definitions for the identifier (6.9).

§6.9 External definitions ¶5

An external definition is an external declaration that is also a definition of a function (other than an inline definition) or an object. If an identifier declared with external linkage is used in an expression (other than as part of the operand of a sizeof or _Alignof operator whose result is an integer constant), somewhere in the entire program there shall be exactly one external definition for the identifier; otherwise, there shall be no more than one.¹⁶¹⁾

¹⁶¹⁾ Thus, if an identifier declared with external linkage is not used in an expression, there need be no external definition for it.

However, the C standard also lists it in informative Annex J as one of the Common extensions.

J.5.11 Multiple external definitions

There may be more than one external definition for the identifier of an object, with or without the explicit use of the keyword extern; if the definitions disagree, or more than one is initialized, the behavior is undefined (6.9.2).

Because this technique is not always supported, it is best to avoid using it, especially if your code needs to be portable. Using this technique, you can also end up with unintentional type punning.

If one of the files above declared l as a double instead of as a long, C's type-unsafe linkers probably would not spot the mismatch. If you're on a machine with 64-bit long and double, you'd not even get a warning; on a machine with 32-bit long and 64-bit double, you'd probably get a warning about the different sizes — the linker would use the largest size, exactly as a Fortran program would take the largest size of any common blocks.

Note that GCC 10.1.0, which was released on 2020-05-07, changes the default compilation options to use -fno-common, which means that by default, the code above no longer links unless you override the default with -fcommon (or use attributes, etc — see the link).

---

The next two files complete the source for prog2:

prog2.h

extern void dec(void);
extern void put(void);
extern void inc(void);

prog2.c

#include "prog2.h"
#include <stdio.h>

int main(void)
{
    inc();
    put();
    dec();
    put();
    dec();
    put();
}

• prog2 uses prog2.c, file10.c, file11.c, file12.c, prog2.h.

---

Warning

As noted in comments here, and as stated in my answer to a similar question, using multiple definitions for a global variable leads to undefined behaviour (J.2; §6.9), which is the standard's way of saying "anything could happen". One of the things that can happen is that the program behaves as you expect; and J.5.11 says, approximately, "you might be lucky more often than you deserve". But a program that relies on multiple definitions of an extern variable — with or without the explicit 'extern' keyword — is not a strictly conforming program and not guaranteed to work everywhere. Equivalently: it contains a bug which may or may not show itself.

Violating the guidelines

There are, of course, many ways in which these guidelines can be broken. Occasionally, there may be a good reason to break the guidelines, but such occasions are extremely unusual.

faulty_header.h

int some_var;    /* Do not do this in a header!!! */

Note 1: if the header defines the variable without the extern keyword, then each file that includes the header creates a tentative definition of the variable. As noted previously, this will often work, but the C standard does not guarantee that it will work.

broken_header.h

int some_var = 13;    /* Only one source file in a program can use this */

Note 2: if the header defines and initializes the variable, then only one source file in a given program can use the header. Since headers are primarily for sharing information, it is a bit silly to create one that can only be used once.

seldom_correct.h

static int hidden_global = 3;   /* Each source file gets its own copy  */

Note 3: if the header defines a static variable (with or without initialization), then each source file ends up with its own private version of the 'global' variable.

If the variable is actually a complex array, for example, this can lead to extreme duplication of code. It can, very occasionally, be a sensible way to achieve some effect, but that is very unusual.

---

Summary

Use the header technique I showed first. It works reliably and everywhere. Note, in particular, that the header declaring the global_variable is included in every file that uses it — including the one that defines it. This ensures that everything is self-consistent.

Similar concerns arise with declaring and defining functions — analogous rules apply. But the question was about variables specifically, so I've kept the answer to variables only.

End of Original Answer

^{If you're not an experienced C programmer, you probably should stop reading here.}

---

Late Major Addition

Avoiding Code Duplication

One concern that is sometimes (and legitimately) raised about the 'declarations in headers, definitions in source' mechanism described here is that there are two files to be kept synchronized — the header and the source. This is usually followed up with an observation that a macro can be used so that the header serves double duty — normally declaring the variables, but when a specific macro is set before the header is included, it defines the variables instead.

Another concern can be that the variables need to be defined in each of a number of 'main programs'. This is normally a spurious concern; you can simply introduce a C source file to define the variables and link the object file produced with each of the programs.

A typical scheme works like this, using the original global variable illustrated in file3.h:

file3a.h

#ifdef DEFINE_VARIABLES
#define EXTERN /* nothing */
#else
#define EXTERN extern
#endif /* DEFINE_VARIABLES */

EXTERN int global_variable;

file1a.c

#define DEFINE_VARIABLES
#include "file3a.h"  /* Variable defined - but not initialized */
#include "prog3.h"

int increment(void) { return global_variable++; }

file2a.c

#include "file3a.h"
#include "prog3.h"
#include <stdio.h>

void use_it(void)
{
    printf("Global variable: %d\n", global_variable++);
}

---

The next two files complete the source for prog3:

prog3.h

extern void use_it(void);
extern int increment(void);

prog3.c

#include "file3a.h"
#include "prog3.h"
#include <stdio.h>

int main(void)
{
    use_it();
    global_variable += 19;
    use_it();
    printf("Increment: %d\n", increment());
    return 0;
}

• prog3 uses prog3.c, file1a.c, file2a.c, file3a.h, prog3.h.

---

Variable initialization

The problem with this scheme as shown is that it does not provide for initialization of the global variable. With C99 or C11 and variable argument lists for macros, you could define a macro to support initialization too. (With C89 and no support for variable argument lists in macros, there is no easy way to handle arbitrarily long initializers.)

file3b.h

#ifdef DEFINE_VARIABLES
#define EXTERN                  /* nothing */
#define INITIALIZER(...)        = __VA_ARGS__
#else
#define EXTERN                  extern
#define INITIALIZER(...)        /* nothing */
#endif /* DEFINE_VARIABLES */

EXTERN int global_variable INITIALIZER(37);
EXTERN struct { int a; int b; } oddball_struct INITIALIZER({ 41, 43 });

^{Reverse contents of #if and #else blocks, fixing bug identified by Denis Kniazhev}

file1b.c

#define DEFINE_VARIABLES
#include "file3b.h"  /* Variables now defined and initialized */
#include "prog4.h"

int increment(void) { return global_variable++; }
int oddball_value(void) { return oddball_struct.a + oddball_struct.b; }

file2b.c

#include "file3b.h"
#include "prog4.h"
#include <stdio.h>

void use_them(void)
{
    printf("Global variable: %d\n", global_variable++);
    oddball_struct.a += global_variable;
    oddball_struct.b -= global_variable / 2;
}

Clearly, the code for the oddball structure is not what you'd normally write, but it illustrates the point. The first argument to the second invocation of INITIALIZER is { 41 and the remaining argument (singular in this example) is 43 }. Without C99 or similar support for variable argument lists for macros, initializers that need to contain commas are very problematic.

^{Correct header file3b.h included (instead of fileba.h) per Denis Kniazhev}

---

The next two files complete the source for prog4:

prog4.h

extern int increment(void);
extern int oddball_value(void);
extern void use_them(void);

prog4.c

#include "file3b.h"
#include "prog4.h"
#include <stdio.h>

int main(void)
{
    use_them();
    global_variable += 19;
    use_them();
    printf("Increment: %d\n", increment());
    printf("Oddball:   %d\n", oddball_value());
    return 0;
}

• prog4 uses prog4.c, file1b.c, file2b.c, prog4.h, file3b.h.

---

Header Guards

Any header should be protected against reinclusion, so that type definitions (enum, struct or union types, or typedefs generally) do not cause problems. The standard technique is to wrap the body of the header in a header guard such as:

#ifndef FILE3B_H_INCLUDED
#define FILE3B_H_INCLUDED

...contents of header...

#endif /* FILE3B_H_INCLUDED */

The header might be included twice indirectly. For example, if file4b.h includes file3b.h for a type definition that isn't shown, and file1b.c needs to use both header file4b.h and file3b.h, then you have some more tricky issues to resolve. Clearly, you might revise the header list to include just file4b.h. However, you might not be aware of the internal dependencies — and the code should, ideally, continue to work.

Further, it starts to get tricky because you might include file4b.h before including file3b.h to generate the definitions, but the normal header guards on file3b.h would prevent the header being reincluded.

So, you need to include the body of file3b.h at most once for declarations, and at most once for definitions, but you might need both in a single translation unit (TU — a combination of a source file and the headers it uses).

Multiple inclusion with variable definitions

However, it can be done subject to a not too unreasonable constraint. Let's introduce a new set of file names:

• external.h for the EXTERN macro definitions, etc.

• file1c.h to define types (notably, struct oddball, the type of oddball_struct).

• file2c.h to define or declare the global variables.

• file3c.c which defines the global variables.

• file4c.c which simply uses the global variables.

• file5c.c which shows that you can declare and then define the global variables.

• file6c.c which shows that you can define and then (attempt to) declare the global variables.

In these examples, file5c.c and file6c.c directly include the header file2c.h several times, but that is the simplest way to show that the mechanism works. It means that if the header was indirectly included twice, it would also be safe.

The restrictions for this to work are:

1. The header defining or declaring the global variables may not itself define any types.

2. Immediately before you include a header that should define variables, you define the macro DEFINE_VARIABLES.

3. The header defining or declaring the variables has stylized contents.

external.h

/*
** This header must not contain header guards (like <assert.h> must not).
** Each time it is invoked, it redefines the macros EXTERN, INITIALIZE
** based on whether macro DEFINE_VARIABLES is currently defined.
*/
#undef EXTERN
#undef INITIALIZE

#ifdef DEFINE_VARIABLES
#define EXTERN              /* nothing */
#define INITIALIZE(...)     = __VA_ARGS__
#else
#define EXTERN              extern
#define INITIALIZE(...)     /* nothing */
#endif /* DEFINE_VARIABLES */

file1c.h

#ifndef FILE1C_H_INCLUDED
#define FILE1C_H_INCLUDED

struct oddball
{
    int a;
    int b;
};

extern void use_them(void);
extern int increment(void);
extern int oddball_value(void);

#endif /* FILE1C_H_INCLUDED */

file2c.h

/* Standard prologue */
#if defined(DEFINE_VARIABLES) && !defined(FILE2C_H_DEFINITIONS)
#undef FILE2C_H_INCLUDED
#endif

#ifndef FILE2C_H_INCLUDED
#define FILE2C_H_INCLUDED

#include "external.h"   /* Support macros EXTERN, INITIALIZE */
#include "file1c.h"     /* Type definition for struct oddball */

#if !defined(DEFINE_VARIABLES) || !defined(FILE2C_H_DEFINITIONS)

/* Global variable declarations / definitions */
EXTERN int global_variable INITIALIZE(37);
EXTERN struct oddball oddball_struct INITIALIZE({ 41, 43 });

#endif /* !DEFINE_VARIABLES || !FILE2C_H_DEFINITIONS */

/* Standard epilogue */
#ifdef DEFINE_VARIABLES
#define FILE2C_H_DEFINITIONS
#endif /* DEFINE_VARIABLES */

#endif /* FILE2C_H_INCLUDED */

file3c.c

#define DEFINE_VARIABLES
#include "file2c.h"  /* Variables now defined and initialized */

int increment(void) { return global_variable++; }
int oddball_value(void) { return oddball_struct.a + oddball_struct.b; }

file4c.c

#include "file2c.h"
#include <stdio.h>

void use_them(void)
{
    printf("Global variable: %d\n", global_variable++);
    oddball_struct.a += global_variable;
    oddball_struct.b -= global_variable / 2;
}

file5c.c

#include "file2c.h"     /* Declare variables */

#define DEFINE_VARIABLES
#include "file2c.h"  /* Variables now defined and initialized */

int increment(void) { return global_variable++; }
int oddball_value(void) { return oddball_struct.a + oddball_struct.b; }

file6c.c

#define DEFINE_VARIABLES
#include "file2c.h"     /* Variables now defined and initialized */

#include "file2c.h"     /* Declare variables */

int increment(void) { return global_variable++; }
int oddball_value(void) { return oddball_struct.a + oddball_struct.b; }

---

The next source file completes the source (provides a main program) for prog5, prog6 and prog7:

prog5.c

#include "file2c.h"
#include <stdio.h>

int main(void)
{
    use_them();
    global_variable += 19;
    use_them();
    printf("Increment: %d\n", increment());
    printf("Oddball:   %d\n", oddball_value());
    return 0;
}

• prog5 uses prog5.c, file3c.c, file4c.c, file1c.h, file2c.h, external.h.

• prog6 uses prog5.c, file5c.c, file4c.c, file1c.h, file2c.h, external.h.

• prog7 uses prog5.c, file6c.c, file4c.c, file1c.h, file2c.h, external.h.

---

This scheme avoids most problems. You only run into a problem if a header that defines variables (such as file2c.h) is included by another header (say file7c.h) that defines variables. There isn't an easy way around that other than "don't do it".

You can partially work around the problem by revising file2c.h into file2d.h:

file2d.h

/* Standard prologue */
#if defined(DEFINE_VARIABLES) && !defined(FILE2D_H_DEFINITIONS)
#undef FILE2D_H_INCLUDED
#endif

#ifndef FILE2D_H_INCLUDED
#define FILE2D_H_INCLUDED

#include "external.h"   /* Support macros EXTERN, INITIALIZE */
#include "file1c.h"     /* Type definition for struct oddball */

#if !defined(DEFINE_VARIABLES) || !defined(FILE2D_H_DEFINITIONS)

/* Global variable declarations / definitions */
EXTERN int global_variable INITIALIZE(37);
EXTERN struct oddball oddball_struct INITIALIZE({ 41, 43 });

#endif /* !DEFINE_VARIABLES || !FILE2D_H_DEFINITIONS */

/* Standard epilogue */
#ifdef DEFINE_VARIABLES
#define FILE2D_H_DEFINITIONS
#undef DEFINE_VARIABLES
#endif /* DEFINE_VARIABLES */

#endif /* FILE2D_H_INCLUDED */

The issue becomes 'should the header include #undef DEFINE_VARIABLES?' If you omit that from the header and wrap any defining invocation with #define and #undef:

#define DEFINE_VARIABLES
#include "file2c.h"
#undef DEFINE_VARIABLES

in the source code (so the headers never alter the value of DEFINE_VARIABLES), then you should be clean. It is just a nuisance to have to remember to write the the extra line. An alternative might be:

#define HEADER_DEFINING_VARIABLES "file2c.h"
#include "externdef.h"

externdef.h

/*
** This header must not contain header guards (like <assert.h> must not).
** Each time it is included, the macro HEADER_DEFINING_VARIABLES should
** be defined with the name (in quotes - or possibly angle brackets) of
** the header to be included that defines variables when the macro
** DEFINE_VARIABLES is defined.  See also: external.h (which uses
** DEFINE_VARIABLES and defines macros EXTERN and INITIALIZE
** appropriately).
**
** #define HEADER_DEFINING_VARIABLES "file2c.h"
** #include "externdef.h"
*/

#if defined(HEADER_DEFINING_VARIABLES)
#define DEFINE_VARIABLES
#include HEADER_DEFINING_VARIABLES
#undef DEFINE_VARIABLES
#undef HEADER_DEFINING_VARIABLES
#endif /* HEADER_DEFINING_VARIABLES */

This is getting a tad convoluted, but seems to be secure (using the file2d.h, with no #undef DEFINE_VARIABLES in the file2d.h).

file7c.c

/* Declare variables */
#include "file2d.h"

/* Define variables */
#define HEADER_DEFINING_VARIABLES "file2d.h"
#include "externdef.h"

/* Declare variables - again */
#include "file2d.h"

/* Define variables - again */
#define HEADER_DEFINING_VARIABLES "file2d.h"
#include "externdef.h"

int increment(void) { return global_variable++; }
int oddball_value(void) { return oddball_struct.a + oddball_struct.b; }

file8c.h

/* Standard prologue */
#if defined(DEFINE_VARIABLES) && !defined(FILE8C_H_DEFINITIONS)
#undef FILE8C_H_INCLUDED
#endif

#ifndef FILE8C_H_INCLUDED
#define FILE8C_H_INCLUDED

#include "external.h"   /* Support macros EXTERN, INITIALIZE */
#include "file2d.h"     /* struct oddball */

#if !defined(DEFINE_VARIABLES) || !defined(FILE8C_H_DEFINITIONS)

/* Global variable declarations / definitions */
EXTERN struct oddball another INITIALIZE({ 14, 34 });

#endif /* !DEFINE_VARIABLES || !FILE8C_H_DEFINITIONS */

/* Standard epilogue */
#ifdef DEFINE_VARIABLES
#define FILE8C_H_DEFINITIONS
#endif /* DEFINE_VARIABLES */

#endif /* FILE8C_H_INCLUDED */

file8c.c

/* Define variables */
#define HEADER_DEFINING_VARIABLES "file2d.h"
#include "externdef.h"

/* Define variables */
#define HEADER_DEFINING_VARIABLES "file8c.h"
#include "externdef.h"

int increment(void) { return global_variable++; }
int oddball_value(void) { return oddball_struct.a + oddball_struct.b; }

---

The next two files complete the source for prog8 and prog9:

prog8.c

#include "file2d.h"
#include <stdio.h>

int main(void)
{
    use_them();
    global_variable += 19;
    use_them();
    printf("Increment: %d\n", increment());
    printf("Oddball:   %d\n", oddball_value());
    return 0;
}

file9c.c

#include "file2d.h"
#include <stdio.h>

void use_them(void)
{
    printf("Global variable: %d\n", global_variable++);
    oddball_struct.a += global_variable;
    oddball_struct.b -= global_variable / 2;
}

• prog8 uses prog8.c, file7c.c, file9c.c.

• prog9 uses prog8.c, file8c.c, file9c.c.

---

However, the problems are relatively unlikely to occur in practice, especially if you take the standard advice to

Avoid global variables

---

Does this exposition miss anything?

^{_Confession_: The 'avoiding duplicated code' scheme outlined here was developed because the issue affects some code I work on (but don't own), and is a niggling concern with the scheme outlined in the first part of the answer. However, the original scheme leaves you with just two places to modify to keep variable definitions and declarations synchronized, which is a big step forward over having exernal variable declarations scattered throughout the code base (which really matters when there are thousands of files in total). However, the code in the files with the names `fileNc.[ch]` (plus `external.h` and `externdef.h`) shows that it can be made to work. Clearly, it would not be hard to create a header generator script to give you the standardized template for a variable defining and declaring header file.}

NB These are toy programs with just barely enough code to make them marginally interesting. There is repetition within the examples that could be removed, but isn't to simplify the pedagogical explanation. (For example: the difference between prog5.c and prog8.c is the name of one of the headers that are included. It would be possible to reorganize the code so that the main() function was not repeated, but it would conceal more than it revealed.)

Several tips:

1. Put inserts/updates in a transaction.
2. For older versions of SQLite - Consider a less paranoid journal mode (pragma journal_mode). There is NORMAL, and then there is OFF, which can significantly increase insert speed if you're not too worried about the database possibly getting corrupted if the OS crashes. If your application crashes the data should be fine. Note that in newer versions, the OFF/MEMORY settings are not safe for application level crashes.
3. Playing with page sizes makes a difference as well (PRAGMA page_size). Having larger page sizes can make reads and writes go a bit faster as larger pages are held in memory. Note that more memory will be used for your database.
4. If you have indices, consider calling CREATE INDEX after doing all your inserts. This is significantly faster than creating the index and then doing your inserts.
5. You have to be quite careful if you have concurrent access to SQLite, as the whole database is locked when writes are done, and although multiple readers are possible, writes will be locked out. This has been improved somewhat with the addition of a WAL in newer SQLite versions.
6. Take advantage of saving space...smaller databases go faster. For instance, if you have key value pairs, try making the key an INTEGER PRIMARY KEY if possible, which will replace the implied unique row number column in the table.
7. If you are using multiple threads, you can try using the shared page cache, which will allow loaded pages to be shared between threads, which can avoid expensive I/O calls.
8. Don't use !feof(file)!

I've also asked similar questions here and here.