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

Hi guys,

Appreciate all you help in this matter. I decided to create indexes for some of the tables. And I think this helped a lot. No timeouts anymore and the responses are way quicker.

I am getting almost the same response time as i am getting on my local server.

So will the indexes on columns data added from now be added automatically?

Answer 3

In the plan I see at least 15 table scans and only 4 index seeks; yes, creating indexes will help to improve execution performance.

data added from now be added automatically?

Yes, index data gets updated automatically.