0

I have the following query (Pseudo SQL warning):

select count(*), groupcolumn from table 
where date is < 6 days ago  and > 7 days ago 
AND date is < 13 days ago 
AND date > 14 days ago... (until 28 days ago) 
group by groupcolumn

I was told to optimize this as:

select count(*), groupcolumn from (
select columns from table
 where date is < 6 days ago  and > 7 days ago 
union all 
select columns from table 
where date is < 13 days ago  and > 14 days ago 
union all 
(repeat until 28 days ago)) group by groupcolumn

This hits the table several times but only brings the rows i need (60K of them for the 4 days specified).

The DBA then told me this query is no good because "it's hitting a transactional table multiple times and it's bad performance". The table in question has records being inserted continuously, a couple to a dozen of them every second.

His suggestion was to change the query to:

select "only the minimum columns i need, if possible only IDs" 
into #temptable
from table where date is < 6 days ago  and > 28 days ago 

And then do my original query against "this much smaller table that also doesn't interfere with the transactional table".

The DBA tells me this query is better because "it's not hitting the transactional table so much". It counter intuitive to me, though, since I'm bringing 15K records per day. So instead of copying 60K records to memory, I'll now be copying 420K records to disk, and running a second query against those.

I'm using SET TRANSACTION LEVEL READ UNCOMMITTED and WITH(NOLOCK) for every table. I don't mind dirty reads as I'm just making approximate statistics, so I don't see why "hitting the table so many times" would be an issue here.

  • Writing 22 days of data to a #temp table just sounds like extra work that is merely masking an ineffective or missing index on the source table. – Aaron Bertrand Feb 13 at 15:36
  • It is but it's related to my previous question: dba.stackexchange.com/questions/229619/… – hjf Feb 13 at 15:38
  • 2
    Well you can't expect readers to know this, you should add something to your question that indicates your DBA doesn't like adding indexes that would support your queries and you need to investigate ways to work around this obstinance. Also, NOLOCK doesn't speed up queries, those queries still aren't free, there is no reason to use both READ UNCOMMITTED and NOLOCK (they do the same thing), and it comes with some potentially really nasty side effects. – Aaron Bertrand Feb 13 at 15:42
  • Can you include the actual query or execution plan? – John Eisbrener Feb 13 at 16:05
1

It could reduce the time you access the table, and possibly the execution time of your query.

If that is true will depend on the indexes on the table, the size of the table, ....

An example of executing your query on an unindexed heap:

enter image description here

While the rows returned are 15K, the residual I/O on the predicate is high, 120K rows for each table scan.

enter image description here

As you already know, adding an index could change this to a seek predicate.

Reducing the time you would access the table can be done by using a temp table.

Insert the data

enter image description here

Add an index

enter image description here

Do index seeks on your temp table

enter image description here

Conclusion

While reducing the time accessing the table, the execution time in total was twice as long on my test server. To help you confirm if it is a solution for you, we would need the table definition, the actual query and additionaly the test data.

If it is not an option to share these things, then you could do the test yourself, and share the execution plans and the SET STATISTICS IO,TIME output.


Test data used

CREATE TABLE CountingDays(CountingDaysId int identity(1,1), groupcolumn varchar(255), countingdate datetime2)

SET NOCOUNT ON;
DECLARE @i int = 0
WHILE @i < 15000
BEGIN
INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(1,getdate())

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(2,dateadd(day,-4,getdate()))


INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(3,dateadd(day,-7,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(@i,dateadd(day,-8,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(1,getdate())

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(2,dateadd(day,-3,getdate()))


INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(3,dateadd(day,-6,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(@i,dateadd(day,-5,getdate()))

SET @i+=1

END




SET STATISTICS IO, TIME ON;
select count(*), groupcolumn from (
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-3,getdate()) and countingdate >= dateadd(day,-4,getdate())
union all 
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-4,getdate()) and countingdate >= dateadd(day,-5,getdate())
 union all 
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-5,getdate()) and countingdate >= dateadd(day,-6,getdate())
union all 
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-6,getdate()) and countingdate >= dateadd(day,-7,getdate())
 union all 
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-7,getdate()) and countingdate >= dateadd(day,-8,getdate())
union all 
select groupcolumn from CountingDays
where countingdate < dateadd(day,-8,getdate()) and countingdate >= dateadd(day,-9,getdate())) a
group by groupcolumn

CREATE TABLE #temp(groupcolumn varchar(255), countingdate datetime2)
INSERT INTO #temp(groupcolumn,countingdate)
select groupcolumn,countingdate FROM CountingDays
CREATE INDEX IX_Countingdate on #temp(Countingdate) include(groupcolumn)

SET STATISTICS IO, TIME ON;
select count(*), groupcolumn from (
select groupcolumn from #temp
 where countingdate < dateadd(day,-3,getdate()) and countingdate >= dateadd(day,-4,getdate())
union all 
select groupcolumn from #temp
 where countingdate < dateadd(day,-4,getdate()) and countingdate >= dateadd(day,-5,getdate())
 union all 
select groupcolumn from #temp
 where countingdate < dateadd(day,-5,getdate()) and countingdate >= dateadd(day,-6,getdate())
union all 
select groupcolumn from #temp
 where countingdate < dateadd(day,-6,getdate()) and countingdate >= dateadd(day,-7,getdate())
 union all 
select groupcolumn from #temp
 where countingdate < dateadd(day,-7,getdate()) and countingdate >= dateadd(day,-8,getdate())
union all 
select groupcolumn from #temp
where countingdate < dateadd(day,-8,getdate()) and countingdate >= dateadd(day,-9,getdate())) a
group by groupcolumn


DROP TABLE #temp
  • 1
  • 1
    @AaronBertrand oops, that is correct, updating as we speak. Thanks! – Randi Vertongen Feb 13 at 16:14
  • Great answer. As I expected, the query will be slower. This may or may not be an issue. It can be slower (as it only calculates stats once a day), but the DBA may be happier with this solution because "it leaves the transactional table alone". Unfortunately I can't run my tests because production data is several orders of magnitude larger than test, and I have no SHOWPLAN permissions on the production DB. So it's mostly a matter of "come up with an idea for a query and have the DBA run it". It's incredibly frustrating. – hjf Feb 13 at 17:22
  • @hjf If you're using NOLOCK the DBA shouldn't care about "leaving the transactional table alone." It's not truly zero locking but your once-a-day reporting queries shouldn't be his concern (those queries can still be slow though, depending on how much data, how you're rendering, etc). Also it is unreasonable for the DBA to expect you to come up with efficient queries without being able to see execution plans. At the very least you should be able to get a copy of production (say, restored from one of the backups I trust you are running and validating) and get the proper rights on the copy. – Aaron Bertrand Feb 13 at 17:28
  • Anything less than that is like telling me to assemble a jigsaw puzzle but not letting me see the picture. Sure I know how to connect pieces together but I need a little more guidance. – Aaron Bertrand Feb 13 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.