Petterns for SQL Tables and Stored Procedures

I am looking for some patterns in SQL Server. The Patterns and Paractices
did not seem to have what I am looking for, but it would seem to me that it
is a very commmon scenario that must have been covered.
Basically, it is the problem of aggregating data for overviews.
Assume the following:
Table Services
[SID][ServiceName][ServiceCategoryID][PI
D] [AmmountDue]
Table Payer
[PID][PayerCategory][PayerName]
Table Payments
[PayID][SID][Date][Amount][PID] ** Occasionally a third party might pay
for someone else
Table ServiceCategory
[ServiceCategoryID][ServiceCategoryName]
Table PayerCategory
[PayerCategoryID][PayerCategoryName]
Now, I want to get a summary of the data very quickly that breaks things
down like:
By ServiceCategory
TotalAmountPaid TotalDue AmountDueFrom30DaysAgo ADF31-60DaysAgo
adf61-90Days Ago
Then break these down by PayerCategory
This would seem like a common type of thing, and Ican think of ways to do
this but that take a lot of time, if there are millions of rows, and I can
imagine that triggers might be useful here to keep up to date, but I am
unfamiliar with them.
If you can give me any guidance on this it owuld be helpful. For extra
points, what about being able to dynamically change the periods from say
0-30days to 0-15 days)
Thanks a lot
BBFor starters, you need to post some ddl, sample data and expected results.
Not just a narrative.
I can tell you this though - without dates in your services table or
payments table to know when the service and payments took place, what you're
looking for is impossible.
"bobbyballgame" wrote:

> I am looking for some patterns in SQL Server. The Patterns and Paractices
> did not seem to have what I am looking for, but it would seem to me that i
t
> is a very commmon scenario that must have been covered.
> Basically, it is the problem of aggregating data for overviews.
> Assume the following:
> Table Services
> [SID][ServiceName][ServiceCategoryID][PI
D] [AmmountDue]
> Table Payer
> [PID][PayerCategory][PayerName]
> Table Payments
> [PayID][SID][Date][Amount][PID] ** Occasionally a third party might pay
> for someone else
> Table ServiceCategory
> [ServiceCategoryID][ServiceCategoryName]
> Table PayerCategory
> [PayerCategoryID][PayerCategoryName]
>
> Now, I want to get a summary of the data very quickly that breaks things
> down like:
> By ServiceCategory
> TotalAmountPaid TotalDue AmountDueFrom30DaysAgo ADF31-60DaysAgo
> adf61-90Days Ago
> Then break these down by PayerCategory
>
> This would seem like a common type of thing, and Ican think of ways to do
> this but that take a lot of time, if there are millions of rows, and I can
> imagine that triggers might be useful here to keep up to date, but I am
> unfamiliar with them.
> If you can give me any guidance on this it owuld be helpful. For extra
> points, what about being able to dynamically change the periods from say
> 0-30days to 0-15 days)
> Thanks a lot
> BB
>
>
>|||Steve,
Thanks. The tables are internal ( I would not be allowed to post them) and a
lot more complicated. For example the Payments Table has 31 fields in it, so
I was trying to simplify.
The Service does have a Date field. Sorry about the ommission. Really, I am
looking for a general pattern for the problem of needing aggregate data from
many, amny rows quickly, so I thought a narrative would be more useful.
I will work on a model that is a little more simple, and for what is worth,
I need the data in XML format from SQL 2000.
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:070D02CF-7367-4593-91F4-6533588D830E@.microsoft.com...
> For starters, you need to post some ddl, sample data and expected results.
> Not just a narrative.
> I can tell you this though - without dates in your services table or
> payments table to know when the service and payments took place, what
> you're
> looking for is impossible.
>
> "bobbyballgame" wrote:
>

pessimistic vs optimistic concurrency control

Could someone explain to me what the difference between pessimistic (for examle 2PL) and optimistic concurrency control is?I have never heard of "2PL" before, but anyway:

Pessimistic means you actually lock the data when you select it to make sure nobody else can update it before you do. For example in Oracle you would SELECT ... FROM ... WHERE ... FOR UPDATE;

Optimistic means that you do not lock the data when you select it, but when you subsequently update it you check that it has not been updated by someone else in the meanwhile, otherwise your update fails. There are various ways to do this - record version numbers, last_update timestamps, or just check all the data values like this:

UPDATE ...
SET val1 = :new_val1, val2 = :new_val2, ...
WHERE key = :key
and val1 = :old_val1, val2 = :old_val2, ...;

(:old_val1, :new_val1 are variables holding the selected and modified values for column val1).

Pessimistic locking requires that a database session is maintained between the select and the update. In web-based applications, no database connection is maintained and so optimistic locking must be used.|||I suspect that mrmonkeyboy meant 2PC, a common abbreviation for two phase commit. I can see a slight resemblance between pessimistic locking and two phase commit... Both of them are intensely statefull.

-PatP|||Ok! Thanks a lot for the info!

-Mr Monkeyboy|||FYI: Two Phase Locking Protocol ( 2PL )

"In a given transaction, all locks precede all unlocks, i.e. once a transaction has released a lock it cannot acquire any more locks.

2PL guarantees serializability for all transactions that go to conclusion"|||Thanks, I hadn't come across that term before.

Pessimistic locking

I am attempting to try a pesimistic lock, meaning that i want to lock a row or table for a period of time and then relase it when i am done. To test this i wrote the following:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANsaction
Select * From configurationitem WITH (ROWLOCK,xlock)
where name = 'NextReceiptNumber' and category = 'AR';

Declare @.i int
set @.i = 0
while @.i < 300000
Begin
print @.i
set @.i = @.i + 1
end
COMMIT TRANsaction

