Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

.net timeout sql server 2005

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
Fraser_Admin
Asked:
Fraser_Admin
  • 6
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> 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
 
Fraser_AdminAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Fraser_AdminAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Fraser_AdminAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Fraser_AdminAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Fraser_AdminAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now