A DBA’s definition of “batch”

sqliteterminology

I've heard it being used in relation to SQLite sometimes but never completely understood what it means.

Best Answer

A batch is a series of SQL commands that are sent to the database server at once. This can be faster than sending each command individually because there is less network communication.

SQLite is an embedded database; SQL commands are executed directly by the SQLite library. There is no separate server, so it would not make sense to have batches. SQLite does not have such batches.

Another meaning of "batch" might be the multi-row form of the INSERT command:

INSERT INTO MyTable(ID, Name) VALUES (1, 'this'), (2, 'that'), (3, 'whatever');

Related Solutions

How to force SQLite to materialize a subquery

From the SQLite docs (my emphasis):

Subqueries might also need to be materialized when they appear in the FROM clause of a SELECT statement. For example:
SELECT * FROM ex1 JOIN (SELECT b FROM ex2) AS t ON t.b=ex1.a;
Depending on the query, SQLite might need to materialize the (SELECT b FROM ex2) subquery into a temporary table, then perform the join between ex1 and the temporary table. The query optimizer tries to avoid this by "flattening" the query. In the previous example the query can be flattened, and SQLite will automatically transform the query into
SELECT ex1.*, ex2.b FROM ex1 JOIN ex2 ON ex2.b=ex1.a;
More complex queries may or may not be able to employ query flattening to avoid the temporary table. Whether or not the query can be flattened depends on such factors as whether or not the subquery or outer query contain aggregate functions, ORDER BY or GROUP BY clauses, LIMIT clauses, and so forth. The rules for when a query and cannot be flattened are very complex and are beyond the scope of this document.

You may be able to work around the problem with a view, but it seems that in this case at least, adding a dummy union is enough to prevent "flattening":

select op, op
from (select abs(random())%10 as op from (select 1) union all select 1 where 1=2);

6|6

Sql-server – a “heterogeneous query”

Does "Heterogeneous" just refer to querying two different database engines in this context?

Basically, yes. A "heterogeneous" query is executed on a "heterogeneous linked server". All queries to this linked server will be heterogeneous.

And "heterogenous" is a subset of distributed queries

Distributed data stored in multiple instances of SQL Server.

Heterogeneous data stored in various relational and nonrelational data sources accessed by using an OLE DB provider.

But "heterogeneous" pops up every now and then:

See linking servers on MSDN

The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

sp_addlinkedserver

A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources.

And an MSDN example of querying Active Directory in "Joining Heterogeneous Data"

And MSDN again "Heterogeneous Database Replication"

Best Answer

Related Solutions

How to force SQLite to materialize a subquery

Sql-server – a “heterogeneous query”

Related Question