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,
Don
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
>
|||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[vbcol=seagreen]
>news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
but
>
>.
>
|||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[vbcol=seagreen]
>news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
but
>
>.
>
|||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[vbcol=seagreen]
>news:2ebe301c46b46$639505d0$a301280a@.phx.gbl...
but
>
>.
>
|||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...[vbcol=seagreen]
> the Query plan looks almost identical.
> there is no clustered index.
> thx,
> don
> You said you
> it and if so did you
> message
> but
|||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?[vbcol=seagreen]
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:2e82f01c46b61$6d268fe0$a501280a@.phx.gbl...
I
>
>.
>