Query Time Out Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Time Out Problem

Author

Topic

avis_sivakumar
Starting Member

2 Posts

Posted - 2004-08-23 : 01:15:32

I have a query as

select * from TransPurchaseOrder1 where Party_Code='Pty018' and PO_No in (Select OurPO_No from TransPurchaseOrderMaster2 where MastPO_No in (Select MastPO_No from TransPurchaseOrderMaster1 where Deleted='N')) and Deleted='N' Order By PO_No

it works fine for all other party code but not does not work for the party code 'Pty018', a connection time out error occurs, if executed in Visual Basic. I am using Ado in vb. If the same query is executed in Query Analyzer, with Party code as Pty018 it takes 36 seconds to retrieve the data, whereas for other party code it takes less than 2 seconds. what could be wrong

Suggest any other join types if any thing is wrong with the join type in the query, with a sample

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-23 : 01:26:45

I would change your sub-queries into JOIN's for a start.
Then if you're still having problems you can have a look at the execution plan to find out where the bottleneck is

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-08-23 : 07:35:16

"in QA it takes 36 seconds"..."in VB it times out"....look at your default connection/command timeout parameter...I suspect 30 seconds will be the value!!!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-08-23 : 08:03:04

Hi there,

Something like this in your VB code might help. 5 minutes should be good, but of course that depends... you might need more time who knows.

Set SQLTeamCNX = New ADODB.Connection
With SQLTeamCNX
.CommandTimeout = (5 * 60)
.ConnectionTimeout = (5 * 60)
End With

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

avis_sivakumar
Starting Member

2 Posts

Posted - 2004-09-01 : 04:45:16

I tried what u have said, i increased the commandtimeout timing and it was working fine for some time, then again i had the same problem. I again increased the timeout time further, the same thing happened, it just went out after some time. I have restructured the query with joins in it.

Nothing is working out.,

Suggest me some other option for debugging the same.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-01 : 09:18:37

You may want to:
enumerate the fields instead of *;
add an index possibly on party_code;
sort the result in your vb application instead of asking sql to do it

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-01 : 11:54:04

changing this to might be better...(it also is easier to read)

select * from TransPurchaseOrder1 c
inner join TransPurchaseOrderMaster2 a on a.OurPO_No = c.PO_No
inner join TransPurchaseOrderMaster1 b on b.MastPO_No = a.MastPO_No AND b.Deleted='N'
where c.Party_Code='Pty018'
and c.Deleted='N'
Order By c.PO_No

you then need to ensure that indices exist for the "a.OurPO_No", "b.MastPO_No" and "c.Party_Code" columns...ie all (or as many as is feasible) columns referenced in ON statements + WHERE clauses....

Posting your Expected-Execution plan would help to advance this faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources