Static Count vs Dynamic Count (Very advanced, gur

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Static Count vs Dynamic Count (Very advanced, gur

Author

Topic

JokerOfACoder
Starting Member

18 Posts

Posted - 2007-05-08 : 16:08:01

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:

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?

People used to tell me that as long as I query by an index, it will take less than 0.0003 seconds

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-08 : 16:24:03

Not sure about the " it will take less than 0.0003 seconds" but yes if your tables are properly indexed at frequent intervals, the COUNTs should be pretty quick even on 20 mil rows. I would recommend getting the count when required.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

JokerOfACoder
Starting Member

18 Posts

Posted - 2007-05-08 : 16:38:32

Thanks. Yep, everything will be properly indexed.
On another note, why are websites like Myspace slow? (I think it's their DB server that's getting slammed). If they have indexes on their tables, why is their site sometimes slow? Some of their forums don't even have 20 million posts.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-08 : 16:40:56

Joins will just about ALWAYS be faster than calling UDF's over and over for each row in your table. Calling a UDF to relate tables is usually a bad idea, in general, if you can avoid it.

- Jeff
http://weblogs.sqlteam.com/JeffS

JokerOfACoder
Starting Member

18 Posts

Posted - 2007-05-08 : 16:57:29

Yeah, joins are definetly faster but I need the "filtering" done on the where clause.

In my OBJECT MODEL API I do:

DataSet GetForCar()
{
string sql = "SELECT BLAHBLAHBLAH FROM Car WHERE Status = 0 AND " + PermissionFilter.GetCarFilter(); //returns IsCarValid(carid) sql funct
}

If I convert to a join, the FINAL SQL WILL BE:

SELECT * FROM Car LEFT JOIN CarBuyers ON Car.Id = CarBuyers.CarId LEFT JOIN CarSellers On Car.Id = CarSellers.CarId;

The problem is....I want the CAR ROW to be INCLUDED if they are EITHER a Buyer or a Seller. The query above only includes if they are both buyer and seller right?

And I want to keep this logic "common"...that's why I moved it inside a user stored function. Any object that implements carid and id foreignkey relationship can be filtered using the function etc.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-05-08 : 17:37:48

>>If they have indexes on their tables, why is their site sometimes slow?

Concurrency..

IME, use the function for low use cases, but switch to the fully expressed SQL for the high demand cases.

You cannot have it both ways... Pretty code or thumping speed.

DavidM

Production is just another testing cycle

JokerOfACoder
Starting Member

18 Posts

Posted - 2007-05-08 : 19:27:19

That doesn't work.......for an example, here's one scenario.

Imagine a meat selling market. Permissions on meat products are controlled by the column BuyAllowed. If BuyAllowed is set to friends, then friends could buy it. If family, family could buy it.

Here's the full table layout

[Meat]
MeatId
Name
Type
Status
BuyAllowed <--- Permission, 0 - All, 1 - Friends, 2 - Family, 3 - Relatives

[Friends]
FriendId
OwnerId <-- Meat owner
UserId - The friend person
OwnerOrder - The sort order for the owner
UserOrder - The sort order for the user

[Family]
FamilyId
OwnerId
UserId - <The family person
InHouse - does family live with owner
Income - Family Income
Expense - Family Expense

[Relative]
RelativeId
OwnerId
UserId
IsLongDistance

Say the current authenticated user is a FRIEND of the owner AND A relative.
The HAM MEAT is for FRIENDS ONLY (BuyAllowed = 1)

So I want to get back a result set of MEATS that is for FRIENDS OR RELATIVE that is RELATIVE to EACH RECORDS ownerid. If the meat's owner is NOT a friend or relative to the current authenticated user, DO NOT include the row in the result set.

A user defined function is the only way to accomplish this

function(ownerid, authenticatedid, buyallowed)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2007-05-08 : 19:44:09

>>A user defined function is the only way to accomplish this

This implies that your query is impossible to accomplish in SQL Server 7.

I highly doubt it....

DavidM

Production is just another testing cycle

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-08 : 19:56:05

I guarantee you a UDF isn't the only way. If you can focus and compact your logic into a few tables, and give us some sample data as well as the rules and expectations using that sample data, I am sure that we can assist you with some standard SQL. If standard SQL won't do the trick, then that usually means that the design might be flawed.

- Jeff
http://weblogs.sqlteam.com/JeffS

mattyblah
Starting Member

49 Posts

Posted - 2007-05-09 : 02:34:25

not sure if everyone is agreeing with you or stating along those lines because that's what you posted, but static values should be faster in your case. if you are talking about carimages, how big can that table balloon to, and is it getting updated so frequently you cant use a transaction? if you are talking about carbuyers, same thing occurs. carsellers? same. adding to these tables, unless a one and done, should fall into transaction territory, and if you are worried about efficiency, add updating the static table at the end of the transaction. it's hard to dissect a problem when you only see barely anything but hopefully this helps...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-09 : 02:43:50

This might perform better

SELECT		c.CarID,
		COUNT(i.CarID) AS ImageCount,
		COUNT(b.CarID) AS BuyerCount,
		COUNT(s.CarID) AS SellerCount
FROM		Cars AS c
LEFT JOIN	CarImages AS i ON i.CarID = c.ID
LEFT JOIN	CarBuyers AS b ON b.CarID = c.ID
LEFT JOIN	CarSellers AS s ON s.CarID = c.ID
WHERE		c.CarCondition > 2
GROUP BY	c.CarID

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources