Stored Procedure call from VB Component timing out

Question

Post reply

Stored Procedure call from VB Component timing out

Sriram Pasham

SSC Rookie

Points: 25
More actions
May 16, 2005 at 6:10 am

#186036

Hi,
In SQL2k, I have a stored procedure that retrieves about 50,000 records by joining 6 tables, data set is not really huge!!!
This stored procedure is called from a ADO Command object VB component which is in COM+, and the data-set will be returned to web page.
When i run the stored procedure from query analyser it takes 5-8 seconds, but when called from component/web page it keeps timing out (after 6-7 minutes).
Query used in side stored procedure is something like this -
Select tab1.col1, tab2.col2, tabl3.col4
from table1 tab1 WITH (NOLOCK)
INNER JOIN table2 tab2 on tab1.col1 = tab2.col1
INNER JOIN table3 tab3 on tab1.col1 = tab3.col1
ORDER BY tab1.col1
Could any one please give some pointers where the query is timing out when called from VB Component/Web Page?
Thanks
Sri

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

Try increasing the timeout property in the connection string.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Mark-163717 SSC Enthusiast Points: 188 More actions · Answer 2

5-8 seconds in Query Analyzer is still quite long. I would suggest Indexing some of the columns.

When you have your query in Query Analyzer....hit CTRL + I and that will bring up the indexing wizzard. This will help to reduce the time it takes to process the query.

Other then that you can try limiting the amout of records you return.

Sriram Pasham SSC Rookie Points: 25 More actions · Answer 3

Thanks for your reply!!!

Dinakar,

Actually connection time out is long enough for this!!

Mark,

Thanks for suggestion, I will try to tune indexes though i did it once for all the tables earlier. But a quick question here...

When we use reference tables in query joins, and use "ID" to JOIN tables but "description" is used in SQL "where" clasue, what is the best strategy to define clustered index? Should it be on "ID" or "Description" in reference table definition?

Appreciate your help!!!

Sriram Pasham SSC Rookie Points: 25 More actions · Answer 4

More on this from VB Code side of it, I am hesitating to post this content as it is more to do with VB rather than SQL,

When the stored procedure returns the recordset of approximately 10MB size(50k records) the VB code then collects it and writes it to a disk file using regular VB file operations, i pasted code below -

I am not very happy with the code that is used currently, I will really appreciate if you can guide me to improve/modify it OR suggest a suitable forum to post for more info on this, VB Code used at the moment as below -

***********************

'Get a free file handle

iFile = FreeFile

'Open file and print content

Open strFileName For Output As #iFile

For Each objfield In objRecordset.Fields

strLine =strLine& objfield.Name& vbTab

Next objfield

strLine = strLine & vbCrLf & objRecordset.GetString

Print #intFile, strLine

CreateReport = "<P><A HREF='../Exports/" & strName & "'>" & strName & "</A>"

Close #iFile

Set objfield = Nothing

***********************

I am sorry for posting NON-SQL stuff, but i am not quite sure if it is a SQL problem or VB!!

Thanks heaps!!!

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 5

sp_MakeWebTask might be a better solution for you than retrieve the recordset to VB and generate the file

Kindest Regards,

Vasc