Performance degradation after changing data type

SQL 7.0 NT 4.0
I have a sudden performance degradatation since several
fields in the table were changed from real to decimal.
The table has a nonclustered index on name and date, but
there are 4 other fields that are of type Real and they
are not associated to any index.
If I change those 4 fields to Decimal, the queries take
10 times longer to return data. I've reindexed, but no
improvement.
How can the change to decimal cause a longer query?
The query would be something like:
"select * from xyz where name is whatever"
It would return:
name, date, number1, number2, number3, number4
Thx,
DonTake a look at the query plan to see what it is doing. You said you
reindexed but does this table have a clustered index on it and if so did you
reindex that column?
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
> SQL 7.0 NT 4.0
> I have a sudden performance degradatation since several
> fields in the table were changed from real to decimal.
> The table has a nonclustered index on name and date, but
> there are 4 other fields that are of type Real and they
> are not associated to any index.
> If I change those 4 fields to Decimal, the queries take
> 10 times longer to return data. I've reindexed, but no
> improvement.
> How can the change to decimal cause a longer query?
> The query would be something like:
> "select * from xyz where name is whatever"
> It would return:
> name, date, number1, number2, number3, number4
> Thx,
> Don
>|||One idea would be to stop using SELECT * ... this will cause less of a hit,
though it should not be 10x.
What were the precision and scale that you assigned with DECIMAL? Are they
bigger than they need to be?
Where are you returning this data? Do you notice a difference in Query
Analyzer if you use results to text vs. results to grid? Have you
investigated the query plan with real and the query plan with decimal,
compared them, anything stand out (maybe there are other subtle changes you
weren't aware of)?
I ran this script and couldn't notice any differences at all, but maybe my
data set is just not a large enough sample.
CREATE TABLE foo1(name VARCHAR(32), id INT,
n1 REAL,
n2 REAL,
n3 REAL,
n4 REAL
)
CREATE TABLE foo2(name VARCHAR(32), id INT,
n1 DECIMAL(19,2),
n2 DECIMAL(19,2),
n3 DECIMAL(19,2),
n4 DECIMAL(19,2)
)
GO
SET NOCOUNT ON
INSERT foo1 SELECT 'bob', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo1 SELECT 'ted', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo1 SELECT 'jim', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo1 SELECT 'sal', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo1 SELECT 'kev', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo1 SELECT 'foo', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo2 SELECT 'bob', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo2 SELECT 'ted', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo2 SELECT 'jim', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo2 SELECT 'sal', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo2 SELECT 'kev', 1, 5.53, 4.52, 3.14, 5.67
INSERT foo2 SELECT 'foo', 1, 5.53, 4.52, 3.14, 5.67
GO
SELECT * FROM foo1
SELECT * FROM foo2
GO
DROP TABLE foo1, foo2
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
> SQL 7.0 NT 4.0
> I have a sudden performance degradatation since several
> fields in the table were changed from real to decimal.
> The table has a nonclustered index on name and date, but
> there are 4 other fields that are of type Real and they
> are not associated to any index.
> If I change those 4 fields to Decimal, the queries take
> 10 times longer to return data. I've reindexed, but no
> improvement.
> How can the change to decimal cause a longer query?
> The query would be something like:
> "select * from xyz where name is whatever"
> It would return:
> name, date, number1, number2, number3, number4
> Thx,
> Don
>|||Another point about the clustered index, which Andrew brought up. Did you
try this query with and without an ORDER BY clause, and use the clustered
index column?
Usually, you wouldn't just say SELECT * FROM table but you would want to
specify the order you want the results as well.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
> SQL 7.0 NT 4.0
> I have a sudden performance degradatation since several
> fields in the table were changed from real to decimal.
> The table has a nonclustered index on name and date, but
> there are 4 other fields that are of type Real and they
> are not associated to any index.
> If I change those 4 fields to Decimal, the queries take
> 10 times longer to return data. I've reindexed, but no
> improvement.
> How can the change to decimal cause a longer query?
> The query would be something like:
> "select * from xyz where name is whatever"
> It would return:
> name, date, number1, number2, number3, number4
> Thx,
> Don
>|||the Query plan looks almost identical.
there is no clustered index.
thx,
don
>--Original Message--
>Take a look at the query plan to see what it is doing.
You said you
>reindexed but does this table have a clustered index on
it and if so did you
>reindex that column?
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
>> SQL 7.0 NT 4.0
>> I have a sudden performance degradatation since several
>> fields in the table were changed from real to decimal.
>> The table has a nonclustered index on name and date,
but
>> there are 4 other fields that are of type Real and they
>> are not associated to any index.
>> If I change those 4 fields to Decimal, the queries take
>> 10 times longer to return data. I've reindexed, but no
>> improvement.
>> How can the change to decimal cause a longer query?
>> The query would be something like:
>> "select * from xyz where name is whatever"
>> It would return:
>> name, date, number1, number2, number3, number4
>> Thx,
>> Don
>
>.
>|||There is no clustered index.
The query is actually issued from a C program and
isn't "select *", but is "select field1, field2, etc". I
was wrong to have said "select *"
>--Original Message--
>Another point about the clustered index, which Andrew
brought up. Did you
>try this query with and without an ORDER BY clause, and
use the clustered
>index column?
>Usually, you wouldn't just say SELECT * FROM table but
you would want to
>specify the order you want the results as well.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
>> SQL 7.0 NT 4.0
>> I have a sudden performance degradatation since several
>> fields in the table were changed from real to decimal.
>> The table has a nonclustered index on name and date,
but
>> there are 4 other fields that are of type Real and they
>> are not associated to any index.
>> If I change those 4 fields to Decimal, the queries take
>> 10 times longer to return data. I've reindexed, but no
>> improvement.
>> How can the change to decimal cause a longer query?
>> The query would be something like:
>> "select * from xyz where name is whatever"
>> It would return:
>> name, date, number1, number2, number3, number4
>> Thx,
>> Don
>
>.
>|||Curiously enough the inserts are not slow...with profiler
I can see that they are quick. It's the "select"
statements that are slow.
The queries are executed from a C program and are
not "Select *" ... but are "Select field1, field2, etc"
The data is being returned to our application residing on
our desktop. It passes through and ODBC connection.
Don
>--Original Message--
>One idea would be to stop using SELECT * ... this will
cause less of a hit,
>though it should not be 10x.
>What were the precision and scale that you assigned with
DECIMAL? Are they
>bigger than they need to be?
>Where are you returning this data? Do you notice a
difference in Query
>Analyzer if you use results to text vs. results to
grid? Have you
>investigated the query plan with real and the query plan
with decimal,
>compared them, anything stand out (maybe there are other
subtle changes you
>weren't aware of)?
>I ran this script and couldn't notice any differences at
all, but maybe my
>data set is just not a large enough sample.
>
>CREATE TABLE foo1(name VARCHAR(32), id INT,
> n1 REAL,
> n2 REAL,
> n3 REAL,
> n4 REAL
>)
>CREATE TABLE foo2(name VARCHAR(32), id INT,
> n1 DECIMAL(19,2),
> n2 DECIMAL(19,2),
> n3 DECIMAL(19,2),
> n4 DECIMAL(19,2)
>)
>GO
>SET NOCOUNT ON
>INSERT foo1 SELECT 'bob', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo1 SELECT 'ted', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo1 SELECT 'jim', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo1 SELECT 'sal', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo1 SELECT 'kev', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo1 SELECT 'foo', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo2 SELECT 'bob', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo2 SELECT 'ted', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo2 SELECT 'jim', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo2 SELECT 'sal', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo2 SELECT 'kev', 1, 5.53, 4.52, 3.14, 5.67
>INSERT foo2 SELECT 'foo', 1, 5.53, 4.52, 3.14, 5.67
>GO
>SELECT * FROM foo1
>SELECT * FROM foo2
>GO
>DROP TABLE foo1, foo2
>GO
>
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
>> SQL 7.0 NT 4.0
>> I have a sudden performance degradatation since several
>> fields in the table were changed from real to decimal.
>> The table has a nonclustered index on name and date,
but
>> there are 4 other fields that are of type Real and they
>> are not associated to any index.
>> If I change those 4 fields to Decimal, the queries take
>> 10 times longer to return data. I've reindexed, but no
>> improvement.
>> How can the change to decimal cause a longer query?
>> The query would be something like:
>> "select * from xyz where name is whatever"
>> It would return:
>> name, date, number1, number2, number3, number4
>> Thx,
>> Don
>
>.
>|||Why is there no clustered index? Is there an order by clause? Have you
considered making it a stored procedure instead of ad hoc select from C?
--
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:2e82f01c46b61$6d268fe0$a501280a@.phx.gbl...
> There is no clustered index.
> The query is actually issued from a C program and
> isn't "select *", but is "select field1, field2, etc". I
> was wrong to have said "select *"|||If there is no clustered index then it is a heap. You can not defrag a heap
by reindexing. So chances are you have lots of fragmentation due to the
changes. You should consider adding a clustered index or changing one of
the existing indexes to be clustered. That will defrag the table and the
other indexes for you as it will rebuild all of them. If you issue SET
STATISTICS IO ON what does it show for the logical and physical reads?
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2e82601c46b61$1d941420$a501280a@.phx.gbl...
> the Query plan looks almost identical.
> there is no clustered index.
> thx,
> don
> >--Original Message--
> >Take a look at the query plan to see what it is doing.
> You said you
> >reindexed but does this table have a clustered index on
> it and if so did you
> >reindex that column?
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
> >> SQL 7.0 NT 4.0
> >>
> >> I have a sudden performance degradatation since several
> >> fields in the table were changed from real to decimal.
> >>
> >> The table has a nonclustered index on name and date,
> but
> >> there are 4 other fields that are of type Real and they
> >> are not associated to any index.
> >>
> >> If I change those 4 fields to Decimal, the queries take
> >> 10 times longer to return data. I've reindexed, but no
> >> improvement.
> >>
> >> How can the change to decimal cause a longer query?
> >>
> >> The query would be something like:
> >> "select * from xyz where name is whatever"
> >>
> >> It would return:
> >> name, date, number1, number2, number3, number4
> >>
> >> Thx,
> >> Don
> >>
> >
> >
> >.
> >|||There's no clustered index because there are almost a
hundred million records and updating this table with
1000s of updates would take a long time to reorganize the
index.
yes, there is an order clause.
it's not my C program so i'm not sure what to think about
a sp returning data to C. Is that faster? if it were
faster, i could lobby for a change.
Don
>--Original Message--
>Why is there no clustered index? Is there an order by
clause? Have you
>considered making it a stored procedure instead of ad
hoc select from C?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:2e82f01c46b61$6d268fe0$a501280a@.phx.gbl...
>> There is no clustered index.
>> The query is actually issued from a C program and
>> isn't "select *", but is "select field1, field2, etc".
I
>> was wrong to have said "select *"
>
>.
>|||That depends on what column(s) the clustered index is on and what your fill
factor is. If the clustered index was on a column such as IDENTITY or
Datetime that was chronological it simply appends to the end of the current
page. Without a clustered index there is no way (short of exporting all the
data, truncate and importing) to control fragmentation and row redirection.
If you are ending up with lots of redirects or half empty pages you can
loose performance, especially with range type queries.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2e91901c46b71$ddd5a860$a501280a@.phx.gbl...
> There's no clustered index because there are almost a
> hundred million records and updating this table with
> 1000s of updates would take a long time to reorganize the
> index.
> yes, there is an order clause.
> it's not my C program so i'm not sure what to think about
> a sp returning data to C. Is that faster? if it were
> faster, i could lobby for a change.
> Don
>
> >--Original Message--
> >Why is there no clustered index? Is there an order by
> clause? Have you
> >considered making it a stored procedure instead of ad
> hoc select from C?
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:2e82f01c46b61$6d268fe0$a501280a@.phx.gbl...
> >> There is no clustered index.
> >>
> >> The query is actually issued from a C program and
> >> isn't "select *", but is "select field1, field2, etc".
> I
> >> was wrong to have said "select *"
> >
> >
> >.
> >|||> There's no clustered index because there are almost a
> hundred million records and updating this table with
> 1000s of updates would take a long time to reorganize the
> index.
But you do it ONCE. In the meantime, your users are suffering EVERY TIME
THEY SELECT.
> yes, there is an order clause.
Is there at least a non-clustered index that is being used with this clause?
Do you see table scans, index scans, index seeks, etc. in the execution plan
for the actual query?
It would be useful to show us the *ACTUAL* query you are using. I've made
several assumptions here based on information you've neglected to provide
and insist on being vague about.
> it's not my C program so i'm not sure what to think about
> a sp returning data to C. Is that faster?
Yes, the query plan will be cached, but don't do it until you fix the table!
You also gain a lot in terms of manageability. Think about it, if the query
changes, you just change the stored procedure, instead of recompiling C
code.
A|||This is my guess, too. REAL only takes up 4 bytes, and DECIMAL takes 5,
9, 13, or 17, depending on scale. That means fewer rows will fit on a
data page after the change, or worse yet, after each change - If the
changes were made with 4 successive ALTER TABLE ALTER COLUMN statements
on a full table, it could have created a huge mess. If you made the
type changes in Enterprise Manager, I wouldn't expect the trouble you're
seeing, and I'm probably off base here.
If you did use ALTER TABLE..., and if the table absolutely can't have a
clustered index, it might help (and be faster than creating a clustered
index) to do something like this:
select column1, column2, ...
into MyNewTable
from OldTable
go
-- if that succeeds...
drop table OldTable
go
exec sp_rename N'MyNewTable', N'OldTable'
-- recreate your index
I also think that using ALTER TABLE ALTER COLUMN to revert to REAL will
not help, though it probably won't make things yet worse, either. It's
no surprise that inserts are still fast, since the index shouldn't be
fragmented, and the new data is independent of the fragmentation in the
heap.
Steve Kass
Drew University
Andrew J. Kelly wrote:
>If there is no clustered index then it is a heap. You can not defrag a heap
>by reindexing. So chances are you have lots of fragmentation due to the
>changes. You should consider adding a clustered index or changing one of
>the existing indexes to be clustered. That will defrag the table and the
>other indexes for you as it will rebuild all of them. If you issue SET
>STATISTICS IO ON what does it show for the logical and physical reads?
>
>|||Ok, I've put a clustered index on here. Will monitor for
a few days and then put a user load on here and see how
it holds up.
Thx,
Don
>--Original Message--
>This is my guess, too. REAL only takes up 4 bytes, and
DECIMAL takes 5,
>9, 13, or 17, depending on scale. That means fewer rows
will fit on a
>data page after the change, or worse yet, after each
change - If the
>changes were made with 4 successive ALTER TABLE ALTER
COLUMN statements
>on a full table, it could have created a huge mess. If
you made the
>type changes in Enterprise Manager, I wouldn't expect
the trouble you're
>seeing, and I'm probably off base here.
>If you did use ALTER TABLE..., and if the table
absolutely can't have a
>clustered index, it might help (and be faster than
creating a clustered
>index) to do something like this:
>select column1, column2, ...
>into MyNewTable
>from OldTable
>go
>-- if that succeeds...
>drop table OldTable
>go
>exec sp_rename N'MyNewTable', N'OldTable'
>-- recreate your index
>
>I also think that using ALTER TABLE ALTER COLUMN to
revert to REAL will
>not help, though it probably won't make things yet
worse, either. It's
>no surprise that inserts are still fast, since the index
shouldn't be
>fragmented, and the new data is independent of the
fragmentation in the
>heap.
>Steve Kass
>Drew University
>Andrew J. Kelly wrote:
>>If there is no clustered index then it is a heap. You
can not defrag a heap
>>by reindexing. So chances are you have lots of
fragmentation due to the
>>changes. You should consider adding a clustered index
or changing one of
>>the existing indexes to be clustered. That will defrag
the table and the
>>other indexes for you as it will rebuild all of them.
If you issue SET
>>STATISTICS IO ON what does it show for the logical and
physical reads?
>>
>.
>sql