SQL query performance issue+Microsoft SQL Server

Hi Experts,

The attached query is taking very long time to return results. Could you please advise how to reduce execution time of attached query.
sql-query.sql
Index.sql
sqldba2013Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
What does the execution plan look like?  Off the top I would recommend adding indexes to the foreign key fields like tblUsers.ManagerID and tblUsers.OrgID.
0
Scott PletcherSenior DBACommented:
Here's some quick thoughts based on what you posted.  Can't fully analyze without a query plan and cardinality (row counts).

1) Change the clustering key on tblTimeSheetDtls to TsDate.

2) Change the following:
       set @StartDate = '20140401';
      set @EndDate = '20140824';
"WHERE ... convert(varchar(10),TsDate,101) between  @StartDate and @EndDate" -- which is not only horribly inefficient but inaccurate as well -- to:
TsDate >= @StartDate AND TsDate < DATEADD(DAY, 1, @EndDate)

3) Add the proper key to #temp1:
create table #temp1 (TotalUser int, orgid int primary key)
0
sqldba2013Author Commented:
Thanks Brian and ScottPletcher for quick response.

I have added indexes to table tblsusers.  I am not able to display execution plan because of below error. Pls advise me on below error.
Msg 208, Level 16, State 0, Line 48
Invalid object name '#temp2'.


1) Change the clustering key on tblTimeSheetDtls to TsDate.
Pls have a look on attached updated index script.

2) Change the following:
       set @StartDate = '20140401';
      set @EndDate = '20140824';
"WHERE ... convert(varchar(10),TsDate,101) between  @StartDate and @EndDate" -- which is not only horribly inefficient but inaccurate as well -- to:
TsDate >= @StartDate AND TsDate < DATEADD(DAY, 1, @EndDate)

I am facing syntax error, please review the attached updated sql script.
Index.sql
sql-query.sql
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
On the indexing, like this instead:

ALTER TABLE [dbo].[tblTimeSheetDtls] DROP  CONSTRAINT [PK_tblTimeSheetDtls]

CREATE CLUSTERED INDEX CL_tblTimeSheetDtls ON dbo.tblTimeSheetDtls ( TsDate ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY]

ALTER TABLE [dbo].[tblTimeSheetDtls] ADD  CONSTRAINT [PK_tblTimeSheetDtls] PRIMARY KEY
(
      [TsDtlsID]
)WITH ( FILLFACTOR = 95 ) ON [PRIMARY]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
macarrillo1Commented:
I agree, you should check your execution plan or run SQL Profiler to see what is taking up most of the time.  Then you can adjust the Specific query, by indexing the join fields.
0
Scott PletcherSenior DBACommented:
On the syntax, you need another close paren to end the subquery / embedded "(SELECT":

TsDate >= @StartDate AND TsDate < DATEADD(DAY, 1, @EndDate))

You should change EVERY place where TsDate is compared.

Note, too, that to make this work correctly, you MUST change the @StartDate and @EndDate values to 'YYYYMMDD' as I noted earlier.
0
sqldba2013Author Commented:
ok, thanks.

Could you please guide me on point #2 and on below error.

2) Change the following:
       set @StartDate = '20140401';
      set @EndDate = '20140824';
"WHERE ... convert(varchar(10),TsDate,101) between  @StartDate and @EndDate" -- which is not only horribly inefficient but inaccurate as well -- to:
TsDate >= @StartDate AND TsDate < DATEADD(DAY, 1, @EndDate)

I am facing syntax error, please review the attached updated sql script.

and

 I am not able to display execution plan because of below error. Pls advise me on below error.
Msg 208, Level 16, State 0, Line 48
Invalid object name '#temp2'.
sql-query.sql
0
Scott PletcherSenior DBACommented:
where tblUsers.UsrID not in (SELECT UsrID FROM tbltimesheetDtls where tbltimesheetdtls.usrid is not null and
                              --convert(varchar(10),TsDate,101) between  @StartDate and @EndDate
                              TsDate >= @StartDate AND TsDate < DATEADD(DAY, 1, @EndDate) --<<-- add a close paren for the DATEADD() function here
                              )
0
sqldba2013Author Commented:
Thanks ScottPletcher, I have modified attached script with above where clause (added close paren for the dateadd function).

Still I am not able to generate execution plan because of below error. Can you guide me on below error

Msg 208, Level 16, State 0, Line 52
Invalid object name '#temp2'.
sql-query.sql
0
Scott PletcherSenior DBACommented:
My guess is there's already a table named #temp2 in your current session.

Add this command at the very top/beginning of the script:

IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
    DROP TABLE #temp2
0
sqldba2013Author Commented:
I have added below code and still I am getting error "Msg 208, Level 16, State 0, Line 51Invalid object name '#temp2'.

IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
    DROP TABLE #temp2
go

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
    DROP TABLE #temp1
go

IF OBJECT_ID('tempdb..#temp5') IS NOT NULL
    DROP TABLE #temp5
go
0
sqldba2013Author Commented:
@ScottPletcher and Brian Crowe:

Just thought....TsDate column contains around 677550 rows. Can I get performance improvement if  I do Table Partition?
0
Scott PletcherSenior DBACommented:
You don't need to partition, as long as cluster by TsDate and write the WHERE conditions correctly, i.e. "SARGable".
0
sqldba2013Author Commented:
Once again thanks a lot for your suggestion.

I have modified script with all your suggestion. Now query is taking 3 min 24 seconds for 7 records..previously it was around 5 min for 7 records.

Can you give me some more tips to reduce execution time?

I am herewith attaching updated query for your review.

Still I am not able display execution plan because of above error.
sql-query.sql
0
Scott PletcherSenior DBACommented:
The actual execution plan would be better than the estimated one anyway.

Add this statement to the front of the script:
SET STATISTICS XML ON

Run it, then post the resulting xml query plan.
0
sqldba2013Author Commented:
0
Scott PletcherSenior DBACommented:
For one thing, you need to get rid of the "IsActive" flags.  They prevent a lot of SQL optimizations and are functionally useless.  Create a NotActive or archive table if you want to keep deleted rows.  [The flags are a hold-over from the days of sequential "master files", when you had to read the entire file anyway, so they didn't cost anything.]
0
Scott PletcherSenior DBACommented:
In the query below, you should comment out the "NOT NULL" condition on TsDate, as the comparison will not be true anyway if the date is NULL.

Why are there NULL UsrIds in the TimeSheet table?  We need to get rid of this: "tblTimeSheetDtls_temp.usrid is not null" if we can.

SELECT  count(Distinct (tblUsers.[UsrFirstName] + ' ' +  tblUsers.[UsrLastName])) as TsUsers,
                              tblOrganizations.Orgid, tblOrganizations.[OrgName] as orgName,
                              tblOrganizations.orgalias into #temp2
                              From tblTimeSheetDtls_temp with(nolock) left join tblUsers on tblTimeSheetDtls_temp.[UsrID] = tblUsers.[UsrID]
                              left join tblUsers as mgrUsers on  tblUsers.[ManagerID] = mgrUsers.[UsrID]
                              left join tblOrganizations on tblUsers.[OrgID] = tblOrganizations.[OrgID]
                              WHERE /*tblTimeSheetDtls_temp.TsDate is not null and */
                              tblTimeSheetDtls_temp.usrid is not null and
                              --convert(varchar(10),TsDate,101) between  @StartDate and @EndDate
                              TsDate >= @StartDate AND TsDate < DATEADD(DAY, 1, @EndDate
                              )
                              and orgisactive=1 and tblUsers.UsrIsActive =1 --and
                              group by tblOrganizations.Orgid, tblOrganizations.[OrgName],
                              tblOrganizations.orgalias
0
sqldba2013Author Commented:
I have commented /*tblTimeSheetDtls_temp.TsDate is not null and */.....there is no improvement in query execution.

Do we need to add any indexes for other tables tblOrganizations, tblusers?
0
Scott PletcherSenior DBACommented:
tblOrg~ seems to have only 7 rows, based on the query plans, so we can ignore indexing on that table :) .

tblUsers may need some new ones, or existing ones changed.  But without knowing the existing indexes (the query plan is showing use of an index which we were never told about) and their stats up front, it's extremely hard to make index adjustments.

I'd make one index that covered all user lookups, and let SQL scan that index if it's not the main lookup key for that query.  You could create separate nonclus index by each key -- tblUsers.[ManagerID] and tblUsers.[OrgID] -- but I'd likely consolidate them instead.  You'll need to include all join values and WHERE condition values, such as ManagerID, OrgID and *IsActive flags.  But don't include the names, since you'd just be duplicating the entire table basically.

The TimeSheet table should be use the @EndDate + 1 as a Seek ending value, not sure why it isn't.  Try removing the "IsActive" flag check and see if SQL can make the @EndDate + 1 an ending Seek key instead of a separate Predicate in the query plan.
0
sqldba2013Author Commented:
<CREATE CLUSTERED INDEX CL_tblTimeSheetDtls ON dbo.tblTimeSheetDtls ( TsDate ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY]>

TsDate column contains duplicate and NULL values and I am getting error while creating CLUSTERED INDEX. How to modify above statement to avoid error.
0
Scott PletcherSenior DBACommented:
You must have added "UNIQUE" to it.  As you'll notice, my original statement does NOT contain "UNIQUE" because the values won't be unique :) .  Remove UNIQUE, remove any existing clustered index if it exists, then run the statement again.
0
sqldba2013Author Commented:
Thanks  ScottPletcher for your help on this issue.

I got some performance improvements and I am marking this case as a closed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.