CODEWITHSUNDEEP
c#oracle-databasewinformsdevexpressxtragrid
Marc Wittmann
Marc Wittmann

Reputation: 2671

Oracle performance issues with linguistic compare in DevExpress DataGrids

I use an oracle db where the standard comparison method is binary. There is a function that loops over a view which will get fast results in standard compare mode which works perfect. When the mode is switched to linguistic for case insensitive search

alter SESSION set NLS_SORT=BINARY_CI;
alter SESSION set NLS_COMP=LINGUISTIC;

The execution time switches from 0,01 Second to whooping 22 Seconds. This can be also achieved directly on DB level when changing the session there. Since devexpress has its own internal "where" builder (like selections for 'last week' / 'last year' etc in datagrids) I have to use these generated oracle statements.

The case insensitive mode is needed but so slow. Is there any way to tell devexpress to optimize database access maybe without altering the session? Comparing with "toUpper" values would be much faster for example.

already tried

  DevExpress.Data.Helpers.ServerModeCore.DefaultForceCaseInsensitiveForAnySource = true;

but this didn`t change anything.

Thx a lot, cheers

Upvotes: 0

Views: 161

Answers (1)

Nikita K
Nikita K

Reputation: 406

The DefaultForceCaseInsensitiveForAnySource option should be the simplest way to enable a case-insensitive search, but it's important that it works only with the binary comparison mode. Using this feature with linguistic comparison does not make sense and can lead to unpredictable results.

If for some reason the use of DefaultForceCaseInsensitiveForAnySource with the binary comparison mode does not meet your requirements, you can improve the linguistic search performance by creating linguistic indexes in your database.

If you have additional questions regarding this case, please create a ticket in the DevExpress support center. Our support team will conduct more thorough research than is possible in the SO thread.

Upvotes: 0

Related Questions