Query Timeout on a single Query

Question

Hello,

I have a POS software that has a query which gets a timeout everytime i run it on the AZURE SQL server databse. But it is running fine in my local SQL SERVER database, and it has been running fine for the past 6 years.

I have other queries in the same POS software that run fine on the same Azure SQL server.

I would appreciate any help on this matter.

The below is the actual code that is havng timeout.

select top(200) p.productid,p.partno,p.Description,p.Brand,p.Category,p.Application,p.OtherDetails,format(p.Price,'###,###,###') as Price, isnull(Op.Ostock,0) - isnull(i.soldqty,0) - isnull(q.soldqty,0) + isnull(g.rqty,0)+isnull(g2.rqty,0)+isnull(adj.inqty,0)-isnull(adj2.outqty,0)+isnull(adj3.inqty,0)-isnull(adj4.outqty,0) +isnull(gti.qtyin,0)-isnull(gto.qtyout,0)+isnull(r.rqty,0)+isnull(cn.rqty,0)-isnull(upcs.soldqty,0)-isnull(upinv.soldqty,0)+isnull(upcn.qtyout,0)+isnull(upcr.qtyout,0) as Stock, Currency,A.qty as OnOrder from (((((((((((((((((products p left join (select productid, sum(qty) as soldqty from tblcs where storename like'%%' group by productid) q on p.productid=q.productid) left join (select productid,sum(qty) as soldqty from tblinv where storename like'%%' group by productid) i on p.productid=i.productid) left join (select productid,sum(qty) as rqty from tblgrn where storename like'%%' group by productid) g on p.productid=g.productid) left join (select productid,sum(qty) as qtyin from tblgtn where gtnin like'%%' group by productid) gti on p.productid=gti.productid) left join (select productid,sum(qty) as rqty from tblcr where storename like'%%' group by productid) r on p.productid=r.productid) left join (select productid,sum(qty) as inqty from tbladj where adjtype='IN' and storename like'%%' group by productid) adj on p.productid=adj.productid) left join (select productid,sum(qty) as outqty from tbladj where adjtype='OUT' and storename like'%%' group by productid) adj2 on p.productid=adj2.productid) left join (select productid,sum(qty) as inqty from uptbladj where adjtype='IN' and storename like'%%' group by productid) adj3 on p.productid=adj3.productid) left join (select productid,sum(qty) as outqty from uptbladj where adjtype='OUT' and storename like'%%' group by productid) adj4 on p.productid=adj4.productid) left join (select productid,sum(qty) as rqty from tblcreditnote where storename like'%%' group by productid) cn on p.productid=cn.productid) left join (select productid,sum(qty) as qtyout from tblgtn where gtnout like'%%' group by productid) gto on p.productid=gto.productid) left join (select productid,sum(qty) as qtyout from tblupcreditnote where storename like'%%' group by productid) upcn on p.productid=upcn.productid) left join (select productid,sum(qty) as qtyout from tblupcr where storename like'%%' group by productid) upcr on p.productid=upcr.productid) left join (select productid,sum(qty) as rqty from tblUPgrn where storename like'%%' group by productid) g2 on p.productid=g2.productid) left join (select productid,sum(qty) as soldqty from tblupcs where storename like '%%' group by productid) upcs on p.productid=upcs.productid) left join (select productid,sum(qty) as soldqty from tblupinv where storename like '%%' group by productid) upinv on p.productid=upinv.productid) left join (select max(arrivaldate) as EDA,productid,sum(qtyordered)-sum(qtyreceived) as Qty from tbltracking group by productid) a on p.productid=a.productid)  left join (select productid,sum(opstock) as Ostock from tblopstock where storename like '%%' group by productid) op on p.productid=op.productid where P.partno like '%%' and P.description LIKE '%%' and P.brand LIKE '%%' and p.category like '%%' order by Description Asc

Answer 1

As Tom says, the timeout is set in the client API. By default it is 30 seconds in most APIs. You can change it on the command object or the corresponding, depending on which API you use.

As for why the query runs slower on this particular Azure instance, there are all sorts of possible reasons, including that this instance is set up with a low-budget edition with insufficient powers for this query. But it can also be that data is different. Or that statistics or index are different. Glancing that the query text, I can't say that I'm not surprised if it would run for more than 30 seconds. It's quite unreadable, but it seems to be complex.

As Tom says, we need to see the query plan in XML format, and preferably that would be the actual execution plan. It would also help to see the plan for the same query on a server where it runs fast enough. The best place to share plans is to upload them at http://www.brentozar.com/pastetheplan.

Answer 2

SQL Server does not timeout any query. The timeout is set by your application. Some queries require more time than the default setting, and require you to adjust the query timeout in your application.

Without a query plan, it is impossible for anyone to guess why your query is slow.

However see:
https://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

Answer 3

Hi @Muzzamil Sajid ,

Before seeing you more detailed information, I suggest to take a look at these two links:
https://dba.stackexchange.com/questions/256928/how-to-quantify-or-locate-command-timeouts-in-azure-sql
https://social.msdn.microsoft.com/Forums/SqlServer/en-US/58b1d199-6f7c-457b-8c78-b7ebece60ea4/azure-sql-server-timeout?forum=ssdsgetstarted
Hope these could give you some help.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 4

Hey guys, Thanks for the responses. I will be going through all documentation relating to this.

I know the wildcards are the ones slowing down the queries. And i do not have indexes set up either. This is the execution plan may be it helps.

Answer 5

Ok Guys Here is my execution Plan from brentazor

https://www.brentozar.com/pastetheplan/?id=HJyBi1AYK