April 30, 2010 at 5:07 am
HI i need better approach for the below one.i know this is very straight forward query but if somebody can help me here . greatly appreciated.create TABLE #Tmp (acct_id ut_synthetic_key,message_stub ut_stub)
CREATE CLUSTERED INDEX cix_tmp_acct_id_message_stub ON #Tmp (acct_id, message_stub)
CREATE TABLE [dbo].[EMAIL_HISTORY](
[message_stub] [dbo].[ut_stub] NOT NULL,
[acct_id] [dbo].[ut_synthetic_key] NOT NULL,
[evt_stub] [dbo].[ut_stub] NOT NULL,
[cont_stub] [dbo].[ut_stub] NOT NULL,
[invitee_stub] [dbo].[ut_stub] NOT NULL,
[act_email_stub] [dbo].[ut_stub] NOT NULL,
[tl_stub] [dbo].[ut_stub] NOT NULL,
[from_email_addr] [dbo].[ut_email] NOT NULL,
[to_email_addr] [dbo].[ut_email] NOT NULL,
[dm_smtp_code] [dbo].[ut_short_description] NOT NULL CONSTRAINT [DF_EMAIL_HISTORY_dm_smtp_code] DEFAULT (''),
[delay_sensitive_flag] [dbo].[ut_flag] NOT NULL,
[email_status_id] [smallint] NOT NULL,
[email_type_id] [smallint] NOT NULL,
[email_viewed_flag] [dbo].[ut_flag] NOT NULL,
[dm_log_date] [datetime] NOT NULL CONSTRAINT [DF_EMAIL_HISTORY_dm_log_date] DEFAULT ('1900-01-01'),
[dm_log_reason] [varchar](60) NULL,
[dm_log_response] [varchar](200) NULL,
[permanent_bounced_flag] [dbo].[ut_flag] NOT NULL,
[created_date] [datetime] NOT NULL,
[last_modified_date] [datetime] NOT NULL,
[email_queue_stub] [dbo].[ut_stub] NOT NULL,
[prod_stub] [dbo].[ut_stub] NULL,
[email_clicked_flag] [dbo].[ut_flag] NULL,
[email_first_clicked_date] [datetime] NULL,
[evt_email_stub] [dbo].[ut_stub] NULL,
CONSTRAINT [PK_EMAIL_HISTORY_message_stub_created_date] PRIMARY KEY NONCLUSTERED
(
[message_stub] ASC,
[created_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_HalfYear_Month]([created_date])
)
Query :
delete e
FROM dbo.EMAIL_HISTORY e
INNER JOIN dbo.#Tmp t
ON e.acct_id = t.acct_id AND
e.message_stub = t.message_stub
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 30, 2010 at 8:14 am
There's little you're going to be able to do. You're selecting a sufficiently large number of rows for deletion that you need scans to retrieve them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2010 at 2:14 pm
To follow up what Grant said, do you have a way you can limit the results to be deleted? Add a range to the query or something that can break it up?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 2, 2010 at 2:45 am
CirquedeSQLeil (4/30/2010)
To follow up what Grant said, do you have a way you can limit the results to be deleted? Add a range to the query or something that can break it up?
you mean to say taking BATCH approach.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 2, 2010 at 8:00 am
Yes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2010 at 2:51 am
Bhuvnesh (4/30/2010)
HI i need better approach for the below one.i know this is very straight forward query but if somebody can help me here . greatly appreciated.
1. When deleting a huge number of rows, it is often faster to drop the non-clustered indexes first, and re-create them (under the BULK_LOGGED recovery model) after the delete.
2. Consider using SELECT...INTO (under the BULK_LOGGED recovery model) to save the rows to keep, drop the original table, and rename the 'saved' table. Recreate indexes.
3. Batch the deletions - see http://www.sqlservercentral.com/articles/67898/
Other thoughts:
1. Consider making this a clustered table. Most tables benefit from having a clustered index - especially partitioned ones.
2. If you must keep it as a heap, and you have SQL Server 2008, issue ALTER TABLE x REBUILD; after the deletion and before the index creation step to optimise storage and remove forwarded record pointers.
Is this intentionally a partitioned heap?
Paul
May 3, 2010 at 5:36 am
Paul White NZ (5/3/2010)
Bhuvnesh (4/30/2010)
HI i need better approach for the below one.i know this is very straight forward query but if somebody can help me here . greatly appreciated.1. When deleting a huge number of rows, it is often faster to drop the non-clustered indexes first, and re-create them (under the BULK_LOGGED recovery model) after the delete.
2. Consider using SELECT...INTO (under the BULK_LOGGED recovery model) to save the rows to keep, drop the original table, and rename the 'saved' table. Recreate indexes.
3. Batch the deletions - see http://www.sqlservercentral.com/articles/67898/
Other thoughts:
1. Consider making this a clustered table. Most tables benefit from having a clustered index - especially partitioned ones.
2. If you must keep it as a heap, and you have SQL Server 2008, issue ALTER TABLE x REBUILD; after the deletion and before the index creation step to optimise storage and remove forwarded record pointers.
Is this intentionally a partitioned heap?
Paul
Oh Excellent Paul, I completely spaced that it was a heap that I was looking at.
Yes, unless you've got a very good reason for it, most tables should be stored with a clustered index. So sorry I wasn't paying proper attention.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 3, 2010 at 6:21 am
Paul White NZ (5/3/2010)
Bhuvnesh (4/30/2010)
HI i need better approach for the below one.i know this is very straight forward query but if somebody can help me here . greatly appreciated.Is this intentionally a partitioned heap?
yes this is partitioned table
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 3, 2010 at 7:18 am
Bhuvnesh (5/3/2010)
yes this is partitioned table
I know - I was wondering why it is a partitioned *heap* rather than a clustered table. That's quite unusual.
May 3, 2010 at 7:23 am
Paul White NZ (5/3/2010)
Bhuvnesh (5/3/2010)
yes this is partitioned tableI know - I was wondering why it is a partitioned *heap* rather than a clustered table. That's quite unusual.
i am not sure but may be because if loys of uniqueidentifier column ( all suffix with "stub") i think ( read many places) making uniqueidentifier column as key column can lead to bad performers.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 3, 2010 at 9:42 am
Bhuvnesh (5/3/2010)
I am not sure but may be because if loys of uniqueidentifier column ( all suffix with "stub") i think ( read many places) making uniqueidentifier column as key column can lead to bad performers.
Ok well I'll stop going on about it now, so the thread can get back to the original point about deleting rows...:-D
May 4, 2010 at 7:31 am
Bhuvnesh (5/3/2010)
Paul White NZ (5/3/2010)
Bhuvnesh (5/3/2010)
yes this is partitioned tableI know - I was wondering why it is a partitioned *heap* rather than a clustered table. That's quite unusual.
i am not sure but may be because if loys of uniqueidentifier column ( all suffix with "stub") i think ( read many places) making uniqueidentifier column as key column can lead to bad performers.
It can be a screaming nightmare performance wise if you use a GUID (or other non-monotonic data element) for your clustered key - you are entirely correct there, but don't assume (as I've seen people do many times) that your primary key *has* to be your clustered index if you have one. CreatedDate might be a reasonable candidate for example, especially if you query often on from date to date
May 4, 2010 at 8:22 am
Andrew Gothard-467944 (5/4/2010)
...CreatedDate might be a reasonable candidate for example, especially if you query often on from date to date
And especially especially since CreatedDate is the partitioning column.
May 4, 2010 at 8:29 am
thanks a ton to all guys , i will keep ur thought and work acc to them while working in it , thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply