multiple queries per request

Forum|Forum|12 years ago
January 17, 2014
2 replies
17 views

+2

norbert_krupa
Participating Frequently

I sometimes see requests being sent that contain multiple queries, for example:

INSERT ... ;
UPDATE ... ;
DELETE ... ;

When this request is sent to Vertica, is it optimized differently? Looking at the request in v_monitor.query_requests, it seems as though the single request is given a request type.

My questions are:
Is the entire request type determined by the first query in the request?
Is the query optimized based on the request type or individual queries within a request?
Should requests with multiple queries be avoided?

+2

colin_loghin
Participating Frequently
Forum|Forum|12 years ago
July 21, 2014

Vertica , to my knowledge ,treats each request on a client connection sequentially and does not have a readahead mechanism to allow any multiple statement optimization.

If you send them via vsql the above should have no problem logging them correctly. Complications occur when doing the same over ODBC / JDBC / ADO in places where normally a single request is expected.

In general, it is best to avoid sending multiple queries in a single request,

There were some errors generated by the database in earlier versions of 6.1 , but those have been fixed.

VER-29389 (Transactions) The database could fail when issuingmultiple SQL statements within a single command when one of thosestatements was a COMMIT. This issue has been resolved.
Was added in 6.1.3-3( see online release notes)