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

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

Tim


It will be fileserver

Roy Lambert

Roy,

<< It will be fileserver >>

Then 2. is still the way to go.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

Sorry, I meant 3. is stil the way to go.

--
Tim Young
Elevate Software
www.elevatesoft.com

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

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