Rewrite the query

[krotha]

I have query like this and takes more cpu usage, Can any one suggest better way of writing this query.


IF EXISTS(SELECT Id FROM dbo.Q1
WHERE Code NOT IN (10, 17, 152) AND getdate() BETWEEN Startdate AND Enddate)


Thanks for help.

 

[donutman]

I think the only way to improve it is to modify the subsequent statements. What are they? However, I suspect that you are stuck with what you have.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[krotha]

Thanks donutman for Response
subsequent statements are ok(Just raising error). If I commented this portion then my cpu is 0. Only causing the "NOT IN" part. If I put "IN" Then CPU is 0 which is ok, but that is not what I want.

I tried to rewrite like (code <> 10 or code <> 17 or code <> 152).

But no luck.

Any Ideas.

 

[SQLSister]

This sounds wierd but try reversing the between getdate part and the not in part. It might change how the execution plan handles this. Not in is inherently inefficent becasue you can't then use indexes. In fact, you might try using a derived table withthe btween dates clause and then use the not in clause against the results of the derived table.

DEpending on how many codes there are, you might try just specifying the ones which you do want.

Not sure if any of these ideas will work, but they are things to try.


Questions about posting. See faq183-874

 

[zhavic]

Try to revrite your query to that one, so there can be index used ( if there is one on 'Code' column ) :

SELECT Id 
FROM dbo.Q1
	WHERE ( Code < 10 OR Code > 10 ) AND 
		  ( Code < 17 OR Code > 17 ) AND
		  ( Code < 152 OR Code > 152 ) AND
		  ( getdate() >= Startdate ) AND
		  ( getdate() <= Enddate )

Also do not use IF EXISTS( SELECT .... )

Try to use

IF ( SELECT TOP 1 [ID]
        FROM dbo.Q1
	WHERE ( Code < 10 OR Code > 10 ) AND 
		  ( Code < 17 OR Code > 17 ) AND
		  ( Code < 152 OR Code > 152 ) AND
		  ( getdate() >= Startdate ) AND
		  ( getdate() <= Enddate )
   ) IS NOT NULL

or

IF ( SELECT COUT(*)
        FROM dbo.Q1
	WHERE ( Code < 10 OR Code > 10 ) AND 
		  ( Code < 17 OR Code > 17 ) AND
		  ( Code < 152 OR Code > 152 ) AND
		  ( getdate() >= Startdate ) AND
		  ( getdate() <= Enddate )
   ) > 0

Also there is another way to write that query but it may not be more efficient:

IF ( SELECT TOP 1 Q1.[Id]
		FROM dbo.Q1
			LEFT JOIN dbo.Q1 AS Q1_exclude ON Q1_exclude.[ID] = Q1.[ID] AND Q1_exclude.[Code] IN ( 10, 17, 152 )
		WHERE Q1_exclude.[ID] IS NULL
   ) IS NOT NULL

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[jonwolds]

You could also try putting getdate() in a variable, which I seem to remember can improve efficiency.

Jon

 

[krotha]

Thanks Guys all your help.

zhavic, Based on your suggestion on TOP1, this reduces the CPU drastically which is very good and that's what I want, but increased the high Reads(5773777377) and High Duration(56664444). Is this causing any Memory Bottleneck and How Shall Check this? Thanks for help.

IF ( SELECT TOP 1 Q1.[Id]
FROM dbo.Q1
LEFT JOIN dbo.Q1 AS Q1_exclude ON Q1_exclude.[ID] = Q1.[ID] AND Q1_exclude.

 IN ( 10, 17, 152 )
        WHERE Q1_exclude.[ID] IS NULL
   ) IS NOT NULL

 

[vongrunt]

I'm not sure about EXISTS() . It instructs optimizer to "probe" subquery and stop after first record. So actually it may be a better choice than TOP 1 or COUNT(*).

There is nothing much to do w/ syntax... create index over Startdate if possible.

Btw. more info is welcomed - how many records per table, distinct

 values in table and  expected records in a subquery?

 

[krotha]

About 1 Million Records. Distinct codes are 5. I created indexes on startdate also.

 

[SQLSister]

If there are only 5 distinct codes, why not just specify the ones you want? That is always faster than using a not in clause or a <>.

Questions about posting. See faq183-874

 

[krotha]

SQLSister,
Right now there are only 5 codes, they may add new codes later,when they add new code I have to include that new code in the procedure. I think this is not feasible. I tried keeping in the variable and change codes dynamically based on table,again there is no improvement in the procedure.

This part of the procedure really having problem with High reads and duration.

 

[SQLSister]

Try the following select in the if exists

Select a.Id from (SELECT Id,code FROM dbo.Q1 
WHERE getdate() BETWEEN Startdate AND Enddate) a
Where Code <> 17 and code <> 10 and code <> 152

Questions about posting. See faq183-874

 

[vongrunt]

What surprises me here is very high server utilization. I have very similar table on my dev machine (1.2M records, 4 distinct department IDs, start-end dates, index on start) - and trace results are all in 2-digit range.

Can you post execution plan (use SET SHOWPLAN_TEXT ON)?

 

[krotha]

Here is my execution plan


1. Select Cost: 26%
2. Clustered Index Scan
Estimated Cost: 4.423025(74%)
Argument:
OBJECT[DB].[dbo].[Q1].[Q1PK]AS [q1]), WHERE((([Q1].

<>152 AND [Q1].[CODE]<>17) AND[Q1].[CODE] <> 10)
	AND GETDATE >= [Q1].[STARTDate]) AND GETDATE<=[Q1].[ENDDate])

Please let me know, if I miss anything.

Thanks

 

[vongrunt]

This doesn't use index on Startdate at all...

Can you tell me result of this query:

select count(*) from Q1 where getdate() between STARTDate and ENDDate

 

[krotha]

Count is 0

 

[vongrunt]

Server performs clustered index scan over entire table - to find nothing.

My guess is that both Startdate and Enddate are always (or mostly) less than getdate(), so even an index on startdate is not of any help. What happens if you remove index on Startdate and create another one on Enddate? If my blind shooting is somewhat accurate, this can help to decrease I/O.