Matt Whitfield (1/9/2009)

---

So, collectively we fixed a logic funny for you, and made it quicker to boot.

Go team! 😛

Ultimate props to Jeff on this one - I think the...

Site Visits Results

Summary

Three tests were run with different methods of calculating site visits.

1. Using a normal cursor - jacroberts

This took 29:36 to complete

2. Using a "pseudo" cursor - Jeff...

TheSQLGuru (1/5/2009)

---

jacroberts, I imagine this query may be succeptible to parameter sniffing due to the where clause. Well, that assumes there is an index that is useful here and...

The first bit of testing is finished. Firstly I am just testing the different initial queries to get the data. So no results from the cursors yet. I've had to...

Matt Whitfield (1/3/2009)

---

@jacroberts - can you include that last one in your test runs monday? I know it's cheating, but I think coming up with the best performing solution is...

Jeff Moden (1/3/2009)

---

jacroberts (1/3/2009)

---

BWAA-HAAA!!! Well, at least I know someone read the code. Yes...

BWAA-HAAA!!! Well, at least I know someone read the code. Yes sir, I made...

Jeff Moden (1/3/2009)

---

OK... here we go. First, I'm gonna recommend that you add the following index to the WebLogEvents table... it will NOT interfere with inserts or deletes, will...

Jeff Moden (1/3/2009)

---

jacroberts (1/2/2009)

---

The stored procedure that calls the function just takes the results table from the TVF and inserts this into a reporting table number of new site visits...

Matt Whitfield (1/2/2009)

---

Before i start - a couple of things. Given the volume of data you're talking about, i'm not confident my first method would be quicker. Because it's a...

Jeff Moden (1/2/2009)

---

Ok... from JacRobert's description, Create Table statement, and a couple of good guesses like the fact that since all the columns are nullable, there's no primary key, here's...

Jeff Moden (1/2/2009)

---

I agree... JacRoberts, would you provide the CREATE TABLE statement for the dbo.WebLogEvents table, please. From that, I'll make a million row table over a period of...

TheSQLGuru (1/2/2009)

---

jacroberts, I am pretty sure (>90%) that your problem can be solved with a self-join set-based solution. If the table is properly indexed that solution could well be...

Jeff Moden (1/2/2009)

---

Alexander Kuznetsov (12/24/2008)

---

Actually in most cases the best alternative is a loop written in a modern language such as C#, not a T-SQL WHILE loop or a CURSOR....

battelofhalfwits (1/1/2009)

---

Also, when you say "Imagine scaling the cursor to tables with millions of rows". I'm imagining it! Are you sure that inserting the rows into a temporary table with...