PDA

View Full Version : OT: Index, Server Only, Server and Client Only



MrHalland
24-Jan-2017, 11:49 AM
Hi!

My question is not a bug, but rather that I forgot the use of the different index types.

When is a Server Only index usable and a Client Only index usable?

How did you create index at runtime?

If you have an index as Server Only and you have two tables where the relation is between two ID Fields only, can you use Server Only index then?

Stephen W. Meeley
24-Jan-2017, 12:42 PM
Good questions. I'll give them a shot...



When is a Server Only index usable and a Client Only index usable?

Server indexes exist on the server and are expressed in the .int file and are fully usable. By usable I mean they have a index number and can be assigned as main indexes for columns.

Client Only indexes do not exist on the server (though they may be "supported" by indexes that do) but are expressed in the .int file and are fully usable (same definition as above).

Server Only indexes exist on the server but are not expressed in the .int file and are not fully usable. They do not have an index number and cannot be assigned as the main indexes for columns. This does not mean that they they don't play a part. Server Only indexes may be directly referenced in ESQL statements and indirectly used by the server in finding and ordering data. But in the traditional DataFlex sense and from the DataFlex language they are not "usable".



How did you create index at runtime?

There is an example of this in the help under "Improved Index Support" | "Runtime Temporary Indexes"



If you have an index as Server Only and you have two tables where the relation is between two ID Fields only, can you use Server Only index then?

Note sure I understand this one. If any table has an ID column there is probably no reason it should ever be a Server Only index. Especially since it is likely the primary key for that table anyway. Can you give a more specific example of why you think you'd want such columns to have Server Only indexes instead of Server indexes?

MrHalland
24-Jan-2017, 12:49 PM
Hi!

Thanx for the answers on my questions.

Now I almost got the big picuture :)

Regarding Clinent Index, any typical example on when to use these? Is it to not "overload" the tables with index that you perhaps use once in a blue moon?

Regarding server index only and relations, I now know in you good answer on why you can't use them...

Stephen W. Meeley
24-Jan-2017, 01:46 PM
Martin,

Let's take the Chinook database as a perfect example. All of the tables have primary keys and associated indexes (Unique and Clustered) and those are server indexes (fully described at both the server and in the .int file).

For those columns that are the basis for "relationships" (eg foreign keys) to other tables, they also have indexes, but non-unique, non-clustered. That's enough for joins in the SQL world but not enough for DataFlex to deal with. Take the Album table for instance; it relates to Artist so it has a column Album.ArtistID and an index on that column. In the DataFlex world, we'd define that index as a minimum of a two segment index, probably Album.ArtistID and then Album.AlbumID in order to make it unique and usable for your application. But in the SQL world they don't do that - they just make it a non-unique, single-segment index and that's enough (for them).

In the past, you would try to go back to the table at the server and add the second segment to the index - but that was really unnecessary. All you really needed to do is "lie" to DataFlex and tell it (in the .int file) that there is two segment index on Album.ArtistID, Album.AlbumID and it would work fine because the server would order the data for you even past the initial segment. Not as efficient in some ways as the full two segment index but that may not matter. Also, changing the tables at the server (even just for indexes) may be completely out of your control. You could always manually edit the .int file to tell your lie, but you had to know how to do it and once you did it could get messed up by other changes.

Client-Only indexes just make the process of lying to the DataFlex side easy and supported! Let's go back to the Artist table. Here is what I see in the .int file...



DRIVER_NAME MSSQLDRV
SERVER_NAME DFCONNID=Chinook
DATABASE_NAME Album
SCHEMA_NAME dbo


RECNUM_TABLE NO
PRIMARY_INDEX 1
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE NO


FIELD_NUMBER 1
FIELD_INDEX 1


FIELD_NUMBER 3
FIELD_INDEX 2
FIELD_RELATED_FILE 2
FIELD_RELATED_FIELD 1


INDEX_NUMBER 1 <- this is a server index
INDEX_NAME PK_Album


INDEX_NUMBER 2 <- this is a client only index
INDEX_NAME IFK_AlbumArtistId_C
INDEX_NUMBER_SEGMENTS 2
INDEX_SEGMENT_FIELD 3
INDEX_SEGMENT_FIELD 1


The thing about index 2 is that there is that real, Server Only index that makes it work pretty efficiently. It's name is IFK_AlbumArtistId (a non-unique, non-clustered index) and we named our companion Client Only index the same, but appended a _C so it's obvious what is going on.

I hope this helps!

Marco
26-Jan-2017, 01:09 AM
Server index = database engine maintained index
Client index = database driver (ck) defined index
Your app does not need knowledge what kind of index it is.