What are the possible solutions for "ASYNC_NETWORK_IO" wait. When I tried to execute the query its returning results in 4-5 secs. But from application it taking ages.. eventually we are...

most of the code looks like someone used to Oracle that was moved to work with SQL Server - in Oracle world majority of things are case sensitive -...

Hi frederico/Jeff Moden/Jeffrey,

I don't know how to ask for help but asking. This is eating my brain. Please forgive me.

We see these kind of poorly written queries every now and...

The developer whom I spoke to mentioned that they...

Thanks Jeffrey. Other thing I was looking for indexing and also to remove the UPPER() function. This database is not case-sentive database. I am not sure, why they are using...

Thank you Ant  for the suggestions.

I also see, dev team is using DISTINCT operator as a common practice. Is there any proper ways to re-write to the queries to avoid the usage of distinct operator...

You said in your narrative in your original post that "it reads last 30 mins of data from the tables involved" and yet ALL of your queries...

Also, they are using a table variable @ResultSet . I believe estimated rows will be always =1 . Replacing it with a temp table is better option?

One more question, I have in my mind, usage (NOLOCK) is any use or shall we ask developer to remove it. RCSI is enabled on the user database. do you...

before we can even suggest anything else...

1 - WHY are they returning 40 Million rows on a function call? what purpose does this accomplish?

ask the devs what is this...

RCSI is turned ON , on the database.

Regards,

Sam

--LOCKS on SELECT 
<Database name="CMX_ORS">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="(null)">
   ...

One more observation, on that database RCSI is enabled. Is there any good reason for using (NOLOCK) hint? I feel, since it is a SELECT  it should get the copy...

can you also get a ACTUAL execution plan of doing those sqls

and there is some duplication of access to table C_B_PARTY

take following snippet.

take following snippet.
first INNER...