Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.

---

Is

quote:

---

> it worth my while making the main view into a user-defined function so

---

that

quote:

---

> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC

---

GROUP

quote:

---

> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>

---

|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

---

Optimising Query based on Views

I would be grateful for some advice.
I have a query which selects from a view which, in turn, is based on three
other view. I want to optimise the query.
The query is built dynamically so cannot be made into a stored procedure. Is
it worth my while making the main view into a user-defined function so that
I can select from it. (I understand that you can't do "SELECT * FROM
SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC GROUP
BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
user-defined function.) I am suggesting this because I have the impression
that stored procedures and user-defined functions are pre-compiled with an
execution plan by SQL server, whereas this is not possible for views.
Many thanks in advance,
Richard Cox.Yes and no. Views are totally transparent to the optimizer and are only
useful as an abstraction layer and security feature.. The optimizer looks
at the underlying tables rather than the view, EXCEPT for partitioned views
which I will conveniently ignore here.
The big advantage to a stored procedure is query plan reuse. The optimizer
figures out its 'best' plan once and reuses it until it is no longer valid
or it is aged out of cache. If you call the procedure once a day, this
won't help much. On the other hand, even stored procedures have limits.
Temporary tables and dynamic SQL are two of the biggest reasons why a stored
procedure will be recompiled.
From your description, the result set and filter conditions may change from
execution to execution so the advantage of plan reuse just doesn't apply.
You may have to construct a few samples and see if the optimizer does what
you think it should. Use the 'View Estimated Execution Plan' button in
Query Analyzer to see what SQL will do with various combinations of your
query.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
CareerBuilder.com
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:en6D1q54DHA.2736@.TK2MSFTNGP09.phx.gbl...
> I would be grateful for some advice.
> I have a query which selects from a view which, in turn, is based on three
> other view. I want to optimise the query.
> The query is built dynamically so cannot be made into a stored procedure.
Is
> it worth my while making the main view into a user-defined function so
that
> I can select from it. (I understand that you can't do "SELECT * FROM
> SP_MYPROC GROUP BY etc.", whereas you can do "SELECT * FROM UDF_MYFUNC
GROUP
> BY etc." where SP_MYPROC is a stored procedure and UDF_MYFUNC is a
> user-defined function.) I am suggesting this because I have the impression
> that stored procedures and user-defined functions are pre-compiled with an
> execution plan by SQL server, whereas this is not possible for views.
> Many thanks in advance,
> Richard Cox.
>
>|||Thanks very much for your explanation, Geoff. Looks like there is nothing
much to be gained in this case then.
Richard.|||Richard
Query Optimyzer does not produce query plan for views. On other hand when
you create clustered index on view it is materialized and store in the same
way as store clusetred index created on the table. I have seen queries that
after adding clustered index have ran more faster.
"Richard Cox" <rpcox@.traqs.com> wrote in message
news:e8IESL$4DHA.1852@.TK2MSFTNGP10.phx.gbl...
> Thanks very much for your explanation, Geoff. Looks like there is nothing
> much to be gained in this case then.
> Richard.
>

Optimise these 3 queries into one?

I have these 3 queries - they are the same except each fetches record counts
for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
nImages (type 2). Is there any way I could get all 3 of these (based on the
Node.Type integer) with a single query?

IF @.Error = 0
BEGIN
SELECT @.nSubsets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 0

SET @.Error = @.ERROR
END

IF @.Error = 0
BEGIN
SELECT @.nAssets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 1

SET @.Error = @.ERROR
END

IF @.Error = 0
BEGIN
SELECT @.nImages = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 2

SET @.Error = @.ERROR
ENDSELECT
@.nSubsets = COUNT(CASE Node.type WHEN 0 THEN 1 END),
@.nAssets = COUNT(CASE Node.type WHEN 1 THEN 1 END),
@.nImages = COUNT(CASE Node.type WHEN 2 THEN 1 END)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'

--
David Portas
----
Please reply only to the newsgroup
--|||Robin,

I'm assuming you wanted the result in one row. This is untested, but
you get the idea...

IF @.Error = 0
BEGIN
SELECT SUM (CASE Node.Type
WHEN 0 THEN 1
ELSE 0
END) AS nSubSets_cnt
,SUM (CASE Node.Type
WHEN 1 THEN 1
ELSE 0
END) AS nAssets_cnt
,SUM (CASE Node.Type
WHEN 2 THEN 1
ELSE 0
END) AS nImages_cnt
FROM Node
INNER JOIN
Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND Node.Type = in (0, 1, 2)

SET @.Error = @.ERROR
END

Christian.|||How about this?

SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
@.nAssets = sum(case when node.type = 1 then 1 else 0 end),
@.nImages = sum(case when node.type = 2 then 1 else 0 end),
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'

I think tyhis will get you what you are looking for. I haven't run it
against anything, but I'm fairly sure it's good to go.

Hope it works for you,
Carl

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> I have these 3 queries - they are the same except each fetches record counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
> nImages (type 2). Is there any way I could get all 3 of these (based on the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||Use the CASE function.
EG:

select

CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 0 then 1
else 0 end "NT=0",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 1 then 1
else 0 end "NT=1",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 2 then 1
else 0 end "NT=2",

from ...

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:bs9jhc$gfo$1$8300dec7@.news.demon.co.uk...
> I have these 3 queries - they are the same except each fetches record
counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> nImages (type 2). Is there any way I could get all 3 of these (based on
the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||All good stuff. Thanks for all 3 replys.

"Carl Reeds" <creeds0001@.msn.com> wrote in message
news:956eb1fa.0312231411.4e7cec31@.posting.google.c om...
> How about this?
> SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
> @.nAssets = sum(case when node.type = 1 then 1 else 0 end),
> @.nImages = sum(case when node.type = 2 then 1 else 0 end),
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> I think tyhis will get you what you are looking for. I haven't run it
> against anything, but I'm fairly sure it's good to go.
> Hope it works for you,
> Carl
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> > I have these 3 queries - they are the same except each fetches record
counts
> > for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> > nImages (type 2). Is there any way I could get all 3 of these (based on
the
> > Node.Type integer) with a single query?
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nSubsets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 0
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nAssets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 1
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nImages = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 2
> > SET @.Error = @.ERROR
> > END

Optimise Select Statement

Hi
I have this select statement that I need to optimise:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
The tblCustomer.Code is a string like 'STAR00000001' the function removes
the padded zeros.
The tblEvents table contains information in a string including the
contracted Customer.Code in this format "Type STAR1 ........"
Any help would be much appreciated.
Thanks
BDon=B4t know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
> Hi
> I have this select statement that I need to optimise:
> SELECT Created, Code, tblEvents.Ref
> FROM tblCustomers, tblEvents
> WHERE tblEvents.description LIKE 'Type ' +
> dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
> The tblCustomer.Code is a string like 'STAR00000001' the function removes
> the padded zeros.
No offense, but I think you need to optimise the design, not the query. Why
are you storing padded zeros if they're irrelevant or different from the
data you're actually modeling? Why are these tables quasi-related via a
string that changes?|||Hi Aaron
I would love to change the design but it is a third party product, although
i have requested the change in a future release I need a temporary solution.
Thanks
B
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#d7CV1evFHA.2292@.TK2MSFTNGP12.phx.gbl...
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
removes
> No offense, but I think you need to optimise the design, not the query.
Why
> are you storing padded zeros if they're irrelevant or different from the
> data you're actually modeling? Why are these tables quasi-related via a
> string that changes?
>|||On Tue, 20 Sep 2005 14:51:05 +0100, Ben wrote:

>I have this select statement that I need to optimise:
>SELECT Created, Code, tblEvents.Ref
>FROM tblCustomers, tblEvents
>WHERE tblEvents.description LIKE 'Type ' +
>dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
>The tblCustomer.Code is a string like 'STAR00000001' the function removes
>the padded zeros.
>The tblEvents table contains information in a string including the
>contracted Customer.Code in this format "Type STAR1 ........"
>Any help would be much appreciated.
Hi Ben,
User-defined functions can be slow. If possible, use builtin functions
that achieve the same effect.
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE
'Type ' + REPLACE(tblCustomer.Code, '0', '') + '%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jens
Would this work for codes such as:
STAR00000102?
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127225495.861651.299360@.g47g2000cwa.googlegroups.com...
Dont know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||DECLARE @.String varchar(2000)
SEt @.String = 'STAR00000102'
SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
results in "STAR%"|||Hi Jens
Thanks for your post.
The problem is that we have codes stored in tblEvents.description as STAR1,
STAR2, STAR3......STAR102, STAR103
and for example we need to distict "Type STAR103 ...." from "Type STAR1
...."
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127294257.162562.55160@.z14g2000cwz.googlegroups.com...
> DECLARE @.String varchar(2000)
> SEt @.String = 'STAR00000102'
> SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
> results in "STAR%"
>|||That=B4s not easy, there has to be a delimiter or something where you
can tell that the trailing zeros start, how do you want to differ
perhaps
STARS100 and STARS1 ?|||On Wed, 21 Sep 2005 09:36:47 +0100, Ben wrote:

>Hi Jens
>Would this work for codes such as:
>STAR00000102?
Hi Ben,
I assume that this has to be "shortened" to STAR102?
DECLARE @.a varchar(20)
SET @.a = 'STAR00000102'
SELECT STUFF(@.a, CHARINDEX('0', @.a),
PATINDEX('%[1-9]%', @.a) - CHARINDEX('0', @.a), '')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Optimise multitable update

Hi
I've got the following scenario:
TableA (4 million rows)
TableB (20 000 rows)
I have two fields on TableA that are the unique fields on TableB,
which I use to set the foreign key from A to B:
UPDATE TableA
SET TableA.B_FK = TableB.B_PK
FROM TableA, TableB
WHERE
TableA.Code = TableB.Code
AND TableA.Name = TableB.Name
Code = varchar(5)
Name = varchar(50)
What would a suitable indexes be to optimise this query as it takes 4
hours to run?
I already have an index on TableA on "Code, Name" and TableB on "B_PK"
- takes 4 hours with these!
Any help? Should I have a covering index on TableB, i.e. "Code, Name,
B_PK" ?
Thanks
Sean
On 25 May 2004 08:14:42 -0700, Sean wrote:

>Hi
>I've got the following scenario:
>TableA (4 million rows)
>TableB (20 000 rows)
>
>I have two fields on TableA that are the unique fields on TableB,
>which I use to set the foreign key from A to B:
>UPDATE TableA
>SET TableA.B_FK = TableB.B_PK
>FROM TableA, TableB
>WHERE
>TableA.Code = TableB.Code
>AND TableA.Name = TableB.Name
>
>Code = varchar(5)
>Name = varchar(50)
>What would a suitable indexes be to optimise this query as it takes 4
>hours to run?
>I already have an index on TableA on "Code, Name" and TableB on "B_PK"
>- takes 4 hours with these!
>Any help? Should I have a covering index on TableB, i.e. "Code, Name,
>B_PK" ?
>Thanks
>Sean
Hi Sean,
Is the current index on TableA(Code, Name) a clustered index? Is it
defined as a unique index?
Do all 20000 rows in TableB match a row in TableA? If so, adding an index
on TableB won't do you any good. If all rows in a table have to be
processed anyway, a table scan is always the best way. If only a few of
the 20000 rows will match, an index on TableB(Code, Name) *might* help,
but I'm not sure. Test it. The covering index you suggest *might* help as
well, but you'll have to test that as well. But, as I said - only if the
majority of rows in TableB will not match against TableA.
Is there an index on TableA(B_FK)? If it is, see if you can remove it;
that saves the time to update this index as the update is carried out.
Check that there are no triggers on TableA. (If you have them, can't
disable them and they're the cause of the long execution, forget about the
query and start optimising the triggers first!)
And the most important thing: Check the execution plan!! From your
description, I would expect a table scan of TableB and an index seek on
the index on TableA(CodaA, Name).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Optimise multitable update

Hi
I've got the following scenario:
TableA (4 million rows)
TableB (20 000 rows)
I have two fields on TableA that are the unique fields on TableB,
which I use to set the foreign key from A to B:
UPDATE TableA
SET TableA.B_FK = TableB.B_PK
FROM TableA, TableB
WHERE
TableA.Code = TableB.Code
AND TableA.Name = TableB.Name
Code = varchar(5)
Name = varchar(50)
What would a suitable indexes be to optimise this query as it takes 4
hours to run?
I already have an index on TableA on "Code, Name" and TableB on "B_PK"
- takes 4 hours with these!
Any help? Should I have a covering index on TableB, i.e. "Code, Name,
B_PK" ?
Thanks
SeanOn 25 May 2004 08:14:42 -0700, Sean wrote:
>Hi
>I've got the following scenario:
>TableA (4 million rows)
>TableB (20 000 rows)
>
>I have two fields on TableA that are the unique fields on TableB,
>which I use to set the foreign key from A to B:
>UPDATE TableA
>SET TableA.B_FK = TableB.B_PK
>FROM TableA, TableB
>WHERE
> TableA.Code = TableB.Code
> AND TableA.Name = TableB.Name
>
>Code = varchar(5)
>Name = varchar(50)
>What would a suitable indexes be to optimise this query as it takes 4
>hours to run?
>I already have an index on TableA on "Code, Name" and TableB on "B_PK"
>- takes 4 hours with these!
>Any help? Should I have a covering index on TableB, i.e. "Code, Name,
>B_PK" ?
>Thanks
>Sean
Hi Sean,
Is the current index on TableA(Code, Name) a clustered index? Is it
defined as a unique index?
Do all 20000 rows in TableB match a row in TableA? If so, adding an index
on TableB won't do you any good. If all rows in a table have to be
processed anyway, a table scan is always the best way. If only a few of
the 20000 rows will match, an index on TableB(Code, Name) *might* help,
but I'm not sure. Test it. The covering index you suggest *might* help as
well, but you'll have to test that as well. But, as I said - only if the
majority of rows in TableB will not match against TableA.
Is there an index on TableA(B_FK)? If it is, see if you can remove it;
that saves the time to update this index as the update is carried out.
Check that there are no triggers on TableA. (If you have them, can't
disable them and they're the cause of the long execution, forget about the
query and start optimising the triggers first!)
And the most important thing: Check the execution plan!! From your
description, I would expect a table scan of TableB and an index seek on
the index on TableA(CodaA, Name).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Optimisation

Hi,
I read that we can optimise from 10 to 15 % the performance of a replication
by changing the default value of the HistoryVerboseLevel and the
OutputVerboseLevel of the Agent Profiles
For exemple for the Distribution Agent we have to use
distrib -HistoryVerboseLevel 1
distrib -OutputVerboseLevel 0
The default value are 2 for both
The distrib program to executable is in
C:\Program Files\Microsoft SQL Server\80\COM
2 questions
01- On which server I have to execute this command on the Distributor or
on the
Publisher
02- Also when i go on the Distributor and view the propreties of the Agent
profile
I dont see the OutputVerboseLevel. Is that normal
Thanks in advance !
With a remote publisher/distributor its on the distributor, with a local
publisher/distributor its on the publisher.
Basically you do it wherever your distribution agents run.
Its normal for the OutputVerboseLevel parameter not to be used.
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
"GC" <GC@.discussions.microsoft.com> wrote in message
news:9B8F89FB-1106-48F2-9ED6-AC2BC752333B@.microsoft.com...
> Hi,
> I read that we can optimise from 10 to 15 % the performance of a
> replication
> by changing the default value of the HistoryVerboseLevel and the
> OutputVerboseLevel of the Agent Profiles
> For exemple for the Distribution Agent we have to use
> distrib -HistoryVerboseLevel 1
> distrib -OutputVerboseLevel 0
> The default value are 2 for both
> The distrib program to executable is in
> C:\Program Files\Microsoft SQL Server\80\COM
> 2 questions
> 01- On which server I have to execute this command on the Distributor or
> on the
> Publisher
> 02- Also when i go on the Distributor and view the propreties of the
> Agent
> profile
> I dont see the OutputVerboseLevel. Is that normal
> Thanks in advance !
>
>
>
>
>
>
>
>
>