Query performence

Hi all.
I'm confuse.
I have a sporadic problem.
I have an application that execute a query on my SQL Server. Normally,
the query takes 1 or 2 secondes to execute. The thing that I have some
difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
execute the same query. I already sent a post about this problem few
month ago and many reply told me to insert a With Recompile option in my
stored procedure. I have insert them but today, with no result.
I think that somebody will tell me to seach for LOCK but I haven't one.
Here you can see the result that I have receive in SQL Profiler when I
have execute the query. The first line represent the result that takes 1
or 2 secondes and the second line is the result that takes 2 or 3
minutes.
Event Class Text Application
Name NT User Name SQL User Name CPU Reads Writes
Duration SPID Start Time
+SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
Analyzer jonathan.chretien 16 219 0
93 66 15:36:07.757
FROM TBLECRAN02 WITH (NOLOCK)
WHERE F4941_LDNM = 10992
ORDER BY ORDRE02
+SQL:BatchCompleted SELECT DISTINCT ORDRE02
Ami002 P-80-125 22797 196112
72 58
FROM TBLECRAN02 WITH (NOLOCK)
WHERE F4941_LDNM = 10992
ORDER BY ORDRE02
We use MS SQL Server 7.0 SP2 Hotfix 851.
Can somebody help me '
(Sorry for my english, I'm french)
Jonathan Chretien
Analyst/Programmerthis may be a shot in the dark, but do you happen to have the table
results open (from the table -- Open Table, Return all Rows)?
Jonathan Chretien wrote:
> Hi all.
> I'm confuse.
> I have a sporadic problem.
> I have an application that execute a query on my SQL Server. Normally,
> the query takes 1 or 2 secondes to execute. The thing that I have some
> difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
> execute the same query. I already sent a post about this problem few
> month ago and many reply told me to insert a With Recompile option in my
> stored procedure. I have insert them but today, with no result.
> I think that somebody will tell me to seach for LOCK but I haven't one.
> Here you can see the result that I have receive in SQL Profiler when I
> have execute the query. The first line represent the result that takes 1
> or 2 secondes and the second line is the result that takes 2 or 3
> minutes.
>
> Event Class Text Application
> Name NT User Name SQL User Name CPU Reads Writes
> Duration SPID Start Time
> +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
> Analyzer jonathan.chretien 16 219 0
> 93 66 15:36:07.757
> FROM TBLECRAN02 WITH (NOLOCK)
> WHERE F4941_LDNM = 10992
> ORDER BY ORDRE02
>
> +SQL:BatchCompleted SELECT DISTINCT ORDRE02
> Ami002 P-80-125 22797 196112
> 72 58
> FROM TBLECRAN02 WITH (NOLOCK)
> WHERE F4941_LDNM = 10992
> ORDER BY ORDRE02
>
> We use MS SQL Server 7.0 SP2 Hotfix 851.
> Can somebody help me '
> (Sorry for my english, I'm french)
>
> Jonathan Chretien
> Analyst/Programmer
>|||No.
Me a écrit :
> this may be a shot in the dark, but do you happen to have the table
> results open (from the table -- Open Table, Return all Rows)?
> Jonathan Chretien wrote:
> > Hi all.
> >
> > I'm confuse.
> >
> > I have a sporadic problem.
> >
> > I have an application that execute a query on my SQL Server. Normally,
> > the query takes 1 or 2 secondes to execute. The thing that I have some
> > difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
> > execute the same query. I already sent a post about this problem few
> > month ago and many reply told me to insert a With Recompile option in my
> > stored procedure. I have insert them but today, with no result.
> >
> > I think that somebody will tell me to seach for LOCK but I haven't one.
> >
> > Here you can see the result that I have receive in SQL Profiler when I
> > have execute the query. The first line represent the result that takes 1
> > or 2 secondes and the second line is the result that takes 2 or 3
> > minutes.
> >
> >
> > Event Class Text Application
> > Name NT User Name SQL User Name CPU Reads Writes
> > Duration SPID Start Time
> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
> > Analyzer jonathan.chretien 16 219 0
> > 93 66 15:36:07.757
> > FROM TBLECRAN02 WITH (NOLOCK)
> > WHERE F4941_LDNM = 10992
> > ORDER BY ORDRE02
> >
> >
> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02
> > Ami002 P-80-125 22797 196112
> > 72 58
> > FROM TBLECRAN02 WITH (NOLOCK)
> > WHERE F4941_LDNM = 10992
> > ORDER BY ORDRE02
> >
> >
> > We use MS SQL Server 7.0 SP2 Hotfix 851.
> >
> > Can somebody help me '
> >
> > (Sorry for my english, I'm french)
> >
> >
> > Jonathan Chretien
> > Analyst/Programmer
> >|||Are you sure that you're server is not getting hammered from time to time? Have you monitored server performance at all?
>--Original Message--
>No.
>Me a =E9crit :
>> this may be a shot in the dark, but do you happen to have the table
>> results open (from the table -- Open Table, Return all Rows)?
>> Jonathan Chretien wrote:
>> > Hi all.
>> >
>> > I'm confuse.
>> >
>> > I have a sporadic problem.
>> >
>> > I have an application that execute a query on my SQL Server. Normally,
>> > the query takes 1 or 2 secondes to execute. The thing that I have some
>> > difficulty to understand, is that sometime, SQL takes 2 or 3 minutes to
>> > execute the same query. I already sent a post about this problem few
>> > month ago and many reply told me to insert a With Recompile option in my
>> > stored procedure. I have insert them but today, with no result.
>> >
>> > I think that somebody will tell me to seach for LOCK but I haven't one.
>> >
>> > Here you can see the result that I have receive in SQL Profiler when I
>> > have execute the query. The first line represent the result that takes 1
>> > or 2 secondes and the second line is the result that takes 2 or 3
>> > minutes.
>> >
>> >
>> > Event Class Text Application
>> > Name NT User Name SQL User Name CPU Reads Writes
>> > Duration SPID Start Time
>> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02 MS SQL Query
>> > Analyzer jonathan.chretien 16 219 0
>> > 93 66 15:36:07.757
>> > FROM TBLECRAN02 WITH (NOLOCK)
>> > WHERE F4941_LDNM =3D 10992
>> > ORDER BY ORDRE02
>> >
>> >
>> > +SQL:BatchCompleted SELECT DISTINCT ORDRE02
>> > Ami002 P-80-
125 22797 196112
>> > 72 58
>> > FROM TBLECRAN02 WITH (NOLOCK)
>> > WHERE F4941_LDNM =3D 10992
>> > ORDER BY ORDRE02
>> >
>> >
>> > We use MS SQL Server 7.0 SP2 Hotfix 851.
>> >
>> > Can somebody help me '
>> >
>> > (Sorry for my english, I'm french)
>> >
>> >
>> > Jonathan Chretien
>> > Analyst/Programmer
>> >
>.
>|||Jonathan
You need to use profiler to capture what is happening. Do
you have either auto close or auto shrink set to on for
the database, as that would explain it. If you do turn
them off.
Regards
John|||Hi CurtM and John.
Thanks for the interrest.
I have start Performance Monitor when the problem occur, the server
wasn't load over 20%.
The only think that I can tell you is that I have made a sp_lock to
seach for some lock and I see that I have an [UPD-STATS] on the same
table that my query is base on. I can't believe that my statistic can
takes 2 or 3 minutes to be update.
I have no Auto close or Auto Shrink enable.
Thanks.
Jonathan Chretien
Analyst/Programmer
John Bandettini a écrit :
> Jonathan
> You need to use profiler to capture what is happening. Do
> you have either auto close or auto shrink set to on for
> the database, as that would explain it. If you do turn
> them off.
> Regards
> John|||Jonathan
If you think it may be update stats, you can turn the
option off and schedule a job to update your stats at a
more suitable time.
Regards
John

query performance issue of Sql server 2005 Mobile edition on device

Executing a select query with left outer joins etc takes .53 seconds to execute on sqlce 2.0.

Same query on sql 2005 mobile ed. takes 11 minutes .

on database having same data.

Sample query

SELECT routes.location,routes.equipment_type, routes.contract_type,

routes.maintenance_interval,routes.bank_description,routes.Unit_Des,

routes.Unit_no,max(task_last_completed.date_completed)as date1,min(case when

task_last_completed.due_date is NULL then getdate()-1 else due_date end) as

due_date FROM routes left outer join tasks on tasks.model = routes.model and

tasks.eqtyp = routes.equipment_type inner join task_by_contract_type on

tasks.task_id = task_by_contract_type.task_id and

task_by_contract_type.contract_type = routes.contract_type and

task_by_contract_type.model = routes.model left outer join

task_last_completed on routes.unit_no = task_last_completed.equipment_Id and

tasks.task_Id = task_last_completed.task_Id WHERE routes.location LIKE

'S153825-01%' group by

routes.location,routes.equipment_type,routes.contract_type,routes.maintenanc

e_interval,routes.bank_description,routes.unit_des,routes.unit_no ORDER BY

routes.location, routes.bank_description, routes.Unit_Des

WHY?

Hi Vijay,

Can you answer the following questions to help us understand your scenario better:

What is the configuration of the device (make/OS version/processor type/processor speed/total memory/available memory for use at the time query was run) on which the query was run against SQL Mobile database?|||

Hi Vijay,

Can you answer the following questions to help us understand your scenario better:

What is the configuration of the device (make/OS version/processor type/processor speed/total memory/available memory for use at the time query was run) on which the query was run against SQL Mobile database?|||

Answer as follows:

1- verizon/Windows Mobile 5.0/os5.1.1700(build 14354.0.1.1)/PXA270-416MHz/49.45MB/~23MB

2 - Yes

3 - Yes as existing schema of Sqlce 2.0 is upgraded to sql mobile 5.0 using upgrade.exe

4 - 9.66 MB sql ce 2.0 and 8.09 MB sql mobile

5 - Running the query through query analyser.

6. can we set buffer pool etc. for query analyser on device.

One thing i have identified later in our datbase it hase number of duplicate records but even then sql ce performance is comparative very high.

Even after deleting duplicate records difference and read that sql mobile deals with the outer joins differently as compared to sqlce 2.0 but even then performance is the issue,

Sql ce 2.0 takes 0.53 Minutes

Sql Mobile takes 1.40 Minutes

Thanks

vijay

Query Performance diff

Hi @.all,

I designed a query in report designer, data tab. This query runs 24 secs, when I execute it from the data tab in report designer.

Publishing the report and running it from the report server. The query needs 40x the time as in report designer.

Looking at the Execution Log, the report needs 800 secs for TimeDataRetrieval.

Why is it so much slower to run the query from report server? The report designer has also to retrieve the data, and runs so much faster.

Thanks for any hints

T

The data tab will only retrieve data, so the report design may be the cause for bad performance. How much time does it take to preview the report in your report designer?

Shyam

|||

I'll try that,

but isn't this time which gets reported in ExecutionLog in Cols TimeProcessing and TimeRendering?

Query Performance

Hi,
I have a query which takes approx 45 sec to execute in Query Analyzer but it
takes more than 60 min to execute in MSRS. What is the reason for it and how
to improve it's performance ?
ThanksHi push:
Could you show us the query?
--
Scott
http://www.OdeToCode.com
On Fri, 20 Aug 2004 02:37:01 -0700, "push"
<push@.discussions.microsoft.com> wrote:
>Hi,
>I have a query which takes approx 45 sec to execute in Query Analyzer but it
>takes more than 60 min to execute in MSRS. What is the reason for it and how
>to improve it's performance ?
>Thanks|||push wrote:
> I have a query which takes approx 45 sec to execute in Query Analyzer but it
> takes more than 60 min to execute in MSRS. What is the reason for it and how
> to improve it's performance ?
I would first try and pinpoint where the time is being taken. Looking
at the ExecutionLog in the Report Server catalog database would tell you
the total row count and how the total report rendering time divides up
between data retrieval, internal processing, and format-specific
rendering. Could you post these numbers as well as a brief description
of the hardware you're running this on? An example query would be nice
as well.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Query Parameter Dialog Box

When I am looking at a dataset and use the "!" execute button I get the pop
up dialog box for parameters. How must I enter my values if i want it to
test multi-value selection?
I've tried
ABC, DEF
'ABC,DEF'
'ABC','DEF'
"ABC","DEF"
ABC;DEF
'ABC';'DEF'
I can't get it to work. Can you only use single values in this view?how do you have the parameter specified? you must use in(@.param) when using
expecting multivalues
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:9E5DF01E-77AA-4D98-8C94-CFEEE2A4E48D@.microsoft.com...
> When I am looking at a dataset and use the "!" execute button I get the
> pop
> up dialog box for parameters. How must I enter my values if i want it to
> test multi-value selection?
> I've tried
> ABC, DEF
> 'ABC,DEF'
> 'ABC','DEF'
> "ABC","DEF"
> ABC;DEF
> 'ABC';'DEF'
> I can't get it to work. Can you only use single values in this view?