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
 New to SQL Server Programming
 Should I use a data warehouse or way quicker

Author  Topic 

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-30 : 08:59:24
HI I have to develope an dashboard app for nurse to see who did not get their medicine yet today. there are many tables involved overall in this app. I was told that using a standard stored procedure is not good enough because time needed to read the entire tables etc.
At first I thought of making a dataset but now was learnt that these are not reccomended on web insert and updates.
So what are my options? to my limited understanding a datawarehouse might be a good solution. what is needed is a data subset if you will that is constantly updated.
consider the issue, if the nurse sees someone didn't get the medicine today but they actually did a few seconds prior.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-30 : 09:18:23
Surely it *needs* to be OLTP.

The consequence of getting double, or triple medicine is probably a lot worse than a query being a little slow.

Why would this be slow. With good indexing I can't imagine a (fairly simple) query accessing those tables would be a bad performer here.

On the other hand, health systems are legendary for being horribly designed pieces of ****

More information please!


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-30 : 09:20:02
Tables involved -- their structure and datasise. Feeds. Indexes. Whether you have any opportunity to change the schema at all....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

gwilson67
Starting Member

42 Posts

Posted - 2011-08-30 : 09:56:05
I would try to create the solution using TSQL first and then if the performance wasn't that good, then I would look into creating fact tables/dimension/cubes.

Greg
http://www.freewebstore.org/tsqlcoderepository
Powerful tool for SQL Server development
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-30 : 14:58:28
I don't know why it would be slow
this is what the boss has stated several times.

so i need to benchmark I would say.

quote:
Originally posted by Transact Charlie

Surely it *needs* to be OLTP.

The consequence of getting double, or triple medicine is probably a lot worse than a query being a little slow.

Why would this be slow. With good indexing I can't imagine a (fairly simple) query accessing those tables would be a bad performer here.

On the other hand, health systems are legendary for being horribly designed pieces of ****

More information please!


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION


Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 15:02:41
Adam, we can't possibly help you with the limited information you've provided. A data warehouse is definitely out of the question for this.

Benchmark? Benchmark what? How about you provide the query and we'll help you get it tuned as well as what indexes you need?

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

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 15:04:34
quote:
Originally posted by gwilson67

I would try to create the solution using TSQL first and then if the performance wasn't that good, then I would look into creating fact tables/dimension/cubes.



Why would the solution even require a data warehouse? The data has to be real time.

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

Subscribe to my blog
Go to Top of Page

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-31 : 10:08:09
OK because I am having to find the absolute fastest method.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-31 : 10:12:28
I don't want to be in the hospital where your software runs then......

This is the priority for your situation
============================================================
1) ACCURACY. Medicine needs to administered to the CORRECT dosage.
2) ACCURACY. Medicine data needs to be up to date. This means locks must be held while administering Medicine.
3) ACCURACY.
4) ACCURACY.
..
...
....
.....
X) Speed. It would be ace to be able to see quickly who still needs to get their medicine.

A data warehouse doesn't help you. You are in a transactional environment. It's critically important that you hold locks while administering medicine.....


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-31 : 13:07:13
quote:
Originally posted by AdamWest

OK because I am having to find the absolute fastest method.



Then you need to design it right. A data warehouse is absolutely not the right solution here. You should do some studying on what a data warehouse actually is.

What's important here is the table design, query design, and indexes.

And who told you a standard stored procedure is not good enough? They are flat out wrong and apparently have very little knowledge.

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

Subscribe to my blog
Go to Top of Page
   

- Advertisement -