zbrskz
asked on
TQuery.UniDirectional := FALSE doesn't seem to work
I'm opening a cursor than fetches more than 100,000 records. I notice that a lot of memory is being allocated during the fetching process and then freed on the close method. I think that setting the UniDirectional property to FALSE should solve this, I am sure it is not working because I can call both next and prior methods. What's wrong here???. Currently using Delphi 3.0, Firebird 1.1 and BDE 6.
Thanks,
Luis Fernandes
Thanks,
Luis Fernandes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi kacor,
Yes, I'll try to upgrade to FB 2.0. But I'm convinced the problem is at the BDE level and not the database server.
I set TQuery.UniDirectional to TRUE (not FALSE, sorry guys...) before the open statement. It is a multiple join, multiple field conditional query. The TQuery.Open statement doesn't actually allocate much memory. I then allocate around 170Mb of data structure to accommodate all the fetch data. My problem is the other around 180Mb of data that are allocated by my application during the 116,000 calls to the TQuery.Next method!!!. I guess this is due to the bidirectional features of the cursor that needs to cache all the data. It doesn't really matters if I set the UniDirectional property to either TRUE or FALSE, on both cases the extra 180Mb of memory are allocated by my application to be later freed when I call the TQuery.Close method.
Limbeck,
- Yes, I'm setting it to TRUE, not FALSE, sorry for that ...
- Yes, my recordset is around 116,000, and I'm fetching each and every one of them ...
- Yes, I'm only selecting the fields I need ...
- I really need to load all this data to a single component view for data management proposes ...
I can not believe there is not a way of avoiding my application to allocate all this amount of memory during the fetching process (starts with 14Mb before Open, moves all the way to 364Mb, to drop down to 184Mb after the Close method, the 184-14=170Mb are fine - actually allocated by me)... I only need to fetch a record once, no bidirectional functionality needed, it is rather stupid actually ..
Yes, I'll try to upgrade to FB 2.0. But I'm convinced the problem is at the BDE level and not the database server.
I set TQuery.UniDirectional to TRUE (not FALSE, sorry guys...) before the open statement. It is a multiple join, multiple field conditional query. The TQuery.Open statement doesn't actually allocate much memory. I then allocate around 170Mb of data structure to accommodate all the fetch data. My problem is the other around 180Mb of data that are allocated by my application during the 116,000 calls to the TQuery.Next method!!!. I guess this is due to the bidirectional features of the cursor that needs to cache all the data. It doesn't really matters if I set the UniDirectional property to either TRUE or FALSE, on both cases the extra 180Mb of memory are allocated by my application to be later freed when I call the TQuery.Close method.
Limbeck,
- Yes, I'm setting it to TRUE, not FALSE, sorry for that ...
- Yes, my recordset is around 116,000, and I'm fetching each and every one of them ...
- Yes, I'm only selecting the fields I need ...
- I really need to load all this data to a single component view for data management proposes ...
I can not believe there is not a way of avoiding my application to allocate all this amount of memory during the fetching process (starts with 14Mb before Open, moves all the way to 364Mb, to drop down to 184Mb after the Close method, the 184-14=170Mb are fine - actually allocated by me)... I only need to fetch a record once, no bidirectional functionality needed, it is rather stupid actually ..
Where is this appointed query: client side or server side?
ASKER
Kacor,
It is on the client side.
It is on the client side.
try to use stored procedure -> the network traffic goes down, the application don't have to move the whole database from the server to the client side and your query will be fast
and this don't need this occupied memory
ASKER
Well, I must have misunderstood your question, the query is ran on the client side but the server only "feeds" the information I need, the whole database is not moved from the server to the client side. Furthermore, the network traffic is not a problem, neither is the query speed. I am quite sure it is returning a bidirectional cursor because I can call both TQuery.Next and TQuery.Prior methods.
Borland says: "Traditionally SQL cursors are unidirectional. They can travel only forward through a dataset. The BDE, however, permits bidirectional travel by caching records. If an application does not need bidirectional access to records in a result set, set UniDirectional to True. When UniDirectional is True, an application requires less memory and performance is improved."
That's what I'm doing: setting UniDirectional to True, but the application is not using less memory!. I guess this is a BDE problem, I've tracked code through the VCL and the call DBiSetProp( hDbiObj(StmtHandle), stmtUNIDIRECTIONAL, LongInt(FUniDirectional) ) is performed (with FUniDirectional=TRUE), the BDE must be ignoring it. I've tried version 4 and 6, the same is verified. Any clues???
Borland says: "Traditionally SQL cursors are unidirectional. They can travel only forward through a dataset. The BDE, however, permits bidirectional travel by caching records. If an application does not need bidirectional access to records in a result set, set UniDirectional to True. When UniDirectional is True, an application requires less memory and performance is improved."
That's what I'm doing: setting UniDirectional to True, but the application is not using less memory!. I guess this is a BDE problem, I've tracked code through the VCL and the call DBiSetProp( hDbiObj(StmtHandle), stmtUNIDIRECTIONAL, LongInt(FUniDirectional) ) is performed (with FUniDirectional=TRUE), the BDE must be ignoring it. I've tried version 4 and 6, the same is verified. Any clues???
Try to transform your query. Now your solution transfers the base data from the server and the client makes the data processing. Your resources would be better used if the data processing makes the server (the server has greater resources, is probably quicker and has special possibilities for this process) and the client side makes only the data visualisation. The client would handle only the result data set.
hm or dont use a query but a table component if you can, that will speed up things as well
but overall, kacor is right. you should try to let the server do the math
but overall, kacor is right. you should try to let the server do the math
ASKER
All "the math" is already done on the server!, the client doesn't "process" any data, it just visualizes it... The problem is that TQuery.Next method is allocating a lot of memory (more than what I allocate to visualize data).
Hi,
Have you tried compiling your code in a more recent version of Delphi? Delphi 3 is very old, and it may well be a VCL bug, in which case it might well have been corrected by now.
On the other hand, it seems like it might well simply be a BDE bug. Unfortunately, the BDE is also old and deprecated, and though it doesn't have that many outstanding bugs, I don't think we can expect another update at this point.
I think the only real solution to your problem, if the above doesn't work, is to move away from the BDE. I would recommend IBX or FIBPlus, though there are a number of other alternatives too (such as UIB, IBO, and a few others). Moving an application from the BDE to another connectivity technology can be a fair amount of work, but if you can afford to spend the required time, it's definitely worth it in the end. It will definitely solve your memory problem, it will avoid you running into other unsolvable problems later on (since there's little hope of any BDE bug ever getting fixed) and it will let you take full advantage of the newer features of Interbase / Firebird which weren't available in verious versions. Even IB 6 isn't officially supported by the BDE (in particular, dialect 3 is problematic), let alone IB 7.5 or FB 2.0. Sooner or later it would be highly advisable to make the switch.
Hope this helps.
Have you tried compiling your code in a more recent version of Delphi? Delphi 3 is very old, and it may well be a VCL bug, in which case it might well have been corrected by now.
On the other hand, it seems like it might well simply be a BDE bug. Unfortunately, the BDE is also old and deprecated, and though it doesn't have that many outstanding bugs, I don't think we can expect another update at this point.
I think the only real solution to your problem, if the above doesn't work, is to move away from the BDE. I would recommend IBX or FIBPlus, though there are a number of other alternatives too (such as UIB, IBO, and a few others). Moving an application from the BDE to another connectivity technology can be a fair amount of work, but if you can afford to spend the required time, it's definitely worth it in the end. It will definitely solve your memory problem, it will avoid you running into other unsolvable problems later on (since there's little hope of any BDE bug ever getting fixed) and it will let you take full advantage of the newer features of Interbase / Firebird which weren't available in verious versions. Even IB 6 isn't officially supported by the BDE (in particular, dialect 3 is problematic), let alone IB 7.5 or FB 2.0. Sooner or later it would be highly advisable to make the switch.
Hope this helps.
ASKER
Hi jonneve,
You're right, it is most likely to be a BDE bug, I have tracked down all the api calls and it seems it's implemented for table cursors but not for query cursors. I also agree I should make the switch to some other technology, but I must confess I haven't for two reasons:
1. There is almost a million lines of code to change! ...
2. Some time ago I tryed IB 7.5 with IBX and for the queries I run it was a bit slower than IB 4.5 and BDE than I'm still using, can you believe this?!.
Anyway, I'll have to move sooner or later, but right now I have to support all running projects, and no time left to convert and test all that "junk" to a new platform.
Thanks!!!
Luis
You're right, it is most likely to be a BDE bug, I have tracked down all the api calls and it seems it's implemented for table cursors but not for query cursors. I also agree I should make the switch to some other technology, but I must confess I haven't for two reasons:
1. There is almost a million lines of code to change! ...
2. Some time ago I tryed IB 7.5 with IBX and for the queries I run it was a bit slower than IB 4.5 and BDE than I'm still using, can you believe this?!.
Anyway, I'll have to move sooner or later, but right now I have to support all running projects, and no time left to convert and test all that "junk" to a new platform.
Thanks!!!
Luis
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if you use win32 platform (suitable for superserver architecture), you can upgrade to either FB 1.5.3 or FB 2.0 version. These are more developed versions and this could solve your problem. Download from http://www.firebirdsql.org/index.php?op=files&id=engine.
To help you please give the related part of your program.
wbr
kacor