email with trigger inserted row

I'm trying to create a trigger which, upon a row insert, an email is sent containing some of the inserted row information. Apparently the built-in stored procedure for email starts it's own session, so I can't use the local variables of the trigger. My solution was to create a temp table, copy the inserted row in, then refer to that from the email SP, then drop the table at the end. When I try to insert a row, it runs for a very long time, then gives an error message saying that it timed out. It was suggested I add COMMIT TRANSACTION in to force it to commit the data to the temp table. Doing this, it gives an error, saying "The transaction ended in the trigger. The batch has been aborted. Mail queued." In the table view, I'm forced to hit esc and abort the insertion. However, if I refresh the table, the row has been inserted ok, and the email does get sent with the inserted row.

Code:
--

CREATE TRIGGER [newTicket_notify]

ON [sysdba].[TICKET]

AFTER INSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

create table insertedTemp

(

TICKETID char(12),

ACCOUNTID char(12),

ACCOUNT varchar(128),

DIVISION varchar(64),

EMAIL varchar(128),

MAINPHONE varchar(32)
)

declare @.ticketID char(12), @.accountID char(12), @.account varchar(128),

@.division varchar(64), @.email varchar(128), @.mainphone varchar(32)

select @.ticketID = TICKETID, @.accountID = ACCOUNTID

from inserted

select @.account = ACCOUNT, @.division = DIVISION, @.email = EMAIL,

@.mainphone = MAINPHONE

from sysdba.ACCOUNT

where @.accountID = ACCOUNTID

insert into insertedTemp values (@.ticketID, @.accountID, @.account,

@.division, @.email, @.mainphone)

commit transaction

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'Test',

@.recipients = 'user@.test.com',

@.body = 'Inserted row info',

@.subject = 'DB Test',

@.query = 'select TICKETID [Ticket ID], ACCOUNTID [Account ID],

ACCOUNT [Account], DIVISION [Division], EMAIL [Email],

MAINPHONE [Mainphone]

from dbo.insertedTEMP',

@.execute_query_database = 'database',

@.attach_query_result_as_file = '0';

drop table insertedTEMP

END

You're really doing a little too much with the trigger here. A trigger should be a quick thing.

Have you considered using Service Broker for this? Or even just a SQL Job which looks for new rows and does the emailing there? You might not get an immediate response (although if you make the SQL Job run every 10 seconds it will feel pretty immediate), but at least your initial insertion will complete happily.

Rob|||

SQL Server 2k5 uses the Sevice broker already for mail sending.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Agreed. And it will make the trigger you write look so much easier. Can you send multiple rows to a Service Broker queue at once? If not I would still create an email queue table and build a job to send emails. Emails aren't immediate things no matter what, and it will be a lot easier to debug an email queue not working if you don't have the added excitement of your ticket system failing because of it.sql

Email Trigger in SQL 2005

I am new to developing as will be evident from this post. Your help will be greatly appreciated.

I am developing an intranet for our company using ASP.NET with a SQL backend. I am currently working on a suggestion box form.

I would like to have an email sent to specific persons when a new entry is made in the suggestion table. I have been able to configure the trigger and generate the email (This was easy). Formatting the email has proven more difficult to resolve.

The format I would like is somewhat as follows:

F_NAME L_NAME submitted the following suggestion:

IDEA

BENEFIT

APPROVE | DECLINE

The items in RED are columns in the table and the Blue Underlines are hyperlinks to change the Status column in the table.

How can I generate the email to contain the data from the inserted record and in the above format.

Being new at this I only now how to send a static email advising that the entry has been made.

Any help creating the dynamic email form for this trigger will be greatly appreciated.

Lastly, what books are most helpful for SQL, ASP.NET, and VBScript referencing and examples?

Thanks

In SQL 2k I was not a fan of triggering the email via a trigger becasue if the mail server was down there was a problem ,that the transaction could lock up all the other processes that were supposed to use the table, but as Service Broker was introduced to send mails, that is another thing for me, lets try this suggestion here to see if it works:

DROP TABLE ProposedNames

CREATE TABLE ProposedNames

(

ProposedNameId INT IDENTITY(1,1) PRIMARY KEY,

Firstname VARCHAR(100),

Lastname VARCHAR(100),

Accepted BIT

)

CREATE TRIGGER INS_ProposedNames

ON ProposedNames

FOR INSERT

AS

BEGIN

SET NOCOUNT ON

DECLARE @.SomeHTMLText NVARCHAR(MAX)

DECLARE @.RowCount INT

DECLARE @.COUNTER INT

SET @.Counter = 1

CREATE TABLE #Names

(

Counted INT IDENTITY(1,1),

ProposedNameId INT,

Firstname VARCHAR(100),

Lastname VARCHAR(100),

)

INSERT INTO #Names

(

ProposedNameId ,

Firstname,

Lastname

)

SELECT

ProposedNameId ,

Firstname,

Lastname

FROM INSERTED

SET @.ROWCOUNT = @.@.ROWCOUNT

WHILE @.ROWCOUNT >= @.Counter

BEGIN

SEt @.SomeHTMLText = ''

SELECT @.SomeHTMLText = '<HTML><BODY>' +

'The following names were proposed<br><br>' +

@.SomeHTMLText +

