Execution time Query analyser vs. asp page = 5 min vs. 2 hours! Help.
-
I have a stored procedure that takes about 5 minutes to run using the Query Alalyser (I know that is a bit long, but there is alot of data being processed). The output of the procedure is only 7 columns and about 20 rows (not much data).
The problem I am having is when calling the procedure from a asp (asp 3.0) page the query takes more than one hour to execute!! Why. How can there be such big difference.
What can I do to fix the problem. I am using sqloledb, dsn-less connection, adodb objects. I am not even looping the result, I only use the getRows() function for the recordset. Can there be a software problem, is the webserver not talking correctly to the database server?
/Erika
-
- did you create the parameters in your app of the same type and length as for the sp ?
- are the parameters used in the sp of the same datatype and length as the columns they are matched to in the queries ?
- maybe add "with recompile" to the create storedprocedure statement. This way it gets recompiled every time (and maybe choosing another path).
- start perfmon capture and examine the executionplan.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
No parameters are used.
-
Does the call from ASP consistently take more then an hour to run? If not maybe you have severe blocking problems.
Are you actually running your stored procedure from ASP and not one with the same name but with the same owner as you use to connect with from ASP?
-
When I first saw that the procedure took so long to run from the ASP page, I temporary altered the procedure to not go over all data and the ASP page loaded ok after a few minutes. So the correct procedure is called, and there seems to be a correct connection.
-
because you use SQL2005 you can capture very nice execution plan information !
Don't get frightened because they are XML, have a look an you may be surprised how much info there is !
You may also want to use SSMS/Query and examine the plan from the create storedproc itself.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I forgot to mention ... What kind of trinsaction isolation level are you using for the connection / command ?
SSMS with uses read committed by default.
I guess your application may be using repeatable read !!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
How much data is returned? Do you try to view the ASP page from another server (maybe the data result set is huge and takes long time to return over the wire) or from the same computer where you try the SP with QA?
-
This query may help out .... If you are sysadmin for the sqlserver.
SELECT
ES
.session_id
, ISNULL(ER.blocking_session_id, 0) as BlockedBy
, ES.login_name
, ES.last_request_start_time
, ES.last_request_end_time
, ES.cpu_time
, ES.[host_name]
, db_name(ER.database_id) as DbName
, ES.program_name
, case ES.transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'RepeatableRead'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else cast(ES.transaction_isolation_level as char(14))
end as TRX_isolation_level
, ES.nt_domain
, ES.nt_user_name
, ES.login_time
, ES.client_interface_name
, ES.status
, ES.memory_usage
, ES.total_scheduled_time
, ES.total_elapsed_time
, ES.reads
, ES.writes
, ES.logical_reads
, ES.is_user_process
, ES.[arithabort]
, ES.[ansi_null_dflt_on]
, ES.[ansi_defaults]
, ES.[ansi_warnings]
, ES.[ansi_padding]
, ES.[ansi_nulls]
, ES.[concat_null_yields_null]
, ES.transaction_isolation_level
, ES.[lock_timeout]
, ES.[deadlock_priority]
, ES.row_count
, ES.prev_error
, ER.start_time
, ER.status
, ER.command
, ER.[user_id]
, ER.wait_type
, ER.wait_time
, ER.last_wait_type
, ER.wait_resource
, ER.open_transaction_count
, ER.open_resultset_count
, ER.cpu_time
, ER.total_elapsed_time
, ER.reads
, ER.writes
, ER.logical_reads
, ER.transaction_isolation_level
, ER.[lock_timeout]
, ER.[deadlock_priority]
, ER.row_count
, ER.nest_level
, ER.executing_managed_code
, EST.dbid
, EST.objectid
, EST.number
, EST.encrypted
, EST.[text]
, EQP.query_plan
-- Blocked by info
, ESBlk.session_id as Blk_session_id
, ESBlk.login_name as Blk_login_name
, ESBlk.nt_domain as Blk_nt_domain
, ESBlk.last_request_start_time as Blk_last_request_start_time
, ESBlk.last_request_end_time as Blk_last_request_end_time
, ESBlk.[host_name] as Blk_host_name
, ESBlk.program_name as Blk_program_name
, ESBlk.status as Blk_status
, ESBlk.cpu_time as Blk_cpu_time
FROM sys.dm_exec_sessions ES
left join sys.dm_exec_requests ER
on ER.session_id = ES.session_id
OUTER Apply sys.dm_exec_sql_text(ER.sql_handle) EST
OUTER Apply sys.dm_exec_query_plan(ER.plan_handle) EQP
left join sys.dm_exec_sessions ESBlk
on ESBlk.session_id = ER.blocking_session_id
order by case when isnull(ER.blocking_session_id,0)=0 then 1 else 0 end, ES.last_request_start_time desc, ES.last_request_end_time desc , ES.login_time desc
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The ASP page resides on a different server than the sql server but very little data is returned(20 rows, 7 columns with only short texts).
-
Is the SP deterministic? (Does it always return the same data?)
I never got an answer if it always takes more then one hour to run, if you only tested once you might have had a temporary problem.
-
I observed the same symptoms a few moths ago. It happened on some procedures and quite sporadically.
After examinig the execution plans for the query launched from the SQL Management Studio and from ASP paegs, I observed that sometimes there were two different ones generated, the one being generated from the ASP page being much worse than the other. (The sporadicity of the problem was later explained by the frequent restart of the SQL Server (which cleared the execution plan cache) )
After weeks of reading, I found out that the ARITH_ABORT setting for the two connections were different. By default the queries launched from the SSMS have this set OFF, but default ADO connections have it on. Try turning it off for the ADO connection.
(Why this setting influences the execution plan generation in such a dramatic way is still unclear (and somehow mystical) for me)...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply