Programming Forums > Community Forums > Coder's Corner Lounge

kurifu · Jan 3rd, 2008, 4:34 PM

Hey guys; Trying to optimize some code that I wrote to not kill our DB server, I was hoping someone could give me some insightful feedback, as googling this isn't very easy.

Basically I have some cats in a database table, all with their own id. Each cat gets certain vaccinations, but never all at once. So I have another table where a record is inserted in a column indicating which shots they have received and when. There is a single column for each of these vaccinations, I put a "y" in it when complete.

So I want to generate a report which displays shots not administered, right now I grab each cat id, and loop through each one finding which ones are done, and which are not.

I am hoping I can take a single query and filter out results which have a Y in each column, problem is that this can be over multiple records. This will save a HUGE load on the DB. Any input?

Thanks,.

Sane · Jan 3rd, 2008, 4:51 PM

Could you clarify your question, or how the tables are set up, so someone can give the right answer?

Maybe even an example of what's in the DB would be great.

But I would have the tables set up so I can just do something like:

SELECT cat_id, vaccination_id FROM shots_table WHERE complete = 'n' ORDER BY cat_id

Then each row will be all the shots that the cat didn't receive, for each cat, ordered by the cat that didn't receive it. That is if this is how your tables are set up. But this seems way too simple an answer.

mbd · Jan 3rd, 2008, 6:04 PM

assuming one cat has many vaccinations and you want to find cats without any vaccinations

 sql Syntax (Toggle Plain Text) 
SELECT * FROM cats
LEFT OUTER JOIN vaccinations
ON vaccinations.cat_id = cats.cat_id
WHERE vaccinations.cat_id IS NULL
select * from cats
left outer join vaccinations
on vaccinations.cat_id = cats.cat_id
where vaccinations.cat_id is null

Jimbo · Jan 3rd, 2008, 10:29 PM

Not knowing how many columns there are, is it feasible to do something like this:

 sql Syntax (Toggle Plain Text) 
SELECT * 
FROM Cats c
   LEFT OUTER JOIN Vaccinations v ON c.cat_id = v.cat_id
WHERE v.vaccination1 != 'Y' OR v.vaccination2 != 'Y' OR ...
SELECT * 
FROM Cats c
   LEFT OUTER JOIN Vaccinations v ON c.cat_id = v.cat_id
WHERE v.vaccination1 != 'Y' OR v.vaccination2 != 'Y' OR ...

Otherwise, what if you redid the database like this:

(Toggle Plain Text)

Cats (Id (PK), Name, Owner, whatever)
Vaccinations (Id(PK), Name, whatever)
Cat_Vacs (CatId (FK), VacId (FK))

and then used a query like this:

 sql Syntax (Toggle Plain Text) 
SELECT c.Id, c.Name 
FROM Cats c LEFT OUTER JOIN
    Cat_Vacs cv ON c.Id = cv.CatId
GROUP BY c.Id, c.Name
HAVING COUNT(cv.VacId) < (SELECT COUNT(*) FROM Vaccinations))
SELECT c.Id, c.Name 
FROM Cats c LEFT OUTER JOIN
    Cat_Vacs cv ON c.Id = cv.CatId
GROUP BY c.Id, c.Name
HAVING COUNT(cv.VacId) < (SELECT COUNT(*) FROM Vaccinations))

Logical idea: get all cats who haven't had the number of vaccines in the Vaccines table.

Of course, this is technically 2 queries, but the inner one will be quick (the DB will know how many rows are in the table so this query is easily optimized internally).

kurifu · Jan 4th, 2008, 12:56 AM

I always thought that LEFT OUTER JOIN required matching records in both tables, now that I know it does not, it should actually work very well. Thanks.

kurifu · Jan 4th, 2008, 2:09 PM

Ok, so officially this is what solved my problem, and despite the number of inner queries, it runs much much faster...

(Toggle Plain Text)

SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD]
FROM MEOW_Cats
LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id
WHERE (cst_status <> 'D' AND cst_status <> 'R' AND cst_status <> 'C') AND
(SELECT COUNT(*) FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fvrcpc_initial = 'Y') = 0 OR
(SELECT COUNT(*) FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fvrcpc_booster = 'Y') = 0 OR
(SELECT COUNT(*) FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fevl_initial = 'Y') = 0 OR
(SELECT COUNT(*) FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fevl_booster = 'Y') = 0 OR
(SELECT COUNT(*) FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_rabies = 'Y') = 0 OR
(SELECT COUNT(*) FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_deworm = 'Y') = 0 OR
(SELECT COUNT(*) FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_alter = 'Y') = 0 
ORDER BY cat_name ASC

mbd · Jan 4th, 2008, 2:26 PM

try modifying your query to use this approach. i have no way of testing this, but i think you could eliminate those subqueries

 sql Syntax (Toggle Plain Text) 
SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD]
FROM MEOW_Cats
LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id
JOIN MEOW_HealthRecords ON hre_cat_id = cat_id
WHERE hre_fvrpc_initial != 'Y' OR hre_fvrcpc_booster != 'Y' ...
SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD]
FROM MEOW_Cats
LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id
JOIN MEOW_HealthRecords on hre_cat_id = cat_id
WHERE hre_fvrpc_initial != 'Y' OR hre_fvrcpc_booster != 'Y' ...

Jimbo · Jan 4th, 2008, 9:32 PM

So, are the counts ever going to be more than 1? If so, presumably that means that either the cat will have to get another one in the future and you maybe should somehow be tracking the date it was last administered, unless getting the shot extra times is optional. If not, then using the inner query will be a performance cost, as you're doing a join and a projection for each sub-query when you could just do the join once and compare to each column.

kurifu · Jan 8th, 2008, 12:52 PM

Quote:

Originally Posted by mbd

try modifying your query to use this approach. i have no way of testing this, but i think you could eliminate those subqueries

 sql Syntax (Toggle Plain Text) 
SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD]
FROM MEOW_Cats
LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id
JOIN MEOW_HealthRecords ON hre_cat_id = cat_id
WHERE hre_fvrpc_initial != 'Y' OR hre_fvrcpc_booster != 'Y' ...
SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD]
FROM MEOW_Cats
LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id
JOIN MEOW_HealthRecords on hre_cat_id = cat_id
WHERE hre_fvrpc_initial != 'Y' OR hre_fvrcpc_booster != 'Y' ...

This won't work because there are multiple health records per cat, the WHERE clause would always return true, returning multiple records for each cat.

I should add that even when using multiple subqueries this improved DB performance by a massive amount.

mbd · Jan 8th, 2008, 1:38 PM

i suspect that your database is not normalized

Jan 3rd, 2008, 4:34 PM	#1
kurifu Expert Programmer Join Date: Jul 2004 Location: Halifax, Nova Scotia (Canada) Posts: 784 Rep Power: 5	Complex SQL Question Question Hey guys; Trying to optimize some code that I wrote to not kill our DB server, I was hoping someone could give me some insightful feedback, as googling this isn't very easy. Basically I have some cats in a database table, all with their own id. Each cat gets certain vaccinations, but never all at once. So I have another table where a record is inserted in a column indicating which shots they have received and when. There is a single column for each of these vaccinations, I put a "y" in it when complete. So I want to generate a report which displays shots not administered, right now I grab each cat id, and loop through each one finding which ones are done, and which are not. I am hoping I can take a single query and filter out results which have a Y in each column, problem is that this can be over multiple records. This will save a HUGE load on the DB. Any input? Thanks,. __________________ Clifford Matthew Roche <geek@cliffordroche.com> Web Hosting: http://www.crd-hosting.com Consulting: http://www.crdev-consulting.com

Jan 3rd, 2008, 4:51 PM	#2
Sane Programming Guru Join Date: Apr 2005 Location: Waterloo, Ontario Posts: 2,032 Rep Power: 6	Re: Complex SQL Question Question Could you clarify your question, or how the tables are set up, so someone can give the right answer? Maybe even an example of what's in the DB would be great. But I would have the tables set up so I can just do something like: `SELECT cat_id, vaccination_id FROM shots_table WHERE complete = 'n' ORDER BY cat_id` Then each row will be all the shots that the cat didn't receive, for each cat, ordered by the cat that didn't receive it. That is if this is how your tables are set up. But this seems way too simple an answer.

Jan 3rd, 2008, 6:04 PM	#3
mbd Programmer Join Date: Nov 2007 Posts: 86 Rep Power: 1	Re: Complex SQL Question Question assuming one cat has many vaccinations and you want to find cats without any vaccinations sql Syntax (Toggle Plain Text) SELECT * FROM cats LEFT OUTER JOIN vaccinations ON vaccinations.cat_id = cats.cat_id WHERE vaccinations.cat_id IS NULL select * from cats left outer join vaccinations on vaccinations.cat_id = cats.cat_id where vaccinations.cat_id is null

Jan 3rd, 2008, 10:29 PM	#4
Jimbo Battle Programmer Join Date: Feb 2006 Location: Bellevue, WA, USA Posts: 769 Rep Power: 3	Re: Complex SQL Question Question Not knowing how many columns there are, is it feasible to do something like this: sql Syntax (Toggle Plain Text) SELECT * FROM Cats c LEFT OUTER JOIN Vaccinations v ON c.cat_id = v.cat_id WHERE v.vaccination1 != 'Y' OR v.vaccination2 != 'Y' OR ... SELECT * FROM Cats c LEFT OUTER JOIN Vaccinations v ON c.cat_id = v.cat_id WHERE v.vaccination1 != 'Y' OR v.vaccination2 != 'Y' OR ... Otherwise, what if you redid the database like this: (Toggle Plain Text) Cats (Id (PK), Name, Owner, whatever) Vaccinations (Id(PK), Name, whatever) Cat_Vacs (CatId (FK), VacId (FK)) Cats (Id (PK), Name, Owner, whatever) Vaccinations (Id(PK), Name, whatever) Cat_Vacs (CatId (FK), VacId (FK)) and then used a query like this: sql Syntax (Toggle Plain Text) SELECT c.Id, c.Name FROM Cats c LEFT OUTER JOIN Cat_Vacs cv ON c.Id = cv.CatId GROUP BY c.Id, c.Name HAVING COUNT(cv.VacId) < (SELECT COUNT() FROM Vaccinations)) SELECT c.Id, c.Name FROM Cats c LEFT OUTER JOIN Cat_Vacs cv ON c.Id = cv.CatId GROUP BY c.Id, c.Name HAVING COUNT(cv.VacId) < (SELECT COUNT() FROM Vaccinations)) Logical idea: get all cats who haven't had the number of vaccines in the Vaccines table. Of course, this is technically 2 queries, but the inner one will be quick (the DB will know how many rows are in the table so this query is easily optimized internally). __________________ <insert disclaimer here> <insert shameless plug for Visual Studio here>

Jan 4th, 2008, 12:56 AM	#5
kurifu Expert Programmer Join Date: Jul 2004 Location: Halifax, Nova Scotia (Canada) Posts: 784 Rep Power: 5	Re: Complex SQL Question Question I always thought that LEFT OUTER JOIN required matching records in both tables, now that I know it does not, it should actually work very well. Thanks. __________________ Clifford Matthew Roche <geek@cliffordroche.com> Web Hosting: http://www.crd-hosting.com Consulting: http://www.crdev-consulting.com

Jan 4th, 2008, 2:09 PM	#6
kurifu Expert Programmer Join Date: Jul 2004 Location: Halifax, Nova Scotia (Canada) Posts: 784 Rep Power: 5	Re: Complex SQL Question Question Ok, so officially this is what solved my problem, and despite the number of inner queries, it runs much much faster... (Toggle Plain Text) SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD] FROM MEOW_Cats LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id WHERE (cst_status <> 'D' AND cst_status <> 'R' AND cst_status <> 'C') AND (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fvrcpc_initial = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fvrcpc_booster = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fevl_initial = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fevl_booster = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_rabies = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_deworm = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_alter = 'Y') = 0 ORDER BY cat_name ASC SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD] FROM MEOW_Cats LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id WHERE (cst_status <> 'D' AND cst_status <> 'R' AND cst_status <> 'C') AND (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fvrcpc_initial = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fvrcpc_booster = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fevl_initial = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_fevl_booster = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_rabies = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_deworm = 'Y') = 0 OR (SELECT COUNT() FROM MEOW_HealthRecords WHERE hre_cat_id = cat_id AND hre_alter = 'Y') = 0 ORDER BY cat_name ASC __________________ Clifford Matthew Roche <geek@cliffordroche.com> Web Hosting: http://www.crd-hosting.com Consulting: http://www.crdev-consulting.com

Jan 4th, 2008, 2:26 PM	#7
mbd Programmer Join Date: Nov 2007 Posts: 86 Rep Power: 1	Re: Complex SQL Question Question try modifying your query to use this approach. i have no way of testing this, but i think you could eliminate those subqueries sql Syntax (Toggle Plain Text) SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD] FROM MEOW_Cats LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id JOIN MEOW_HealthRecords ON hre_cat_id = cat_id WHERE hre_fvrpc_initial != 'Y' OR hre_fvrcpc_booster != 'Y' ... SELECT DISTINCT cat_id, cat_name, cst_status, cat_birthday, cat_intake_date, CONVERT(VARCHAR(10), cat_birthday, 111) AS [YYYY/MM/DD] FROM MEOW_Cats LEFT JOIN MEOW_CatsStatus ON MEOW_Cats.cat_id = MEOW_CatsStatus.cst_cat_id JOIN MEOW_HealthRecords on hre_cat_id = cat_id WHERE hre_fvrpc_initial != 'Y' OR hre_fvrcpc_booster != 'Y' ...

Jan 4th, 2008, 9:32 PM	#8
Jimbo Battle Programmer Join Date: Feb 2006 Location: Bellevue, WA, USA Posts: 769 Rep Power: 3	Re: Complex SQL Question Question So, are the counts ever going to be more than 1? If so, presumably that means that either the cat will have to get another one in the future and you maybe should somehow be tracking the date it was last administered, unless getting the shot extra times is optional. If not, then using the inner query will be a performance cost, as you're doing a join and a projection for each sub-query when you could just do the join once and compare to each column. __________________ <insert disclaimer here> <insert shameless plug for Visual Studio here>

Jan 8th, 2008, 1:38 PM	#10
mbd Programmer Join Date: Nov 2007 Posts: 86 Rep Power: 1	Re: Complex SQL Question Question i suspect that your database is not normalized

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Question regarding data input into PHP/Mysql using something other than GET	davil	PHP	6	Nov 20th, 2007 9:06 AM
SQL: Rewrite a complex join without #temp tables?	jonyzz	Other Scripting Languages	1	Sep 5th, 2007 11:51 AM
How to post a question	nnxion	C++	10	Jun 3rd, 2005 12:53 PM
How to post a question	nnxion	C++	0	Jun 3rd, 2005 9:55 AM
How to post a question	nnxion	C	0	Jun 3rd, 2005 9:55 AM

Programming Forums > Community Forums > Coder's Corner Lounge

Complex SQL Question Question