Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Based on these questions and the answers given:

SQL 2008 Server - performance loss possibly connected with a very large table

Large table with historical data allocates too much of SQL Server 2008 Std. memory - performance loss for other databases

I have a table in a database SupervisionP defined like this:

CREATE TABLE [dbo].[PenData](
    [IDUkazatel] [smallint] NOT NULL,
    [Cas] [datetime2](0) NOT NULL,
    [Hodnota] [real] NULL,
    [HodnotaMax] [real] NULL,
    [HodnotaMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IDUkazatel] ASC,
    [Cas] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Ukazatel] FOREIGN KEY([IDUkazatel])
REFERENCES [dbo].[Ukazatel] ([IDUkazatel])

ALTER TABLE [dbo].[PenData] CHECK CONSTRAINT [FK_Data_Ukazatel]

It contains cca 211 milion rows.

I run following statement:

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;

SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24
SELECT min(cas) from PenData p WHERE IDUkazatel=25
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;


SET @t1 = GETDATE();
SELECT min(cas) from PenData p WHERE IDUkazatel=24 OR IDUkazatel=25 
SET @t2 = GETDATE();
SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;

The result is shown here:

Execution plan

The third SELECT also loads much more data into SQL Server memory cache.

Why is the third SELECT so much slower (8.5 s) then the first two SELECTs(16 ms)? How can I improve performance of the third select with OR? I want to run following SQL command but it seems to me that creating cursor and running separate queries is much faster than a single select in this case.

 SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM  ...)

EDIT

As David suggested I have hovered over the fat arrow:

FatArrow

share|improve this question
    
You can improve the performance of the third select, by taking the first two and performing a UNION between both result sets. –  Mark Sinkinson Jul 30 at 10:58
    
Yes, but it would not help me with the last one mentioned... –  VDohnal Jul 30 at 11:03
    
You can put a UNION in a sub-query... –  Mark Sinkinson Jul 30 at 11:04
    
@markSinkinson Please how? SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (SELECT IDUkazatel FROM Ukazatel WHERE IDZapisovac=10) –  VDohnal Jul 30 at 11:09

2 Answers 2

up vote 7 down vote accepted

For the first two queries all it has to do is scan in the clustered index to the first entry for that value of IDUkazatel - because of the order of the index that row will be the lowest value for cas for that value of IDUkazatel.

In the second query this optimisation is not value and it is probably seeking to the first row for IDUkazatel=24 then scanning down the index until the last row with IDUkazatel=25 to find the minimum value of cas over all those rows.

If you hover over that fat arrow you'll see it is reading many rows (certainly all those for 24, probably all those for 25 too), whereas the thin arrows in the plan output for the other two show the top action causing it to only consider one row.

You could try run each query and then get the minimum for the minimums found:

SELECT MIN(cas)
FROM   (
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 24
        UNION ALL
        SELECT cas=MIN(cas) FROM PenData p WHERE p.IDUkazatel = 25
    ) AS minimums

That said, it seems you have a table with IDUkazatel values rather than an explicit OR clause. The code below will work with that arrangement, simply replace the table name @T with the name of the table containing IDUkazatel values:

SELECT 
    MinCas = MIN(CA.PartialMinimum)
FROM @T AS T
CROSS APPLY 
(
    SELECT 
        PartialMinimum = MIN(PD.Cas)
    FROM dbo.PenData AS PD
    WHERE 
        PD.IDUkazatel = T.IDUkazatel
) AS CA;

In an ideal world, the SQL Server query optimizer would perform this rewrite for you, but it does not always consider this option today.

share|improve this answer
    
You can rewrite the last one without derived table SELECT TOP (1) min_cas=MIN(CAS) ... ORDER BY min_cas; (but I guess the plan will be the same as yours.) –  ypercube Jul 30 at 11:24
    
Thanks, I have tried to add index to cas on my old testing server and it somehow run out of the disk space... Nevertheless I prefer not to add indexes that are not necessary since I do lots of bulk inserts. But the second query you provided has a fat arrow too - the same problem as with OR –  VDohnal Jul 30 at 11:32
    
It is at the bottom of the question, ok, thank you –  VDohnal Jul 30 at 13:03
    
I have added my answer to yours, David. Feel free to roll it back if you disagree, and I will reinstate my answer. –  Paul White Jul 30 at 13:07

For completness see my solution. To replace the simple command:

SELECT MIN(cas) from PenData p WHERE IDUkazatel IN (...)

there is fast and ugly one:

DECLARE @IDZapisovac int = 1

DECLARE @MinCas DateTime2(2) = NULL
DECLARE @IDUkazatel smallint
DECLARE @Cas DateTime2(2) = NULL
DECLARE helper CURSOR FOR 
   SELECT IDUkazatel FROM ...
OPEN helper
FETCH NEXT FROM helper INTO @IDUkazatel
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @Cas = MIN(Cas) FROM PenData WHERE PenData.IDUkazatel=@IDUkazatel
    IF (@cas IS NOT NULL AND @cas<ISNULL(@MaxCas, '21000101'))
        SET @MinCas = @cas
    FETCH NEXT FROM helper INTO @IDUkazatel
END 
CLOSE helper;
DEALLOCATE helper;
SELECT @MinCas

I found until now no other solution.

EDIT

Thanks - the accepted solution is now perfect.

share|improve this answer
    
What indexes do you have on the Ukazatel table? –  ypercube Jul 30 at 12:32
    
Only primary clustered key on IDUkazatel. –  VDohnal Jul 30 at 12:34
    
I would very much like to see a better solution, but till now none was presented and this is working... –  VDohnal Jul 30 at 12:35
    
That's another problem then. You need an index on (IDZapisovac) for this query. And using cursors is rarely a good answer. And the other answer (which you accepeted) had a solution - for the OR query. Did you try that query? Or did you use a cursor for that query too? –  ypercube Jul 30 at 12:35
1  
This answer is no good in any case. because it answers not the question (which does not mention this query, with the join to Ukazatel) –  ypercube Jul 30 at 12:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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