speed of ODBC lookups

Greetings all,

I'm running into a performance problem with one of my scripts, and I'm
wondering if anyone has any advice they'd care to share with me...

I'm accessing a visual foxpro database that is an ODBC data source using
Empressa 3.96 on an XP box running at 2Ghz with plenty of disk space.

Here's the origin of the performance problem (in pseudo code):

<MvOPEN>
<MvWHILE>
<MvOPENVIEW QUERY = "SELECT time FROM db1 WHERE value = '&[someValue]'">
<MvASSIGN NAME = "l.time" VALUE = "{ d.time }">
<MvCLOSEVIEW>

</MvWHILE>
<MvCLOSE>

Everything works fine, aside from the performance issue. If I don't do
the lookup, the script executes 4 times more quickly.

All I need to do is look up one value for each time the loop iterates,
but I can't use MvFIND since it's an ODBC database. The documentation
says that <MvQUERY> is only for use with sql queries that don't return
anything, like CREATE. Is there a better way to do this, or am I locked
into the poor performance?

Thanks,
- Roque

speed of ODBC lookups



Roque,

Not sure if the ODBC semantics support this query, but it's likely to
give you a little better performance. Your overhead is likely that you're
executing 140 queries against the database, which in ODBC can be very
expensive.

Pseudo Code follows:

SELECT id,time from db1 WHERE value IN ('val1,val2,....,'val140')
<MvWHILE NOT db1.d.eof>
do something
<MvSKIP>
</mvWHILE>

This executes 1 query and loops across the result set- rather than
executing 140 queries in a loop.

James

>-----Original Message-----
>From: [email protected] [mailto:[email protected]]On
>Behalf Of Roque Napoli
>Sent: Tuesday, June 03, 2003 2:19 PM
>To: [email protected]
>Subject: [meu] speed of ODBC lookups
>
>
> Greetings all,
>
>I'm running into a performance problem with one of my scripts, and I'm
>wondering if anyone has any advice they'd care to share with me...
>
>I'm accessing a visual foxpro database that is an ODBC data source using
>Empressa 3.96 on an XP box running at 2Ghz with plenty of disk space.
>
>Here's the origin of the performance problem (in pseudo code):
>
><MvOPEN>
><MvWHILE>
> <MvOPENVIEW QUERY = "SELECT time FROM db1 WHERE value =
>'&[someValue]'">
> <MvASSIGN NAME = "l.time" VALUE = "{ d.time }">
> <MvCLOSEVIEW>
>
></MvWHILE>
><MvCLOSE>
>
>Everything works fine, aside from the performance issue. If I don't do
>the lookup, the script executes 4 times more quickly.
>
>All I need to do is look up one value for each time the loop iterates,
>but I can't use MvFIND since it's an ODBC database. The documentation
>says that <MvQUERY> is only for use with sql queries that don't return
>anything, like CREATE. Is there a better way to do this, or am I locked
>into the poor performance?
>
>Thanks,
> - Roque
>
>

speed of ODBC lookups



Hi roque,

The mistake in your script is that you don't specify a name for your view.
Once you have that one, you can loop through the view just as if it's a
regular dbIII table. Here are some excerpts from a script of mine:

<Miva MvOPENVIEW_ERROR = "nodisplay,nonfatal">
<MvOPENVIEW NAME="{ g.connection_table }" VIEW="metadata" QUERY="{
l.query }">
<MvIF EXPR = "{ MvOPENVIEW_ERROR }">
<MvFUNCRETURN VALUE="{ MvOPENVIEW_ERROR$':'$l.query }">
<MvELSE>
<MvWHILE EXPR="{ not metadata.d.eof}">
...... get results here....... i.e.
<MvASSIGN NAME = "g.sql_metadata_array"
MEMBER="{ l.table }"
INDEX="{ l.counter }" VALUE = "{ metadata.d.columnname }">
<MvSKIP NAME="{ g.connection_table }" ROWS="1" VIEW="metadata">
</MvWHILE>
</MvIF>

In your script, you essentially perform the same query 140 times. Outch.

Markus





-----Original Message-----
From: [email protected] [mailto:[email protected]]On
Behalf Of Roque Napoli
Sent: Tuesday, June 03, 2003 2:19 PM
To: [email protected]
Subject: [meu] speed of ODBC lookups


Greetings all,

I'm running into a performance problem with one of my scripts, and I'm
wondering if anyone has any advice they'd care to share with me...

I'm accessing a visual foxpro database that is an ODBC data source using
Empressa 3.96 on an XP box running at 2Ghz with plenty of disk space.

Here's the origin of the performance problem (in pseudo code):

<MvOPEN>
<MvWHILE>
<MvOPENVIEW QUERY = "SELECT time FROM db1 WHERE value = '&[someValue]'">
<MvASSIGN NAME = "l.time" VALUE = "{ d.time }">
<MvCLOSEVIEW>

</MvWHILE>
<MvCLOSE>

Everything works fine, aside from the performance issue. If I don't do
the lookup, the script executes 4 times more quickly.

All I need to do is look up one value for each time the loop iterates,
but I can't use MvFIND since it's an ODBC database. The documentation
says that <MvQUERY> is only for use with sql queries that don't return
anything, like CREATE. Is there a better way to do this, or am I locked
into the poor performance?

Thanks,
- Roque

speed of ODBC lookups



You had the right idea, James, so here's what I'm doing now:

SELECT * FROM db1 WHERE caseno IN (&[caseList]) ORDER BY caseno

The value for "caseList" is similar to:

'03-00001','03-00002','03-00003','03-00004',.....

The number of cases in the list changes every day, and can be quite
large. This example is only 140 cases. Unfortunately, I get an error
"SQL expression is too complex" with that many cases. I guess I'll have
to experiment with breaking my list up into subsets and using a few
"OR"'s in my query, unless you have another idea. (In case you couldn't
tell, my sql is a bit rusty these days.)


Thanks, James & Markus for your excellent suggestions - I think I'm on
the right track now, and just need to get the train rolling.

- Roque


James Harrell wrote:

>Roque,
>
>Not sure if the ODBC semantics support this query, but it's likely to
>give you a little better performance. Your overhead is likely that you're
>executing 140 queries against the database, which in ODBC can be very
>expensive.
>
>Pseudo Code follows:
>
>SELECT id,time from db1 WHERE value IN ('val1,val2,....,'val140')
><MvWHILE NOT db1.d.eof>
> do something
> <MvSKIP>
></mvWHILE>
>
>This executes 1 query and loops across the result set- rather than
>executing 140 queries in a loop.
>
>James
>
>
>
>>-----Original Message-----
>>From: [email protected] [mailto:[email protected]]On
>>Behalf Of Roque Napoli
>>Sent: Tuesday, June 03, 2003 2:19 PM
>>To: [email protected]
>>Subject: [meu] speed of ODBC lookups
>>
>>
>> Greetings all,
>>
>>I'm running into a performance problem with one of my scripts, and I'm
>>wondering if anyone has any advice they'd care to share with me...
>>
>>I'm accessing a visual foxpro database that is an ODBC data source using
>>Empressa 3.96 on an XP box running at 2Ghz with plenty of disk space.
>>
>>Here's the origin of the performance problem (in pseudo code):
>>
>><MvOPEN>
>><MvWHILE>
>> <MvOPENVIEW QUERY = "SELECT time FROM db1 WHERE value =
>>'&[someValue]'">
>> <MvASSIGN NAME = "l.time" VALUE = "{ d.time }">
>> <MvCLOSEVIEW>
>>
>></MvWHILE>
>><MvCLOSE>
>>
>>Everything works fine, aside from the performance issue. If I don't do
>>the lookup, the script executes 4 times more quickly.
>>
>>All I need to do is look up one value for each time the loop iterates,
>>but I can't use MvFIND since it's an ODBC database. The documentation
>>says that <MvQUERY> is only for use with sql queries that don't return
>>anything, like CREATE. Is there a better way to do this, or am I locked
>>into the poor performance?
>>
>>Thanks,
>> - Roque
>>
>>