February 10, 2010 at 6:50 am
The table having 42 columns in that 2 columns in where condition and another 1 column used with other table for join condition.
Can I create all the 3 columns in a single non clustered index or create two separate index. In which one index for where condition and another one for join condition. please advice.
February 10, 2010 at 7:10 am
Not enough information.
You can do either, which will work better is another question. There's no way to answer that without seeing the query that you're tuning indexes for. From your description, probably all three in 1 index. Probably.
In the meantime, this may be of interest: http://qa.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2010 at 7:33 am
Thanks Gail.please find my query below.In that TrEmployee is view derived from another Database and rest are table belongs to one database.
SELECT
tr.TransactionID As [SRNumber],
tr.Createddate AS [Created Date],
tr.EmployeeID [EmpNumber],
em.emEmpNumber AS [Associate EmpNumber],
em.emFirstName AS [Associate Name],
tr.EmpVersion,
tr.AvayaId AS [AvayaID],
tr.PhoneNumber AS [Phone No],
tl.emEmpNumber AS [TL EmpNumber],
tl.emFirstName AS [TL Name],
cf.CommittedTime AS [Committed time]
issue.IssueDesc as [Issue Description]
fe.SubIssueDesc as [SubIssue Description]
Callout.StatDesc as [Status Description]
FROM TTrtrans tr --CFCOMMENTS--Status='A'
INNER JOIN TTrcallfollow cf (NOLOCK) ON cf.Transsno=tr.id
LEFT JOIN TTrItemList issue (NOLOCK) ON issue.itemCode=tr.gtIssueID
LEFT JOIN TTrItemList fe (NOLOCK) ON fe.itemCode=tr.SubIssueId
LEFT JOIN TTrItemList Callout (NOLOCK) ON Callout.itemCode=tr.CallStatCode
LEFT JOIN TrEmployee em on tr.EmployeeID=em.EmpNumber
LEFT JOIN TrEmployee tl on tr.EmpManagerId=tl.EmpNumber
WHERE tr.ProgramCode=480 and tr.F47='A'
AND tr.Createddate between convert(varchar,@FROMDATE,101) AND convert(varchar,@TODATE,101)
AND cf.call=@flagCode
order by tr.Createddate
February 10, 2010 at 7:38 am
What's the definition of the Employees view?
Which table do you want to index?
Why are you converting dates to strings?
Why are you using NoLock? Are incorrect results acceptable? See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2010 at 7:54 am
What's the definition of the Employees view?
we have read permission to read all columns in the table belong to EmployeeInfo DB
Which table do you want to index?
TTrtrans Table. I think that need to create one non clustered index for Createdate and ProgramCode columns used in where condition.Another column id will be created as another non clustered index .Because this table and another table TTrcallfollow having records more than 5 lakes.
Why are you converting dates to strings?
sorry i forget to put datetime conversion.the actual condition is
tr.Createddate between Cast(convert(varchar,@FROMDATE,101) as Datetime) AND Cast(convert(varchar,@TODATE,101) as Datetime)
Why are you using NoLock?
Sometimes Data blocked due to locking.That's why we used nolock.
February 10, 2010 at 8:06 am
amalanto (2/10/2010)
What's the definition of the Employees view?we have read permission to read all columns in the table belong to EmployeeInfo DB
That's not what I asked. Can you get the CREATE VIEW statement?
Why are you using NoLock?
Sometimes Data blocked due to locking.That's why we used nolock.
So no one's going to complain if the query sometimes returns incorrect data? The fix for blocking is tuning the query, not throwing consistency away.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2010 at 8:11 am
Sorry for the wrong answer.
1.
CREATE VIEW [dbo].[Employee]
AS
SELECT *
FROM DVEL.dbo.EmployeeInfo
2.yes.we are receiving more Complaints.That time we request them to Re run the report or run at non production hours.
February 10, 2010 at 8:32 am
Can you run that and get me the actual execution plan? Way to do that's described in this article.
http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply