Solved

how to make the date wise report query faster

Posted on 2014-04-28
18
2,161 Views
Last Modified: 2014-06-11
Below query displays the datewise report from '2014-04-01' to '2014-04-30'.but query is taking 1 min time to display the report.


i used SQL profiler and also database tuned advisor but its not giving recommendations.
i am able to write big queries but not efficient ones.

i want some tutorials or videos on how to make the queries fast?how to know which condition is taking lot of time and what index needs to created.i have seen execution plan but could not get much out of it.

below conditions are mainly taking lot of time to display the query.how to rewrite it to make it fast?plz note that i created the Non clustered index on EMP_ID in  the EMP_INSTANCES table and included the columns(CREATE_DATE,PURGE_DATE) in that index.

D.[Date] >= CONVERT(VARCHAR(10), EI.CREATE_DATE, 101)  AND
(EI.PURGE_DATE IS NULL OR D.[Date] <= CONVERT(VARCHAR(10), EI.PURGE_DATE, 101))


DECLARE
      @STARTDATE DATETIME ,
      @ENDDATE DATETIME ,
      @CUSTOMERID INTEGER
     
SET @STARTDATE='2014-04-01 00:00'
SET @ENDDATE='2014-04-30 00:00'
SET @CUSTOMERID=1234567
 
;WITH Dates AS
(
    SELECT DATEADD(DAY,number,@STARTDATE) [Date]
    FROM master.dbo.spt_values
    WHERE type = 'P'
    AND number >= 0
    AND DATEADD(DAY,number,@STARTDATE) <= @ENDDATE
)
SELECT D.[Date] AS [DATE], LOC.NAME, sum(A.FILESIZE) FILESIZE
FROM Dates D,
EMP_INSTANCES EI ,
EMP A ,
POLICIES P, 
CUSTOMERS C,
LOCATIONS LOC
WHERE
a.id=ai.EMP_id and
a.policy_id=p.id and
c.id=p.customer_id and
sl.id=ai.location_id and
C.ID = @CUSTOMERID AND
D.[Date] >= CONVERT(VARCHAR(10), EI.CREATE_DATE, 101)  AND
(EI.PURGE_DATE IS NULL OR D.[Date] <= CONVERT(VARCHAR(10), EI.PURGE_DATE, 101))
GROUP BY D.[Date], LOC.NAME

Open in new window

0
Comment
Question by:chaitu chaitu
[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
  • 6
  • 4
  • 3
  • +3
18 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027403
your query looks pretty good, I wouldn't rrally write it a differnt way at this time.
My suggestion would be to index the tables associated with the query to see if that improves performance... Have you tried that yet ?
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40027446
i created the Non clustered index on EMP_ID in  the EMP_INSTANCES table and included the columns(CREATE_DATE,PURGE_DATE) in that index.

do you want me to create any other indexes on EMP_INSTANCES table?remaining indexes will be created by defauly if it is a primay key.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027461
i would create NC indexes on every table associated with the query to be honest. I am willing to wager that it will cut the time down significantly
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40027468
may i know NC indexes on which columns?

you mean  policy_id in emp table
and customer_id  in policies table.


do i missing anything?
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 40027482
My fault My communication was poor :)

I would create NC indexes on Every PK in the tables from which you are referencing for your query
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40031195
created indexes.still taking same time.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40033983
if i comment this condition output is coming below 30 secs.but if i put this condition its taking more than 2 mins.

D.[Date] >= CONVERT(VARCHAR(10), EI.CREATE_DATE, 101)  AND
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40039210
Why are you doing the CONVERT in the first place? It removes the ability to use an index on CREATE_DATE and PURGE_DATE.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40039371
Try to use joins on related fields.

Leave extra limiting conditions in the where clause.

Check this article on joins.

http://www.experts-exchange.com/Database/MS_Access/A_3597-INNER-JOIN-a-Number-Of-Tables.html

I see you are using 6 tables. Assuming an average of 10 records in each table.
In the current case you are using a cartesian join where each record from table 1 is repeated with every record of table 2, giving 100 records.
The next table will make the set 1000 records. Including all 6 tables, you are dealing with 1,000,000 record set loaded in memory, then applying the logic and conversion.

With joins the records are limited by each join, and a few number of records are in memory to apply the extra logic and conversion.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40039939
Qlemo,

i need to take date only not datetime thats why used CONVERT function.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40040084
And you can't manage to store dates only in those two fields? CREATE_DATE sounds like there is no time portion.
Otherwise you can create computed columns on both datetime fields to set an index on them.
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 40040258
but we are time portion as well.what do you mean by computed columns on both datetime fields?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40040268
You can create "virtual" columns as expressions in a table, and the DBMS will manage their content whenever the column(s) they are based on are changed. In this case you would create one column for convert(CREATE_DATE, 101) and convert(PURGE_DATE, 101).
After that, you can index those new columns. That should speed up search, but introduces higher update/insert costs for maintaining those columns and indexes.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40040345
No feedback on my comment: http:#a40039371
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40040358
hnasr,

If the Query Optimizer does its job well, it doesn't matter for inner joins whether to include conditions in ON or WHERE. However, it is good style to clearly separate join conditions from filter conditions.
For outer joins it is necessary to do so, because that makes a difference.
0
 
LVL 18

Accepted Solution

by:
vasto earned 500 total points
ID: 40040969
Convert to Date instead of Varchar

D.[Date] >= CONVERT(Date, EI.CREATE_DATE)  AND
(EI.PURGE_DATE IS NULL OR D.[Date] <= CONVERT(Date, EI.PURGE_DATE))
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 40041834
vasto is right - in your comparisons you're comparing a datetime (d.Date) with a varchar ( CONVERT(VARCHAR(10), EI.PURGE_DATE, 101) ). At the very least, convert  ei.PurgeDate into a datetime, not a varchar. If it's already a date then just leave it alone!
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
store vs query adhoc - no show rows 4 36
Need more granular date groupings 4 44
Search Text in Views 2 27
Migrate SQL 2005 DB to SQL 2016 4 22
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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