Need Help: SP fast in QA, slow in APP

I have a strored procedure which takes .016 seconds in Query Analyzer,
but takes 16.8 seconds when run in my ASP.NET application. [yes, you rea
d
that right]
I've run profiler to find this info.
The machine has all the updates and service packs.
Where should I look next for an answer?
Thanks in advance.
Server:
Windows 2000 Server
SQL Server 2000 Standard
SP3I suspect that your problem might be related to parameter sniffing. Here
are a few links:
http://groups-beta.google.com/group...e4a2438bed08aca
http://groups-beta.google.com/group...eb556c8dfb6a82c
Keith
"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you r
ead
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
> Thanks in advance.
> Server:
> Windows 2000 Server
> SQL Server 2000 Standard
> SP3
>
>|||"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you r
ead
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
>
Also look at the SET settings used by ASP.NET. If you have any indexed
views or indexes on computed columns they cannot be used unless your
connection settings are correct.
Also when you say that it runs in .016 seconds in QA, how are you running
it? Just running the body of the stored procedure with hard-coded values is
not the same. You can always capture application's activity from Profiler
and replay it in QA.
David|||David:
Thanks for the response.
To answer your questions:
What I am doing is running Profiler,
1) watching the line " exec mySP_getMatch @.ClientID=2, @.thisOther=1 " on
Profiler, as the web app fires the sp.
2) Cutting and pasting the exact line that was run with ASP.NET into Query
Analyzer
3) Running the query. [returns a value immediately]
Also, the SP is just grabbing values from a table, with indexes where
necessary.
No views or computed columns.
What do you mean by the "SET" settings?
This one has me perplexed.
I've never had a case where performance what night and day when comparing a
query run in asp.net vs. query analyzer.
Thanks,
Rich
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uf2pAhvLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> "Rich Miller" <rooster575@.hotmail.com> wrote in message
> news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
> Also look at the SET settings used by ASP.NET. If you have any indexed
> views or indexes on computed columns they cannot be used unless your
> connection settings are correct.
> Also when you say that it runs in .016 seconds in QA, how are you running
> it? Just running the body of the stored procedure with hard-coded values
> is not the same. You can always capture application's activity from
> Profiler and replay it in QA.
> David
>

Need Help: SP fast in QA, slow in APP

I have a strored procedure which takes .016 seconds in Query Analyzer,
but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
that right]
I've run profiler to find this info.
The machine has all the updates and service packs.
Where should I look next for an answer?
Thanks in advance.
Server:
Windows 2000 Server
SQL Server 2000 Standard
SP3
I suspect that your problem might be related to parameter sniffing. Here
are a few links:
http://groups-beta.google.com/group/...4a2438bed08aca
http://groups-beta.google.com/group/...b556c8dfb6a82c
Keith
"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
> Thanks in advance.
> Server:
> Windows 2000 Server
> SQL Server 2000 Standard
> SP3
>
>
|||"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
>
Also look at the SET settings used by ASP.NET. If you have any indexed
views or indexes on computed columns they cannot be used unless your
connection settings are correct.
Also when you say that it runs in .016 seconds in QA, how are you running
it? Just running the body of the stored procedure with hard-coded values is
not the same. You can always capture application's activity from Profiler
and replay it in QA.
David
|||David:
Thanks for the response.
To answer your questions:
What I am doing is running Profiler,
1) watching the line " exec mySP_getMatch @.ClientID=2, @.thisOther=1 " on
Profiler, as the web app fires the sp.
2) Cutting and pasting the exact line that was run with ASP.NET into Query
Analyzer
3) Running the query. [returns a value immediately]
Also, the SP is just grabbing values from a table, with indexes where
necessary.
No views or computed columns.
What do you mean by the "SET" settings?
This one has me perplexed.
I've never had a case where performance what night and day when comparing a
query run in asp.net vs. query analyzer.
Thanks,
Rich
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uf2pAhvLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> "Rich Miller" <rooster575@.hotmail.com> wrote in message
> news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
> Also look at the SET settings used by ASP.NET. If you have any indexed
> views or indexes on computed columns they cannot be used unless your
> connection settings are correct.
> Also when you say that it runs in .016 seconds in QA, how are you running
> it? Just running the body of the stored procedure with hard-coded values
> is not the same. You can always capture application's activity from
> Profiler and replay it in QA.
> David
>

Need Help: SP fast in QA, slow in APP

I have a strored procedure which takes .016 seconds in Query Analyzer,
but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
that right]
I've run profiler to find this info.
The machine has all the updates and service packs.
Where should I look next for an answer?
Thanks in advance.
Server:
Windows 2000 Server
SQL Server 2000 Standard
SP3I suspect that your problem might be related to parameter sniffing. Here
are a few links:
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/a8f61dc6ac3aedcd/1e4a2438bed08aca?q=parameter+sniffing+group:microsoft.public.sqlserver.*#1e4a2438bed08aca
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/b6bf1ec648ae8815/3eb556c8dfb6a82c?q=parameter+sniffing+group:microsoft.public.sqlserver.*#3eb556c8dfb6a82c
--
Keith
"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
> Thanks in advance.
> Server:
> Windows 2000 Server
> SQL Server 2000 Standard
> SP3
>
>|||"Rich Miller" <rooster575@.hotmail.com> wrote in message
news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>I have a strored procedure which takes .016 seconds in Query Analyzer,
> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
> that right]
> I've run profiler to find this info.
> The machine has all the updates and service packs.
> Where should I look next for an answer?
>
Also look at the SET settings used by ASP.NET. If you have any indexed
views or indexes on computed columns they cannot be used unless your
connection settings are correct.
Also when you say that it runs in .016 seconds in QA, how are you running
it? Just running the body of the stored procedure with hard-coded values is
not the same. You can always capture application's activity from Profiler
and replay it in QA.
David|||David:
Thanks for the response.
To answer your questions:
What I am doing is running Profiler,
1) watching the line " exec mySP_getMatch @.ClientID=2, @.thisOther=1 " on
Profiler, as the web app fires the sp.
2) Cutting and pasting the exact line that was run with ASP.NET into Query
Analyzer
3) Running the query. [returns a value immediately]
Also, the SP is just grabbing values from a table, with indexes where
necessary.
No views or computed columns.
What do you mean by the "SET" settings?
This one has me perplexed.
I've never had a case where performance what night and day when comparing a
query run in asp.net vs. query analyzer.
Thanks,
Rich
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uf2pAhvLFHA.3868@.TK2MSFTNGP10.phx.gbl...
> "Rich Miller" <rooster575@.hotmail.com> wrote in message
> news:OZ8bHOvLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>>I have a strored procedure which takes .016 seconds in Query Analyzer,
>> but takes 16.8 seconds when run in my ASP.NET application. [yes, you read
>> that right]
>> I've run profiler to find this info.
>> The machine has all the updates and service packs.
>> Where should I look next for an answer?
>
> Also look at the SET settings used by ASP.NET. If you have any indexed
> views or indexes on computed columns they cannot be used unless your
> connection settings are correct.
> Also when you say that it runs in .016 seconds in QA, how are you running
> it? Just running the body of the stored procedure with hard-coded values
> is not the same. You can always capture application's activity from
> Profiler and replay it in QA.
> David
>

Need Help, DTS is Running extremely Slow!

Hi guys.

I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).

The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.

The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.

by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status

i have tried everything and cant seem to get this problem fixed

Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You

GilTo maximize CPU resources, you can define a CPU idle condition for SQL Server Agent. SQL Server Agent uses the CPU idle condition setting to determine the most advantageous time to execute jobs.

For example, you can schedule a daily backup job to occur during CPU idle time and slow production periods.

Before you define jobs to execute during CPU idle time, determine how much CPU the job requires. You can use SQL Server Profiler or Windows NT Performance Monitor to monitor server traffic and collect statistics. You can use the information you gather to set the CPU idle time percentage.

Define the CPU idle condition as a percentage below which the average CPU usage must remain for a specified time. Next, set the amount of time. When this time has been exceeded, SQL Server Agent starts all jobs that have a CPU idle time schedule.

But test it before deploying on production.|||Thnx for replying Satya,

I am afraid this ahs nothing to do with CPU resources. Even when i start the job manualy when there is no activity at all an the server the DTS runs Very Very Slow.

I am prety sure that there is somthing else to be done , allthow i will try your advise.

Any suggestions?|||The are a number of thing you can do. It's specific to your job. First of all who has control. AS400 or the database(SQL). I don't know of a DTS package in AS400. So start there, next, you might want to look at the parameters manually vs job. Are there any environmental issues to look at. Such as: numbers of records to commit, indexes, maintenance and backups, buffers size, Look it up on books online SQL Server.|||AS400?

You mean DB2, no?

Have the DB2 dba set up an unload then copy the data to your box...

Then bcp the data in

I know that doesn't address your problem...

What's the execution step in the job look like?

One difference in your methods is the context of which ID is being used.

When you execute it from EM, it's under the context of your id, AND your location.

From the job, it's under the context of the agent AND the server drive mappings..

I'm guessing it's a network issue...

Did the job ever finish?|||Thnx alll 4 replying,

But i am afriad that that is not the problem...

I think i have solved it though...

The Client Access has an option to translate data by a DLL.
it was using one...dont know its name.

removing this dll solved the problem and the job is executing fine now.

thank you all for helping

Gil