To test, while the above is looping i open another query window and select from the same table using the following:

Select ConfigurationItemValue From configurationitem where ItemID = 418

This does not work because this query returns IMMEDIATELY. However, if I change the query to the following:

Select ConfigurationItemValue From configurationitem where name = 'NextReceiptNumber' and category = 'AR';

It does not return until the transaction query above is finished (which is the way it should work).

So, my question is, why does it not lock when i select by a primary key but lock when i do NOT select by a primary key (ItemID is a primary key).

thanks in advance.

Ok, I think we are missing something here. Is the primary key value for this row = 418? You should only have an exclusive lock on the row.

This is the table that I tested with, and it did wait when I looked for 418, and not for any other row. Any query that requires a table scan (snapshot isolation not withstanding) will not be able to complete (which would be the case for a query that looks for name and category, no matter what your values are.) This is because other queries will take a lock on every row in the table eventually and will get stuck on the locked rows.

drop table configurationItem
go
create table configurationItem
(
itemId int primary key,
name varchar(100),
category char(2),
configurationItemValue varchar(10)
)
insert into configurationItem
select 418,'NextReceiptNumber','AR','sals'
union all
select 2,'asldfjlka','AT','sals'
union all
select 3,'aqjsadklfaj','DR','sals'
union all
select 4,'ao2ioi23jkasd','DD','sals'
union all
select 5,'alifdjald','CD','sals'
union all
select 6,'ajsdflkasdlkja','CF','sals'
union all
select 7,'juqoiwfewoijlk','TT','sals'
union all
select 8,'asdancas','QR','sals'

|||Using XLOCK in SELECT statements will not prevent reads from happening. This is because SQL Server has a special optimization under read committed isolation level that checks if the row is dirty or not and ignores the xlock if the row has not changed. Since this is acceptable under the read committed isolation level semantics it is by design. So you will have to use a more aggressive locking hint like UPDLOCK with ROWLOCK. But what are you trying that requires such pessimistic locking strategies? Why do you want to do row-by-row procedural processing? Can't you use set-based operations instead?

Pesky Home Page

I can't seem to get my home page to stay put. SOMEHOW, my
home page is from some th.msie.cc/index.php?aid=2003 and
I cannot get rid of it. It has made itself my default
home page. I don't know where in the heck it came from in
the first place...probably my husband and his dirty web
sites !! Please HELP!!RUn internet Explorer. then choose the Tools menu, -> Internet Options... ON
the general tab there is a box where you can set the home page... You can
choose Use Default or set it whereever ie( www.msn.com)
Good luck
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Atlantis" <anonymous@.discussions.microsoft.com> wrote in message
news:1345401c41200$34f0c1f0$a101280a@.phx
.gbl...
> I can't seem to get my home page to stay put. SOMEHOW, my
> home page is from some th.msie.cc/index.php?aid=2003 and
> I cannot get rid of it. It has made itself my default
> home page. I don't know where in the heck it came from in
> the first place...probably my husband and his dirty web
> sites !! Please HELP!!|||This may not be enough because such spyware will overwrite the homepage on s
tartup. Download a copy of SpyBot and/or Adaware. Both will scan and kill sp
ywares.|||
>--Original Message--
>This may not be enough because such spyware will
overwrite the homepage on startup. Download a copy of
SpyBot and/or Adaware. Both will scan and kill spywares.
>.
>Thanks for the help you gave. I ended up installing a
SpyKiller and it has eliminated the problem. The real
problem was solved, through much trial and error, by
going into the registry and resetting the home page and
search site to the web address I desired. I discovered,
too, that all of this was caused when I downloaded AOL
onto my computer. The media player needed for the 9.0 has
the spyware in it and overwrites the homepage settings
for Internet Explorer. I, of course, uninstalled AOL and
went back to Bell South where I was relatively
safe...except for the terrible experience I had with
their Accelerator. DO NOT download it. BellSouth even
says that it is only experimental!!
Again...Thanks!!|||I had the same problem. SpyBot and Ad-ware do not cure it. Delete a file c
:/windows/dp.dll, if you can. I couldn't delete it, but I renamed it, and m
y homepage stays put, now.

Pervassive Client Install

Would anyone install a Pervassive client application into the windows & sql
2005 cluster environment?
I don't see why not, although I'd prefer not to install Pervasive in any
environment.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"John Kamensky" <jkamensky@.whitfordww.com> wrote in message
news:%23fTtgDCVGHA.5332@.TK2MSFTNGP10.phx.gbl...
> Would anyone install a Pervassive client application into the windows &
> sql 2005 cluster environment?
>
sql

Pervassive Client Install

Would anyone install a Pervassive client application into the windows & sql
2005 cluster environment?I don't see why not, although I'd prefer not to install Pervasive in any
environment.
--
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"John Kamensky" <jkamensky@.whitfordww.com> wrote in message
news:%23fTtgDCVGHA.5332@.TK2MSFTNGP10.phx.gbl...
> Would anyone install a Pervassive client application into the windows &
> sql 2005 cluster environment?
>

Pervassive Client Install

Would anyone install a Pervassive client application into the windows & sql
2005 cluster environment?I don't see why not, although I'd prefer not to install Pervasive in any
environment.
Hal Berenson, President
PredictableIT, LLC
http://www.predictableit.com
"John Kamensky" <jkamensky@.whitfordww.com> wrote in message
news:%23fTtgDCVGHA.5332@.TK2MSFTNGP10.phx.gbl...
> Would anyone install a Pervassive client application into the windows &
> sql 2005 cluster environment?
>