Complex SQL QUERY with DateDIFF

Question

Complex SQL QUERY with DateDIFF

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Eugene Elutin (3/13/2013)
Those logtimes look sequential to me - are you sure?
What do you mean by "sequential"? These LogTimes are for the same UserId, they are unique and they grow in its "value" which is kind of expected from LogTime.

No loop;

Looks like CTE goes into endlesss loop...

The rCTE returns all or nearly all rows of your sample data and of course it's dog slow. The performance of this method is related to the proportion of rows which can be discarded. The OP's data fits the method well enough - 12 rows retained out of 47.

I'm still curious about this -

And the above version still doesn't work...

I'm not seeing any missed or phantom results...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

"Looks like" - it just really "Looks like" nothing more. That is whole problem for recursive CTE's - they are fine for limited number of recursions, they not very well suited to unkown/unlimited number of recursions. And that is were "quirky" update will outperform it (or, actually, any "triangular"-join based solution).

I am not sure about this exact OP case, but LogTable's usually are not limited to just few records per user, so I would definitely recommend "quirky" update method over recursive CTE there. Saying the above, if solution with CROSS APPLY wouldn't need recursive CTE, it would most likely win over.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

Eugene Elutin (3/14/2013)
"Looks like" - it just really "Looks like" nothing more. That is whole problem for recursive CTE's - they are fine for limited number of recursions, they not very well suited to unkown/unlimited number of recursions. And that is were "quirky" update will outperform it (or, actually, any "triangular"-join based solution).
I am not sure about this exact OP case, but LogTable's usually are not limited to just few records per user, so I would definitely recommend "quirky" update method over recursive CTE there. Saying the above, if solution with CROSS APPLY wouldn't need recursive CTE, it would most likely win over.

Not all recursvie CTEs are slow

http://www.sqlservercentral.com/Forums/Topic873124-338-5.aspx#bm1013407

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Mark-101232 (3/14/2013)
Eugene Elutin (3/14/2013)
"Looks like" - it just really "Looks like" nothing more. That is whole problem for recursive CTE's - they are fine for limited number of recursions, they not very well suited to unkown/unlimited number of recursions. And that is were "quirky" update will outperform it (or, actually, any "triangular"-join based solution).
I am not sure about this exact OP case, but LogTable's usually are not limited to just few records per user, so I would definitely recommend "quirky" update method over recursive CTE there. Saying the above, if solution with CROSS APPLY wouldn't need recursive CTE, it would most likely win over.
Not all recursvie CTEs are slow
http://www.sqlservercentral.com/Forums/Topic873124-338-5.aspx#bm1013407

Heh you recognised the code, Mark!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Mark-101232 (3/14/2013)
Eugene Elutin (3/14/2013)
"Looks like" - it just really "Looks like" nothing more. That is whole problem for recursive CTE's - they are fine for limited number of recursions, they not very well suited to unkown/unlimited number of recursions. And that is were "quirky" update will outperform it (or, actually, any "triangular"-join based solution).
I am not sure about this exact OP case, but LogTable's usually are not limited to just few records per user, so I would definitely recommend "quirky" update method over recursive CTE there. Saying the above, if solution with CROSS APPLY wouldn't need recursive CTE, it would most likely win over.
Not all recursvie CTEs are slow
http://www.sqlservercentral.com/Forums/Topic873124-338-5.aspx#bm1013407

Yep, I remember this great one from a while ago. But...

There are caveats! Check the last post from Jeff Moden in this thread:

It IS VERY fast indeed! But only if the number of distinct rows out number those that have duplicates. If you have a lot of duplicate rows, you may want to revert back to a simple DISTINCT or GROUP BY clause.

So, in order for it to be fast you need to know what kind of data distribution you have in your database before hand. In the example provided by Paul, It's super-fast as there is a high number of duplicate data in his test table, so recursive CTE doesn't have really high level of "recursiveness".

Same here! When you run similar solution on the small example of Log data, where there are only few records per user, the performance is acceptable and may even win over any other solutions. But, if the number of log entries per user grows, recursive CTE based solution becomes slower and slower, until it's start to look like endless loop;-).

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Eugene Elutin (3/14/2013)
Mark-101232 (3/14/2013)
Eugene Elutin (3/14/2013)
"Looks like" - it just really "Looks like" nothing more. That is whole problem for recursive CTE's - they are fine for limited number of recursions, they not very well suited to unkown/unlimited number of recursions. And that is were "quirky" update will outperform it (or, actually, any "triangular"-join based solution).
I am not sure about this exact OP case, but LogTable's usually are not limited to just few records per user, so I would definitely recommend "quirky" update method over recursive CTE there. Saying the above, if solution with CROSS APPLY wouldn't need recursive CTE, it would most likely win over.
Not all recursvie CTEs are slow
http://www.sqlservercentral.com/Forums/Topic873124-338-5.aspx#bm1013407
Yep, I remember this great one from a while ago. But...
There are caveats! Check the last post from Jeff Moden in this thread:
It IS VERY fast indeed! But only if the number of distinct rows out number those that have duplicates. If you have a lot of duplicate rows, you may want to revert back to a simple DISTINCT or GROUP BY clause.
So, in order for it to be fast you need to know what kind of data distribution you have in your database before hand. In the example provided by Paul, It's super-fast as there is a high number of duplicate data in his test table, so recursive CTE doesn't have really high level of "recursiveness".
Same here! When you run similar solution on the small example of Log data, where there are only few records per user, the performance is acceptable and may even win over any other solutions. But, if the number of log entries per user grows, recursive CTE based solution becomes slower and slower, until it's start to look like endless loop;-).

Eugene, that is absolutely spot on, and your last sole-user sample is an excellent demonstration of the distribution limitation. However, the sample sets provided by the OP are in my opinion quite suitable for the rCTE method as only 1 row in 4 is retained. The results speak for themselves - six seconds for a million rows is ball park with the QU - and the rCTE scales linearly too.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

ChrisM@Work (3/14/2013)
Eugene, that is absolutely spot on, and your last sole-user sample is an excellent demonstration of the distribution limitation. However, the sample sets provided by the OP are in my opinion quite suitable for the rCTE method as only 1 row in 4 is retained. The results speak for themselves - six seconds for a million rows is ball park with the QU - and the rCTE scales linearly too.

Agree on this. Unfortunately, it does look like OP has left this thread. So we don't know what kind of real data he has in his database...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Eugene Elutin (3/14/2013)
ChrisM@Work (3/14/2013)
Eugene, that is absolutely spot on, and your last sole-user sample is an excellent demonstration of the distribution limitation. However, the sample sets provided by the OP are in my opinion quite suitable for the rCTE method as only 1 row in 4 is retained. The results speak for themselves - six seconds for a million rows is ball park with the QU - and the rCTE scales linearly too.
Agree on this. Unfortunately, it does look like OP has left this thread. So we don't know what kind of real data he has in his database...

That's you off the hook then 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden