March 25, 2010 at 8:22 am
I'm about to start through Itzik Ben-Gan's "T-SQL Querying".
If I run across an explanation, I'll let you know.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 25, 2010 at 8:27 am
The Dixie Flatline (3/25/2010)
I'm about to start through Itzik Ben-Gan's "T-SQL Querying".
It's not in there 🙁
Something I read on one of the SQL Server Team blogs once upon a time. Frustrating that I can't remember enough about it to find it again. The reason might even have been locking/correctness concerns...don't know, but it is bugging me.
Good book by the way.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 25, 2010 at 8:38 am
Improved recursive solution:
WITH [Recursive]
AS (
SELECT TOP (1)
T.AssetID,
T.Date
FROM #T1 T
WHERE T.Date < T.InceptionDate
ORDER BY
T.AssetID,
T.InceptionDate
UNION ALL
SELECT SQ.AssetID, SQ.Date
FROM (
SELECT T.AssetID,
T.Date,
rn = ROW_NUMBER() OVER (ORDER BY T.AssetID, T.Date)
FROM Recursive R
JOIN #T1 T
ON T.AssetID > R.AssetID
WHERE T.Date < T.InceptionDate
) SQ
WHERE SQ.rn = 1
)
SELECT R.AssetID, R.Date
FROM Recursive R;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 25, 2010 at 11:46 pm
Paul White NZ (3/25/2010)
What do you mean here? Having an index prevents a seek???
I guess optimizer will not use seek for HAVING MIN(Date).What do you think ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 26, 2010 at 1:03 am
Bhuvnesh (3/25/2010)
Paul White NZ (3/25/2010)
What do you mean here? Having an index prevents a seek???I guess optimizer will not use seek for HAVING MIN(Date).What do you think ?
I see. It wasn't clear what you meant from your first post. The query you are referring to didn't even compile, so it's hard to say 😉
The HAVING clause with a MIN that I posted earlier in the thread did seek on an index...so the answer is 'it depends'.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 26, 2010 at 11:19 pm
I used
1) rownumber() over (partition ....order by ....)
2) TOP & CROSS APPLY
methods.
But I didn't see any difference in the execution time.
karthik
March 28, 2010 at 6:28 am
karthikeyan-444867 (3/26/2010)
But I didn't see any difference in the execution time.
Please post your COMPLETE table structure, including triggers and keys.
All indexes too, please.
Then post your execution plan in "sqlplan" xml format for investigation.
N 56°04'39.16"
E 12°55'05.25"
March 29, 2010 at 9:30 am
select AssetID, Date
from t1
where Date < InceptionDate
group by AssetID
Having Date = Min(Date)
March 29, 2010 at 10:48 pm
I removed a function which was used in the WHERE clause
say for example
select <columns>
from <tablename>
where <column_name> = dbo.fngetreportfield('Estimated','Final')
i modified the above query as
Declare @reportfield int
select @reportfield = dbo.fngetreportfield('Estimated','Final')
select <columns>
from <tablename>
where <column_name> = @reportfield
It is taking only 4 seconds.
will the usage of function in the WHERE clause hit performance issue?
karthik
March 29, 2010 at 11:09 pm
karthikeyan-444867 (3/29/2010)
will the usage of function in the WHERE clause hit performance issue?
Yes.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 1:37 am
Maybe off-topic but this is an alternative.
Select top 1 myDate
from myTable
group by myDate desc
Franky L.
March 30, 2010 at 7:35 am
Paul White NZ (3/29/2010)
karthikeyan-444867 (3/29/2010)
will the usage of function in the WHERE clause hit performance issue?Yes.
Actually I thought that MIN() function caused the performance issue. Thats why i asked get the minimum date without using MIN() function.
I just implemented and tested ROWNUMBER() method, TOP & CROSS APPLY methods. It is useful for me.
Thanks for all !
karthik
March 30, 2010 at 9:33 am
Paul White NZ (3/29/2010)
karthikeyan-444867 (3/29/2010)
will the usage of function in the WHERE clause hit performance issue?Yes.
In the past 3 years I have made more money consulting helping clients with performance problems due to UDFs than any single other issue - that should say all you need to know about how bad UDFs can be (actually almost always are is a better way to put it)!! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 30, 2010 at 11:15 am
TheSQLGuru (3/30/2010)
In the past 3 years I have made more money consulting helping clients with performance problems due to UDFs than any single other issue - that should say all you need to know about how bad UDFs can be (actually almost always are is a better way to put it)!! :hehe:
Scalar T-SQL UDFs = Very often evil
Multi-statement T-SQL UDTVFs = Very often evil
In-line T-SQL UDTVFs = Hugely useful and cool
CLR scalar UDFs = not as bad as the T-SQL ones...it depends
CLR streaming TVFs = Hugely useful and cool
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 12:03 pm
Paul White NZ (3/30/2010)
TheSQLGuru (3/30/2010)
In the past 3 years I have made more money consulting helping clients with performance problems due to UDFs than any single other issue - that should say all you need to know about how bad UDFs can be (actually almost always are is a better way to put it)!! :hehe:Scalar T-SQL UDFs = Very often evil
Multi-statement T-SQL UDTVFs = Very often evil
In-line T-SQL UDTVFs = Hugely useful and cool
CLR scalar UDFs = not as bad as the T-SQL ones...it depends
CLR streaming TVFs = Hugely useful and cool
IIRC the two you mention as being useful and cool still carry the downside of preventing parallel query plans at a minimum.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply