Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and where
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and where
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Friday, March 30, 2012
Relative Performance: Native SQL vs User Functions
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wher
e
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endBecause of the like you are using, a table scan in involved, resulting in
mostly IO.
Try running the same query and function, but using only like to the right of
the search string (select * from addrb4 where custname like 'ar'+'%') and
have an index on custname.
You will find that the difference becomes clearer as soon as more records
can be eliminated by a index seek or scan.
Functions are faster sometimes, and sometimes they destroy performance. It
varies and there is no hard and fast rule.
Regards
Mike
"Sienko" wrote:
> Hi all,
> I'm interested in knowing, for a fact, what the performance overhead of
> using a functional-language based query e.g.
> select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
> is over a conventional SQL statement:
> select * from addrb4 where custname like '%'+'ar'+'%'
> From the execution plan in my SQL2000 query analyser, it shows the
> functional query is slightly better costing the batch of 49.97%. However,
> both queries take 28 seconds to execute. I was expecting the functional
> query to be slower so I'm a bit surprised.
> I know the perfomance of the functional query depends on how complex the
> function is. In my applications, the functions will be quite simple and wh
ere
> they will be complex, a convenctional equivalent will equally complex to
> build.
> Your opinions, experience and test runs are welcome.
> --
> NB: declaration for ufn_stringcontains:
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
>|||"Sienko" <Sienko@.discussions.microsoft.com> wrote in message
news:0ABAE066-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
> create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
> returns bit
> as
> begin
> declare @.ret bit
> if (@.m like '%'+@.s+'%')
> set @.ret=1
> else
> set @.ret=0
> return @.ret
> end
You could speed it up a tad by doing early returns:
if (@.m like '%'+@.s+'%' )
return 1
else
return 0
Personally, I find this style easier to understand, as well.|||The problem is mostly in how you use them. You could inappropriately use a
system function as well and in this case the wildcard prefix only makes the
problem worse.
For example:
SELECT <something>
FROM <some table> AS t1
WHERE DAY(DATEADD(day, 1, t1.<some date column> )) = 12
These are system supplied functions but this query is going to be costly reg
ardless if the date column is indexed or not. User defined functions are no
exception.
In your query, not only are you doing a lousy string search using the wildca
rd prefix, you are attempting to use a function to encapsulate a criteria.
You shouldn't see any difference in performance at all. In this case, the h
aystack is going to swamp in needle differences between straw.
Sincerely,
Anthony Thomas
--
"Sienko" <Sienko@.discussions.microsoft.com> wrote in message news:0ABAE066
-2E11-4679-9AAA-8E9B8A636DD2@.microsoft.com...
Hi all,
I'm interested in knowing, for a fact, what the performance overhead of
using a functional-language based query e.g.
select * from customers where dbo.ufn_stringcontains(custname, 'ar')=1
is over a conventional SQL statement:
select * from addrb4 where custname like '%'+'ar'+'%'
From the execution plan in my SQL2000 query analyser, it shows the
functional query is slightly better costing the batch of 49.97%. However,
both queries take 28 seconds to execute. I was expecting the functional
query to be slower so I'm a bit surprised.
I know the perfomance of the functional query depends on how complex the
function is. In my applications, the functions will be quite simple and wh
ere
they will be complex, a convenctional equivalent will equally complex to
build.
Your opinions, experience and test runs are welcome.
--
NB: declaration for ufn_stringcontains:
create function ufn_stringontains (@.m varchar(8000) , @.s varchar(8000) )
returns bit
as
begin
declare @.ret bit
if (@.m like '%'+@.s+'%')
set @.ret=1
else
set @.ret=0
return @.ret
endsql
Monday, March 12, 2012
Reindexing can cause slowness?
I have a database that I recently reindexed (all tables) and users are
complaining of slow report performance. What could cause this? Is it updating
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
--
Message posted via http://www.sqlmonster.comDid you check (and compare!) execution plans on each server?
A
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database...
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:
>Did you check (and compare!) execution plans on each server?
>A
>>I have a database that I recently reindexed (all tables) and users are
>> complaining of slow report performance. What could cause this? Is it
>[quoted text clipped - 8 lines]
>> Thank you in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200712/1|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via SQLMonster.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>|||If you rebuild an index, the stats are updated automatically.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via SQLMonster.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>
complaining of slow report performance. What could cause this? Is it updating
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
--
Message posted via http://www.sqlmonster.comDid you check (and compare!) execution plans on each server?
A
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database...
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:
>Did you check (and compare!) execution plans on each server?
>A
>>I have a database that I recently reindexed (all tables) and users are
>> complaining of slow report performance. What could cause this? Is it
>[quoted text clipped - 8 lines]
>> Thank you in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200712/1|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via SQLMonster.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>|||If you rebuild an index, the stats are updated automatically.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via SQLMonster.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.sqlmonster.com
>
Reindexing can cause slowness?
I have a database that I recently reindexed (all tables) and users are
complaining of slow report performance. What could cause this? Is it updating
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
Message posted via http://www.droptable.com
Did you check (and compare!) execution plans on each server?
A
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database...
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>Did you check (and compare!) execution plans on each server?
>A
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200712/1
|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics go
out of sync with the index information and degrade performance.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
|||If you rebuild an index, the stats are updated automatically.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
complaining of slow report performance. What could cause this? Is it updating
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
Message posted via http://www.droptable.com
Did you check (and compare!) execution plans on each server?
A
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database...
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>Did you check (and compare!) execution plans on each server?
>A
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200712/1
|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics go
out of sync with the index information and degrade performance.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
|||If you rebuild an index, the stats are updated automatically.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
Reindexing can cause slowness?
I have a database that I recently reindexed (all tables) and users are
complaining of slow report performance. What could cause this? Is it updatin
g
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
Message posted via http://www.droptable.comDid you check (and compare!) execution plans on each server?
A
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database..
.
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>Did you check (and compare!) execution plans on each server?
>A
>
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200712/1|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics g
o
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updat
ing
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server th
at
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>|||If you rebuild an index, the stats are updated automatically.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
complaining of slow report performance. What could cause this? Is it updatin
g
statistics some how? Or rebuilding the cache?
I ran the same complaint reports on the same database on another server that
was not reindexed and the reports ran just fine.
Auto update and auto create statistics are checked in the above databases.
Thank you in advance.
Message posted via http://www.droptable.comDid you check (and compare!) execution plans on each server?
A
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:7d53e85ad059a@.uwe...
>I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>|||I was able to get statistics on the query on the database that completes the
query, I'm still waiting for the query to complete on the problem database..
.
I'll post when finished.
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
>Did you check (and compare!) execution plans on each server?
>A
>
>[quoted text clipped - 8 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200712/1|||Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics g
o
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it updat
ing
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server th
at
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>|||If you rebuild an index, the stats are updated automatically.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:23D02E88-B53F-415C-9DF8-571EC80004B4@.microsoft.com...
Aaron might be correcting me on this ...
But I was talking to Microsoft SQL Eng. about Indexrebuild. I believe you
have to manually rebuild the statics if you do a rebuild or the statistics
go
out of sync with the index information and degrade performance.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"fnadal via droptable.com" wrote:
> I have a database that I recently reindexed (all tables) and users are
> complaining of slow report performance. What could cause this? Is it
> updating
> statistics some how? Or rebuilding the cache?
> I ran the same complaint reports on the same database on another server
> that
> was not reindexed and the reports ran just fine.
> Auto update and auto create statistics are checked in the above databases.
> Thank you in advance.
> --
> Message posted via http://www.droptable.com
>
Subscribe to:
Posts (Atom)