Posted by Pedro Graca on 10/12/06 16:17

Sonnich wrote:
> The slow thing is that for each row in DB1, I run a query in DB2.
>
> The code is basically this - there is some more, but

Try this:

> // get data from MS SQL
> $result2=odbc_exec($conn2, $sql);
>
> while(odbc_fetch_row($result2) )
> {
$MSSQLarr[] = odbc_result();
}

// Oracle SQL
$ORACLEsql = "select ... where RECORDID in (" . implode(',', $MSSQLarr) . ") ...";

#> // test for this is Oracle DB (here might be multiple rows too)
#> $sql2= select....
#> $result=odbc_exec($conn, $sql2);
#>
#> // if row exist
#> while(odbc_fetch_row($result))
#> //if(odbc_num_rows($result)>1)
#> {
#> // add to array - for next step to search - do not add twice
#> $k=count($part1)-1;
#> while(($k>-1) && ($part1[$k]!=odbc_result($result2,1)) &&
#> ($part2[$k]!=odbc_result($result2,2)) &&
#> $part8[$k]!=trim(odbc_result($result,1)))
#> $k--;
#> if($k==-1)
#> {
> $part1[]=trim(odbc_result($result2,1));
> $part2[]=trim(odbc_result($result2,2));
> $part3[]=trim(odbc_result($result2,3));
> $part4[]

So, if MSSQL returns 42 rows, instead of 43 queries (1 on MSSQL + 42 on
Oracle) you do 2 queries only.

--
File not found: (R)esume, (R)etry, (R)erun, (R)eturn, (R)eboot

[Back to original message]