Application Architecture issue

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

General SQL Server Forums

Database Design and Application Architecture

Application Architecture issue

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 14:30:15

"I know NOTHING" -- Schulzie

About the application.

I do know that someone opens a session and logs in to the application, it stays open

When they open up a session they call a sproc to see if they have a message in a sql server table

Whether the do or don't, I log the call

After every 3 minutes..whether there is activity or not, the session calls the sproc to look for a message

Now I think that is a bad design

I got about 500 Users right now, making about 30,000 calls a day

IF they open the flood gates and roll it out to lets say 50,000 users, that will be 3 million calls a day for that 1 sproc

THAT'S JUST ONE FREAKING SPROC

How would you architect that? Can the application server poll to see who has messages and update the session for those that ONLY have a message?

It's like they built this backward

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-01 : 15:57:36

3 million isn't so many that I'd worry about it unless it does a lot of reads. So the proc is something like this right?

SELECT	messageId
FROM	messageQueue
WHERE	userId = @userId

probably does less than 10 reads. This is how outlook talks to exchange, and how lots of other messaging applications work. You can use a message queueing system, but it just shifts the load, doesn't reduce it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-01 : 16:07:36

I don't see any issues with this approach. We have stored procedures being called millions of times a day without issue.

Maybe we need some more information as to why you think this is a bad design? As long as it's efficient, low reads and low cpu, I don't see an issue with this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 22:05:35

OK....

Does the Application Server know about all of the sessions that are open?

My sprocs that I log are like 14/1000 of a second

Still...can the application, "Send" Information to the sessions?

Like, it calls the database, finds out who has a message, then updates the session?

Instead of EVERY Session making a db call every 3 minutes?

THAT would be A LOT Cheaper

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 22:34:04

quote:
Originally posted by tkizer

I don't see any issues with this approach. We have stored procedures being called millions of times a day without issue.

Maybe we need some more information as to why you think this is a bad design? As long as it's efficient, low reads and low cpu, I don't see an issue with this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

OK...so is this..but this is ONE freaken sproc...why not multiple that by 20 or 200 they way that these ignorant developers call stuff

THEN WHAT?

I AIN'T AMAZON

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-01 : 23:04:58

Yes the app server should know about the open sessions. If it doesn't, then there's a bug. I've seen some developers send "SELECT 1" to determine if one is still open, but I've never understood why that's done but it does work.

We would need more information about these "messages" in order to help.

The length of the stored procedure doesn't matter. Reads/CPU does matter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-02 : 01:37:35

Good Point....

However..CPU/Read must be tiny for a sproc takin 14/1000 of a second...

No?

I will check, anyway.."a bowl of oatmeal stared me down and won..."

"And if you see me tonight, with an illegal smile..."

http://www.youtube.com/watch?v=5eccz7D0QK0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-02 : 07:19:17

quote:
Originally posted by X002548

Good Point....

However..CPU/Read must be tiny for a sproc takin 14/1000 of a second...

No?

I will check, anyway.."a bowl of oatmeal stared me down and won..."

"And if you see me tonight, with an illegal smile..."

http://www.youtube.com/watch?v=5eccz7D0QK0

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Reads/Writes are everything when it comes to performance.

I have that song on my iPod.

TimSman
Posting Yak Master

127 Posts

Posted - 2012-03-07 : 15:22:59

What kind of application is this?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 15:34:30

It's part of IBM Connections. They Poll a database that is setup to store messages sent to users. The session looks to see if the user that's logged in has an messages in the User_Message Table..every 3 minutes.

I would have though that they could have done server side processing to check for all messages and update ONLY the sessions that have messages. As it stands, every users session calls the Database Sproc. so it could be 60,000 calss every 3 minutes.

As compared to a server side call to the database, once every 3 minutes, and then update the sessions.

I wonder if there is anyway from the database side to "talk" out to the sessions..not my gig..I have no idea

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 16:13:47

OK...I got my answer...

Since it is not persistent, and it can't be done

I was told that Amazon for example IS persistent and that push technology would have worked

so what will 2000 sproc calls a second do?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources