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.
| Author |
Topic |
|
deepikasyam
Starting Member
4 Posts |
 Posted - 2006-10-22 : 04:19:23
|
| Hi,I have a situation wherein a particular stored proc X runs fine in Query Analyser, but takes lot of time when called thru a web interface. As a result, many times i encounter a page time-out error.The stored proc typically does a select query and returns about 150,000 rows. Can anyone tell me what typically is the problem ? |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-22 : 07:34:22
|
| Probably the network transmission time, or the way the Result Set is handled by the application - e.g. sub-optimal settings in ADO.Or the Browser is taking too long to render the data.How long does it take in QA on a Client machine (NOT the server) to save the query to a file (i.e. no screen refresh required)?Stick a debug in your application before/after the SQL call in your Client application to see how long that takes - I doubt if it will be any longer - and then the rest of the time is down to whatever the application is doing, and will have nothing to do with the actual execution of the SProcKristen |
 |
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2006-10-22 : 08:34:58
|
| Do you really need all 150,000 rows, and if so, does the sp retourn more coloumns than needed, its a lot of data to process ...-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter." |
|
|
|
deepikasyam
Starting Member
4 Posts |
Posted - 2006-10-26 : 08:46:54
|
| Thanks for the info. Will check how the result set of the stored procedure is being handled by the application |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-10-27 : 02:14:47
|
| Indeed. Does it make sense to display 150k rows of data to a user? pagination seems to be in order here.SqlSpec - a fast, cheap, and comprehensive data dictionary generator forSQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org |
|
|
|
|
|
|
|
|
Performance Tuning query