0

I have tried this line of code but it ran into an error

CMR cmr = _cmrServices.GetAllWithAll().Where(x => x.CMRSerial == cmrSearchDeliverySerialNumber).FirstOrDefault();

Inner Exception 1:
SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Inner Exception 2:
Win32Exception: The wait operation timed out

I couldn't change the type of my fields to integer in the database also neither can compare my value to the string of a number because it gets a timeout error.

enter image description here but as you can see they are numeric with string type. enter image description here

this field cmrSearchDeliverySerialNumber is string though ,no matter what type it is I can't filter CMRSerial field as an integer.

I dont know should I use SqlFunctions.StringConvert or somthing else to convert CMRSerial` to an integer.

unfortunately I cant set index on CMRSerial field due its type is nvarchar(max) and I can't compare it as string to another string.

I ran this line of codes after executin my query that was created by profiler

SELECT QP.query_plan as [Query Plan], 
       ST.text AS [Query Text]
FROM sys.dm_exec_requests AS R
   CROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QP
   CROSS APPLY sys.dm_exec_sql_text(R.plan_handle) ST;

enter image description here

  • This has already been answered: https://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation – James Blackburn Jun 09 '21 at 08:51
  • 1
    Put an index on CMRSerial – Tim Schmelter Jun 09 '21 at 08:53
  • @JamesBlackburn my problem is not the timeout. it is converting –  Jun 09 '21 at 08:55
  • 1
    You are doing an implicit convertion, this is really bad in terms of performance. I would recommand to do an explicit cast/convert and then comapre it. As a rule of thumb, if it looks like integer but isn't and you want to compare it, cast it first (but say goodby to any index on that column) – Karlheim Jun 09 '21 at 08:59
  • @GertArnold I just asked about converting a string field to an integer in linq. –  Jun 09 '21 at 09:03
  • Also, it would help to know details of your environment:, mainly Entity Framework (?) version, mapping details. – Gert Arnold Jun 09 '21 at 09:07
  • @GertArnold I use version 6.0.0.0 Entity Framework –  Jun 09 '21 at 09:11
  • Mapping info? What matters most is if the property is mapped as unicode. – Gert Arnold Jun 09 '21 at 09:50
  • Please share the query plan via https://brentozar.com/pastetheplan. You can get it via SQL Profiler, Extended Events or Query Store (profiler is prob easiest) – Charlieface Jun 09 '21 at 10:29
  • @Charlieface How can see my query plan ?is it correct?```SELECT QP.query_plan as [Query Plan], ST.text AS [Query Text] FROM sys.dm_exec_requests AS R CROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QP CROSS APPLY sys.dm_exec_sql_text(R.plan_handle) ST;``` –  Jun 09 '21 at 10:49
  • Yes you could run that query in SSMS while your other one is running. Once you have it in SSMS, you can click on the XML and it will load it up. You should see pretty quickly whether it's the right one. Please share it via the link above – Charlieface Jun 09 '21 at 10:55

1 Answers1

-1

It will be much more performant to convert the search value to string in c# code and then compare that against the database value (compared to casting the values in database). Though this will work only if the string values in the database are generated with consistent format.

var serialSearchValue = cmrSearchDeliverySerialNumber.ToString(System.Globalization.CultureInfo.InvariantCulture) and use that in LINQ query.

Knaģis
  • 20,827
  • 7
  • 66
  • 80
  • by default it is string ,why I should do it again?! –  Jun 09 '21 at 09:27
  • if both are strings, then your problem is not related to string vs number - just a lack of index most likely as other comments suggested. – Knaģis Jun 09 '21 at 09:35
  • u think I want to convert my field in linq to an integer ,what should I do? –  Jun 09 '21 at 09:44
  • As the question states: *this field cmrSearchDeliverySerialNumber is string*. @amirkian You certainly don't want to convert the database field to integer before filtering, that's guaranteed to kill performance. I suspect what happens here is an internal nvarchar/varchar conversion, which should be visible in the query plan. – Gert Arnold Jun 09 '21 at 09:59
  • @GertArnold I wantt to convert databse field to integer before filtering in the linq expression, in this case. –  Jun 09 '21 at 10:08
  • @amirkian That's not happening. The expression is translated into SQL. – Gert Arnold Jun 09 '21 at 11:15