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
>
>.
>

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...
but[vbcol=seagreen]
>
>.
>|||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...
but[vbcol=seagreen]
>
>.
>|||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...
but[vbcol=seagreen]
>
>.
>|||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?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
><anonymous@.discussions.microsoft.com> wrote in message
> news:2e82f01c46b61$6d268fe0$a501280a@.phx
.gbl...
I[vbcol=seagreen]
>
>.
>

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

Performance Counter Data

I have a couple performance counters logging data to a sql 2000 database.
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use the
DATEADD function and the BOL reference states that character data can be used
in the DATEADD function if it is in a date format. The format of the date is
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get around
it? I have no control over the setup of the table that holds the performance
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!
phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:

> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform any
> date functions against this column in my sql queries. I am trying to use the
> DATEADD function and the BOL reference states that character data can be used
> in the DATEADD function if it is in a date format. The format of the date is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get around
> it? I have no control over the setup of the table that holds the performance
> counter data. It is created automatically when the counter is set to log to
> a database. Thanks for any help!
|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your test
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However if
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
[vbcol=seagreen]
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does your
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your test
> supports that. I used query analyzer to return a small amount of data from
> the column in question, and in the results pane it looks correct. However if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance counter
> seems to be doing something funny as it logs data, but I'm not sure what or
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying to
> run it as a select statement in query analyzer. Thanks for any other ideas!
> "Michael Evanchik" wrote:
|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, if
I go to that cell and delete whatever extra is at the end, I can then perform
date functions. It does not appear to simply be a space, because I can add a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
[vbcol=seagreen]
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pasting
> into notepad or wordpad, no data will be lost that way. Also you can paste
> special i think in excel as pure text but im no excel expert. also does your
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Yeah, I realized the Excel test was no good after I made my last post. I've
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counter,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of the
> dates to a different table that I created, I get the same error. However, if
> I go to that cell and delete whatever extra is at the end, I can then perform
> date functions. It does not appear to simply be a space, because I can add a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to cleanup
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
[vbcol=seagreen]
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
sql

Performance Counter Data

I have a couple performance counters logging data to a sql 2000 database.
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use th
e
DATEADD function and the BOL reference states that character data can be use
d
in the DATEADD function if it is in a date format. The format of the date i
s
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get aroun
d
it? I have no control over the setup of the table that holds the performanc
e
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:

> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform an
y
> date functions against this column in my sql queries. I am trying to use
the
> DATEADD function and the BOL reference states that character data can be u
sed
> in the DATEADD function if it is in a date format. The format of the date
is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEA
DD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get aro
und
> it? I have no control over the setup of the table that holds the performa
nce
> counter data. It is created automatically when the counter is set to log
to
> a database. Thanks for any help!|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your tes
t
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However i
f
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
[vbcol=seagreen]
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
>|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does you
r
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your t
est
> supports that. I used query analyzer to return a small amount of data fro
m
> the column in question, and in the results pane it looks correct. However
if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance count
er
> seems to be doing something funny as it logs data, but I'm not sure what o
r
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying
to
> run it as a select statement in query analyzer. Thanks for any other idea
s!
> "Michael Evanchik" wrote:
>|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, i
f
I go to that cell and delete whatever extra is at the end, I can then perfor
m
date functions. It does not appear to simply be a space, because I can add
a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
[vbcol=seagreen]
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pastin
g
> into notepad or wordpad, no data will be lost that way. Also you can past
e
> special i think in excel as pure text but im no excel expert. also does y
our
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
>|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Yeah, I realized the Excel test was no good after I made my last post. I'
ve
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counte
r,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of t
he
> dates to a different table that I created, I get the same error. However,
if
> I go to that cell and delete whatever extra is at the end, I can then perf
orm
> date functions. It does not appear to simply be a space, because I can ad
d a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to clean
up
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
>|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
[vbcol=seagreen]
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
>|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
[vbcol=seagreen]
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
>

Performance Counter Data

I have a couple performance counters logging data to a sql 2000 database.
The date column has a data type of varchar and I am not able to perform any
date functions against this column in my sql queries. I am trying to use the
DATEADD function and the BOL reference states that character data can be used
in the DATEADD function if it is in a date format. The format of the date is
2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
function, I receive an error: "error converting datetime from character
string". Does anyone know what might be causing this or how I can get around
it? I have no control over the setup of the table that holds the performance
counter data. It is created automatically when the counter is set to log to
a database. Thanks for any help!phavel,
i created a table with one field varchar 50.
created a view doing select dateadd(day,1,myfield) and it worked fine.
can you elaborate on your code and your schema?
Mike
www.michaelevanchik.com
"phavel" wrote:
> I have a couple performance counters logging data to a sql 2000 database.
> The date column has a data type of varchar and I am not able to perform any
> date functions against this column in my sql queries. I am trying to use the
> DATEADD function and the BOL reference states that character data can be used
> in the DATEADD function if it is in a date format. The format of the date is
> 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> function, I receive an error: "error converting datetime from character
> string". Does anyone know what might be causing this or how I can get around
> it? I have no control over the setup of the table that holds the performance
> counter data. It is created automatically when the counter is set to log to
> a database. Thanks for any help!|||Thanks for the response. This appears to be something specific to the way
the performance counter logs the data. BOL states that there should be no
problem using date functions against columns of character data, and your test
supports that. I used query analyzer to return a small amount of data from
the column in question, and in the results pane it looks correct. However if
I copy some of the dates out of query analyzer and paste them into Excel,
they lose the data and only the minutes are pasted. The performance counter
seems to be doing something funny as it logs data, but I'm not sure what or
if there's any way to work around it. The VIEW I'm trying to create is
essentially the same as what you wrote. I get the same error when trying to
run it as a select statement in query analyzer. Thanks for any other ideas!
"Michael Evanchik" wrote:
>
> phavel,
> i created a table with one field varchar 50.
> created a view doing select dateadd(day,1,myfield) and it worked fine.
> can you elaborate on your code and your schema?
> Mike
> www.michaelevanchik.com
> "phavel" wrote:
> > I have a couple performance counters logging data to a sql 2000 database.
> > The date column has a data type of varchar and I am not able to perform any
> > date functions against this column in my sql queries. I am trying to use the
> > DATEADD function and the BOL reference states that character data can be used
> > in the DATEADD function if it is in a date format. The format of the date is
> > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > function, I receive an error: "error converting datetime from character
> > string". Does anyone know what might be causing this or how I can get around
> > it? I have no control over the setup of the table that holds the performance
> > counter data. It is created automatically when the counter is set to log to
> > a database. Thanks for any help!|||phavel,
i wouldnt paste into excel if you really want to look at data. try pasting
into notepad or wordpad, no data will be lost that way. Also you can paste
special i think in excel as pure text but im no excel expert. also does your
convert work if you specifiy one column in one row ? or does it only error
when you try to convert the whole table of that field?
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Thanks for the response. This appears to be something specific to the way
> the performance counter logs the data. BOL states that there should be no
> problem using date functions against columns of character data, and your test
> supports that. I used query analyzer to return a small amount of data from
> the column in question, and in the results pane it looks correct. However if
> I copy some of the dates out of query analyzer and paste them into Excel,
> they lose the data and only the minutes are pasted. The performance counter
> seems to be doing something funny as it logs data, but I'm not sure what or
> if there's any way to work around it. The VIEW I'm trying to create is
> essentially the same as what you wrote. I get the same error when trying to
> run it as a select statement in query analyzer. Thanks for any other ideas!
> "Michael Evanchik" wrote:
> >
> >
> > phavel,
> >
> > i created a table with one field varchar 50.
> > created a view doing select dateadd(day,1,myfield) and it worked fine.
> >
> > can you elaborate on your code and your schema?
> >
> > Mike
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > I have a couple performance counters logging data to a sql 2000 database.
> > > The date column has a data type of varchar and I am not able to perform any
> > > date functions against this column in my sql queries. I am trying to use the
> > > DATEADD function and the BOL reference states that character data can be used
> > > in the DATEADD function if it is in a date format. The format of the date is
> > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > function, I receive an error: "error converting datetime from character
> > > string". Does anyone know what might be causing this or how I can get around
> > > it? I have no control over the setup of the table that holds the performance
> > > counter data. It is created automatically when the counter is set to log to
> > > a database. Thanks for any help!|||Yeah, I realized the Excel test was no good after I made my last post. I've
figured this out to a certain extent, but I'm still not sure of a good way
around it. When the date is placed in the table by the performance counter,
it is putting something "extra" at the end of the value. It appears that
this is why I cannot perform date functions. If I copy and paste one of the
dates to a different table that I created, I get the same error. However, if
I go to that cell and delete whatever extra is at the end, I can then perform
date functions. It does not appear to simply be a space, because I can add a
trailing space manually and it still works fine. Any ideas on what can be
done? I guess I can have a separate query that runs periodically to cleanup
the date data, but this could get messy with timing. Any suggestions
welcome. Thanks!
"Michael Evanchik" wrote:
> phavel,
> i wouldnt paste into excel if you really want to look at data. try pasting
> into notepad or wordpad, no data will be lost that way. Also you can paste
> special i think in excel as pure text but im no excel expert. also does your
> convert work if you specifiy one column in one row ? or does it only error
> when you try to convert the whole table of that field?
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
> > Thanks for the response. This appears to be something specific to the way
> > the performance counter logs the data. BOL states that there should be no
> > problem using date functions against columns of character data, and your test
> > supports that. I used query analyzer to return a small amount of data from
> > the column in question, and in the results pane it looks correct. However if
> > I copy some of the dates out of query analyzer and paste them into Excel,
> > they lose the data and only the minutes are pasted. The performance counter
> > seems to be doing something funny as it logs data, but I'm not sure what or
> > if there's any way to work around it. The VIEW I'm trying to create is
> > essentially the same as what you wrote. I get the same error when trying to
> > run it as a select statement in query analyzer. Thanks for any other ideas!
> >
> > "Michael Evanchik" wrote:
> >
> > >
> > >
> > > phavel,
> > >
> > > i created a table with one field varchar 50.
> > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > >
> > > can you elaborate on your code and your schema?
> > >
> > > Mike
> > >
> > > www.michaelevanchik.com
> > >
> > > "phavel" wrote:
> > >
> > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > The date column has a data type of varchar and I am not able to perform any
> > > > date functions against this column in my sql queries. I am trying to use the
> > > > DATEADD function and the BOL reference states that character data can be used
> > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > function, I receive an error: "error converting datetime from character
> > > > string". Does anyone know what might be causing this or how I can get around
> > > > it? I have no control over the setup of the table that holds the performance
> > > > counter data. It is created automatically when the counter is set to log to
> > > > a database. Thanks for any help!|||dateadd(day,1,substring(field,1,12))
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Yeah, I realized the Excel test was no good after I made my last post. I've
> figured this out to a certain extent, but I'm still not sure of a good way
> around it. When the date is placed in the table by the performance counter,
> it is putting something "extra" at the end of the value. It appears that
> this is why I cannot perform date functions. If I copy and paste one of the
> dates to a different table that I created, I get the same error. However, if
> I go to that cell and delete whatever extra is at the end, I can then perform
> date functions. It does not appear to simply be a space, because I can add a
> trailing space manually and it still works fine. Any ideas on what can be
> done? I guess I can have a separate query that runs periodically to cleanup
> the date data, but this could get messy with timing. Any suggestions
> welcome. Thanks!
> "Michael Evanchik" wrote:
> > phavel,
> >
> > i wouldnt paste into excel if you really want to look at data. try pasting
> > into notepad or wordpad, no data will be lost that way. Also you can paste
> > special i think in excel as pure text but im no excel expert. also does your
> > convert work if you specifiy one column in one row ? or does it only error
> > when you try to convert the whole table of that field?
> >
> > Michael Evanchik
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > Thanks for the response. This appears to be something specific to the way
> > > the performance counter logs the data. BOL states that there should be no
> > > problem using date functions against columns of character data, and your test
> > > supports that. I used query analyzer to return a small amount of data from
> > > the column in question, and in the results pane it looks correct. However if
> > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > they lose the data and only the minutes are pasted. The performance counter
> > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > if there's any way to work around it. The VIEW I'm trying to create is
> > > essentially the same as what you wrote. I get the same error when trying to
> > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > >
> > > "Michael Evanchik" wrote:
> > >
> > > >
> > > >
> > > > phavel,
> > > >
> > > > i created a table with one field varchar 50.
> > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > >
> > > > can you elaborate on your code and your schema?
> > > >
> > > > Mike
> > > >
> > > > www.michaelevanchik.com
> > > >
> > > > "phavel" wrote:
> > > >
> > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > function, I receive an error: "error converting datetime from character
> > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > it? I have no control over the setup of the table that holds the performance
> > > > > counter data. It is created automatically when the counter is set to log to
> > > > > a database. Thanks for any help!|||Worked pefect. Thanks a ton for the help.
"Michael Evanchik" wrote:
> dateadd(day,1,substring(field,1,12))
> Michael Evanchik
> www.michaelevanchik.com
> "phavel" wrote:
> > Yeah, I realized the Excel test was no good after I made my last post. I've
> > figured this out to a certain extent, but I'm still not sure of a good way
> > around it. When the date is placed in the table by the performance counter,
> > it is putting something "extra" at the end of the value. It appears that
> > this is why I cannot perform date functions. If I copy and paste one of the
> > dates to a different table that I created, I get the same error. However, if
> > I go to that cell and delete whatever extra is at the end, I can then perform
> > date functions. It does not appear to simply be a space, because I can add a
> > trailing space manually and it still works fine. Any ideas on what can be
> > done? I guess I can have a separate query that runs periodically to cleanup
> > the date data, but this could get messy with timing. Any suggestions
> > welcome. Thanks!
> >
> > "Michael Evanchik" wrote:
> >
> > > phavel,
> > >
> > > i wouldnt paste into excel if you really want to look at data. try pasting
> > > into notepad or wordpad, no data will be lost that way. Also you can paste
> > > special i think in excel as pure text but im no excel expert. also does your
> > > convert work if you specifiy one column in one row ? or does it only error
> > > when you try to convert the whole table of that field?
> > >
> > > Michael Evanchik
> > >
> > > www.michaelevanchik.com
> > >
> > > "phavel" wrote:
> > >
> > > > Thanks for the response. This appears to be something specific to the way
> > > > the performance counter logs the data. BOL states that there should be no
> > > > problem using date functions against columns of character data, and your test
> > > > supports that. I used query analyzer to return a small amount of data from
> > > > the column in question, and in the results pane it looks correct. However if
> > > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > > they lose the data and only the minutes are pasted. The performance counter
> > > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > > if there's any way to work around it. The VIEW I'm trying to create is
> > > > essentially the same as what you wrote. I get the same error when trying to
> > > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > > >
> > > > "Michael Evanchik" wrote:
> > > >
> > > > >
> > > > >
> > > > > phavel,
> > > > >
> > > > > i created a table with one field varchar 50.
> > > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > > >
> > > > > can you elaborate on your code and your schema?
> > > > >
> > > > > Mike
> > > > >
> > > > > www.michaelevanchik.com
> > > > >
> > > > > "phavel" wrote:
> > > > >
> > > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > > function, I receive an error: "error converting datetime from character
> > > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > a database. Thanks for any help!|||awesome! click yes this post was helpful to close this thread =)
Michael Evanchik
www.michaelevanchik.com
"phavel" wrote:
> Worked pefect. Thanks a ton for the help.
> "Michael Evanchik" wrote:
> > dateadd(day,1,substring(field,1,12))
> >
> > Michael Evanchik
> >
> > www.michaelevanchik.com
> >
> > "phavel" wrote:
> >
> > > Yeah, I realized the Excel test was no good after I made my last post. I've
> > > figured this out to a certain extent, but I'm still not sure of a good way
> > > around it. When the date is placed in the table by the performance counter,
> > > it is putting something "extra" at the end of the value. It appears that
> > > this is why I cannot perform date functions. If I copy and paste one of the
> > > dates to a different table that I created, I get the same error. However, if
> > > I go to that cell and delete whatever extra is at the end, I can then perform
> > > date functions. It does not appear to simply be a space, because I can add a
> > > trailing space manually and it still works fine. Any ideas on what can be
> > > done? I guess I can have a separate query that runs periodically to cleanup
> > > the date data, but this could get messy with timing. Any suggestions
> > > welcome. Thanks!
> > >
> > > "Michael Evanchik" wrote:
> > >
> > > > phavel,
> > > >
> > > > i wouldnt paste into excel if you really want to look at data. try pasting
> > > > into notepad or wordpad, no data will be lost that way. Also you can paste
> > > > special i think in excel as pure text but im no excel expert. also does your
> > > > convert work if you specifiy one column in one row ? or does it only error
> > > > when you try to convert the whole table of that field?
> > > >
> > > > Michael Evanchik
> > > >
> > > > www.michaelevanchik.com
> > > >
> > > > "phavel" wrote:
> > > >
> > > > > Thanks for the response. This appears to be something specific to the way
> > > > > the performance counter logs the data. BOL states that there should be no
> > > > > problem using date functions against columns of character data, and your test
> > > > > supports that. I used query analyzer to return a small amount of data from
> > > > > the column in question, and in the results pane it looks correct. However if
> > > > > I copy some of the dates out of query analyzer and paste them into Excel,
> > > > > they lose the data and only the minutes are pasted. The performance counter
> > > > > seems to be doing something funny as it logs data, but I'm not sure what or
> > > > > if there's any way to work around it. The VIEW I'm trying to create is
> > > > > essentially the same as what you wrote. I get the same error when trying to
> > > > > run it as a select statement in query analyzer. Thanks for any other ideas!
> > > > >
> > > > > "Michael Evanchik" wrote:
> > > > >
> > > > > >
> > > > > >
> > > > > > phavel,
> > > > > >
> > > > > > i created a table with one field varchar 50.
> > > > > > created a view doing select dateadd(day,1,myfield) and it worked fine.
> > > > > >
> > > > > > can you elaborate on your code and your schema?
> > > > > >
> > > > > > Mike
> > > > > >
> > > > > > www.michaelevanchik.com
> > > > > >
> > > > > > "phavel" wrote:
> > > > > >
> > > > > > > I have a couple performance counters logging data to a sql 2000 database.
> > > > > > > The date column has a data type of varchar and I am not able to perform any
> > > > > > > date functions against this column in my sql queries. I am trying to use the
> > > > > > > DATEADD function and the BOL reference states that character data can be used
> > > > > > > in the DATEADD function if it is in a date format. The format of the date is
> > > > > > > 2005-05-31 11:11:23.123. When I try and create a VIEW that uses the DATEADD
> > > > > > > function, I receive an error: "error converting datetime from character
> > > > > > > string". Does anyone know what might be causing this or how I can get around
> > > > > > > it? I have no control over the setup of the table that holds the performance
> > > > > > > counter data. It is created automatically when the counter is set to log to
> > > > > > > a database. Thanks for any help!

