Solved

TQuery.UniDirectional := FALSE doesn't seem to work

Posted on 2006-11-15
16
709 Views
Last Modified: 2013-12-09
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
0
Comment
Question by:zbrskz
  • 5
  • 5
  • 2
  • +1
16 Comments
 
LVL 10

Expert Comment

by:kacor
ID: 17953738
Hi Luis,

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
0
 
LVL 19

Accepted Solution

by:
Limbeck earned 250 total points
ID: 17954012
- unidirectional should be set to true not, false
- you are fetching 100,000 or are you opening a recordset with 100,000?
- avoid select * statements, remove the fields you dont need (just to be sure)
- why opening such a big recordset, you cant use a where clause to decrease the size?

0
 

Author Comment

by:zbrskz
ID: 17954857
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 ..
0
 
LVL 10

Expert Comment

by:kacor
ID: 17954903
Where is this appointed query: client side or server side?
0
 

Author Comment

by:zbrskz
ID: 17954951
Kacor,

It is on the client side.
0
 
LVL 10

Expert Comment

by:kacor
ID: 17955120
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
0
 
LVL 10

Expert Comment

by:kacor
ID: 17955133
and this don't need this occupied memory
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:zbrskz
ID: 17955477
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???
0
 
LVL 10

Expert Comment

by:kacor
ID: 17958933
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.
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 17958969
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
0
 

Author Comment

by:zbrskz
ID: 17962320
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).
0
 
LVL 1

Expert Comment

by:jonneve
ID: 18101745
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.
0
 

Author Comment

by:zbrskz
ID: 18104771
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
0
 
LVL 1

Assisted Solution

by:jonneve
jonneve earned 250 total points
ID: 18107823
1. Yes, I can understand that that would be a big hinderance... Of course, depending on which alternative solution you choose, it may not be so difficult to convert. For example, I've heard that IBObjects is very similar in concepts and structure to the BDE (more so than IBX or FIBPlus). I don't personally have any experience with IBO, as I use FIBPlus instead, but still, it might be worth investigating. I've heard that if you're coming from IBX, FIBPlus is the best alternative (and that's certainly true, for the two are very close), but if you're coming from the BDE, it's easier to switch to IBO than to FIBPlus (or IBX).

2. I do find that very surprising. I've never heard anyone say that the BDE was faster than IBX, and indeed, it doesn't seem possible since both use the same client library but IBX accesses it at a lower-level (that is, with fewer intermediate layers). Probably the difference comes rather from the switch to IB 7.5. Did you backup/restore your database on IB 7.5, or did you just use it? I expect (though I'm not sure) that the performance would be better if you do so because it will allow Interbase to recreate the database using its new On Disk Structure, which is surely the one it is optimized for. It seems highly unlikely that the issue couldn't be resolved (with perhaps a bit of tweeking) to make IB 7.5 much faster, for that's certainly the reputation it has. Did you try Firebird 2?
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now