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_e xec($conn2, $sql);

while(odbc_fetc h_row($result2) )
{
// test for this is Oracle DB (here might be multiple rows too)
$sql2= select....
$result=odbc_ex ec($conn, $sql2);

// if row exist
while(odbc_fetc h_row($result))
//if(odbc_num_row s($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_res ult($result,1)) )
$k--;
if($k==-1)
{
$part1[]=trim(odbc_resu lt($result2,1)) ;
$part2[]=trim(odbc_resu lt($result2,2)) ;
$part3[]=trim(odbc_resu lt($result2,3)) ;
$part4[]

ASO....

[Pedro Graca]

Re: speedy code? (with 2 databases)

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_e xec($conn2, $sql);
>
while(odbc_fetc h_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_ex ec($conn, $sql2);
#>
# // if row exist
# while(odbc_fetc h_row($result))
# //if(odbc_num_row s($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_res ult($result,1)) )
# $k--;
# if($k==-1)
# {

$part1[]=trim(odbc_resu lt($result2,1)) ;
$part2[]=trim(odbc_resu lt($result2,2)) ;
$part3[]=trim(odbc_resu lt($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]

Re: speedy code? (with 2 databases)


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_e xec($conn2, $sql);

while(odbc_fetc h_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