How to improve the performance of Linq queries regarding Trim ()

Question

Our company tables were created with fields filled with spaces.

I do not have access / permissions to make changes to the database.

However, I noticed that when I create LINQ queries using the Trim () function, performance decreases slightly.

The query is as simple as it shows that performance degradation:

Companies .Where(c => c.CompanyName.Equals("Apple")) .Select(c => new { Tick = c.Ticker.Trim(), Address = c.Address.Trim() }); 

Is there a way to change the request so that there is no performance loss?

Or does it only stay with my DBA?

Answer 1

A quick solution is to fill out your company name before submitting your request. For example, if the column is char(50) :

 var paddedName = "Apple".PadRight(50); var result = Companies .Where(c => c.CompanyName.Equals(paddedName)) .Select(c => new { Tick = c.Ticker.Trim(), Address = c.Address.Trim() }); 

However, you should consider updating the database to avoid further problems.

Answer 2

I have not tried to work if we use the "Like" instruction to filter the first round and do this .ToList (), the second round only internally performs equal checking without calling the database.

 var result = (Companies .Where(c => c.CompanyName.StartsWith("Apple")) .Select(c => new { Tick = c.Ticker.Trim(), Address = c.Address.Trim() })).ToList(); var result1=result .Where(c=>c.CompanyName.Trim().Equals("Apple")) .Select(c => c); 

Answer 3

Besides the Entity Framework, linq-to-sql can sometimes switch to linq-to-objects under the hood when it encounters method calls that cannot be translated into SQL. So if you do

 .... .Select(c => new { Tick = c.Ticker.TrimEnd().TrimStart(), Address = c.Address.TrimEnd().TrimStart() 

You will notice that the generated SQL no longer contains LTRIM(RTRIM()) , but only the field name and that the trimmers are executed in client memory. Apparently, somehow LTRIM(RTRIM()) calls a less efficient query plan (surprisingly).

Maybe only TrimEnd() enough if there are no leading spaces.

In addition, I totally agree with pswg that you should go out of your way to try to clear the database instead of committing bad data in queries. If you cannot do this work, find the right people and turn their hands.