speedy code? (with 2 databases)

[Sonnich]

Hi all!

I cannot share the code, but maybe someone can gibve me an idea...
I have to lookup something in a database (updates from specific time,
MS SQL Server), and check them in another database (Oracle). Both might
contain multiple rows, which makes this big...
The point is, that the code below runs at app 10 rows/second, which is
slow... any ideas how I can do it better?

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

// get data from MS SQL
$result2=odbc_exec($conn2, $sql);

while(odbc_fetch_row($result2) )
{
// 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[]

ASO....

[Pedro Graca]

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

[Sonnich]

Pedro Graca wrote:

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) . ") ...";

snip

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

I like the idea, but if:

$sql = ..... where code = '1234' and version = '03';

The in would return a lot more than I want :-)

Br
S