Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
Index type & filtering/SELECT/range |
Fri, Apr 25 2008 3:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I'm thinking about table structures and I have an idea how to do something interesting but I'm not sure about performance impact. Is there a way to calculate the relative speeds of various approaches? In outline the table will be emails and currently I "select" out using a range based on an integer (mail box number). What I'm thinking of doing is adding a user identifier and a privacy flag. When a user changes the mail box they're viewing the selection will be box & (user or not private). Box will be integer, Private will be boolean and User will be either varchar(5) or integer. I prefer the former because that's what I'll use for login purposes (their initials). I'm filling an AdvStringGrid so I'll need to loop through the result set however obtained. Obviously I'll do some testing but my thoughts are: Index separately on Box & User 1. pure sql SELECT on all criteria 1. sql SELECT just on Box then check the others as I loop round 2. table range on Box then filter 3. table range on Box then check the others as I loop round I'm not concerned with index size just overall speed. It would be great to have some way to come up with a rough comparison to aid in decision making without having to test all the alternatives. What I'm interested in is option a = twice as long as option b type stuff since I know a lot will depend on size and content of table. So, for example, you could say filtering/selecting on an integer is 5 x as fast as filtering on a varchar(5) that would go a long way towards giving me what I want. If then you could say a compound index is 1.7 times slower than the sum of its separate indices I'd be on cloud 9 Roy Lambert |
Fri, Apr 25 2008 7:37 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
If you're already using a TEDBTable, and this is local only, then I would go with 3. If you're using a remote session, then 2. is the way to go to minimize the number of rows travelling across the wire. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Apr 25 2008 7:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
It will be fileserver Roy Lambert |
Fri, Apr 25 2008 9:19 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< It will be fileserver >> Then 2. is still the way to go. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Apr 25 2008 9:19 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | |
Fri, Apr 25 2008 10:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I'm building my testbed now. And I came up with yet another option - set the index to the box number, FindKey to the right record and go Roy Lambert |
Sat, Apr 26 2008 4:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
Strangely enough you're right, but the fascinating thing is how close all the different approaches were, and how little difference switching from a VARCHAR(5) to an integer made. Roy Lambert |
This web page was last updated on Friday, May 3, 2024 at 08:07 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |