May 6, 2015 at 9:44 am
I have this query which runs every night pulls 800,000+ records. I just wanted to know if there is a way to make it run better.
SELECT Audit_Object_Type AS [Audit_Object_Type_Int] ,
Field ,
Field_Type AS [Field_Type_Int] ,
From_Value ,
To_Value ,
Updated_By_Login ,
Updated_By ,
Parent_Hierarchy_Key ,
DATEADD(S, Create_Date, '1970-01-01 00:00:00') AS [Create Date] ,
Request_ID
FROM COL_CORE_Audit_Data
WHERE Modified_Date >= (DATEDIFF(s, '19700101', GETDATE())- 2678400);
I also look at the execution plan and I see there is Clustered Index Scan which is same as the table scan. So I ended up creating a non clustered index on the column (Modified_Date), including all the columns but the query is still using different Index. I even ran the query with
WITH (INDEX(Test)), but it still uses different index. Any help will be highly appreciated. I also included the execution plan for anyone to look at and make some recommendation.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 6, 2015 at 9:55 am
New Born DBA (5/6/2015)
I have this query which runs every night pulls 800,000+ records. I just wanted to know if there is a way to make it run better.
SELECT Audit_Object_Type AS [Audit_Object_Type_Int] ,Field ,
Field_Type AS [Field_Type_Int] ,
From_Value ,
To_Value ,
Updated_By_Login ,
Updated_By ,
Parent_Hierarchy_Key ,
DATEADD(S, Create_Date, '1970-01-01 00:00:00') AS [Create Date] ,
Request_ID
FROM COL_CORE_Audit_Data
WHERE Modified_Date >= (DATEDIFF(s, '19700101', GETDATE())- 2678400);
I also look at the execution plan and I see there is Clustered Index Scan which is same as the table scan. So I ended up creating a non clustered index on the column (Modified_Date), including all the columns but the query is still using different Index. I even ran the query with
WITH (INDEX(Test)), but it still uses different index. Any help will be highly appreciated. I also included the execution plan for anyone to look at and make some recommendation.
Looks like you are storing date information as seconds since midnight 1970-01-01, correct? So, what is the where clause attempting to filter?
May 6, 2015 at 10:05 am
Lynn Pettis (5/6/2015)
Looks like you are storing date information as seconds since midnight 1970-01-01, correct?
Yes, that is correct.
So, what is the where clause attempting to filter?
Sorry, but I don't know.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 6, 2015 at 10:21 am
So, what is the where clause attempting to filter?
the last 31 days...?????
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 6, 2015 at 10:22 am
New Born DBA (5/6/2015)
Lynn Pettis (5/6/2015)
Looks like you are storing date information as seconds since midnight 1970-01-01, correct?
Yes, that is correct.
So, what is the where clause attempting to filter?
Sorry, but I don't know.
Query returns all data modified in the past 31 days.
Without seeing the indexes defined on the table, not much more can be done.
May 6, 2015 at 10:24 am
New Born DBA (5/6/2015)
Lynn Pettis (5/6/2015)
Looks like you are storing date information as seconds since midnight 1970-01-01, correct?
Yes, that is correct.
So, what is the where clause attempting to filter?
Sorry, but I don't know.
Yes
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 6, 2015 at 10:25 am
J Livingston SQL (5/6/2015)the last 31 days...?????
Yes
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 6, 2015 at 10:29 am
Query returns all data modified in the past 31 days.
Without seeing the indexes defined on the table, not much more can be done.
Well the thing is this query uses this index.
/****** Object: Index [PK__T387__32149A1373FAF2FC] Script Date: 05/06/2015 11:28:12 ******/
ALTER TABLE [dbo].[T387] ADD PRIMARY KEY CLUSTERED
(
[C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 6, 2015 at 10:29 am
New Born DBA (5/6/2015)
Query returns all data modified in the past 31 days.
Without seeing the indexes defined on the table, not much more can be done.
Index Info.
/****** Object: Index [PK__T387__32149A1373FAF2FC] Script Date: 05/06/2015 11:28:12 ******/ALTER TABLE [dbo].[T387] ADD PRIMARY KEY CLUSTERED
(
[C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 6, 2015 at 10:38 am
New Born DBA (5/6/2015)
New Born DBA (5/6/2015)
Query returns all data modified in the past 31 days.
Without seeing the indexes defined on the table, not much more can be done.
Index Info.
/****** Object: Index [PK__T387__32149A1373FAF2FC] Script Date: 05/06/2015 11:28:12 ******/ALTER TABLE [dbo].[T387] ADD PRIMARY KEY CLUSTERED
(
[C1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Well, that doesn't really help as what the heck is C1? Could also use the DDL for the table itself.
May 6, 2015 at 10:53 am
Could be that the index on Modified_date is not selective enough. Or, that statistics are outdated and the optimizer just thinks that it is not selective enough.
Try sp_updatestats to see if that helps.
May 6, 2015 at 11:01 am
See the issue is that I don't even know why SQL Server uses this Index? This Index is useless in this situation because column C1 is ID column which is not in the query.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 6, 2015 at 11:08 am
New Born DBA (5/6/2015)
See the issue is that I don't even know why SQL Server uses this Index? This Index is useless in this situation because column C1 is ID column which is not in the query.
For what ever reason, SQL Server believes that a clustered index scan is the most efficient means of retrieving the data.
Would still help to see the DDL (CREATE TABLE statement) and all the indexes defined on the table.
May 6, 2015 at 11:09 am
Lynn Pettis (5/6/2015)
New Born DBA (5/6/2015)
See the issue is that I don't even know why SQL Server uses this Index? This Index is useless in this situation because column C1 is ID column which is not in the query.For what ever reason, SQL Server believes that a clustered index scan is the most efficient means of retrieving the data.
Would still help to see the DDL (CREATE TABLE statement) and all the indexes defined on the table.
Just FYI, It is a View
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 6, 2015 at 11:15 am
New Born DBA (5/6/2015)
Lynn Pettis (5/6/2015)
New Born DBA (5/6/2015)
See the issue is that I don't even know why SQL Server uses this Index? This Index is useless in this situation because column C1 is ID column which is not in the query.For what ever reason, SQL Server believes that a clustered index scan is the most efficient means of retrieving the data.
Would still help to see the DDL (CREATE TABLE statement) and all the indexes defined on the table.
Just FYI, It is a View
So COL_CORE_Audit_Data is a view? Need to see the DDL for the view and the underlying tables and the indexes on the tables.
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply