[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How can I find the best optimalization for the query performance?

Oscar

3/25/2007 3:49:00 PM

In case I run any SQL query within my VB application for the first time
after that I started my application, the performance of SQL server 2005 to
create the recordset is the slowest. For example it takes 30 seconds to
process the following query :

"SELECT ID_wrn FROM tblArbCtr WHERE Contract = 0 AND ((datumStart IS NULL
AND datumEind IS NULL ) OR (datumStart IS NULL AND datumEind >= " &
beginDate & ") OR (datumEind IS NULL AND datumStart <= " & eindDate & ") OR
(datumStart <= " & eindDate & " AND datumEind >= " & beginDate & ")) GROUP
BY ID_wrn"

OR

"SELECT cred FROM tblSalary WHERE ID_wrn=" & ID_emp & " AND year=" & yearnr
& " AND weeknr=" & weeknumber & " ORDER BY ID DESC")

However it shows that in case I ran the same query a couple of times again
and again, after the 3rd or 4th attempt, the processing time takes only 2
seconds, which is a very good performance improvement of course. However
when I continue to do other queries by navigating to another process and
return to the original process again, it takes about 30 seconds again to
perform the original query. Then when running the query again and again it
takes only 2 seconds to perform the query. So it looks like SQL server is
capable of finding an optimal way to process the query when the same query
is fired again and again, however, it forgets this optimalisation once it
receives another queries subsequently.

How can I investigate and find the way to have SQL Server perform such
queries always the best? Particularly, I want to find out how SQL finds out
a better way to improve the query in order to ensure the best performance
also in case of the first time the query has to be processed.

regards,
Oscar
10 Answers

Uri Dimant

3/25/2007 4:12:00 PM

0

Oscar
http://www.sql-server-performance.com/faq/sqlviewfaq.as...




"Oscar" <oku@xs4all.nl> wrote in message
news:%23o8CYUvbHHA.2316@TK2MSFTNGP04.phx.gbl...
> In case I run any SQL query within my VB application for the first time
> after that I started my application, the performance of SQL server 2005 to
> create the recordset is the slowest. For example it takes 30 seconds to
> process the following query :
>
> "SELECT ID_wrn FROM tblArbCtr WHERE Contract = 0 AND ((datumStart IS NULL
> AND datumEind IS NULL ) OR (datumStart IS NULL AND datumEind >= " &
> beginDate & ") OR (datumEind IS NULL AND datumStart <= " & eindDate & ")
> OR (datumStart <= " & eindDate & " AND datumEind >= " & beginDate & "))
> GROUP BY ID_wrn"
>
> OR
>
> "SELECT cred FROM tblSalary WHERE ID_wrn=" & ID_emp & " AND year=" &
> yearnr & " AND weeknr=" & weeknumber & " ORDER BY ID DESC")
>
> However it shows that in case I ran the same query a couple of times again
> and again, after the 3rd or 4th attempt, the processing time takes only 2
> seconds, which is a very good performance improvement of course. However
> when I continue to do other queries by navigating to another process and
> return to the original process again, it takes about 30 seconds again to
> perform the original query. Then when running the query again and again it
> takes only 2 seconds to perform the query. So it looks like SQL server is
> capable of finding an optimal way to process the query when the same query
> is fired again and again, however, it forgets this optimalisation once it
> receives another queries subsequently.
>
> How can I investigate and find the way to have SQL Server perform such
> queries always the best? Particularly, I want to find out how SQL finds
> out a better way to improve the query in order to ensure the best
> performance also in case of the first time the query has to be processed.
>
> regards,
> Oscar
>
>
>


Oscar

3/25/2007 5:24:00 PM

0

Uri,

The link deals with stored procedures while I don't have any stored
procedures within the SQL Server Database. The queries are run from a VB
application that's all. All I see is that SQL Server sometimes is able to
process the same query much much faster as it does when the query is
processed the first time.

Oscar


"Uri Dimant" <urid@iscar.co.il> schreef in bericht
news:%23HMc1hvbHHA.4656@TK2MSFTNGP03.phx.gbl...
> Oscar
> http://www.sql-server-performance.com/faq/sqlviewfaq.as...
>
>
>
>
> "Oscar" <oku@xs4all.nl> wrote in message
> news:%23o8CYUvbHHA.2316@TK2MSFTNGP04.phx.gbl...
>> In case I run any SQL query within my VB application for the first time
>> after that I started my application, the performance of SQL server 2005
>> to create the recordset is the slowest. For example it takes 30 seconds
>> to process the following query :
>>
>> "SELECT ID_wrn FROM tblArbCtr WHERE Contract = 0 AND ((datumStart IS
>> NULL AND datumEind IS NULL ) OR (datumStart IS NULL AND datumEind >= " &
>> beginDate & ") OR (datumEind IS NULL AND datumStart <= " & eindDate & ")
>> OR (datumStart <= " & eindDate & " AND datumEind >= " & beginDate & "))
>> GROUP BY ID_wrn"
>>
>> OR
>>
>> "SELECT cred FROM tblSalary WHERE ID_wrn=" & ID_emp & " AND year=" &
>> yearnr & " AND weeknr=" & weeknumber & " ORDER BY ID DESC")
>>
>> However it shows that in case I ran the same query a couple of times
>> again and again, after the 3rd or 4th attempt, the processing time takes
>> only 2 seconds, which is a very good performance improvement of course.
>> However when I continue to do other queries by navigating to another
>> process and return to the original process again, it takes about 30
>> seconds again to perform the original query. Then when running the query
>> again and again it takes only 2 seconds to perform the query. So it looks
>> like SQL server is capable of finding an optimal way to process the query
>> when the same query is fired again and again, however, it forgets this
>> optimalisation once it receives another queries subsequently.
>>
>> How can I investigate and find the way to have SQL Server perform such
>> queries always the best? Particularly, I want to find out how SQL finds
>> out a better way to improve the query in order to ensure the best
>> performance also in case of the first time the query has to be processed.
>>
>> regards,
>> Oscar
>>
>>
>>
>
>


Tibor Karaszi

3/25/2007 6:08:00 PM

0

I'd use Profiler to catch the execution plan, determine if the plans differs and if so, why. Also,
blocking might be a cause.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"Oscar" <oku@xs4all.nl> wrote in message news:elGuxJwbHHA.4140@TK2MSFTNGP06.phx.gbl...
> Uri,
>
> The link deals with stored procedures while I don't have any stored procedures within the SQL
> Server Database. The queries are run from a VB application that's all. All I see is that SQL
> Server sometimes is able to process the same query much much faster as it does when the query is
> processed the first time.
>
> Oscar
>
>
> "Uri Dimant" <urid@iscar.co.il> schreef in bericht news:%23HMc1hvbHHA.4656@TK2MSFTNGP03.phx.gbl...
>> Oscar
>> http://www.sql-server-performance.com/faq/sqlviewfaq.as...
>>
>>
>>
>>
>> "Oscar" <oku@xs4all.nl> wrote in message news:%23o8CYUvbHHA.2316@TK2MSFTNGP04.phx.gbl...
>>> In case I run any SQL query within my VB application for the first time after that I started my
>>> application, the performance of SQL server 2005 to create the recordset is the slowest. For
>>> example it takes 30 seconds to process the following query :
>>>
>>> "SELECT ID_wrn FROM tblArbCtr WHERE Contract = 0 AND ((datumStart IS NULL AND datumEind IS
>>> NULL ) OR (datumStart IS NULL AND datumEind >= " & beginDate & ") OR (datumEind IS NULL AND
>>> datumStart <= " & eindDate & ") OR (datumStart <= " & eindDate & " AND datumEind >= " &
>>> beginDate & ")) GROUP BY ID_wrn"
>>>
>>> OR
>>>
>>> "SELECT cred FROM tblSalary WHERE ID_wrn=" & ID_emp & " AND year=" & yearnr & " AND weeknr=" &
>>> weeknumber & " ORDER BY ID DESC")
>>>
>>> However it shows that in case I ran the same query a couple of times again and again, after the
>>> 3rd or 4th attempt, the processing time takes only 2 seconds, which is a very good performance
>>> improvement of course. However when I continue to do other queries by navigating to another
>>> process and return to the original process again, it takes about 30 seconds again to perform the
>>> original query. Then when running the query again and again it takes only 2 seconds to perform
>>> the query. So it looks like SQL server is capable of finding an optimal way to process the query
>>> when the same query is fired again and again, however, it forgets this optimalisation once it
>>> receives another queries subsequently.
>>>
>>> How can I investigate and find the way to have SQL Server perform such queries always the best?
>>> Particularly, I want to find out how SQL finds out a better way to improve the query in order to
>>> ensure the best performance also in case of the first time the query has to be processed.
>>>
>>> regards,
>>> Oscar
>>>
>>>
>>>
>>
>>
>
>

Oscar

3/25/2007 6:39:00 PM

0

Hi Tibor,

since it is always much faster after 2-3 requeries I don't think blocking
might be an issue.
In Profiler I only see rows with Eventclass BatchStarting and
BatchCompleted, in TextData it shows the query syntax. In case that the
query takes too much time I see that there are 2 tabels which contribute
with a rather high duration to the process time while they are almost empty.

How can I catch the execution plan within the Profiler?




"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef
in bericht news:eP%23tKiwbHHA.3408@TK2MSFTNGP03.phx.gbl...
> I'd use Profiler to catch the execution plan, determine if the plans
> differs and if so, why. Also, blocking might be a cause.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://sqlblog.com/blogs/tib...
>
>
> "Oscar" <oku@xs4all.nl> wrote in message
> news:elGuxJwbHHA.4140@TK2MSFTNGP06.phx.gbl...
>> Uri,
>>
>> The link deals with stored procedures while I don't have any stored
>> procedures within the SQL Server Database. The queries are run from a VB
>> application that's all. All I see is that SQL Server sometimes is able to
>> process the same query much much faster as it does when the query is
>> processed the first time.
>>
>> Oscar
>>
>>
>> "Uri Dimant" <urid@iscar.co.il> schreef in bericht
>> news:%23HMc1hvbHHA.4656@TK2MSFTNGP03.phx.gbl...
>>> Oscar
>>> http://www.sql-server-performance.com/faq/sqlviewfaq.as...
>>>
>>>
>>>
>>>
>>> "Oscar" <oku@xs4all.nl> wrote in message
>>> news:%23o8CYUvbHHA.2316@TK2MSFTNGP04.phx.gbl...
>>>> In case I run any SQL query within my VB application for the first time
>>>> after that I started my application, the performance of SQL server 2005
>>>> to create the recordset is the slowest. For example it takes 30 seconds
>>>> to process the following query :
>>>>
>>>> "SELECT ID_wrn FROM tblArbCtr WHERE Contract = 0 AND ((datumStart IS
>>>> NULL AND datumEind IS NULL ) OR (datumStart IS NULL AND datumEind >= "
>>>> & beginDate & ") OR (datumEind IS NULL AND datumStart <= " & eindDate &
>>>> ") OR (datumStart <= " & eindDate & " AND datumEind >= " & beginDate &
>>>> ")) GROUP BY ID_wrn"
>>>>
>>>> OR
>>>>
>>>> "SELECT cred FROM tblSalary WHERE ID_wrn=" & ID_emp & " AND year=" &
>>>> yearnr & " AND weeknr=" & weeknumber & " ORDER BY ID DESC")
>>>>
>>>> However it shows that in case I ran the same query a couple of times
>>>> again and again, after the 3rd or 4th attempt, the processing time
>>>> takes only 2 seconds, which is a very good performance improvement of
>>>> course. However when I continue to do other queries by navigating to
>>>> another process and return to the original process again, it takes
>>>> about 30 seconds again to perform the original query. Then when running
>>>> the query again and again it takes only 2 seconds to perform the query.
>>>> So it looks like SQL server is capable of finding an optimal way to
>>>> process the query when the same query is fired again and again,
>>>> however, it forgets this optimalisation once it receives another
>>>> queries subsequently.
>>>>
>>>> How can I investigate and find the way to have SQL Server perform such
>>>> queries always the best? Particularly, I want to find out how SQL finds
>>>> out a better way to improve the query in order to ensure the best
>>>> performance also in case of the first time the query has to be
>>>> processed.
>>>>
>>>> regards,
>>>> Oscar
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>


Tibor Karaszi

3/25/2007 7:15:00 PM

0

Check "Show all events", then you can see all the events that are available. For instance, lots of
events in the Performance group.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"Oscar" <oku@xs4all.nl> wrote in message news:%23OH9KzwbHHA.2408@TK2MSFTNGP02.phx.gbl...
> Hi Tibor,
>
> since it is always much faster after 2-3 requeries I don't think blocking might be an issue.
> In Profiler I only see rows with Eventclass BatchStarting and BatchCompleted, in TextData it shows
> the query syntax. In case that the query takes too much time I see that there are 2 tabels which
> contribute with a rather high duration to the process time while they are almost empty.
>
> How can I catch the execution plan within the Profiler?
>
>
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef in bericht
> news:eP%23tKiwbHHA.3408@TK2MSFTNGP03.phx.gbl...
>> I'd use Profiler to catch the execution plan, determine if the plans differs and if so, why.
>> Also, blocking might be a cause.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://sqlblog.com/blogs/tib...
>>
>>
>> "Oscar" <oku@xs4all.nl> wrote in message news:elGuxJwbHHA.4140@TK2MSFTNGP06.phx.gbl...
>>> Uri,
>>>
>>> The link deals with stored procedures while I don't have any stored procedures within the SQL
>>> Server Database. The queries are run from a VB application that's all. All I see is that SQL
>>> Server sometimes is able to process the same query much much faster as it does when the query is
>>> processed the first time.
>>>
>>> Oscar
>>>
>>>
>>> "Uri Dimant" <urid@iscar.co.il> schreef in bericht
>>> news:%23HMc1hvbHHA.4656@TK2MSFTNGP03.phx.gbl...
>>>> Oscar
>>>> http://www.sql-server-performance.com/faq/sqlviewfaq.as...
>>>>
>>>>
>>>>
>>>>
>>>> "Oscar" <oku@xs4all.nl> wrote in message news:%23o8CYUvbHHA.2316@TK2MSFTNGP04.phx.gbl...
>>>>> In case I run any SQL query within my VB application for the first time after that I started
>>>>> my application, the performance of SQL server 2005 to create the recordset is the slowest. For
>>>>> example it takes 30 seconds to process the following query :
>>>>>
>>>>> "SELECT ID_wrn FROM tblArbCtr WHERE Contract = 0 AND ((datumStart IS NULL AND datumEind IS
>>>>> NULL ) OR (datumStart IS NULL AND datumEind >= " & beginDate & ") OR (datumEind IS NULL AND
>>>>> datumStart <= " & eindDate & ") OR (datumStart <= " & eindDate & " AND datumEind >= " &
>>>>> beginDate & ")) GROUP BY ID_wrn"
>>>>>
>>>>> OR
>>>>>
>>>>> "SELECT cred FROM tblSalary WHERE ID_wrn=" & ID_emp & " AND year=" & yearnr & " AND weeknr=" &
>>>>> weeknumber & " ORDER BY ID DESC")
>>>>>
>>>>> However it shows that in case I ran the same query a couple of times again and again, after
>>>>> the 3rd or 4th attempt, the processing time takes only 2 seconds, which is a very good
>>>>> performance improvement of course. However when I continue to do other queries by navigating
>>>>> to another process and return to the original process again, it takes about 30 seconds again
>>>>> to perform the original query. Then when running the query again and again it takes only 2
>>>>> seconds to perform the query. So it looks like SQL server is capable of finding an optimal way
>>>>> to process the query when the same query is fired again and again, however, it forgets this
>>>>> optimalisation once it receives another queries subsequently.
>>>>>
>>>>> How can I investigate and find the way to have SQL Server perform such queries always the
>>>>> best? Particularly, I want to find out how SQL finds out a better way to improve the query in
>>>>> order to ensure the best performance also in case of the first time the query has to be
>>>>> processed.
>>>>>
>>>>> regards,
>>>>> Oscar
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>
>

Hugo Kornelis

3/25/2007 7:21:00 PM

0

On Sun, 25 Mar 2007 17:48:50 +0200, Oscar wrote:

>In case I run any SQL query within my VB application for the first time
>after that I started my application, the performance of SQL server 2005 to
>create the recordset is the slowest. For example it takes 30 seconds to
>process the following query :
(snip)
>However it shows that in case I ran the same query a couple of times again
>and again, after the 3rd or 4th attempt, the processing time takes only 2
>seconds, which is a very good performance improvement of course. However
>when I continue to do other queries by navigating to another process and
>return to the original process again, it takes about 30 seconds again to
>perform the original query. Then when running the query again and again it
>takes only 2 seconds to perform the query.
(snip)

Hi Oscar,

I'm a bit bewildered by the "3rd or 4th" attempt at which performance
improves. The symptoms you describe sound a lot like the effect of
caching, but if that's the case, you should see the faster response time
from the 2nd time you run the query.

In case yoou don't know, caching is the process that SQL Server uses to
keep data that is read from disk in memory. If the same data has to be
read again, it is read directly from memory, which is of corse a lot
faster than reading from disk. However, memory is limited, so if you
start running other queries, requiring other data to be read, the "old"
data will eventually be pushed from memory to make room for other data.
Then, when you execute the query again, it will once more have to be
read from disk first.

But unless you have a very large database, 30 seconds sounds like a long
time for a query. Maybe you should try if you can optimize that by
adding or modifying indexes, or by rewriting the query.

If you need help with that, I recommend you to post your table structure
(as CREATE TABLE statements, including all constraints, indexes, and
properties; you may omit irrelevant columns), some sample data (as
INSERT statements that we can copy, paste and run to recreate your
sample data in our database), the current query, and expected results.
You should also sppecify which version of SQL Server you're running
(7.0, 2000, 2005 - or better yet, post the output of SELECT @@VERSION).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Oscar

3/25/2007 7:50:00 PM

0

I've been checking the query details within the performance group and
couldn't see any difference between the 'slow' and 'fast' run.
Is it normal that SQL Server queries don't perform in their first run since
I see this behaviour in all queries in my application?

Oscar




"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef
in bericht news:eWT0iHxbHHA.4140@TK2MSFTNGP06.phx.gbl...
> Check "Show all events", then you can see all the events that are
> available. For instance, lots of events in the Performance group.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://sqlblog.com/blogs/tib...
>
>
> "Oscar" <oku@xs4all.nl> wrote in message
> news:%23OH9KzwbHHA.2408@TK2MSFTNGP02.phx.gbl...
>> Hi Tibor,
>>
>> since it is always much faster after 2-3 requeries I don't think blocking
>> might be an issue.
>> In Profiler I only see rows with Eventclass BatchStarting and
>> BatchCompleted, in TextData it shows the query syntax. In case that the
>> query takes too much time I see that there are 2 tabels which contribute
>> with a rather high duration to the process time while they are almost
>> empty.
>>
>> How can I catch the execution plan within the Profiler?
>>
>>
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
>> schreef in bericht news:eP%23tKiwbHHA.3408@TK2MSFTNGP03.phx.gbl...
>>> I'd use Profiler to catch the execution plan, determine if the plans
>>> differs and if so, why. Also, blocking might be a cause.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/d...
>>> http://sqlblog.com/blogs/tib...
>>>
>>>
>>> "Oscar" <oku@xs4all.nl> wrote in message
>>> news:elGuxJwbHHA.4140@TK2MSFTNGP06.phx.gbl...
>>>> Uri,
>>>>
>>>> The link deals with stored procedures while I don't have any stored
>>>> procedures within the SQL Server Database. The queries are run from a
>>>> VB application that's all. All I see is that SQL Server sometimes is
>>>> able to process the same query much much faster as it does when the
>>>> query is processed the first time.
>>>>
>>>> Oscar
>>>>
>>>>
>>>> "Uri Dimant" <urid@iscar.co.il> schreef in bericht
>>>> news:%23HMc1hvbHHA.4656@TK2MSFTNGP03.phx.gbl...
>>>>> Oscar
>>>>> http://www.sql-server-performance.com/faq/sqlviewfaq.as...
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> "Oscar" <oku@xs4all.nl> wrote in message
>>>>> news:%23o8CYUvbHHA.2316@TK2MSFTNGP04.phx.gbl...
>>>>>> In case I run any SQL query within my VB application for the first
>>>>>> time after that I started my application, the performance of SQL
>>>>>> server 2005 to create the recordset is the slowest. For example it
>>>>>> takes 30 seconds to process the following query :
>>>>>>
>>>>>> "SELECT ID_wrn FROM tblArbCtr WHERE Contract = 0 AND ((datumStart IS
>>>>>> NULL AND datumEind IS NULL ) OR (datumStart IS NULL AND datumEind >=
>>>>>> " & beginDate & ") OR (datumEind IS NULL AND datumStart <= " &
>>>>>> eindDate & ") OR (datumStart <= " & eindDate & " AND datumEind >= " &
>>>>>> beginDate & ")) GROUP BY ID_wrn"
>>>>>>
>>>>>> OR
>>>>>>
>>>>>> "SELECT cred FROM tblSalary WHERE ID_wrn=" & ID_emp & " AND year=" &
>>>>>> yearnr & " AND weeknr=" & weeknumber & " ORDER BY ID DESC")
>>>>>>
>>>>>> However it shows that in case I ran the same query a couple of times
>>>>>> again and again, after the 3rd or 4th attempt, the processing time
>>>>>> takes only 2 seconds, which is a very good performance improvement of
>>>>>> course. However when I continue to do other queries by navigating to
>>>>>> another process and return to the original process again, it takes
>>>>>> about 30 seconds again to perform the original query. Then when
>>>>>> running the query again and again it takes only 2 seconds to perform
>>>>>> the query. So it looks like SQL server is capable of finding an
>>>>>> optimal way to process the query when the same query is fired again
>>>>>> and again, however, it forgets this optimalisation once it receives
>>>>>> another queries subsequently.
>>>>>>
>>>>>> How can I investigate and find the way to have SQL Server perform
>>>>>> such queries always the best? Particularly, I want to find out how
>>>>>> SQL finds out a better way to improve the query in order to ensure
>>>>>> the best performance also in case of the first time the query has to
>>>>>> be processed.
>>>>>>
>>>>>> regards,
>>>>>> Oscar
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>
>>
>


JXStern

3/25/2007 8:24:00 PM

0

Oscar,

See Hugo's post, it sounds like simple caching.

You can verify this quickly by putting the query into the management
studio (query analyzer, whatever), and first run a "set statistics io
on". Look at the figures for physical reads. It will be high the
first time, and low going to zero on the later executes.

To set it back down to slow, free all buffers by "dbcc
dropcleanbuffers". If it's a big piece of code, clear the compiled
plans by "dbcc freeproccache".

Josh


On Sun, 25 Mar 2007 21:49:46 +0200, "Oscar" <oku@xs4all.nl> wrote:

>I've been checking the query details within the performance group and
>couldn't see any difference between the 'slow' and 'fast' run.
>Is it normal that SQL Server queries don't perform in their first run since
>I see this behaviour in all queries in my application?
>
>Oscar
>
>
>
>
>"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> schreef
>in bericht news:eWT0iHxbHHA.4140@TK2MSFTNGP06.phx.gbl...
>> Check "Show all events", then you can see all the events that are
>> available. For instance, lots of events in the Performance group.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://sqlblog.com/blogs/tib...
>>
>>
>> "Oscar" <oku@xs4all.nl> wrote in message
>> news:%23OH9KzwbHHA.2408@TK2MSFTNGP02.phx.gbl...
>>> Hi Tibor,
>>>
>>> since it is always much faster after 2-3 requeries I don't think blocking
>>> might be an issue.
>>> In Profiler I only see rows with Eventclass BatchStarting and
>>> BatchCompleted, in TextData it shows the query syntax. In case that the
>>> query takes too much time I see that there are 2 tabels which contribute
>>> with a rather high duration to the process time while they are almost
>>> empty.
>>>
>>> How can I catch the execution plan within the Profiler?
>>>
>>>
>>>
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
>>> schreef in bericht news:eP%23tKiwbHHA.3408@TK2MSFTNGP03.phx.gbl...
>>>> I'd use Profiler to catch the execution plan, determine if the plans
>>>> differs and if so, why. Also, blocking might be a cause.
>>>>
>>>> --
>>>> Tibor Karaszi, SQL Server MVP
>>>> http://www.karaszi.com/sqlserver/d...
>>>> http://sqlblog.com/blogs/tib...
>>>>
>>>>
>>>> "Oscar" <oku@xs4all.nl> wrote in message
>>>> news:elGuxJwbHHA.4140@TK2MSFTNGP06.phx.gbl...
>>>>> Uri,
>>>>>
>>>>> The link deals with stored procedures while I don't have any stored
>>>>> procedures within the SQL Server Database. The queries are run from a
>>>>> VB application that's all. All I see is that SQL Server sometimes is
>>>>> able to process the same query much much faster as it does when the
>>>>> query is processed the first time.
>>>>>
>>>>> Oscar
>>>>>
>>>>>
>>>>> "Uri Dimant" <urid@iscar.co.il> schreef in bericht
>>>>> news:%23HMc1hvbHHA.4656@TK2MSFTNGP03.phx.gbl...
>>>>>> Oscar
>>>>>> http://www.sql-server-performance.com/faq/sqlviewfaq.as...
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> "Oscar" <oku@xs4all.nl> wrote in message
>>>>>> news:%23o8CYUvbHHA.2316@TK2MSFTNGP04.phx.gbl...
>>>>>>> In case I run any SQL query within my VB application for the first
>>>>>>> time after that I started my application, the performance of SQL
>>>>>>> server 2005 to create the recordset is the slowest. For example it
>>>>>>> takes 30 seconds to process the following query :
>>>>>>>
>>>>>>> "SELECT ID_wrn FROM tblArbCtr WHERE Contract = 0 AND ((datumStart IS
>>>>>>> NULL AND datumEind IS NULL ) OR (datumStart IS NULL AND datumEind >=
>>>>>>> " & beginDate & ") OR (datumEind IS NULL AND datumStart <= " &
>>>>>>> eindDate & ") OR (datumStart <= " & eindDate & " AND datumEind >= " &
>>>>>>> beginDate & ")) GROUP BY ID_wrn"
>>>>>>>
>>>>>>> OR
>>>>>>>
>>>>>>> "SELECT cred FROM tblSalary WHERE ID_wrn=" & ID_emp & " AND year=" &
>>>>>>> yearnr & " AND weeknr=" & weeknumber & " ORDER BY ID DESC")
>>>>>>>
>>>>>>> However it shows that in case I ran the same query a couple of times
>>>>>>> again and again, after the 3rd or 4th attempt, the processing time
>>>>>>> takes only 2 seconds, which is a very good performance improvement of
>>>>>>> course. However when I continue to do other queries by navigating to
>>>>>>> another process and return to the original process again, it takes
>>>>>>> about 30 seconds again to perform the original query. Then when
>>>>>>> running the query again and again it takes only 2 seconds to perform
>>>>>>> the query. So it looks like SQL server is capable of finding an
>>>>>>> optimal way to process the query when the same query is fired again
>>>>>>> and again, however, it forgets this optimalisation once it receives
>>>>>>> another queries subsequently.
>>>>>>>
>>>>>>> How can I investigate and find the way to have SQL Server perform
>>>>>>> such queries always the best? Particularly, I want to find out how
>>>>>>> SQL finds out a better way to improve the query in order to ensure
>>>>>>> the best performance also in case of the first time the query has to
>>>>>>> be processed.
>>>>>>>
>>>>>>> regards,
>>>>>>> Oscar
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>

Oscar

3/25/2007 10:30:00 PM

0

Hi Hugo,

thanks for your detailed reply.
I think you're right since it looks like it responds to caching.

So I'd better focus on the table/query itself. The tables concerned were
converted from an MS Access database and are almost empty except of one
table containing about 1700 records. As you can see, the query is a simple
one and I don't expect the query to be the problem.

Oscar





"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> schreef in bericht
news:mhid03tsj146qvd2s0s6c4pdpcfl8vpjjj@4ax.com...
> On Sun, 25 Mar 2007 17:48:50 +0200, Oscar wrote:
>
>>In case I run any SQL query within my VB application for the first time
>>after that I started my application, the performance of SQL server 2005 to
>>create the recordset is the slowest. For example it takes 30 seconds to
>>process the following query :
> (snip)
>>However it shows that in case I ran the same query a couple of times again
>>and again, after the 3rd or 4th attempt, the processing time takes only 2
>>seconds, which is a very good performance improvement of course. However
>>when I continue to do other queries by navigating to another process and
>>return to the original process again, it takes about 30 seconds again to
>>perform the original query. Then when running the query again and again it
>>takes only 2 seconds to perform the query.
> (snip)
>
> Hi Oscar,
>
> I'm a bit bewildered by the "3rd or 4th" attempt at which performance
> improves. The symptoms you describe sound a lot like the effect of
> caching, but if that's the case, you should see the faster response time
> from the 2nd time you run the query.
>
> In case yoou don't know, caching is the process that SQL Server uses to
> keep data that is read from disk in memory. If the same data has to be
> read again, it is read directly from memory, which is of corse a lot
> faster than reading from disk. However, memory is limited, so if you
> start running other queries, requiring other data to be read, the "old"
> data will eventually be pushed from memory to make room for other data.
> Then, when you execute the query again, it will once more have to be
> read from disk first.
>
> But unless you have a very large database, 30 seconds sounds like a long
> time for a query. Maybe you should try if you can optimize that by
> adding or modifying indexes, or by rewriting the query.
>
> If you need help with that, I recommend you to post your table structure
> (as CREATE TABLE statements, including all constraints, indexes, and
> properties; you may omit irrelevant columns), some sample data (as
> INSERT statements that we can copy, paste and run to recreate your
> sample data in our database), the current query, and expected results.
> You should also sppecify which version of SQL Server you're running
> (7.0, 2000, 2005 - or better yet, post the output of SELECT @@VERSION).
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hug...


Hugo Kornelis

3/27/2007 10:06:00 PM

0

On Mon, 26 Mar 2007 00:29:43 +0200, Oscar wrote:

>Hi Hugo,
>
>thanks for your detailed reply.
>I think you're right since it looks like it responds to caching.
>
>So I'd better focus on the table/query itself. The tables concerned were
>converted from an MS Access database and are almost empty except of one
>table containing about 1700 records. As you can see, the query is a simple
>one and I don't expect the query to be the problem.

Hi Oscar,

To be hones, the query does include some things that can decrease
performance.

OTOH, any query on a 1700-row table should actually finish in subsecond
time, so there might be something fishy.

If you post the information I requested in my previous message, I'll try
if I can find a cause and a cure.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...