Query length

[IvorW]

Hemant Verma wrote:
> Hi,
>
> Please let me know if there is any particular length of query perl
> supports.
>   
No limit in what perl supports. There may be limits as to what the
database can handle.

What is your database platform? (this isn't clear from the code you
presented)
> E.g. I have to execute following 10 queries to do my task or to execute
> main single query.
>
> @insert_string = ();
>         $insert_string[1]   =   "insert into SID_FIELD_PERIODS (SURVEYID,
> FIELD_PERIOD, OUTPUT_CODE, TLA, SURVEY_YEAR_CODE, CALENDAR_YEAR_CODE)
> values ($surveyid, $field_period, $output_code, \'$survey_tla\',
> \'$survey_year_code\', \'$calendar_year_code\')";
>         $insert_string[2]   =   "update SID_FIELD_PERIODS set
> STAGE_CODE=\'$stage_code\', SURV_SAMP=$surv_samp, USED=$used,
> QUOTAS=$quotas, ADDS=$adds, FIELD_STAR=\'$start_date\',
> FIELD_FIN=\'$end_date\' where FIELD_PERIOD=$field_period";
>         $insert_string[3]   =   "update SID_FIELD_PERIODS set
> ADVANCE_LETTERS=$advance_letters, POST=$post, RELEASE_DATE=$release_date,
> READY_TO_GO=$ready_to_go, BLAISE_NAME=$blaise_name where
> FIELD_PERIOD=$field_period";
>         $insert_string[4]   =   "update SID_FIELD_PERIODS set
> BLAISE_VERSION=$blaise_version, COMPLETED=$completed,
> SAMPLING_FRAME=$sampling_frame, SF_DATE=$sf_date, SAMPLE=$sample where
> FIELD_PERIOD=$field_period";
>         $insert_string[5]   =   "update SID_FIELD_PERIODS set
> SAMPLE_DATE=$sample_date, ADDRESS_LIST=$address_list,
> ADDRESS_LIST_DATE=$address_list_date, SAT_READY=$sat_ready where
> FIELD_PERIOD=$field_period";
>         $insert_string[6]   =   "update SID_FIELD_PERIODS set
> SAT_DATE=$sat_date, RESPONSE=$response, SADJ_SAMP=$sadj_samp,
> SEFF_SAMP=$seff_samp, SCO_OP=$sco_op, SRESPONSE=$sresponse where
> FIELD_PERIOD=$field_period";
>         $insert_string[7]   =   "update SID_FIELD_PERIODS set
> FADJ_SAMP=$fadj_samp, FEFF_SAMP=$feff_samp, FCO_OP=$fco_op,
> FRESPONSE=$fresponse, FIELD_END=$field_end, STAGE_NAME=$stage_name where
> FIELD_PERIOD=$field_period";
>         $insert_string[8]   =   "update SID_FIELD_PERIODS set
> REISSUES=$reissues, REISSUE_FF=$reissue_ff, REISSUE_STAR=$reissue_star,
> REISSUE_FIN=$reissue_fin, ORIGSTATEATTEMPT=$origstateattempt,
> RESISSTAGEATTEMPT=$resisstageattempt, ACTIVEFP=$activefp where
> FIELD_PERIOD=$field_period";
>
> Then I execute query using a loop.
>
> for ($x = 1; $x <= $num_inserts; $x++)
>             {
>                 print "insert_string_$x = $insert_string[$x] \n" if $debug;
>                 $db->sql($insert_string[$x]);
>                 $db->sql("commit");
>             }
>
> Is there any way I can do it at one go instead of making so many small
> small queries.
>   
Is there any reason why you are doing a commit inside the loop? If you
do the commit afterwards, this will improve performance, using more
transaction log resources.