Solved

.net timeout sql server 2005

Posted on 2007-04-09
11
176 Views
Last Modified: 2013-11-26
I have a query in sql server 2005 which takes 36 seconds to run.  When I try to run it from vb.net (web ap), it times out.

How can I set up .net so that it only times out when appropriate.  I don't find that 36 seconds is excessive?
0
Comment
Question by:Fraser_Admin
  • 6
  • 5
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18876481
> I don't find that 36 seconds is excessive?
depends on what you are trying to run. if it is a "long-running" task, you might consider running it as job, so the user does not have to wait for it.
consider also checking if the query is fully tuned, 36 seconds does not look like it could not be tuned...
you might consider posting your query here.

anyhow, the SqlCommand object has a CommandTimeout property, which is by default at 30 (seconds), hence 36 seconds will timeout.
you could increase that value.
0
 

Author Comment

by:Fraser_Admin
ID: 18882030
what does fully turned mean?

this is a report that i setup that the user can pick tons of different options, so it would be difficult to have it setup as a job.

the report normally works fine, but i wanted to do a report for all 2006 data, and that is when it runs in 36 seconds on management studio.

I will try to increase that value, but is there a different option I should try?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18883637
>so it would be difficult to have it setup as a job.
why? you can fully automate setup of a job using existing stored procedures in the msdb database.


> but is there a different option I should try?
apart from tuning, increasing time or running the job async, no, there is no other option
0
 

Author Comment

by:Fraser_Admin
ID: 18884946
ok how do i go about tuning?  I'm using several tables in this job.  I have no clue what extra indicies may help.  Is there a way I can run my sp, and somehow have a suggestion of what may help to speed it up?  I used index tuning wizard on sql server 2000, havn't done it yet on 2005.  but when i did it on 2000, i didn't find it worked very good for dynamic queries, since things users may want to see can change from query to query.  should indicies be based on what columns you want to see, or on what columns you have in your where clause.  I've never really understood how they worked.

I just put in a box that will allow the user to up the time if it times out.  Is there a way I can cancel the request so I don't have to wait for the timeout.  I found while I was testing, that I may have screwed up on my options, but I have to wait for the timeout to come back or for it to finish.  Is there any code I could setup on a button click event that would send a cancel request for that job?

No the job option will not work.  The query is dynamic based on what the user selects.  I have no way of knowing what a user will select for options.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18885102
in case of dynamic queries, you have basically to play/tune each possible version of the sql.

>should indicies be based on what columns you want to see, or on what columns you have in your where clause.
indexes are a complex matter, but in short, they should contain the columns in the where / join clause.
however, depending on the queries, group by and order by are also potential candidates.
and in some cases, the fields to be returned might also be of interest.
it's sometimes a matter of compromise.

however, in sql 2000, the query analyser has the "show execution plan" menu, which generated a graphical execution plan when running the query. based on that, you could see which part of the query takes the most of the time/resources.
for example, having on the right side a big large arrow, while in the end result you have only a thin line shows that indexes are missing.


>Is there a way I can cancel the request so I don't have to wait for the timeout.
I am not that advanced in .net programming yet, but I assume it is possible if you program with threads.

>No the job option will not work. The query is dynamic based on what the user selects.
you can setup a job dynamically also, including the job steps and it's contained sql, and mark it to get deleted automatically once it completed.
>I have no way of knowing what a user will select for options.
once he hits submit, you know :-)



0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Fraser_Admin
ID: 18885155
yes i know once he hits submit, but what advantage is there to doing a job once he hits submit?  right now i just execute a sp based on the values he selected.

i don't have 2000 anymore, i only have 2005, is there a way to show the execution plan in that?

so i'm still no further ahead with indicies.  i have no clue when you should make one, and when you shouldn't.  how can i tell based on a select statement where an index would help and on what columns?

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18885282
>but what advantage is there to doing a job once he hits submit?
your application does not get blocked at all, and you don't need any complex programming in regards to that.

>i don't have 2000 anymore, i only have 2005, is there a way to show the execution plan in that?
I don't have sql 2005 here...

>so i'm still no further ahead with indicies.  i have no clue when you should make one, and when you shouldn't.  how can i tell based on a select statement where an index would help and on what columns?

as I told you, to start with, the coluumns in the WHERE clause are the first candidates for the indexes.

here a sample:
SELECT * FROM yourtable WHERE col1 = 'A' AND col2 = 'B'

for that query, 4 indexes structures are possible.
index1 ( col1 )
index2 ( col2 )
index3 ( col1, col2 )
index4 ( col2, col1 )

all 4 indexes will help, but taking into account only the above query, index3 and index5 are the best candidates.
to further decide on the choice of the indexes, one has to know about the cardinality of the values of col1 and col2.
if col1 has only few distinct values, then col2 should be the first column in the index -> ie index4 would be the best choice.





0
 

Author Comment

by:Fraser_Admin
ID: 18888637
oh wow, i have so many possibilities, i wouldn't know where to begin.

i have a scaling application, so users can select anything under the sun to query on.  source property, destination property, zone, product type, product group, product grade, product contract, hauling contract, loading contract, unloading contract...etc.etc.etc.

i also implement parents on these fields so a user can group.  ie. product group has a product category and a product form.  then a product form has a product form family.  those are each in a lookup table.  so here is my structure...

lookup product group
product group id
product group name
product category id
product form id

lookup product form
product form id
product form name
product form family id

lookup produt form family
product form family id
product form family name

so sometimes the user want to do a report based on the group, sometimes based on the category, form or form family.

how would you go about setting up indicies on this type of structure.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18888973
>how would you go about setting up indicies on this type of structure.
you have to take basically each individual query and check if the performance / explain plan is ok.
0
 

Author Comment

by:Fraser_Admin
ID: 18897955
so you could potentially end up with several indicies?

but i thought that they were also a performance issue, when you have too many??
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18898032
>so you could potentially end up with several indicies?
yes

>but i thought that they were also a performance issue, when you have too many??
yes, but having 1-3 indexes is completely normal, and usually the max you need.
having more than 4 indexes is usually result of incorrect database design, although the performance issues are still lower with 6-7 indexes. but again, this can be an exact science (takes alot of time) or more of a "pi times watch" rule, estimating based on experience...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now