'FirstName: ' + Firstname + '<br><br>' +

'LastName: ' + Lastname + '<br><br>' +

'<a href="http://www.someserver.com/Page.aspx?Action=Accept&ID=' + CAST(ProposedNameId AS VARCHAR(10)) + '"> I Accept</a> | ' +

'<a href="http://www.someserver.com/Page.aspx?Action=Decline&ID=' + CAST(ProposedNameId AS VARCHAR(10)) + '"> I Decline</a>' +

'</BODY></HTML>'

FROM #Names

WHERE Counted = @.Counter

EXEC msdb..sp_send_dbmail

@.recipients= 'TheRecipient@.domain.com',

@.subject = 'Its up to you',

@.body = @.SomeHTMLText,

@.body_format = 'HTML'

SET @.Counter = @.Counter +1

END

DROP TABLE #Names

END

GO

--Try it:

INSERT INTO ProposedNames

(Firstname,LastName)

VALUES ('Jens', 'Sü?meyer')

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I wish to thank Jens Suessmeyer for the helpful infomraiton. Although the information provided did not fully resolve my issue the information provided me with enough knowledge to research and discover the solutions provided below.

I have created two triggers. The first trigger sends and e-mail message to the person in charge of reviewing the submission from the web form. The second trigger sends a confirmation e-mail to the person whom submitted the suggestion.

CREATE Trigger [triggername]

on [tablename]

for insert

as

declare @.text varchar(max)

declare @.name varchar(max)

declare @.idea varchar(max)

declare @.benefit varchar(max)

set @.text = ''

set @.name = ''

set @.idea = ''

set @.benefit = ''

select @.name = firstname + ' ' + lastname, @.idea = idea, @.benefit = benefit

from tablename

where id = ident_current('tablename')

set @.text = '<html><body>' + 'The following Bright Idea was submitted by ' +

@.name + ':

' + '<b>Idea: </b>' + @.idea + '

' + '<b>Benefit: </b>' +

@.benefit + '

' + 'Please reveiw this idea as soon as possible.' +

'</body></html>'

exec msdb.dbo.sp_send_dbmail

@.profile_name = 'profilename',

@.recipients = 'recipientemailaddress',

@.subject = 'New Bright Idea Submitted',

@.body = @.text,

@.body_format = 'HTML'

CREATE Trigger [triggername]

on [tablename]

for insert

as

declare @.text varchar(max)

declare @.name varchar(max)

declare @.idea varchar(max)

declare @.benefit varchar(max)

declare @.email varchar(max)

set @.text = ''

set @.name = ''

set @.idea = ''

set @.benefit = ''

set @.email = ''

select @.name = name, @.idea = idea, @.benefit = benefit, @.email = email

from tablename

where id = ident_current('tablename')

set @.text = '<html><body>' + @.name + ',

Thank you for submitting your idea ' +

'using our web based service. Your suggestion has been received and will be reveiwed ' +

'by our management staff in the next couple of weeks.

Below is a copy of the idea ' +

'we received from you:

' + '<b>Idea: </b>' + @.idea +

'

<b>Benefit: </b>' + @.benefit + '

Once again we would like to thank you for ' +

'submitting your idea.' +

'</body></html>'

exec msdb.dbo.sp_send_dbmail

@.profile_name = 'profilename',

@.recipients = @.email,

@.subject = 'New Bright Idea Submitted',

@.body = @.text,

@.body_format = 'HTML'

|||Your trigger doesn't handle for multiple rows being affected by the DML statement or no rows affected or concurrency issues (use of IDENT_CURRENT). Instead of using IDENT_CURRENT, you need to query the inserted virtual table to get the inserted information. And you need to use a cursor loop to send email for each affected row for example.

email trigger

Hello

Can you setup a trigger to mail some one when a record is updated in SQL server 2005?

If you can, can anyone help me?

C

Yes, you can call a extended procedure 'xp_sendmail' in an update trigger. The following article shows an example:

http://msdn.microsoft.com/library/en-us/architec/8_ar_da_1tup.asp?frame=true

sql

Email Trigger

Hi.
I am trying to set a trigger to send an email notification. is this possible
in SQL 2000?
What i am trying to do is send an email saying that a client has been here
for 12 months and would like to raise the rent.
Regards WilburSomething similar to this laid out in http://www.aspfaq.com/2403
"panda" <panda@.discussions.microsoft.com> wrote in message
news:30401819-8EE7-42E3-8898-2F8E75880C40@.microsoft.com...
> Hi.
> I am trying to set a trigger to send an email notification. is this
> possible
> in SQL 2000?
> What i am trying to do is send an email saying that a client has been here
> for 12 months and would like to raise the rent.
> Regards Wilbur|||thank you very much.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Something similar to this laid out in http://www.aspfaq.com/2403
>
>
> "panda" <panda@.discussions.microsoft.com> wrote in message
> news:30401819-8EE7-42E3-8898-2F8E75880C40@.microsoft.com...
>
>

email trigger

