Solved

SQL query performance issue+Microsoft SQL Server

Posted on 2014-09-10
23
301 Views
Last Modified: 2014-09-14
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
0
Comment
Question by:sqldba2013
  • 11
  • 10
  • +1
23 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40314821
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 40314838
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
 

Author Comment

by:sqldba2013
ID: 40314915
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40314942
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
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40314946
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40314949
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
 

Author Comment

by:sqldba2013
ID: 40314957
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40314970
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
 

Author Comment

by:sqldba2013
ID: 40315018
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40315035
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
 

Author Comment

by:sqldba2013
ID: 40315049
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
 

Author Comment

by:sqldba2013
ID: 40315129
@ScottPletcher and Brian Crowe:

Just thought....TsDate column contains around 677550 rows. Can I get performance improvement if  I do Table Partition?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40315227
You don't need to partition, as long as cluster by TsDate and write the WHERE conditions correctly, i.e. "SARGable".
0
 

Author Comment

by:sqldba2013
ID: 40315269
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40315286
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
 

Author Comment

by:sqldba2013
ID: 40315321
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40315349
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40315370
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
 

Author Comment

by:sqldba2013
ID: 40315446
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40315485
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
 

Author Comment

by:sqldba2013
ID: 40316233
<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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40318235
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
 

Author Closing Comment

by:sqldba2013
ID: 40322484
Thanks  ScottPletcher for your help on this issue.

I got some performance improvements and I am marking this case as a closed.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SqlDataBase 7 46
best counters for cpu high usage 3 25
Querying data from 3 SQL tables 2 31
Deal with apostrophe in stored procedures 8 41
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now