Performance Condition Alert Type

I am trying to setup a Performance Condition Alert and on this one SQL
2000 Server the 'Type:' drop down does not give me the option to choose
"SQL Server performance condition alert". "SQL Server event alert" is
the only item listed in the drop down. Is there some setting on the
Server that I am missing that needs to be activated? I am connected
with the SA account; Windows 2000 sp4; SQL 2000 sp2; SQL Litespeed
(this is the only server with LiteSpeed).

Thank you."Stephanie" <stuttle@.centene.com> wrote in message
news:1122068829.573542.227430@.g43g2000cwa.googlegr oups.com...
>I am trying to setup a Performance Condition Alert and on this one SQL
> 2000 Server the 'Type:' drop down does not give me the option to choose
> "SQL Server performance condition alert". "SQL Server event alert" is
> the only item listed in the drop down. Is there some setting on the
> Server that I am missing that needs to be activated? I am connected
> with the SA account; Windows 2000 sp4; SQL 2000 sp2; SQL Litespeed
> (this is the only server with LiteSpeed).
> Thank you.

In some cases, it seems that the SQL Server performance counters are not
shown in the Windows Performance Monitor, so it may be that you're seeing
something similar:

http://groups.google.ch/group/micro...ec3d36b0b?hl=en
http://www.extremeexperts.com/SQL/F...erCounters.aspx

It would also be worth installing SP4 - whether or not it helps to resolve
this issue, you need at least SP3 for protection from the Slammer worm.

Simon

Performance between Hierarchy and Custom Member

Hi,

I have a simple dimension about product:

code, name, type
1111, coke, drink
1112, milk, drink
1113, coffee, drink
2111, cookieA, other
2112, cookieA, other

I can design a product dimension which has a hierarchy. Level 1 is type, and Level 2 is name. Then I can get total amount about drink and other. This design is the dimension which has a hierarchy.

However, I can design Custom Member to get total amount about drink and other:

code, name, CMColumn
1111, coke,
1112, milk,
1113, coffee,
2111, cookieA,
2112, cookieA,
9991, drink, ([Product].[1111]+[Product].[1112]+[Product].[1113])
9992, other, ([Product].[2111]+[Product].[2112])

Custom Member is designed instead of Hiearchy. The property "CustomRollupColumn" in dimension is set as CMColumn. So that I can get total amount about drink and other, too.

My question is which one's performance is better in the two designs?

thanks,

The first option has potentially better performance (if there are aggregations at the product type level), because queries for data at the type level could be directly answered from aggregations, rather than relying on rollups at runtime.|||

Deepak Puri wrote:

The first option has potentially better performance (if there are aggregations at the product type level), because queries for data at the type level could be directly answered from aggregations, rather than relying on rollups at runtime.