I'd like to setup a trigger to send an email, but can't use SQLMail because
I don't control the password to the account that is running the MSSQLSERVER
service. Is there any other way to have SQL send an email when an insert is
made to a particular table? Plus IT doesn't like the idea of Outlook
installed on a production server.You could use xp_smtp_mail. It requires that the ability to access a smtp
mail server. Quite 'safe' for a production server -unlike MAPI.
Mail -Sending
SQL 2000 - http://www.sqldev.net/xp/xpsmtp.htm
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:%23IQu$hjxGHA.3892@.TK2MSFTNGP03.phx.gbl...
> I'd like to setup a trigger to send an email, but can't use SQLMail
> because I don't control the password to the account that is running the
> MSSQLSERVER service. Is there any other way to have SQL send an email
> when an insert is made to a particular table? Plus IT doesn't like the
> idea of Outlook installed on a production server.
>|||Hi Colin
It is not really advisable to send emails from triggers as you will
potentially be increasing the transaction time significantly and therefore
increasing contention (blocking/deadlocking etc..). An alternative would be
to populate another table and then periodically have a process that emails
the information and clears the table down.
John
"Colin" wrote:
> I'd like to setup a trigger to send an email, but can't use SQLMail because
> I don't control the password to the account that is running the MSSQLSERVER
> service. Is there any other way to have SQL send an email when an insert is
> made to a particular table? Plus IT doesn't like the idea of Outlook
> installed on a production server.
>
>

email trigger

I'd like to setup a trigger to send an email, but can't use SQLMail because
I don't control the password to the account that is running the MSSQLSERVER
service. Is there any other way to have SQL send an email when an insert is
made to a particular table? Plus IT doesn't like the idea of Outlook
installed on a production server.You could use xp_smtp_mail. It requires that the ability to access a smtp
mail server. Quite 'safe' for a production server -unlike MAPI.
Mail -Sending
SQL 2000 - http://www.sqldev.net/xp/xpsmtp.htm
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:%23IQu$hjxGHA.3892@.TK2MSFTNGP03.phx.gbl...
> I'd like to setup a trigger to send an email, but can't use SQLMail
> because I don't control the password to the account that is running the
> MSSQLSERVER service. Is there any other way to have SQL send an email
> when an insert is made to a particular table? Plus IT doesn't like the
> idea of Outlook installed on a production server.
>|||Hi Colin
It is not really advisable to send emails from triggers as you will
potentially be increasing the transaction time significantly and therefore
increasing contention (blocking/deadlocking etc..). An alternative would be
to populate another table and then periodically have a process that emails
the information and clears the table down.
John
"Colin" wrote:

> I'd like to setup a trigger to send an email, but can't use SQLMail becaus
e
> I don't control the password to the account that is running the MSSQLSERVE
R
> service. Is there any other way to have SQL send an email when an insert
is
> made to a particular table? Plus IT doesn't like the idea of Outlook
> installed on a production server.
>
>

Email results from trigger tables

I'm trying to e-mail the results of a trigger that fires on an employee
table. I'm trying to included the results of either the DELETED or INSERTED
tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
table queries in the @.query = parameter?
I'm trying to send an e-mail to someone whenever a new employee is added or
deleted.
Thanks.
Regardless that doesn′t work because the new session with XP_sendmail won′t
know about your deleted tables, the action you want to perform is not
preferable, because mail sending will be done synchronisly. So if your
mailserver is stuck in a problem and need 5 minutes for sending a mail (for
some reason) your transaction will hold on for that time, if the process
throws an error, your transaction might rollback. You don′t want that, erh ?
I would suggest (as this is not time critical) to write the data in a table
which is regulary checked for content to be sent.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Shawn Barrow" wrote:

> I'm trying to e-mail the results of a trigger that fires on an employee
> table. I'm trying to included the results of either the DELETED or INSERTED
> tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
> table queries in the @.query = parameter?
> I'm trying to send an e-mail to someone whenever a new employee is added or
> deleted.
> Thanks.

Email results from trigger tables

I'm trying to e-mail the results of a trigger that fires on an employee
table. I'm trying to included the results of either the DELETED or INSERTED
tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
table queries in the @.query = parameter?
I'm trying to send an e-mail to someone whenever a new employee is added or
deleted.
Thanks.Regardless that doesn´t work because the new session with XP_sendmail won´t
know about your deleted tables, the action you want to perform is not
preferable, because mail sending will be done synchronisly. So if your
mailserver is stuck in a problem and need 5 minutes for sending a mail (for
some reason) your transaction will hold on for that time, if the process
throws an error, your transaction might rollback. You don´t want that, erh ?
I would suggest (as this is not time critical) to write the data in a table
which is regulary checked for content to be sent.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Shawn Barrow" wrote:
> I'm trying to e-mail the results of a trigger that fires on an employee
> table. I'm trying to included the results of either the DELETED or INSERTED
> tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
> table queries in the @.query = parameter?
> I'm trying to send an e-mail to someone whenever a new employee is added or
> deleted.
> Thanks.

Email results from trigger tables

I'm trying to e-mail the results of a trigger that fires on an employee
table. I'm trying to included the results of either the DELETED or INSERTED
tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTE
D
table queries in the @.query = parameter?
I'm trying to send an e-mail to someone whenever a new employee is added or
deleted.
Thanks.Regardless that doesn′t work because the new session with XP_sendmail won′
t
know about your deleted tables, the action you want to perform is not
preferable, because mail sending will be done synchronisly. So if your
mailserver is stuck in a problem and need 5 minutes for sending a mail (for
some reason) your transaction will hold on for that time, if the process
throws an error, your transaction might rollback. You don′t want that, erh
?
I would suggest (as this is not time critical) to write the data in a table
which is regulary checked for content to be sent.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Shawn Barrow" wrote:

> I'm trying to e-mail the results of a trigger that fires on an employee
> table. I'm trying to included the results of either the DELETED or INSERT
ED
> tables, but nothing is returned. Can xp_sendmail include DELETED or INSER
TED
> table queries in the @.query = parameter?
> I'm trying to send an e-mail to someone whenever a new employee is added o
r
> deleted.
> Thanks.

email on insert

Hi all,

I wanted sql server to shoot an email upon insert into a table. I treated a
trigger on that table as below.

CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
FOR INSERT
AS
exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
table_name table', 'Please investigate and take necessary actions.',
@.cc='', @.BCC = '',
@.Importance=1,
@.Attachments='', @.HTMLFormat = 0,@.From =
'notification@.sqlserver.com'

Is this solution a good method?

Thanks,

Guju"Guju" <patelroshanr@.yahoo.com.au> wrote in message
news:42508c0c_1@.news.iprimus.com.au...
> Hi all,
> I wanted sql server to shoot an email upon insert into a table. I treated
a
> trigger on that table as below.
> CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
> FOR INSERT
> AS
> exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
> table_name table', 'Please investigate and take necessary actions.',
> @.cc='', @.BCC = '',
> @.Importance=1,
> @.Attachments='', @.HTMLFormat = 0,@.From =
> 'notification@.sqlserver.com'
>
> Is this solution a good method?

No.

It will greatly slow down insert speeds.

> Thanks,
> Guju|||Guju wrote:
> Hi all,
> I wanted sql server to shoot an email upon insert into a table. I
treated a
> trigger on that table as below.
> CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
> FOR INSERT
> AS
> exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
> table_name table', 'Please investigate and take necessary actions.',
> @.cc='', @.BCC = '',
> @.Importance=1,
> @.Attachments='', @.HTMLFormat = 0,@.From =
> 'notification@.sqlserver.com'
>
> Is this solution a good method?
> Thanks,
> Guju

It may be a better idea to create a script that checks for new records
in the table every x number of hours (run it as an sql agent job).
the script may save the last record id that it already saw in a table
for this purpose.
As mentioned above, the solution you implemented means the email is
sent at the expense of the insert statement , making it horribly slow.
My way you can also send one mail if 10 records were inserted in stead
of 10, with the data of all 10, which you may trust me is more useful
to the sorry person actually receiving these mails.

hope this helps.

Tzvika|||Is it possible for you to post a sample script..I am attempting to get
the same result as the author.|||Is it possible for you to post a sample script..I am attempting to get
the same result as the author.

e-mail Delivery report by trigger or user solicitation.

It's possivel delivery a report after trigger execution ou user solicitation?

Thanks in advance, Rui Figueiredo

Yes, you can externally trigger the execution and email delivery of a report (= report subscription) using the FireEvent SOAP API. Here's a link that explains the required steps:

http://blogs.msdn.com/lukaszp/archive/2005/10/07/478391.aspx

Also check out the RS script utility that will make it easier fire the event:

http://msdn2.microsoft.com/en-us/library/ms162839.aspx

Email Alert

I'm trying to develop a email alert feature on my project. I was trying to approach with SQL trigger wich I think is the best option. Basically, when a new record is inserted into ad table, a email alert goes to peolpe who selected to receive alert wehen certain conditions are met. What would be the best approach? any examples?
ThanksThat sounds a lot likeSQL Server Notification Services, a free download if you are using SQL Server.
|||Thanks for the reply. Is MS Notification Services SP1 what I need?

Efficient Query

Hi, I need an expert help on this, i have this query, but it seems to
trigger the parallelism on execution, i want to get around that and have a
more efficient query, can some please help me with this query. thank you in
advance.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SELECT DISTINCT
ProductOrder.*,
Agency.AgencyName,
zStatus.Status,
zStatus.StatusID,
Asset.AssetTypeID
FROM
ProductOrder
INNER JOIN
[User] ON ProductOrder.UserGUID = [User].UserGUID
LEFT OUTER JOIN
GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
LEFT OUTER JOIN
[Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
LEFT OUTER JOIN
Agency ON [Group].AgencyGUID = Agency.AgencyGUID
INNER JOIN
ProductOrderItem on ProductOrder.ProductOrderGUID = ProductOrderItem.ProductOrderGUID
INNER JOIN
zStatus on ProductOrderItem.Status = zStatus.StatusID
LEFT OUTER JOIN
Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
WHERE
zStatus.StatusOrder IN
(SELECT
MIN(zStatus.StatusOrder)
FROM
ProductOrderItem
INNER JOIN
zStatus ON ProductOrderItem.Status = zStatus.StatusID
WHERE
ProductOrderItem.ProductOrderGUID = ProductOrder.ProductOrderGUID)
AND
ProductOrder.OrderTypeID = 4
AND
zStatus.StatusCompleted = 0
AND
ProductOrder.IsBasket = 0
AND
zStatus.StatusID <> 47
ORDER BY
DateCreated ASCJust a quick note..
You can set the "Cost Threshold for Parallelism" option to a higher value to
essentially turn off parallel query execution.
You can set it from 0 - 32k. The default is 5.
You may want to read up on the affinity mask (if you are using multiple
processors) and the max degree of parallelism option which can affect the
Cost Threshold.
Rick
"Daniel" <danielk@.adstream.com.au> wrote in message
news:%23wVcefujEHA.2788@.tk2msftngp13.phx.gbl...
> Hi, I need an expert help on this, i have this query, but it seems to
> trigger the parallelism on execution, i want to get around that and have a
> more efficient query, can some please help me with this query. thank you
in
> advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID => ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status = zStatus.StatusID
> WHERE
> ProductOrderItem.ProductOrderGUID => ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
> ORDER BY
> DateCreated ASC
>|||Daniel wrote:
> Hi, I need an expert help on this, i have this query, but it seems to
> trigger the parallelism on execution, i want to get around that and
> have a more efficient query, can some please help me with this query.
> thank you in advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID => ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status => zStatus.StatusID WHERE
> ProductOrderItem.ProductOrderGUID => ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
> ORDER BY
> DateCreated ASC
If you add a MAXDOP 1 to the query, it will only use one processor.
--
David G.|||thanks for the reply. But as far as i know, setting the cost threshold would
have set from the server. I only want to make changes to this query.
I don't want to have the parallelism because, the result of the query is
quite small, probably less than 20 rows. that's why, I think, this
paralellism is quite expensive for this query. Do you think the query can be
made more efficient, or is it already as efficient as it can be..' thank
you.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:Ovg6youjEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Just a quick note..
> You can set the "Cost Threshold for Parallelism" option to a higher value
> to
> essentially turn off parallel query execution.
> You can set it from 0 - 32k. The default is 5.
>
> You may want to read up on the affinity mask (if you are using multiple
> processors) and the max degree of parallelism option which can affect the
> Cost Threshold.
>
> Rick
>
> "Daniel" <danielk@.adstream.com.au> wrote in message
> news:%23wVcefujEHA.2788@.tk2msftngp13.phx.gbl...
>> Hi, I need an expert help on this, i have this query, but it seems to
>> trigger the parallelism on execution, i want to get around that and have
>> a
>> more efficient query, can some please help me with this query. thank you
> in
>> advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status = zStatus.StatusID
>> WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
>>
>|||it doesn't really help, it even make it worse... well, what i'm trying to
reduce is the subtree cost... right now, my subtree cost is 110...
that's way too high... that's because i have a distinct. if i take of the
distinct, it still cost me 31... i'm trying to get it down as low as 10...
anyone can help me with this..'
"David G." <david_nospam@.nospam.com> wrote in message
news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
> Daniel wrote:
>> Hi, I need an expert help on this, i have this query, but it seems to
>> trigger the parallelism on execution, i want to get around that and
>> have a more efficient query, can some please help me with this query.
>> thank you in advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status =>> zStatus.StatusID WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
> If you add a MAXDOP 1 to the query, it will only use one processor.
> --
> David G.|||Daniel wrote:
> it doesn't really help, it even make it worse... well, what i'm
> trying to reduce is the subtree cost... right now, my subtree cost
> is 110...
> that's way too high... that's because i have a distinct. if i take
> of the distinct, it still cost me 31... i'm trying to get it down as
> low as 10... anyone can help me with this..'
>
> "David G." <david_nospam@.nospam.com> wrote in message
> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
>> Daniel wrote:
>> Hi, I need an expert help on this, i have this query, but it seems
>> to trigger the parallelism on execution, i want to get around that
>> and have a more efficient query, can some please help me with this
>> query. thank you in advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status =>> zStatus.StatusID WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
>> If you add a MAXDOP 1 to the query, it will only use one processor.
>> --
>> David G.
You said in your original post you want to get rid of the parallelism.
Are you saying that what you really want is the query to run more
efficiently? If so, try either reducing the number of tables (joins) in
the query. Also try removing the zStatus.StatusOrder IN subselect. It
looks like this subselect returns one value using the MIN(). if so, try
grabbing that value first and building the query using sp_executesql
since local variables used as bind variables in a stored procedure may
not be optimized well by SQL Server.
Also, what are the performance stats for the query? What amount of CPU
is used? How many reads? Are table scans being performed? If so, on what
tables? Are indexes in place to prevent the scan operations?
David G.|||> You said in your original post you want to get rid of the parallelism.
> Are you saying that what you really want is the query to run more
> efficiently? If so, try either reducing the number of tables (joins) in
> the query. Also try removing the zStatus.StatusOrder IN subselect. It
> looks like this subselect returns one value using the MIN(). if so, try
> grabbing that value first and building the query using sp_executesql
> since local variables used as bind variables in a stored procedure may
> not be optimized well by SQL Server.
> Also, what are the performance stats for the query? What amount of CPU
> is used? How many reads? Are table scans being performed? If so, on what
> tables? Are indexes in place to prevent the scan operations?
how can i see all those things...' performance stats, cpu used, reads..
the estimate row counts is 35,700 and the subtreecost is 110.
i don't have any table scan as everything is using either clustered index
scan or index seek.
it looks quite efficient, but it's still expensive. is there any other way
to optimize the query..'
the process that is most expensive is the distinct, but i cannot live
without it...
and i can't remove the zstatusorder min(), as it is checking the the lowest
status of the productorderitem of each productorder
and i don't think i can reduce the tables coz i need all of them...
"David G." <david_nospam@.nospam.com> wrote in message
news:uQagcYxjEHA.1040@.TK2MSFTNGP10.phx.gbl...
> Daniel wrote:
>> it doesn't really help, it even make it worse... well, what i'm
>> trying to reduce is the subtree cost... right now, my subtree cost
>> is 110...
>> that's way too high... that's because i have a distinct. if i take
>> of the distinct, it still cost me 31... i'm trying to get it down as
>> low as 10... anyone can help me with this..'
>>
>> "David G." <david_nospam@.nospam.com> wrote in message
>> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
>> Daniel wrote:
>> Hi, I need an expert help on this, i have this query, but it seems
>> to trigger the parallelism on execution, i want to get around that
>> and have a more efficient query, can some please help me with this
>> query. thank you in advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status =>> zStatus.StatusID WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
>> If you add a MAXDOP 1 to the query, it will only use one processor.
>> --
>> David G.
> You said in your original post you want to get rid of the parallelism.
> Are you saying that what you really want is the query to run more
> efficiently? If so, try either reducing the number of tables (joins) in
> the query. Also try removing the zStatus.StatusOrder IN subselect. It
> looks like this subselect returns one value using the MIN(). if so, try
> grabbing that value first and building the query using sp_executesql
> since local variables used as bind variables in a stored procedure may
> not be optimized well by SQL Server.
> Also, what are the performance stats for the query? What amount of CPU
> is used? How many reads? Are table scans being performed? If so, on what
> tables? Are indexes in place to prevent the scan operations?
>
> --
> David G.
>|||On Tue, 31 Aug 2004 09:19:56 +1000, "Daniel" <danielk@.adstream.com.au>
wrote:
>Hi, I need an expert help on this, i have this query, but it seems to
>trigger the parallelism on execution, i want to get around that and have a
>more efficient query, can some please help me with this query. thank you in
>advance.
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> SET NOCOUNT ON
> SELECT DISTINCT
> ProductOrder.*,
I'm no expert, but I read loads of times not to use * in the select on
production server. So thats the advice I'll offer here.
Name the ProductOrder columns you want (even if theres 50 of them).
HTH
al.
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM
> ProductOrder
> INNER JOIN
> [User] ON ProductOrder.UserGUID = [User].UserGUID
> LEFT OUTER JOIN
> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> LEFT OUTER JOIN
> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> LEFT OUTER JOIN
> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> INNER JOIN
> ProductOrderItem on ProductOrder.ProductOrderGUID =>ProductOrderItem.ProductOrderGUID
> INNER JOIN
> zStatus on ProductOrderItem.Status = zStatus.StatusID
> LEFT OUTER JOIN
> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> WHERE
> zStatus.StatusOrder IN
> (SELECT
> MIN(zStatus.StatusOrder)
> FROM
> ProductOrderItem
> INNER JOIN
> zStatus ON ProductOrderItem.Status = zStatus.StatusID
> WHERE
> ProductOrderItem.ProductOrderGUID =>ProductOrder.ProductOrderGUID)
> AND
> ProductOrder.OrderTypeID = 4
> AND
> zStatus.StatusCompleted = 0
> AND
> ProductOrder.IsBasket = 0
> AND
> zStatus.StatusID <> 47
>ORDER BY
> DateCreated ASC
>|||On Tue, 31 Aug 2004 17:05:39 +1000, Daniel wrote:
>> You said in your original post you want to get rid of the parallelism.
>> Are you saying that what you really want is the query to run more
>> efficiently? If so, try either reducing the number of tables (joins) in
>> the query. Also try removing the zStatus.StatusOrder IN subselect. It
>> looks like this subselect returns one value using the MIN(). if so, try
>> grabbing that value first and building the query using sp_executesql
>> since local variables used as bind variables in a stored procedure may
>> not be optimized well by SQL Server.
>> Also, what are the performance stats for the query? What amount of CPU
>> is used? How many reads? Are table scans being performed? If so, on what
>> tables? Are indexes in place to prevent the scan operations?
>how can i see all those things...' performance stats, cpu used, reads..
>the estimate row counts is 35,700 and the subtreecost is 110.
>i don't have any table scan as everything is using either clustered index
>scan or index seek.
>it looks quite efficient, but it's still expensive. is there any other way
>to optimize the query..'
>the process that is most expensive is the distinct, but i cannot live
>without it...
>and i can't remove the zstatusorder min(), as it is checking the the lowest
>status of the productorderitem of each productorder
>and i don't think i can reduce the tables coz i need all of them...
Hi Daniel,
A clustered index IS the table, so if you get a clustered index scan, you
have a table scan. There might be room for improvement.
See if supplying a column list instead of SELECT * (as suggested by Harag)
helps. You might also try if it replacing StatusOrder IN (subselect) by
StatusOrder = (subselect) helps. The subselect will never return more than
one value, so they should be equivalent - maybe = instead of IN will give
the optimizer new ideas.
For further help, you'll have to provide more information: the design of
your tables (CREATE TABLE statements, including all constraints and all
indexes but excluding irrelevant columns), some sample data (as INSERT
statements, so we don't have to type them ourselves <g>) and expected
output based on the sample data. Plus a description of the business
problem.
See http://www.aspfaq.com/etiquette.asp?id=5006
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Daniel,
Don't focus too much on an individual 'expensive' part of the query
plan. In the end, the only thing that matters is the total elapsed time
of the query. Proper indexing and rewriting the query can dramatically
change the optimizer strategy (= query plan).
It looks like you can remove your IN by rewriting it as below. Of
course, you need to test if the results are still correct.
set quoted_identifier on
SELECT DISTINCT
ProductOrder.*,
Agency.AgencyName,
zStatus.Status,
zStatus.StatusID,
Asset.AssetTypeID
FROM ProductOrder
INNER JOIN ProductOrderItem ON ProductOrder.ProductOrderGUID =ProductOrderItem.ProductOrderGUID
INNER JOIN zStatus ON ProductOrderItem.Status =zStatus.StatusID
INNER JOIN "User" ON ProductOrder.UserGUID ="User".UserGUID
LEFT JOIN GroupMapping ON "User".UserGUID =GroupMapping.UserGUID
LEFT JOIN "Group" ON GroupMapping.GroupGUID ="Group".GroupGUID
LEFT JOIN Agency ON "Group".AgencyGUID =Agency.AgencyGUID
LEFT JOIN Asset ON Asset.AssetGUID =ProductOrderItem.AssetGUID
WHERE zStatus.StatusOrder = (
SELECT MIN(zStatus.StatusOrder)
FROM ProductOrderItem
INNER JOIN zStatus ON ProductOrderItem.Status =zStatus.StatusID
WHERE ProductOrderItem.ProductOrderGUID =ProductOrder.ProductOrderGUID
)
AND ProductOrder.OrderTypeID = 4
AND zStatus.StatusCompleted = 0
AND ProductOrder.IsBasket = 0
AND zStatus.StatusID <> 47
ORDER BY DateCreated ASC
If you expect just one row for each combination of
(ProductOrder.*,zStatus.Status,Status.StatusID), then you can remove
DISTINCT by rewriting the LEFT OUTER JOINs as scalar subqueries.
For example, you can rewrite
SELECT DISTINCT ... , Asset.AssetTypeID
FROM ...
LEFT JOIN Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
as
SELECT ... , (
SELECT AssetTypeID
FROM Asset
WHERE Asset.AssetGUID = ProductOrderItem.AssetGUID
) AS AssetTypeID
FROM ...
Hope this helps,
Gert-Jan
Daniel wrote:
> it doesn't really help, it even make it worse... well, what i'm trying to
> reduce is the subtree cost... right now, my subtree cost is 110...
> that's way too high... that's because i have a distinct. if i take of the
> distinct, it still cost me 31... i'm trying to get it down as low as 10...
> anyone can help me with this..'
> "David G." <david_nospam@.nospam.com> wrote in message
> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
> > Daniel wrote:
> >> Hi, I need an expert help on this, i have this query, but it seems to
> >> trigger the parallelism on execution, i want to get around that and
> >> have a more efficient query, can some please help me with this query.
> >> thank you in advance.
> >>
> >> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> >> SET NOCOUNT ON
> >>
> >> SELECT DISTINCT
> >> ProductOrder.*,
> >> Agency.AgencyName,
> >> zStatus.Status,
> >> zStatus.StatusID,
> >> Asset.AssetTypeID
> >> FROM
> >> ProductOrder
> >> INNER JOIN
> >> [User] ON ProductOrder.UserGUID = [User].UserGUID
> >> LEFT OUTER JOIN
> >> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
> >> LEFT OUTER JOIN
> >> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
> >> LEFT OUTER JOIN
> >> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
> >> INNER JOIN
> >> ProductOrderItem on ProductOrder.ProductOrderGUID => >> ProductOrderItem.ProductOrderGUID
> >> INNER JOIN
> >> zStatus on ProductOrderItem.Status = zStatus.StatusID
> >> LEFT OUTER JOIN
> >> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> >> WHERE
> >> zStatus.StatusOrder IN
> >> (SELECT
> >> MIN(zStatus.StatusOrder)
> >> FROM
> >> ProductOrderItem
> >> INNER JOIN
> >> zStatus ON ProductOrderItem.Status => >> zStatus.StatusID WHERE
> >> ProductOrderItem.ProductOrderGUID => >> ProductOrder.ProductOrderGUID)
> >> AND
> >> ProductOrder.OrderTypeID = 4
> >> AND
> >> zStatus.StatusCompleted = 0
> >> AND
> >> ProductOrder.IsBasket = 0
> >> AND
> >> zStatus.StatusID <> 47
> >> ORDER BY
> >> DateCreated ASC
> >
> > If you add a MAXDOP 1 to the query, it will only use one processor.
> >
> > --
> > David G.
--
(Please reply only to the newsgroup)|||Gert-Jan Strik wrote:
> Hi Daniel,
> Don't focus too much on an individual 'expensive' part of the query
> plan. In the end, the only thing that matters is the total elapsed
> time of the query. Proper indexing and rewriting the query can
> dramatically change the optimizer strategy (= query plan).
>
> It looks like you can remove your IN by rewriting it as below. Of
> course, you need to test if the results are still correct.
> set quoted_identifier on
> SELECT DISTINCT
> ProductOrder.*,
> Agency.AgencyName,
> zStatus.Status,
> zStatus.StatusID,
> Asset.AssetTypeID
> FROM ProductOrder
> INNER JOIN ProductOrderItem ON ProductOrder.ProductOrderGUID => ProductOrderItem.ProductOrderGUID
> INNER JOIN zStatus ON ProductOrderItem.Status => zStatus.StatusID
> INNER JOIN "User" ON ProductOrder.UserGUID => "User".UserGUID
> LEFT JOIN GroupMapping ON "User".UserGUID => GroupMapping.UserGUID
> LEFT JOIN "Group" ON GroupMapping.GroupGUID => "Group".GroupGUID
> LEFT JOIN Agency ON "Group".AgencyGUID => Agency.AgencyGUID
> LEFT JOIN Asset ON Asset.AssetGUID => ProductOrderItem.AssetGUID
> WHERE zStatus.StatusOrder = (
> SELECT MIN(zStatus.StatusOrder)
> FROM ProductOrderItem
> INNER JOIN zStatus ON ProductOrderItem.Status => zStatus.StatusID
> WHERE ProductOrderItem.ProductOrderGUID => ProductOrder.ProductOrderGUID
> )
> AND ProductOrder.OrderTypeID = 4
> AND zStatus.StatusCompleted = 0
> AND ProductOrder.IsBasket = 0
> AND zStatus.StatusID <> 47
> ORDER BY DateCreated ASC
>
> If you expect just one row for each combination of
> (ProductOrder.*,zStatus.Status,Status.StatusID), then you can remove
> DISTINCT by rewriting the LEFT OUTER JOINs as scalar subqueries.
> For example, you can rewrite
> SELECT DISTINCT ... , Asset.AssetTypeID
> FROM ...
> LEFT JOIN Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
> as
> SELECT ... , (
> SELECT AssetTypeID
> FROM Asset
> WHERE Asset.AssetGUID = ProductOrderItem.AssetGUID
> ) AS AssetTypeID
> FROM ...
> Hope this helps,
> Gert-Jan
>
> Daniel wrote:
>> it doesn't really help, it even make it worse... well, what i'm
>> trying to reduce is the subtree cost... right now, my subtree cost
>> is 110...
>> that's way too high... that's because i have a distinct. if i take
>> of the distinct, it still cost me 31... i'm trying to get it down
>> as low as 10... anyone can help me with this..'
>> "David G." <david_nospam@.nospam.com> wrote in message
>> news:eKXapvujEHA.2680@.TK2MSFTNGP15.phx.gbl...
>> Daniel wrote:
>> Hi, I need an expert help on this, i have this query, but it seems
>> to trigger the parallelism on execution, i want to get around that
>> and have a more efficient query, can some please help me with this
>> query. thank you in advance.
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> SET NOCOUNT ON
>> SELECT DISTINCT
>> ProductOrder.*,
>> Agency.AgencyName,
>> zStatus.Status,
>> zStatus.StatusID,
>> Asset.AssetTypeID
>> FROM
>> ProductOrder
>> INNER JOIN
>> [User] ON ProductOrder.UserGUID = [User].UserGUID
>> LEFT OUTER JOIN
>> GroupMapping ON [User].UserGUID = GroupMapping.UserGUID
>> LEFT OUTER JOIN
>> [Group] ON GroupMapping.GroupGUID = [Group].GroupGUID
>> LEFT OUTER JOIN
>> Agency ON [Group].AgencyGUID = Agency.AgencyGUID
>> INNER JOIN
>> ProductOrderItem on ProductOrder.ProductOrderGUID =>> ProductOrderItem.ProductOrderGUID
>> INNER JOIN
>> zStatus on ProductOrderItem.Status = zStatus.StatusID
>> LEFT OUTER JOIN
>> Asset ON Asset.AssetGUID = ProductOrderItem.AssetGUID
>> WHERE
>> zStatus.StatusOrder IN
>> (SELECT
>> MIN(zStatus.StatusOrder)
>> FROM
>> ProductOrderItem
>> INNER JOIN
>> zStatus ON ProductOrderItem.Status =>> zStatus.StatusID WHERE
>> ProductOrderItem.ProductOrderGUID =>> ProductOrder.ProductOrderGUID)
>> AND
>> ProductOrder.OrderTypeID = 4
>> AND
>> zStatus.StatusCompleted = 0
>> AND
>> ProductOrder.IsBasket = 0
>> AND
>> zStatus.StatusID <> 47
>> ORDER BY
>> DateCreated ASC
>> If you add a MAXDOP 1 to the query, it will only use one processor.
>> --
>> David G.
And I would add that you should eliminate any Order By clauses from
production queries unless the application processing the returned data
absolutely needs the information returned sorted. Most apps either won't
care about the order of the rows or will use some type of grid control
that can be easily sorted on the client. Sorting results just adds
overhead to a query.
--
David G.|||thanks guys, much appreciated for your reply.
i've changed the 'in' to '=' and the have changed the *, but it doesn't help
much.
well, i'm handing that query over to my guru to check. i'll let you know
after he finished optimizing it.
cheers,
Daniel R. Kusnadi