SQL Monster

[tim222]

I have created an SQL statement that has become a living, breathing monster with its own ZIP Code. It has more Inner Joins than a hippy commune.

[phranque]

how long does it take to execute?

[tim222]

Less than one second. It actually takes longer to display the results... like around 1.5 seconds. The largest joined table has 70,000 records, and the result is typically around 300 items.

I posted this topic because I felt exhausted after I finally got the results I wanted :)

[LifeinAsia]

largest joined table has 70,000 records

Our largest joined table has about 12 million records and is often joined with over a half dozen other tables. And I'm sure others will can easily top that.

Isn't data fun? :)

[tim222]

The table grows at about 1000 records a week, so I should be safe for at least a year or two before I'll need to write something more efficient. Hopefully by then I will have won the lottery and it will be someone else's problem.

[wolfadeus]

[youtube.com...]

:)

W.

[rocknbil]

I have created an SQL statement that has become a living, breathing monster with its own ZIP Code

I feel your pain, and it's probably as fragile as a house of cards.

What I started doing was avoiding these like the plague. Now that you know how crazy it can get, you can head these off by rethinking the data structure in advance so it's a little more simple. It's almost better to take a performance hit over the potential for maintenance nightmares.

[tim222]

I feel your pain, and it's probably as fragile as a house of cards.

I read your comment earlier today while I was on a conference call and I almost burst out laughing!

Now here's the big difference between having an application on an intranet versus having it on the internet... In house, your users become the guinea pigs. They can break something faster than I could in a week of testing. So yeah, although nothing has come up yet, I'm sure there will be problems somewhere down the line :)

[bsterz]

It has more Inner Joins than a hippy commune

Let's just hope it doesn't get as many viruses