thanks.

I think the first option (using Hierarchy) costs more process time but less query response time.

The second option (using Custom Member) costs less process time but more query response time.

Is my opinion correct?

|||Probably correct - it depends on the dimension structure in the second option, and what aggregations are created for that structure...

Performance - Joins vs Filters

Hi
I reckon this is a "how long's a piece of string"-type of question but I'll
try it anyway. If you could provide any pointers, even if it is not a direct
answer then I'd be really grateful.
I've written an app generates SQL. I'm joining many tables and it's stable.
However, I now need to enhance it some more and link in another table. I
have the option of extending the WHERE clause instead of modifying the
joining mechanism in the FROM clause. Extending the WHERE clause means
adding a subselect and the way to do this is *far* easier to implement than
to rework the joining mechanisms to include an extra table - most especially
for Left Joins.
So my preference would be just extend the filter but I'm not sure about the
impact on performance. Will left joining from the additional table (and
extending the filter) be significantly faster than SubSelecting from it and
using an IN?
Thanks
Simonit depends. You need to do your own benchmarks|||Recently, I developed a data warehouse and in terms of SQL performance tips
etc. I was a complete novice. I did alot of investigation and research into
the fastest way to query and I found that subselects in general were a big
performance hit. I found by creating intermediate tables that I could join
into other queries that performance was greatly enhanced. Of course this is
subjective to the scenario and I imagine there are plenty of exceptions, why
dont you try both and find out?
"Simon Woods" wrote:

> Hi
> I reckon this is a "how long's a piece of string"-type of question but I'l
l
> try it anyway. If you could provide any pointers, even if it is not a dire
ct
> answer then I'd be really grateful.
> I've written an app generates SQL. I'm joining many tables and it's stable
.
> However, I now need to enhance it some more and link in another table. I
> have the option of extending the WHERE clause instead of modifying the
> joining mechanism in the FROM clause. Extending the WHERE clause means
> adding a subselect and the way to do this is *far* easier to implement tha
n
> to rework the joining mechanisms to include an extra table - most especial
ly
> for Left Joins.
> So my preference would be just extend the filter but I'm not sure about th
e
> impact on performance. Will left joining from the additional table (and
> extending the filter) be significantly faster than SubSelecting from it an
d
> using an IN?
> Thanks
> Simon
>
>|||Simon,
Why don't you find out instead of guess? Type the two
possible queries you're considering into query analyzer
and either compare their execution plans or test them on
sample data.
If for some reason you can't test the queries you're considering,
and want more advice here, you'll have better luck if you
post specific queries along with the relevant CREATE TABLE
statements and some sample data.
Steve Kass
Drew University
Simon Woods wrote:

>Hi
>I reckon this is a "how long's a piece of string"-type of question but I'll
>try it anyway. If you could provide any pointers, even if it is not a direc
t
>answer then I'd be really grateful.
>I've written an app generates SQL. I'm joining many tables and it's stable.
>However, I now need to enhance it some more and link in another table. I
>have the option of extending the WHERE clause instead of modifying the
>joining mechanism in the FROM clause. Extending the WHERE clause means
>adding a subselect and the way to do this is *far* easier to implement than
>to rework the joining mechanisms to include an extra table - most especiall
y
>for Left Joins.
>So my preference would be just extend the filter but I'm not sure about the
>impact on performance. Will left joining from the additional table (and
>extending the filter) be significantly faster than SubSelecting from it and
>using an IN?
>Thanks
>Simon
>
>|||Well, first question in my mind is "do you need any of this data for
output?" If yes, then join, if no, then where clause. If it is too slow,
then optimize.
If you are just filtering data, then an exists in the where should be
faster, it will certainly express what you are trying to do in a more
correct manner.
And as everyone else has stated, test it out :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Simon Woods" <simonSPAMMENOT.woods@.virginNOTMESPAM.net> wrote in message
news:eQ94X$tEGHA.4036@.TK2MSFTNGP09.phx.gbl...
> Hi
> I reckon this is a "how long's a piece of string"-type of question but
> I'll try it anyway. If you could provide any pointers, even if it is not a
> direct answer then I'd be really grateful.
> I've written an app generates SQL. I'm joining many tables and it's
> stable. However, I now need to enhance it some more and link in another
> table. I have the option of extending the WHERE clause instead of
> modifying the joining mechanism in the FROM clause. Extending the WHERE
> clause means adding a subselect and the way to do this is *far* easier to
> implement than to rework the joining mechanisms to include an extra
> table - most especially for Left Joins.
> So my preference would be just extend the filter but I'm not sure about
> the impact on performance. Will left joining from the additional table
> (and extending the filter) be significantly faster than SubSelecting from
> it and using an IN?
> Thanks
> Simon
>|||On Fri, 6 Jan 2006 16:40:02 -0000, "Simon Woods"
<simonSPAMMENOT.woods@.virginNOTMESPAM.net> wrote:
>So my preference would be just extend the filter but I'm not sure about the
>impact on performance. Will left joining from the additional table (and
>extending the filter) be significantly faster than SubSelecting from it and
>using an IN?
If you're very lucky, the optimizer will turn out exactly the same
code for any of the top three or four ways to code it.
Actually, it's pretty common to see that.
J.|||Hi Simon
can't argue with Alexander and Steve's recommendation of "try both and
find out which is better"!
However, my experience (10 years) is that subselects are almost always
less efficient than joins. I never use them now, and I'm pleasantly
surprised again and again at how SQL Server can gobble up the most
evil-looking multiple join operations and flip the results back in
seconds.
The divantage of joins as you say is that they can take quite a bit
of work to get right.
try out the subselect with some (sufficiently large set of) sample
data, I reckon.
cheers
Seb

