Question If We Were Increase A Field In Temp-table Index Either Primary Or Not .do We Get Performance Improve

[kalaiarasan]

If we were increase a field in temp-table index either primary or not . do we get performance improvement in a query
coded below primary with one field and next one with three field
& similiary non primary index too
/* primary index */
eg Define temp-table tablename no-lock
field1 as char
field2 as char
field3 as char
field4 as char
index index1 is primary
field field1



eg Define temp-table tablename no-lock
field1 as char
field2 as char
field3 as char
field4 as char
index index1 is primary
field field1
field field2
field field2



/* non primary index */
eg Define temp-table tablename no-lock
field1 as char
field2 as char
field3 as char
field4 as char
index index1
field field1



eg Define temp-table tablename no-lock
field1 as char
field2 as char
field3 as char
field4 as char
index index1
field field1
field field2
field field2

 

[Cringer]

The "primary" keyword is just telling Progress which is the main index. If you don't specify it then Progress will select the primary itself. In the case of the temp-table I think it's the first defined index that is selected. Progress will use the Primary index to search through the records if no other index is used, either on the basis of the query written, or no USE-INDEX is used.

 

[GregTomkins]

I did a quick benchmark of this to prove that PRIMARY makes no difference to speed. This is mostly just because I had nothing better to do.

FYI, from your examples, you can't define a temp-table NO-LOCK. You can do a NO-LOCK *FIND* on a temp-table, but if you do, you are probably confused about what it does (it does nothing and IMO should be syntactically invalid).

 

[TheMadDBA]

The bottom line on performance and index usage is... it depends. Without seeing your WHERE clauses and knowing the distribution of values in data it is impossible for us to tell you if one way would be faster or not.

If you have a WHERE clause that would benefit from additional filters on the data and you have a meaningful number of records in the temp-table then index changes would help.

 

[TomBascom]

There is no magic.

Any performance improvement related to defining indexes also depends on the actual data in the (temp-)table and the WHERE clause used to select that data.

While it is true that even a blind squirrel finds a nut once in a while, defining an index that does not in some way address the patterns of data or the needs of a WHERE clause will usually get you exactly nowhere. Indeed, if you do it enough you will slow down inserts and updates because your useless index will need to be updated too.

 

[Rob Fitzpatrick]

I think you meant to define your temp-table as no-undo.

 

[GregTomkins]

I think he was asking about whether PRIMARY made a difference, not whether indices per se made a difference?

Anyway, the ratio of writes to reads is probably pretty important too.

 

[TomBascom]

The example seems to be saying that he thinks that simply making indexes with more fields in them is going to increase performance. Along with that he seems to be thinking that "primary" might also help performance.

Both of those things *might* increase performance -- but not in a vacuum and not simply because you added fields or designated and index as "primary" on a whim. And given what we have to work with here this is, at best, making changes on a whim -- or throwing "stuff" up on the wall to see if anything sticks.

 

[kalaiarasan]

first of all sorry for typo error that no-lock instead of no-undo .
i am looking performance improvement in temp table with primary index rather than non primary index and additionally if index has more number of field and would it be helpful to fetch the record more faster? .
I understood based on your comments that primary index is just telling Progress which is the main index and performance improvement related to defining indexes also depends on the actual data in the (temp-)table and the WHERE clause used to select that data.
Thank-you to all for your valuable comments .