Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Tables Vs Queries |
Tue, Apr 10 2007 9:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I'd pretty well (in my own mind at least) figured out the relative benefits of tables and queries in DBISAM. Can you give guidance for ElevateDB please? I haven't converted a big enough table yet to test things out properly so its things like eg SELECT _BoxNo, _BoxName, _BoxType FROM BandA ORDER BY _BoxName and then allowing the user to set a filter on _BoxName. For a large table (say 150k records), with _BoxName indexed, would this be fast or would I need to alter the query? Any comments about other areas would be much appreciated. Roy Lambert |
Tue, Apr 10 2007 5:26 PM | Permanent Link |
Dave Harrison | Roy Lambert wrote:
> Tim > > I'd pretty well (in my own mind at least) figured out the relative benefits of tables and queries in DBISAM. Can you give guidance for ElevateDB please? > > I haven't converted a big enough table yet to test things out properly so its things like eg > > SELECT _BoxNo, _BoxName, _BoxType FROM BandA ORDER BY _BoxName > > and then allowing the user to set a filter on _BoxName. For a large table (say 150k records), with _BoxName indexed, would this be fast or would I need to alter the query? > > > Any comments about other areas would be much appreciated. That should work. Keep in mind that if you are not using a Live Query, then a static query has to send a copy of the results to a temporary table. For small tables, 5k or less, this is fine. But for >100k tables static queries will take time. Also static queries are not updateable. So if you have a form or updateable grid attached to a static query, you need to handle the update mechanism yourself. Dave |
Tue, Apr 10 2007 9:12 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I'd pretty well (in my own mind at least) figured out the relative benefits of tables and queries in DBISAM. Can you give guidance for ElevateDB please? I haven't converted a big enough table yet to test things out properly so its things like eg SELECT _BoxNo, _BoxName, _BoxType FROM BandA ORDER BY _BoxName and then allowing the user to set a filter on _BoxName. For a large table (say 150k records), with _BoxName indexed, would this be fast or would I need to alter the query? >> What Dave said is correct - make sure the RequestSensitive property is set to True, and you'll be fine. BTW, testing out Harry's billion rows stored procedure, I now have a table with 55 million rows in it, and it works just fine in the scenario that you describe. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 11 2007 1:52 AM | Permanent Link |
Dave Harrison | Tim Young [Elevate Software] wrote:
> Roy, > > << I'd pretty well (in my own mind at least) figured out the relative > benefits of tables and queries in DBISAM. Can you give guidance for > ElevateDB please? > > I haven't converted a big enough table yet to test things out properly so > its things like eg > > SELECT _BoxNo, _BoxName, _BoxType FROM BandA ORDER BY _BoxName > > and then allowing the user to set a filter on _BoxName. For a large table > (say 150k records), with _BoxName indexed, would this be fast or would I > need to alter the query? >> > > What Dave said is correct - make sure the RequestSensitive property is set > to True, and you'll be fine. > > BTW, testing out Harry's billion rows stored procedure, I now have a table > with 55 million rows in it, and it works just fine in the scenario that you > describe. > Tim, What happened to the other 945 million rows? What happens if you need a case insensitive index on a table that large? Is it going to be slower building the index or accessing the rows? Dave |
Wed, Apr 11 2007 4:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dave
Blast I was going to ask that question Roy Lambert |
Wed, Apr 11 2007 3:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dave,
<< What happened to the other 945 million rows? >> It rightly crapped out with a "table full" message when it reached 4 gigs (I didn't turn on LargeFileSupport on purpose . << What happens if you need a case insensitive index on a table that large? Is it going to be slower building the index or accessing the rows? >> It will be slower than a smaller table, sure. But it should be usable just fine. Even though you can't see it, internally there's already an index on the table even if you don't define any. It is used to provide insert ordering by when the insert occurred (internal row ID). -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |