I work for a shared web hosting company. One of our offerings is Cold Fusion as well as MS SQL Server. For 4 hours in the morning our Cold Fusion MX servers were crashing every 15 minutes! I know CFMX can be unstable but this kind of downtime is very strange. I eventually linked the downtime to the SQL Server going down. What's interesting is that our IIS6 ASP.NET platform handled the downtime of the SQL Server without any problems. Anyways... I ran a Profiler on the server and found this query to be the cause of the crashes:
SELECT job_id,employee_id,customer_id,job_date,appointment_date
FROM job
WHERE job_id NOT IN (thousands of ids)
AND ((isDeleted IS NULL) OR (isDeleted = 0))
ORDER BY appointment_date
the job table is about 8000 rows. What's really funny is that all those ids in the NOT IN section is from another table called invoice! The basics of the query is to return all jobs that have not been invoiced. This customer has been working on fixing this query for the last day! They have also been very vocal in their complaints about the CFMX servers going down, yet they are the ones causing it! I phoned them and they didn't seem to have a clue.
I e-mailed him a new query:
SELECT j.*
FROM job j LEFT OUTER JOIN invoice i
ON j.job_id = i.job_id
WHERE i.job_id IS NULL AND ((j.isDeleted IS NULL) OR (j.isDeleted = 0))
ORDER BY appointment_date
This query gets the results instantly. He responded back saying he's fixed it already. I load up the profiler and I see my query being used
.
Shared hosting administration is a thankless job. I've fixed so many customers broken code and receive no acknowledgement for my efforts. I really wish there was a governing body over programmers. I've seen some pretty bad code, and I've only been working in IT for 3.5 years!
Anyways... there's not much point to this thread, but I'm sure some SQL programmers may get a kick out of this persons highly inefficent query.
SELECT job_id,employee_id,customer_id,job_date,appointment_date
FROM job
WHERE job_id NOT IN (thousands of ids)
AND ((isDeleted IS NULL) OR (isDeleted = 0))
ORDER BY appointment_date
the job table is about 8000 rows. What's really funny is that all those ids in the NOT IN section is from another table called invoice! The basics of the query is to return all jobs that have not been invoiced. This customer has been working on fixing this query for the last day! They have also been very vocal in their complaints about the CFMX servers going down, yet they are the ones causing it! I phoned them and they didn't seem to have a clue.
I e-mailed him a new query:
SELECT j.*
FROM job j LEFT OUTER JOIN invoice i
ON j.job_id = i.job_id
WHERE i.job_id IS NULL AND ((j.isDeleted IS NULL) OR (j.isDeleted = 0))
ORDER BY appointment_date
This query gets the results instantly. He responded back saying he's fixed it already. I load up the profiler and I see my query being used
.Shared hosting administration is a thankless job. I've fixed so many customers broken code and receive no acknowledgement for my efforts. I really wish there was a governing body over programmers. I've seen some pretty bad code, and I've only been working in IT for 3.5 years!
Anyways... there's not much point to this thread, but I'm sure some SQL programmers may get a kick out of this persons highly inefficent query.
Facebook
Twitter