Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL query performance issue+Microsoft SQL Server

Posted on 2014-09-10
23
Medium Priority
?
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 2000 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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 70

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 70

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 70

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 70

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 70

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 70

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 70

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 70

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

704 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