topic Re: Help with large key performance in Application Development and Automation Discussions

Help with large key performance

Former Member — Mon, 27 Jul 2009 14:29:43 GMT

HI ,

I have a to build 3 Z DB table and in the table one of the field is (application ID )

is in size char 255 and it's table key (and i cant change this fields) .

the tables

Table 1 
mandt type mandt     " key
user   type bname    " key 
application_id type char255   " key 
date...


Table 2
mandt type mandt     " key
user   type bname    " key 
application_id type char255   " key 
application_ver type char10   " key
additional fields (not keys).

Table 3
mandt type mandt     " key
user   type bname    " key 
application_id type char255   " key 
application_ver type char10    " key 
app_additonl    type char50     " key
additional fields (not keys).

The problem is that when i activate the table i get warning of performance

since the key is larger then 120 charcter .

the table num 3 can have more than 1000000 recoreds .

and i can have lot of read access to the table

Regards

JOy

Re: Help with large key performance

Former Member — Mon, 27 Jul 2009 14:44:25 GMT

You could make the 255 chars wide field a non key, and introduce in it's place a Hexadecimal hash calculated from that field. If you use one with one hash per record, you'll maintain key integrity while keeping the perfomance at check.

It's not easy, and it has some drawbacks, as you can't get user input for selection directly for that key field, you'll need to use the 255 char, calculate the hash for it and then use the select.

Re: Help with large key performance

Former Member — Mon, 27 Jul 2009 16:02:19 GMT

HI Gustavo,

Since i am on design now can u please tell me what is the drawback for it ?

how it influence (the hash solution) on the performance against the large table key ?

Regards

Joy

Re: Help with large key performance

Former Member — Mon, 27 Jul 2009 17:06:14 GMT

Well, the drawback was already mentioned. You can't use direct user input. You'll need to calculate the hash each time a user tries to use the 255 chars field for selection purposes.

Now, once it's calculated, it's lower lenght will help you with the long key performance issue.

Re: Help with large key performance

Former Member — Mon, 27 Jul 2009 18:45:39 GMT

Hi ,

Do u suggest to use the calculate Hash for char with MD5 algorithm or SH1 ,or there is other recommended scenario i that take the performance side in the picture too since the performance side is very impotent to my design .

or there is other ideas/ recommendation to my design?

Best Regards

Joy

Edited by: Joy Stpr on Jul 29, 2009 8:51 AM

Re: Help with large key performance

Former Member — Wed, 29 Jul 2009 08:49:27 GMT

You can look at option of splitting application_id field into 3 different fields - provided you have way of splitting the data stored in the key field.