Query taking too long - Hangs VB app

[Nunina]

Hi!!!

I have this query:

SELECT DISTINCT a.num_cell as phone,
a.cod_client as codclient,
a.cod_acct as acct,
a.num_contract as ncontract,
b.nam_client||' '||b.nom_lastname AS CName,
DECODE (a.tip_plantarif, 'I', 'Individual', 'C', 'Corp', 'H', 'Holding') AS CType,
a.cod_situation as csituation,
a.cod_vendor as nvendor,
a.date1 as date1,
a.date2 as date2 ,
a.date3 as date3,
a.date4 as date4,
a.esn as esn,
a.num_seriehex as hex

FROM GA_ABOCEL a,
GE_CLIENTS b

WHERE b.cod_client = a.cod_client

The problem I'm having is that it returns aprox 300,000 records (which I need to show the user), and it's taking between 35 seconds and 2 minutes to return the rows.

But this only happens when I incorporate the query into my VB app. If I test it anywhere else, it takes about 15 seconds to return the rows. But in VB it hangs the application.

Is there a way to optimize this query, so that it runs just as fast in VB?

Thanks in advance.


Nunina

P. S. - By the way, the primary key is a.cod_client. Thanks!

 

[akshay127]

Nunina,

It seems that VB is having trouble with the response volume. Assuming that your app is on a different box than Oracle, monitor its memory (physical and virtual) usage, to see how it handles the result sets.

A very different approach would be to create a stored package, and activate it first to start accumulating results. Then repeatedly get multiple sets of data from it, but keep the sizes small enough for your VB box.

- Akshay

 

[kevineva]

How fast do your users read? I'm pretty good at it, but 300,000 rows is a LOT of data. (300,000 rows x 5 secs. per row = 17 days 24/7)

Do your users really need to see more than a tiny, tiny subset of that information? Like one?

I think that's what you should focus on. Writing 300,000 rows to a Windows control (grid, listbox, listview, whatever) is going to take a long time; be very hard to manipulate; permit a dangerous update scenario [update 300,000 rows in a single transaction??? how much rollback will that take?]

 

[Nunina]

Hi guys!

Thanks for all your advice. What I did now was to ask the user to enter a year (2003 for example) and sort all the records that were entered during this year. This considerably lowered the retrieving speed to 3 secs (there are aprox. 40,000 records for this year).

But... the 3 secs I mention, is the time it takes if I run it in SQL Navigator or TOAD. But once I incorporate the code into VB, it takes about 20 secs to show them. And I am not showing them in a grid or anything! If I was, uff! it would take a whole lot longer. I'm filling txt boxes with the info retrieved...

So, now you see. I would really appreciate any suggestions you might have. Thanks in advance.

Nunina