Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

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
0
sqldba2013
Asked:
sqldba2013
  • 11
  • 10
  • +1
2 Solutions
 
Brian CroweCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 11
  • 10
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now