•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 423,020 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,980 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser: Programming Forums
Views: 1405 | Replies: 1 | Solved
 |
•
•
Join Date: May 2007
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Static Count vs Dynamic Count (Very advanced, gurus take your shot) I'm building a large scale application and was wondering the difference between static counts vs dynamic counts.
Right now, I'm using dynamic counts. For an example:
What happens is that for each car row, carimage count, car buyer count, car seller count is retrieved. Then the car id is passed to the IsCarHasGoodRecrd to find out if the car has a good record and whether to include it in the result set. As you can tell from the query above, about 3 correlated subqueries are executed in the main query, and 2 other correlate subqueries are returned in the function...totaling 5 correlated subqueries for each row in car table.
Now, if everything is properly INDEXED, on a high-end server (2x dual opteron, 4gigs ram, 2x 250 gig)...with a large concurrent userbase and 20 million rows in all tables......what is performance going to be like? I never dealt with such databases or userbase so I don't know what to expect.
I was thinking of using static counts (where the there is a column for each count in the parent table) but it's too much maintenance and i would have to use transactions everytime i update it to make sure the counts are not off.
Table Schema:
The reason I use a stored function is to keep the SQL syntax simple. There's alot of tables OTHER than [car] table which follows the SAME pattern (like a [parts] table OR [upgrades] table). If I put the logic in a stored procedure...I could just write:
SELECT * FROM ([car] OR [parts] or [upgrades] or whatever that follows the same pattern) WHERE iscarvalid(blahblahblah);
I could do that with left joins but it's very long and tedious to write. I heard left joins maybe faster.
However, if everything is indexed..what performance am I expecting with the server configuration in my above post?
Right now, I'm using dynamic counts. For an example:
SELECT *,
(SELECT COUNT(1) FROM CarImages WHERE CarImages.carid = Cars.id) as ImageCount,
(SELECT COUNT(1) FROM CarBuyers WHERE CarBuyers.carid = Cars.id) as BuyerCount,
(SELECT COUNT(1) FROM CarSellers WHERE CarSellers.carid = Cars.id) as SellerCount,
FROM Cars WHERE Cars.CarCondition > 2 AND IsCarHasGoodRecord(Cars.carid);
Inside IsCarHasGoodRecord function:
function IsCarHasGoodRecrd(carid INT) RETURNS BOOLEAN
BEGIN
DECLARE GoodCondition INT; DECLARE CarMaxConditionId INT;
SELECT 1 INTO CarMaxConditionId FROM CarMaxConditions WHERE carid = carid;
SELECT 1 INTO GoodCondition FROM KellyBlueBookConfirm WHERE carid = carid AND carmaxid = CarMaxConditionId;
--If the current "car row" is good condition, return true so that the car will be included in the result set
if GoodCondition = 1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END$; What happens is that for each car row, carimage count, car buyer count, car seller count is retrieved. Then the car id is passed to the IsCarHasGoodRecrd to find out if the car has a good record and whether to include it in the result set. As you can tell from the query above, about 3 correlated subqueries are executed in the main query, and 2 other correlate subqueries are returned in the function...totaling 5 correlated subqueries for each row in car table.
Now, if everything is properly INDEXED, on a high-end server (2x dual opteron, 4gigs ram, 2x 250 gig)...with a large concurrent userbase and 20 million rows in all tables......what is performance going to be like? I never dealt with such databases or userbase so I don't know what to expect.
I was thinking of using static counts (where the there is a column for each count in the parent table) but it's too much maintenance and i would have to use transactions everytime i update it to make sure the counts are not off.
Table Schema:
[Car] id - identity name - carname status - active, deleted etc created modified [CarImage] id - identity carid - foreign key, points to [car].id name - image name status created [CarBuyers] id - identity carid - foreign key, points to [car].id buyerid - foreign key, points to [users].id created status [CarSellers] id - identity carid - foreign key, points to [car].id sellerid - foreign key, points to [users].id created status [users] id - identity name created status
The reason I use a stored function is to keep the SQL syntax simple. There's alot of tables OTHER than [car] table which follows the SAME pattern (like a [parts] table OR [upgrades] table). If I put the logic in a stored procedure...I could just write:
SELECT * FROM ([car] OR [parts] or [upgrades] or whatever that follows the same pattern) WHERE iscarvalid(blahblahblah);
I could do that with left joins but it's very long and tedious to write. I heard left joins maybe faster.
However, if everything is indexed..what performance am I expecting with the server configuration in my above post?
Last edited by JokerOfACoder : May 8th, 2007 at 2:27 pm.
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
I'll let someone else tackle the issue of expectations, but I did have a few thoughts....
Left joins may be tedious to write, but performance is more important. I could reduce a 10 line query to 1 line. But if the 1 line query takes 45 minutes longer to run, its hardly worth it.
I'm not an expert but I would be leery of using that many correlated subqueries with that kind of volume. Afaik, correlated subqueries execute once for each value in the outer query. So _generally_ subqueries _tend_ to be less efficient than joins.
However, databases and optimizers differ. Some may be capable of generating more efficient plans for subqueries, resulting in better performance. While other optimizers might not, yielding poorer performance for subqueries. Especially when dealing with a large volume of data.
I would read up on your db specifically, and learn how it handles joins versus correlated subqueries. That way you could make more of an informed decision.
I don't know if that helps any, but there it is ;-)
Left joins may be tedious to write, but performance is more important. I could reduce a 10 line query to 1 line. But if the 1 line query takes 45 minutes longer to run, its hardly worth it.
I'm not an expert but I would be leery of using that many correlated subqueries with that kind of volume. Afaik, correlated subqueries execute once for each value in the outer query. So _generally_ subqueries _tend_ to be less efficient than joins.
However, databases and optimizers differ. Some may be capable of generating more efficient plans for subqueries, resulting in better performance. While other optimizers might not, yielding poorer performance for subqueries. Especially when dealing with a large volume of data.
I would read up on your db specifically, and learn how it handles joins versus correlated subqueries. That way you could make more of an informed decision.
I don't know if that helps any, but there it is ;-)
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Similar Threads
- Combining Static & Dynamic content (JSP)
- Calling button-click events from different classes (Python)
- static ip (OS X)
- works for static need help to make it dynamic (C++)
- static ip? (Networking Hardware Configuration)
Other Threads in the MySQL Forum
- Previous Thread: Sql Joins
- Next Thread: Any help would be appreciated
