question

SanjayKumar-8159 avatar image
0 Votes"
SanjayKumar-8159 asked MelissaMa-msft edited

Update Query fail sometime in SQL server

I am running a SQL query to update the column,

UPDATE sip

SET

ScheduleID = 123222

FROM Price sip

INNER JOIN

(

SELECT

sip.ItemPriceID

FROM Charge sipc

INNER JOI Price sip ON sip.ItemPriceID = sipc.ItemPriceID

INNER JOIN CtlCycle ctl ON ctl.BillingRunID = @BillingRunID

WHERE sipc.BillingRunID = @BillingRunID

AND CONVERT(DATE, BilledThruDate) >= CONVERT(DATE, DATEADD(DAY, -1, sipc.ItemPriceEndDate))

AND ISNULL(sip.OrderDate, '2000-01-01') <= ctl.OrdersThruDate

GROUP BY

sip.ItemPriceID

)AS t ON t.ItemPriceID = sip.ItemPriceID



It works fine in one environment and failed in another environment with the SQL timeout error.

I check the table charge having lots of null in columns ItemPriceEndDate, and if I add null check for column ItemPriceEndDate it works(AND sipc.ItemPriceEndDate is not null) fine.

The Question :

Why it works in one environment without any change and failed in another environment. Have the same amount of data in both environments.

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Unless there is a blocking issue, you apparently have different execution plans in the two environments. This is can be for a quite a few reasons:

  1. Different set of indexes.

  2. Different statistics information.

  3. Different server configuration for parallelism.

  4. Different amount of memory available.

And that is not an exhaustive list.

You will need to capture the query plans to work further.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered SanjayKumar-8159 edited

Hi @SanjayKumar-8159,

Welcome to Microsoft Q&A!

Please check whether the failed one had any deadlock or open transactions which might cause timeout error and also check whether both environment had the same indexes, execution plans,settings and so on.

You could also try with below statement:

 UPDATE sip
 SET ScheduleID = 123222
 FROM Price sip
 INNER JOIN Charge  sipc ON sip.ItemPriceID = sipc.ItemPriceID
 INNER JOIN CtlCycle ctl ON ctl.BillingRunID = sipc.BillingRunID
 WHERE sipc.BillingRunID = @BillingRunID
 AND CONVERT(DATE, BilledThruDate) >= CONVERT(DATE, DATEADD(DAY, -1, ISNULL(sipc.ItemPriceEndDate,'2000-01-01')))
 AND ISNULL(sip.OrderDate, '2000-01-01') <= ctl.OrdersThruDate

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MelissaMa-msft,
I have compared the environment and both are the same, setups and count of records. I notice, the same query work in the failed environment sometimes, which looks weird to me.

AND CONVERT(DATE, BilledThruDate) >= CONVERT(DATE, DATEADD(DAY, -1, ISNULL(sipc.ItemPriceEndDate,'2000-01-01')))

if ItemPriceEndDate will have the null, no need to update the ScheduleID
so this way we can use.

wondering how it works sometimes.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered SanjayKumar-8159 commented

failed in another environment with the SQL timeout error

A command timeout isn't raised by SQL Server, it's raised by the client, when query execution elapse more then a defined time out; default is 30 seconds.
Optimize the query or increase the timeout period for the application.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @OlafHelper-2800,

This query took 1-2 min for 10 million records, and both the environment have the same time out(3600).

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @SanjayKumar-8159,

Thanks for your update.

Since you mentioned that the same query sometimes works in a failing environment, there may be some problems in other aspects than the query itself.

As mentioned by Erland, you could check below while it is running:

  1. Find out any Blocking.

  2. Display Execution Plans and compare with each other.

  3. Find out any memory/CPU/disk related issue by using performance counters.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.