Solved

SQL query performance issue+Microsoft SQL Server

Posted on 2014-09-10
23
297 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:ScottPletcher
ScottPletcher 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
 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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:ScottPletcher
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

746 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

12 Experts available now in Live!

Get 1:1 Help Now