Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2122232425»»

Twenty tips to write a good stored procedure Expand / Collapse
Author
Message
Posted Monday, August 24, 2009 11:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
I understand that what you wrote was an illistration of how IN works. My point is if you actually write a WHERE clause like that, you would use OR somecol IS NULL, not OR somecol = NULL. Two different things. Have I clarified my post yet? I fully understand yours.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #776182
Posted Monday, August 24, 2009 1:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:17 AM
Points: 147, Visits: 548
Yes. It is now clear that your post was about evaluating NULLs in WHERE clauses. I agree that it is correct to use "is NULL" as shown in three of the example queries in my 8/18/2009 post. I apologize for incorrectly assuming you misinterpreted my illustration of how IN works.



Post #776268
Posted Tuesday, August 25, 2009 9:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
Arup has updated the content and I have replaced this content with the updates.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #776830
Posted Tuesday, August 25, 2009 10:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 22, 2014 7:25 AM
Points: 338, Visits: 1,438
Thought I'd get in with the first comment, only a spelling mistake that gets through a spell checker:

Update: I would recommend you to use SET NOCOUNT ON for the shake of performance unless there is a very good reason for using it.
Post #776850
Posted Tuesday, August 25, 2009 10:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 22, 2014 7:25 AM
Points: 338, Visits: 1,438

10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their precedence.

=, >, <, >=, <=, <>, !=, !>, !<

for details, refer to the article: http://msdn.microsoft.com/en-us/library/ms190276.aspx



Don't all the operators have the same precedence? From reading the article they are all at level 4.
Post #776858
Posted Tuesday, August 25, 2009 10:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 22, 2014 7:25 AM
Points: 338, Visits: 1,438

11. More WHERE clause hints - Avoid unnecessary conditions in the WHERE Clause. Also try to avoid a function in the WHERE clause as it presents SQL engine to do index seek. Even it forces SQL full index scans or even table scans.

prevents the SQL engine doing an
Post #776866
Posted Monday, August 31, 2009 9:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:08 AM
Points: 77, Visits: 169
Lynn Pettis (8/24/2009)
I understand that what you wrote was an illistration of how IN works. My point is if you actually write a WHERE clause like that, you would use OR somecol IS NULL, not OR somecol = NULL. Two different things. Have I clarified my post yet? I fully understand yours.

There could be a role for this IN equivalent (I hope),
WHERE ( col = @value1 OR col = @value2 OR col = @value3 )

in which one or more of @value1, @value2, @value3 may be NULL, of course.

Is it better in that case to rewrite the query separately with one condition fewer, or to re-use one version? I would like to think that the server is smart enough not to spend much time on the comparison to NULL, which counts against writing a separate version for that case.
Post #779981
Posted Monday, August 31, 2009 9:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:08 AM
Points: 77, Visits: 169
jacroberts (8/25/2009)

11. More WHERE clause hints - Avoid unnecessary conditions in the WHERE Clause. Also try to avoid a function in the WHERE clause as it presents SQL engine to do index seek. Even it forces SQL full index scans or even table scans.

should be "prevents the SQL engine doing an".

And without objecting to the general point, I think in some queries, there'll be one condition that does the heavy work of selecting the records that you're playing with and then some kind of internal intermediate resultset that other conditions are applied to, so that your function is evaluated for 10 rows instead of 10 million (ouch). But on the other hand, the server may decide to select rows only on on one condition, separately select rows on another condition, and then compare and match up the two resultsets to produce the query result. I don't feel verybad that I don't recall correct terminology for this sort of thing, because that wouldn't get make much closer to saying what I mean, but... did I have a point?

Well, I guess (is this a dummy-level comment?) that if your query strictly looks like

"SELECT * FROM table WHERE ( dbo.ufn_validity(@nearRowID) = 1 )"

and you know that the following gets the same
results,

"SELECT * FROM table WHERE ( dbo.ufn_validity(@nearRowID) = 1 AND rowNumber BETWEEN @nearRowID-20 AND @nearRowID+20 )"

then the second version may leave the first for dead-slow.
Post #779995
Posted Monday, August 31, 2009 10:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
arup_kc (8/24/2009)
Unfortunately, in this case, you only substitted one form of RBAR for another form of RBAR. The best solution in this case is to find a set-based solution to replace a cursor-based solution (and a WHILE + Temp Table is still a cursor-based solution).

RBAR = Row By Agonizing Row (A Modenism)

Hi Lenn,

What you are telling is absolutely correct. Some there are some situations where we cant avoid cursors...I am telling abt that situation. Also, I have checked the Temp+While solutions, indeed it gave better performance...thats why I included that in this article.


Heh... ya just gotta know that will get my attention... Now I have to go back and look again.

And the fellow's name is "Lynn", not "Lenn".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #780029
Posted Monday, August 31, 2009 10:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
jacroberts (8/25/2009)

10. WHERE clauses - In a WHERE clause, the various operators used directly affect how fast a query can run. Here are the conditional operators used in the WHERE clause, ordered by their precedence.

=, >, <, >=, <=, <>, !=, !>, !<

for details, refer to the article: http://msdn.microsoft.com/en-us/library/ms190276.aspx



Don't all the operators have the same precedence? From reading the article they are all at level 4.


Yep.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #780061
« Prev Topic | Next Topic »

Add to briefcase «««2122232425»»

Permissions Expand / Collapse