performance

Hi all,
I have strange problem.
I have query (where I have to use cursors, functions, subqueries, variables
of type table) and it runs aprox 7 seconds on new built and filled database.
New built and filled database means :
- create tables
- load data
- create constrains, indexes, views, procedures, functions
After restart of SQL server query runs longer (aprox 11 seconds) and it
stays on 11 seconds all the time.
Also it is very strange that on server with more memory, SCSI disks it runs
slower than on my local workstation.
Any ideas ?
Regards,
jan
Hello Jan,
It is possible that your pages have been flushed from the buffer pool on the
server, if there are lots of other queries going on. A way to test the actual
performance is to flush the cache on both machines before running the query.
CAUTION: This will cause a performance degradation to users on both machines
as SQL Server will need to get all flushed pages from disk.
Use:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then run your query.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Jan Hruz" wrote:

> Hi all,
> I have strange problem.
> I have query (where I have to use cursors, functions, subqueries, variables
> of type table) and it runs aprox 7 seconds on new built and filled database.
> New built and filled database means :
> - create tables
> - load data
> - create constrains, indexes, views, procedures, functions
> After restart of SQL server query runs longer (aprox 11 seconds) and it
> stays on 11 seconds all the time.
> Also it is very strange that on server with more memory, SCSI disks it runs
> slower than on my local workstation.
> Any ideas ?
> Regards,
> jan
>
>