Interbase with views or stored procedures. 
Author Message
 Interbase with views or stored procedures.

We have a problem with Interbase 4.0 for Windows NT. There is a table
with about 500.000 records and one with about 7.000. When we create a
view wherin a calculation between fields in both tables is made. (A
very simple percentage calculation x*y/100 ) When we select from the
first table (with calculations) this goes extremely fast. From the
second even faster :-) But when the view is used (or select procedure)
it takes about halve an hour to complete. Can this be done faster?
When you need the source code, I will mail it to you..
One sollution we found is to create a static table with the values
from the view, but this will need (extremely) difficult triggers to
update the table. So we thought about creating a view, but it is sooo
slowwwwww..

Hope anyone can help us further..

Thanx in advance,
Andre Broers, Automation.




Sat, 04 Jul 1998 03:00:00 GMT  
 Interbase with views or stored procedures.

Quote:

>We have a problem with Interbase 4.0 for Windows NT. There is a table
>with about 500.000 records and one with about 7.000. When we create a
>view wherin a calculation between fields in both tables is made. (A
>very simple percentage calculation x*y/100 ) When we select from the
>first table (with calculations) this goes extremely fast. From the
>second even faster :-)

I don't really understand this. If you can compute by reading from
just one table, why try to access via a view, which is just another
select statement?

Quote:
>But when the view is used (or select procedure)
>it takes about halve an hour to complete. Can this be done faster?
>When you need the source code, I will mail it to you..
>One sollution we found is to create a static table with the values
>from the view, but this will need (extremely) difficult triggers to
>update the table. So we thought about creating a view, but it is sooo
>slowwwwww..

If you use a view to operate the join of the two tables, I'd like to
see the create statement for that view. I just created some on Oracle7
Server to improve response-time when joining tables and yes, I got a
rather dramatic increase in speed...

Jasper

"The only thing I know for sure
 is that I don't know much...  "



Tue, 07 Jul 1998 03:00:00 GMT  
 Interbase with views or stored procedures.


Quote:
>We have a problem with Interbase 4.0 for Windows NT. There is a table
>with about 500.000 records and one with about 7.000. When we create a
>view wherin a calculation between fields in both tables is made. (A
>very simple percentage calculation x*y/100 ) When we select from the
>first table (with calculations) this goes extremely fast. From the
>second even faster :-) But when the view is used (or select procedure)
>it takes about halve an hour to complete. Can this be done faster?
>When you need the source code, I will mail it to you..
>One sollution we found is to create a static table with the values
>from the view, but this will need (extremely) difficult triggers to
>update the table. So we thought about creating a view, but it is sooo
>slowwwwww..

I'm not sure but it may be that the view approach recalculates the
whole table and then the selection is applied to that whereas if
you query the tables directly the selection is made before the
calculation reducing the number of records.

--
Donald Oddy
Grove Systems Ltd.                                    0161-224 4465



Sat, 11 Jul 1998 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Interbase with views or stored procedures.

2. Live resultsets from Interbase stored procedure?

3. BLOB FROM DELPHI TO INTERBASE STORED PROCEDURES

4. Creating Interbase Stored Procedure

5. Local Interbase Server - Stored Procedures ?

6. Stored procedure question - Interbase NLM.

7. Interbase - stored procedures

8. Dynamic WHERE clause in Stored Procedures within INTERBASE ?

9. Granting Interbase privledges from within a stored procedure

10. Interbase Stored Procedures

11. Problem with Interbase Stored Procedure

12. Interbase stored procedures

 

 
Powered by phpBB® Forum Software