multiple queries per request

norbert_krupa

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?

colin_loghin

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) 

FiliN

Hi Norbert.

Two days ago I've got the same issue.

My ETL tool put three queries in one query request. And it was wierd for me that only 1st query actualy ran.

 

So I made an experiment. I repeated the same method in ETL tool. Take a look at the attached picture

 

In target table I've got three rows with 1,2,3 values.