Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 11 to 20 of 21 total |
Problem getting Range to work in v1.02 |
Wed, Apr 11 2007 5:05 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< Arggh. Operator error. I used KeyFieldCount:=1 just once, before the SetRangeEnd but not before the ApplyRange. I thought the ApplyRange would use the existing KeyFieldCount value. I added the extra KeyFieldCount and its working find now. Oh well. Stand down from red alert. >> Ah, cool, thanks. But, it still is a little disconcerting that it is taking ~6 seconds to do a range in the first case. They should always be instantaeous as of 1.02. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 11 2007 6:44 PM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Dave, > > << Arggh. Operator error. I used KeyFieldCount:=1 just once, before the > SetRangeEnd but not before the ApplyRange. I thought the ApplyRange would > use the existing KeyFieldCount value. I added the extra KeyFieldCount and > its working find now. Oh well. Stand down from red alert. >> > > Ah, cool, thanks. But, it still is a little disconcerting that it is > taking ~6 seconds to do a range in the first case. They should always be > instantaeous as of 1.02. > I re-did the range benchmark and with the proper KeyFieldCount:=1 at both locations, the range works faster. The first invocation takes between 500ms and 900ms to display the range. Subsequent calls on the same range will get it down to 16ms to 30ms due largely I think to the Windows cache. I did notice that the more rows that were returned by the range, the slower the range. So I suspect the culprit might be the record count. It may be counting the # of rows returned which is slower than the execution of the range. Is there any way to turn the RecordCount off? Dave |
Thu, Apr 12 2007 3:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< I did notice that the more rows that were returned by the range, the slower the range. So I suspect the culprit might be the record count. It may be counting the # of rows returned which is slower than the execution of the range. Is there any way to turn the RecordCount off? >> It's not counting, but it does have to set a bit in a bitmap for each row that satisfies the range. That could take some time for hundreds of thousands of rows. DBISAM uses statistics to get the count without a bitmap, but EDB doesn't use statistics so it has to use the bitmap to get the count. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Apr 13 2007 5:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>It's not counting, but it does have to set a bit in a bitmap for each row >that satisfies the range. That could take some time for hundreds of >thousands of rows. DBISAM uses statistics to get the count without a >bitmap, but EDB doesn't use statistics so it has to use the bitmap to get >the count. Are there implications with this for tests like if table.RecordCount > 0 then .. ? Should we convert them to if not (table.eof and table.bof) then .... ? Roy Lambert |
Fri, Apr 13 2007 11:30 AM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Dave, > > << I did notice that the more rows that were returned by the range, the > slower the range. So I suspect the culprit might be the record count. It may > be counting the # of rows returned which is slower than the execution of the > range. Is there any way to turn the RecordCount off? >> > > It's not counting, but it does have to set a bit in a bitmap for each row > that satisfies the range. That could take some time for hundreds of > thousands of rows. It takes time for a few hundred rows or a couple thousand rows too. When the range is repeated, the average time to execute is 35ms with dips down to 15ms. It never goes to 0ms which a competing db can achieve. It might not seem like much of a difference, but on a webserver it means a difference of 50 ranges/sec compared to 5,000+ ranges/sec (yes, I did get the other db's range going that fast after a few hours because I had record count turned off and the data ended up in the db cache). > DBISAM uses statistics to get the count without a > bitmap, but EDB doesn't use statistics so it has to use the bitmap to get > the count. What if you didn't need the count? Shouldn't you be able to turn it off if you needed more speed? Dave |
Fri, Apr 13 2007 11:32 AM | Permanent Link |
Dave Harrison | Roy Lambert wrote:
> Tim > > > >>It's not counting, but it does have to set a bit in a bitmap for each row >>that satisfies the range. That could take some time for hundreds of >>thousands of rows. DBISAM uses statistics to get the count without a >>bitmap, but EDB doesn't use statistics so it has to use the bitmap to get >>the count. > > > Are there implications with this for tests like if table.RecordCount > 0 then .. ? > > Should we convert them to if not (table.eof and table.bof) then .... ? > > Roy Lambert Roy, For databases in general, I've always found testing for EOF to be faster than counting rows because you can stop after the first row is found. Dave |
Sat, Apr 14 2007 3:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
< Are there implications with this for tests like if table.RecordCount > 0 then .. ? >> No, once the count is calculated, it isn't re-evaluated again unless the data is refreshed due to other changes. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Apr 14 2007 3:26 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< It takes time for a few hundred rows or a couple thousand rows too. When the range is repeated, the average time to execute is 35ms with dips down to 15ms. It never goes to 0ms which a competing db can achieve. It might not seem like much of a difference, but on a webserver it means a difference of 50 ranges/sec compared to 5,000+ ranges/sec (yes, I did get the other db's range going that fast after a few hours because I had record count turned off and the data ended up in the db cache). >> Yes, but all of this is only an issue if the web server is running into load issues. For most people, 15ms one way or the other is not going to make that much of a difference. A file seek that ends up going to disk could end up costing many times more than that, and there is no way to control for that with multiple clients/sessions/threads updating/reading data. << What if you didn't need the count? Shouldn't you be able to turn it off if you needed more speed? >> It's always possible that we could add such a thing, but we have it in there because the count is usually something that is useful to have. -- Tim Young Elevate Software www.elevatesoft.com |
Sun, Apr 15 2007 6:49 PM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Dave, > > << It takes time for a few hundred rows or a couple thousand rows too. When > the range is repeated, the average time to execute is 35ms with dips down to > 15ms. It never goes to 0ms which a competing db can achieve. It might not > seem like much of a difference, but on a webserver it means a difference of > 50 ranges/sec compared to 5,000+ ranges/sec (yes, I did get the other db's > range going that fast after a few hours because I had record count turned > off and the data ended up in the db cache). >> > > Yes, but all of this is only an issue if the web server is running into load > issues. Correct. I do expect load problems because there will be a lot of database access via ranges. I will likely have to use ranges because queries may not execute fast enough. > For most people, 15ms one way or the other is not going to make > that much of a difference. That's true if you have only 5 people on the site. But if there are 1,000 people clicking away, they are going to have to wait almost 7 seconds seconds for a response (if the max range speed is 148 ranges/second-see below). Either that, or I need to get 7 servers instead of 1. I've benchmarked Elevatedb ranges and it won't go faster than 148 ranges/second. At first this seems fast until I compare it to other databases that I've tested. MySQL (improperly tuned) will exceed 1000 queries/second (7x faster) and I'm hoping I can improve on this. The other database I tested will do well over 2500 ranges/second (17x faster). All of these tests ran for several hours and the speed increased as more and more hits are found in the cache. Suprisingly it doesn't appear that ElevateDb uses cache because the Range speed starts at around 140 range/sec and stays there no matter how long I let it run. I checked the memory consumption and sure enough, the ElevateDb app does not use much memory at all. The memory useage is pretty much rock stable without the life of the program and there is no decline in memory even if the program runs for hours. Is there any way to assign cache to ElevateDb? > A file seek that ends up going to disk could end > up costing many times more than that, and there is no way to control for > that with multiple clients/sessions/threads updating/reading data. In my webapp 99% of disk access for my app will be reads (85% ranges and 14% fulltext searches). Only 1% will be updates but they will be cached to a memory table and will written out every few minutes so the writes won't block reads. > << What if you didn't need the count? Shouldn't you be able to turn it off > if you needed more speed? >> > > It's always possible that we could add such a thing, but we have it in there > because the count is usually something that is useful to have. The record count is useful to have only if you reference it in your code. If you don't reference it, why get penalized for using it? Dave |
Mon, Apr 16 2007 2:36 AM | Permanent Link |
Dave Harrison | Tim,
I see there is a "Maximum Row Buffer Pool Size" and "Maximum Index Buffer Pool Size" which may help caching, but EDBM allows me to change this values when I alter table, but the changes do not take effect. They are always set to 32k and 64k respectively. I also tried SQL with Alter Table to change these values and although it says the SQL completed ok, these buffers are never changed. Is this working, or slated for a later release? TIA Dave |
« Previous Page | Page 2 of 3 | Next Page » |
Jump to Page: 1 2 3 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |