September 18, 2009 at 8:57 am
select empid, empspec
into tmpempids
from dbo.vwEmp as a inner join tmpeligdates as b
on a.CustID=b.CustID, tmpEmpRange
where (servcdate between job_start and job_end)
and
(servcdate between efdate and erdate)
and
(empcode between '99201' and '99205' or empcode between '99211' and '99215' or
empcode between '99241' and '99245' or empcode between '99354' and '99355' or
empcode between '99381' and '99406' or empcode between '90804' and '90815' or
empcode IN('90845', '90862', '90875'))
AND (empid not like 'ITS%')
AND (ECFlag = 'P')
Index
CREATE NONCLUSTERED INDEX [index_ecode] ON [dbo].[Emp_Rev]
(
[empcode] ASC,
[ServcDate] ASC,
[CustID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Part_Schm_Month]([ServcDate])
Above query takes 36 min to run which effects 3126899, is there a way i can improve performance.
can an indexed view improve performance, how would i create though there is a physical index already existing.
September 18, 2009 at 9:22 am
Tara, please read the second article I have linked in my signature problem regarding performance problems. Follow the instructions in that article regarding what you should post to get the best help.
September 18, 2009 at 9:42 am
Actually i am looking help to create indexed views or any type of views that can be created on fly during query execution and so i have given my existing index definition.
September 18, 2009 at 11:46 am
That may be what you are looking for, but don't restrict yourself to just that. There may be other things that maybe better than an indexed view. Leave your options open.
Regarding an indexed view, please post the code for the view you want to index, please.
September 18, 2009 at 12:03 pm
There's a good chance that this
(empcode between '99201' and '99205' or empcode between '99211' and '99215' or
empcode between '99241' and '99245' or empcode between '99354' and '99355' or
empcode between '99381' and '99406' or empcode between '90804' and '90815' or
empcode IN('90845', '90862', '90875'))
is stopping the optimiser from using your index. Very often, an OR on a column will make the optimer abandon the use of what seems to be a sensible index.
Is this the only index on the table, or does it also have clustered index?
Unless you post some of the DDL that Lynne asked for, I doubt you'll get much further here.
September 18, 2009 at 12:15 pm
You may have an index, but is it getting used? Have you looked at the actual execution plan? Can you post it?
"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
September 18, 2009 at 12:25 pm
If nothing else, the NOT LIKE will likely force a scan.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2009 at 12:25 pm
Ian Scarlett (9/18/2009)
There's a good chance that this(empcode between '99201' and '99205' or empcode between '99211' and '99215' or
empcode between '99241' and '99245' or empcode between '99354' and '99355' or
empcode between '99381' and '99406' or empcode between '90804' and '90815' or
empcode IN('90845', '90862', '90875'))
is stopping the optimiser from using your index. Very often, an OR on a column will make the optimer abandon the use of what seems to be a sensible index.
Is this the only index on the table, or does it also have clustered index?
Unless you post some of the DDL that Lynne asked for, I doubt you'll get much further here.
My bet is that this is spot on. Put the 'empcode' values in a temporary table that has a single clustered column, join onto that and see where it gets you. But I'll repeat what everyone else is saying, though using different words:
'Help us to help you'.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 18, 2009 at 12:29 pm
Jeff Moden (9/18/2009)
If nothing else, the NOT LIKE will likely force a scan.
Building on this, empid isn't even in the index, so sounds like a good possibility for a table scan.
September 18, 2009 at 12:34 pm
Is this part hard-coded:
(empcode between '99201' and '99205' or empcode between '99211' and '99215' or
empcode between '99241' and '99245' or empcode between '99354' and '99355' or
empcode between '99381' and '99406' or empcode between '90804' and '90815' or
empcode IN('90845', '90862', '90875')
If so, you'll be better off creating a table of valid values and joining to that, than using all those "or" and "between" statements. It'll perform better, and it'll be more predictable. (Predictable because "between" on strings can do very odd things. For example, in string comparison, "12" is between "1" and "2". So is "1bob", for that matter.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 18, 2009 at 12:56 pm
Lynn Pettis (9/18/2009)
Building on this, empid isn't even in the index, so sounds like a good possibility for a table scan.
...in fact - do we even know that that index is on the table that's referenced by the view?
And what is the tmpEmpRange table doing?
Where am i?
Are there free sweets? 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 18, 2009 at 1:06 pm
Lynn Pettis (9/18/2009)
Jeff Moden (9/18/2009)
If nothing else, the NOT LIKE will likely force a scan.Building on this, empid isn't even in the index, so sounds like a good possibility for a table scan.
Agreed. On top of that - if you really want to help perf, make sure to include those columns in the SELECT (that are not in the WHERE clause) in the index under the included columns.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 18, 2009 at 1:40 pm
You could also take the OR's and do a union instead.
Select
From
table
Where
column between a and b AND
other_criteria
UNION
Select
From
table
Where
column between c and d AND
other_criteria
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 28, 2009 at 8:42 am
Jack Corbett (9/18/2009)
You could also take the OR's and do a union instead.
Select
From
table
Where
column between a and b AND
other_criteria
UNION
Select
From
table
Where
column between c and d AND
other_criteria
That still has the potential of creating odd results because of using Between on string data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 28, 2009 at 10:54 am
GSquared (9/28/2009)
Jack Corbett (9/18/2009)
You could also take the OR's and do a union instead.
Select
From
table
Where
column between a and b AND
other_criteria
UNION
Select
From
table
Where
column between c and d AND
other_criteria
That still has the potential of creating odd results because of using Between on string data.
Very true. I didn't really catch the quotes around the data originally.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply