There is this sp which is used in the webpage, it takes 3 seconds to execute from the
query analyser, but when it is called from the asp page it ends up in
"Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired "
I know it is an General error and there can be many reasons to it,
but on setting a profiler I found out that the sp is called only when the page timeouts,
meaning for some time there is no activity say for 5 -8 seconds and after that the sp is called and
the page timeouts...
There is no rendering/formatting on the page. what can be the possible cause of it??Whada heck...
the sp just needed a recompile...
can anyone explain why?
no base tables were modified, tables are referenced acrossed two different
databases, WITH (NOLOCK) query hint is used in the sp.|||I've got the same prob. but the asp pages use dynamic SQL rather than SPs. When a user enters credentials into login page and press GO, it takes 3-5 minutes for welcome page to get displayed!!! :mad: When i run trance on the server there's no execution (only one query executes and the second comes after several seconds) being made nor there's any processor usage; what the page is spinning and doing. Is the problem with IIS. ?
Any body had the same experience? How to square this.
Howdy.
Showing posts with label analyser. Show all posts
Showing posts with label analyser. Show all posts
Friday, February 24, 2012
Thursday, February 9, 2012
Affect of deleting error messages
I'm using a unique index on a table with ignore_dup_key to get around using distinct in a stored procedure. In Query analyser, when I issue the stored procedure, you get a message and also the data in the Grid. In a stored procedure call, initiated from MS Access, the message means I have to put additional client side programming to work around it. I know that duplicate keys are ignored; that is why I've created the table with this feature (unique index in combination with ignore_dup_key). So what's with the message. If I delete the Error message 3604 will I still get an error message?I'm still trying to figure out when I would ever want to do this.
What are you trying to do?
From BOL
Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
The table shows when IGNORE_DUP_KEY can be used.|||I'm trying to use a server side solution to improve performance of a stored procedure. I've got a one-to many table with Street names (being the many) where the recno links back to the main table. It is possible for say King St to have a cross street of Kingsway. Hence Recno 13 for both. When I then match back to the main table I'd get multiple rows where I desire 1. To get around this I originally used the distinct clause. It works but is much slower than if I create a unique index with the ignore_dup_key option on a temp table. Problem being is the message. I've explicitly requested this action 'ignore_dup_key' so why tell me 'Duplicate key was ignored.' as if it is an error? We were force to a slower server and now my endusers are complaining about speed, that is why I wish to solve this server side instead of client side. I could write a routine to deal with the error client side but why go this route if I don't have too.|||Select Distinct
But you need to resolve this from a sql perspective...
did you read the quote...
You really should address handling MUCH more closely...you'r going to need to as well as have a general expectance of what the stored procedures are doing and what the expect...|||And that's not an error it's a warning...|||Similar to using a "set nocount" to eliminate an unwanted response to an end user. I'd expected and hoped an equivalent for eliminating an unwanted message from the use of a unique index. Yes I did read "SQL Server issues a warning and ignores the duplicate row" of your message. I wish to eliminate the message as it is unwanted. This is the contents of the message window in Query Analyzer "Server: Msg 3604, Level 16, State 1, Procedure ZZZStreet, Line 15
Duplicate key was ignored." The only way I can think of eliminating this is to delete the error message 3604 from the SQL Box. I've never done it and I am looking for guidance as to whether this will or will not fix the unwanted message from being given. I don't see any downside to this error being gone as the performance improvements should outweigh the loss of a strictly informational message. Maybe there is a downside that I'm unware of, an that's what I'm trying to figure out here.|||funny, I'm using EM here but when I create a table say Table1 with a varchar column (indexed, unique, ignore duplicate key) I am not able to insert duplicate values. From BOL it says: "Ignore duplicate key If you create a unique index, you can set this option to ensure each value in an indexed column is unique.".
Why am I confused?
What are you trying to do?
From BOL
Controls what happens when an attempt is made to insert a duplicate key value into a column that is part of a unique clustered index. If IGNORE_DUP_KEY was specified for the index and an INSERT statement that creates a duplicate key is executed, SQL Server issues a warning and ignores the duplicate row.
If IGNORE_DUP_KEY was not specified for the index, SQL Server issues an error message and rolls back the entire INSERT statement.
The table shows when IGNORE_DUP_KEY can be used.|||I'm trying to use a server side solution to improve performance of a stored procedure. I've got a one-to many table with Street names (being the many) where the recno links back to the main table. It is possible for say King St to have a cross street of Kingsway. Hence Recno 13 for both. When I then match back to the main table I'd get multiple rows where I desire 1. To get around this I originally used the distinct clause. It works but is much slower than if I create a unique index with the ignore_dup_key option on a temp table. Problem being is the message. I've explicitly requested this action 'ignore_dup_key' so why tell me 'Duplicate key was ignored.' as if it is an error? We were force to a slower server and now my endusers are complaining about speed, that is why I wish to solve this server side instead of client side. I could write a routine to deal with the error client side but why go this route if I don't have too.|||Select Distinct
But you need to resolve this from a sql perspective...
did you read the quote...
You really should address handling MUCH more closely...you'r going to need to as well as have a general expectance of what the stored procedures are doing and what the expect...|||And that's not an error it's a warning...|||Similar to using a "set nocount" to eliminate an unwanted response to an end user. I'd expected and hoped an equivalent for eliminating an unwanted message from the use of a unique index. Yes I did read "SQL Server issues a warning and ignores the duplicate row" of your message. I wish to eliminate the message as it is unwanted. This is the contents of the message window in Query Analyzer "Server: Msg 3604, Level 16, State 1, Procedure ZZZStreet, Line 15
Duplicate key was ignored." The only way I can think of eliminating this is to delete the error message 3604 from the SQL Box. I've never done it and I am looking for guidance as to whether this will or will not fix the unwanted message from being given. I don't see any downside to this error being gone as the performance improvements should outweigh the loss of a strictly informational message. Maybe there is a downside that I'm unware of, an that's what I'm trying to figure out here.|||funny, I'm using EM here but when I create a table say Table1 with a varchar column (indexed, unique, ignore duplicate key) I am not able to insert duplicate values. From BOL it says: "Ignore duplicate key If you create a unique index, you can set this option to ensure each value in an indexed column is unique.".
Why am I confused?
Subscribe to:
Posts (Atom)