Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David
|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in the
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
sql
Showing posts with label slow. Show all posts
Showing posts with label slow. Show all posts
Tuesday, March 20, 2012
Bad sql query and slow response
Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in th
e
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in th
e
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
Bad sql query and slow response
Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
--
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in the
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
--
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in the
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
Monday, March 19, 2012
Bad replication performance in SQL2k5 on links with high latency
I tested replication performance on slow network channels, for SQL2k and
SQL2k5. And quite disapplointed by SQL2k5 results...
Network channels were simulated, ping time 200 ms between distributor and
subscriber, unlimited bandwidth.
I tested how many transactions replication can deliver to subsciber. All
transactions are nearby same, they represent typical transactions in my
system.
Publisher, distributor and subsciber are all located on different computers.
In order to check capabilities of hardware, on each of the computers I run
tests to check speed of how many transactions per second it may insert into
database with turned on replication. For each computer, result was ~112
transactions per second, both for SQL2k and SQL2k5.
Next I started to measure replication delivery rate, how many transactions
per second distributor will be able to deliver to subscriber.
In SQL2k, replication in such condition was able to deliver 9 transactions
per second. The best result was obtained with 1 sec polling interval for log
reader and distribution agent, 64kb packet size for distribution agent. No
more differences from default profile.
In case if turn off network delay, rate of inserting transactions on
publisher become same as rate of delivering - replication start to deliver
~112 transactions per second.
For SQL2k5, results were around 5 transactions per second. The best result
was obtained on 1 sec polling interval for log reader and distribution agent,
64kb packet size for distribution agent, 3 SubscriptionStreams. No more
differences from default profile. If turn off network delays, it again
deliver at same rate as it inserting on publisher, about 112 transactions per
second.
Network delay simulator was tested several times to check is it really
simulate network delays as expected. All tests showed correct work of network
delay simulator. For simulating network delays, was used Shundra Cloud.
The question is - Is the replication in SQL2k5 really lost performance in
comparison with SQL2k? Is anything can be done to improve replication
delivery rate in SQL2k5?
the distribution agents in SQL 2005 has autosense network link technology
built into them. They will close down when they detect the link going down
and start up again when it is reconnected. I suspect you are bumping into
this.
I would open a support incident with MS to see if you can disable this
behavior or troubleshoot it further.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:D153A24B-63DF-45A6-A4B4-22A00221CA29@.microsoft.com...
>I tested replication performance on slow network channels, for SQL2k and
> SQL2k5. And quite disapplointed by SQL2k5 results...
> Network channels were simulated, ping time 200 ms between distributor and
> subscriber, unlimited bandwidth.
> I tested how many transactions replication can deliver to subsciber. All
> transactions are nearby same, they represent typical transactions in my
> system.
> Publisher, distributor and subsciber are all located on different
> computers.
> In order to check capabilities of hardware, on each of the computers I run
> tests to check speed of how many transactions per second it may insert
> into
> database with turned on replication. For each computer, result was ~112
> transactions per second, both for SQL2k and SQL2k5.
> Next I started to measure replication delivery rate, how many transactions
> per second distributor will be able to deliver to subscriber.
> In SQL2k, replication in such condition was able to deliver 9 transactions
> per second. The best result was obtained with 1 sec polling interval for
> log
> reader and distribution agent, 64kb packet size for distribution agent. No
> more differences from default profile.
> In case if turn off network delay, rate of inserting transactions on
> publisher become same as rate of delivering - replication start to deliver
> ~112 transactions per second.
> For SQL2k5, results were around 5 transactions per second. The best result
> was obtained on 1 sec polling interval for log reader and distribution
> agent,
> 64kb packet size for distribution agent, 3 SubscriptionStreams. No more
> differences from default profile. If turn off network delays, it again
> deliver at same rate as it inserting on publisher, about 112 transactions
> per
> second.
> Network delay simulator was tested several times to check is it really
> simulate network delays as expected. All tests showed correct work of
> network
> delay simulator. For simulating network delays, was used Shundra Cloud.
> The question is - Is the replication in SQL2k5 really lost performance in
> comparison with SQL2k? Is anything can be done to improve replication
> delivery rate in SQL2k5?
|||But network, during the tests, is not goes down - it is always work, at
constant ping time.
"Hilary Cotter" wrote:
> the distribution agents in SQL 2005 has autosense network link technology
> built into them. They will close down when they detect the link going down
> and start up again when it is reconnected. I suspect you are bumping into
> this.
> I would open a support incident with MS to see if you can disable this
> behavior or troubleshoot it further.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:D153A24B-63DF-45A6-A4B4-22A00221CA29@.microsoft.com...
>
>
|||I would open a support incident with Microsoft on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:D9A495E2-6AD2-480E-B78F-A999A3781FC6@.microsoft.com...[vbcol=seagreen]
> But network, during the tests, is not goes down - it is always work, at
> constant ping time.
> "Hilary Cotter" wrote:
SQL2k5. And quite disapplointed by SQL2k5 results...
Network channels were simulated, ping time 200 ms between distributor and
subscriber, unlimited bandwidth.
I tested how many transactions replication can deliver to subsciber. All
transactions are nearby same, they represent typical transactions in my
system.
Publisher, distributor and subsciber are all located on different computers.
In order to check capabilities of hardware, on each of the computers I run
tests to check speed of how many transactions per second it may insert into
database with turned on replication. For each computer, result was ~112
transactions per second, both for SQL2k and SQL2k5.
Next I started to measure replication delivery rate, how many transactions
per second distributor will be able to deliver to subscriber.
In SQL2k, replication in such condition was able to deliver 9 transactions
per second. The best result was obtained with 1 sec polling interval for log
reader and distribution agent, 64kb packet size for distribution agent. No
more differences from default profile.
In case if turn off network delay, rate of inserting transactions on
publisher become same as rate of delivering - replication start to deliver
~112 transactions per second.
For SQL2k5, results were around 5 transactions per second. The best result
was obtained on 1 sec polling interval for log reader and distribution agent,
64kb packet size for distribution agent, 3 SubscriptionStreams. No more
differences from default profile. If turn off network delays, it again
deliver at same rate as it inserting on publisher, about 112 transactions per
second.
Network delay simulator was tested several times to check is it really
simulate network delays as expected. All tests showed correct work of network
delay simulator. For simulating network delays, was used Shundra Cloud.
The question is - Is the replication in SQL2k5 really lost performance in
comparison with SQL2k? Is anything can be done to improve replication
delivery rate in SQL2k5?
the distribution agents in SQL 2005 has autosense network link technology
built into them. They will close down when they detect the link going down
and start up again when it is reconnected. I suspect you are bumping into
this.
I would open a support incident with MS to see if you can disable this
behavior or troubleshoot it further.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:D153A24B-63DF-45A6-A4B4-22A00221CA29@.microsoft.com...
>I tested replication performance on slow network channels, for SQL2k and
> SQL2k5. And quite disapplointed by SQL2k5 results...
> Network channels were simulated, ping time 200 ms between distributor and
> subscriber, unlimited bandwidth.
> I tested how many transactions replication can deliver to subsciber. All
> transactions are nearby same, they represent typical transactions in my
> system.
> Publisher, distributor and subsciber are all located on different
> computers.
> In order to check capabilities of hardware, on each of the computers I run
> tests to check speed of how many transactions per second it may insert
> into
> database with turned on replication. For each computer, result was ~112
> transactions per second, both for SQL2k and SQL2k5.
> Next I started to measure replication delivery rate, how many transactions
> per second distributor will be able to deliver to subscriber.
> In SQL2k, replication in such condition was able to deliver 9 transactions
> per second. The best result was obtained with 1 sec polling interval for
> log
> reader and distribution agent, 64kb packet size for distribution agent. No
> more differences from default profile.
> In case if turn off network delay, rate of inserting transactions on
> publisher become same as rate of delivering - replication start to deliver
> ~112 transactions per second.
> For SQL2k5, results were around 5 transactions per second. The best result
> was obtained on 1 sec polling interval for log reader and distribution
> agent,
> 64kb packet size for distribution agent, 3 SubscriptionStreams. No more
> differences from default profile. If turn off network delays, it again
> deliver at same rate as it inserting on publisher, about 112 transactions
> per
> second.
> Network delay simulator was tested several times to check is it really
> simulate network delays as expected. All tests showed correct work of
> network
> delay simulator. For simulating network delays, was used Shundra Cloud.
> The question is - Is the replication in SQL2k5 really lost performance in
> comparison with SQL2k? Is anything can be done to improve replication
> delivery rate in SQL2k5?
|||But network, during the tests, is not goes down - it is always work, at
constant ping time.
"Hilary Cotter" wrote:
> the distribution agents in SQL 2005 has autosense network link technology
> built into them. They will close down when they detect the link going down
> and start up again when it is reconnected. I suspect you are bumping into
> this.
> I would open a support incident with MS to see if you can disable this
> behavior or troubleshoot it further.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:D153A24B-63DF-45A6-A4B4-22A00221CA29@.microsoft.com...
>
>
|||I would open a support incident with Microsoft on this one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:D9A495E2-6AD2-480E-B78F-A999A3781FC6@.microsoft.com...[vbcol=seagreen]
> But network, during the tests, is not goes down - it is always work, at
> constant ping time.
> "Hilary Cotter" wrote:
bad querry - with very slow response
Can someone please tell me why this code is returning results after too much
time and can you please tell me how I can fix it? Thank you for your help.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(FIRSTNAME)) like 'nunu%' order by C_codes
James
asked and answered...
time and can you please tell me how I can fix it? Thank you for your help.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(FIRSTNAME)) like 'nunu%' order by C_codes
James
asked and answered...
bad querry - with very slow response
Can someone please tell me why this code is returning results after too much
time and can you please tell me how I can fix it? Thank you for your help.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(FIRSTNAME)) like 'nunu%' order by C_codes
Jamesasked and answered...
time and can you please tell me how I can fix it? Thank you for your help.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(FIRSTNAME)) like 'nunu%' order by C_codes
Jamesasked and answered...
bad querry - with very slow response
Can someone please tell me why this code is returning results after too much
time and can you please tell me how I can fix it? Thank you for your help.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(FIRSTNAME)) like 'nunu%' order by C_codes
Jamesasked and answered...
time and can you please tell me how I can fix it? Thank you for your help.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(FIRSTNAME)) like 'nunu%' order by C_codes
Jamesasked and answered...
Subscribe to:
Posts (Atom)