|
While you can join the 2 sets of data (you say the productid is common) by building sub selects and inner joins it will look horrible and will be difficult to support.
Go with the temp table or the table var, I often use a table var for pivot stuff because you are usually using dynamic SQL to build the pivot and therefore building the target table code is trivial. Also pivot data tends to be small in volume!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
More reason not to use stored procedures. Try a table-valued function instead.
|
|
|
|
|
hi to all,
I have an application that requires a lot of calculation, there are a lot of clients that uses that application having separated DB’s. Now I am going to develop another web-application that will get the records of all those databases and on the basis of filter will display the results. I want to ask you a question that
1. Should I change the structure of the application in which all of my clients share a same database and from where I could get all the records I needed and perform calculation on them?
2. Would I keep the all databases separate (as it is condition) and develop a service that will fetch the records from the entire database into one from where I would perform my calculation and display the data.
Do also tell me if there is some other solution to this application architecture.
|
|
|
|
|
What is the rationale for every client to have their own database? Would always be easier to have all of the information in one database but by no means a must - it would depend on the design and needs of the application.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
My vote is for a centralized DB where you store all of the data. This has the advanatage of 1 backup, transactions, no import logic and overall less headaches.
The only advantage of each client having a local DB is that they can operate while disconnected from the network.
Do your clients have a reliable connection to a central DB ?
Good luck.
|
|
|
|
|
Thnx for your reply, but what if the Database grows big coz of the data and the client. Also the backup will also become an issue due to larger size. All the users databases are also managed by us.
|
|
|
|
|
Unless there are very good reasons not to do so, I would go for a central DB. It may take some doing to get there, but it should be worth it. Remember, your app will grow, and maybe the number of clients will increase too, so delaying the decision probably will only make matters worse.
Good reasons not to do so would be special circumstances, such as a relatively slow network. If so, I'd suggest to remedy them.
|
|
|
|
|
adilsardar wrote: Would I keep the all databases separate (as it is condition)
If that's the condition, then yes, keep them separate. Who came up with this restriction, and why?
I can imagine some clients not feeling happy about mingling their valuable data with the data of their competitors. It would also mean that things go over "a line" outside the building, and some customers don't trust SSL.
Do you need live access to their database, or could you suffice with a backup that they send you? Have you explained to them in detail what information you're going to pull from 'their' database?
Do you need much of their data? Often? If so, it might be a better idea to replicate or synchronize the data. If not, could the query run during the night? Could it run at a lower priority when run during the day?
Happy thinking
I are Troll 
|
|
|
|
|
The database contains approx 260 tables and what we need is the data from appox 150 tables.
Is ssis packages are good solution if we use separate db's and then sych them.?
|
|
|
|
|
adilsardar wrote: The database contains approx 260 tables and what we need is the data from appox 150 tables.
That's over 50%. How much data would that be, in megabytes? And how often will you run this query?
adilsardar wrote: Is ssis packages are good solution if we use separate db's and then sych them.?
I don't have personal experience with SSIS, but replication sounds good.
I are Troll
|
|
|
|
|
Probably one database.
As for making calculations, it depends on the calculations. Are they such that you can keep a summary table from which to calculate them?
|
|
|
|
|
thnx for the reply, this involves a lot of calculation from multiple tables.
|
|
|
|
|
Furthermore, you will need a database engine which is capable of those amounts of calculations - go for Microsoft SQL Server or Oracle, postgres or MySQL (even worse) cannot be used for such a purpose.
|
|
|
|
|
I'm trying to edit the CommandText for the Insert Command in a TableAdapter in the Designer in vb.net. Whenever I make changes to the CommandText, Visual Studio (2008) automagically changes the Parameters to a Collection consisting of one Parameter that existed before editing the Command, immersed in a sea of complete garbage Parameters, and then I either have to undo (unless this was the first thing I tried editing, in which case I have to close the Designer to discard changes and reopen it) or manually change ALL the Parameters back to something that isn't complete garbage.
Has anybody seen anything like this, or know how to stop Visual Studio from murdering my carefully tended Parameters? Any way to change the CommandText without finding yet another reason to curse the name of M$ would be greatly appreciated.
|
|
|
|
|
I expect that's what it's supposed to do.
Never use a DataAdapter / TableAdapter -- they are too much trouble for real work.
|
|
|
|
|
That is why i dont use wizards to do my Data Layer
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Nor do I. 
|
|
|
|
|
Parameters are build based on CommandText. So if you are going to change command text, you would need to change the parameters as well.
Abhishek Sur
Don't forget to click "Good Answer" if you like this Solution.
Visit My Website-->www.abhisheksur.com
|
|
|
|
|
Not always, I can have parameters set up according to the columns of a particular table and then wish to set the command as appropriate for a task and not need to alter the Parameters collection. But to do that I need to roll my own -- which I do.
|
|
|
|
|
Yes, I understand this, and I am quite fine with this. The problem is, VS changes the Parameters for me, instead of giving me the option to specify the Parameters myself.
|
|
|
|
|
Trevortni wrote: VS changes the Parameters for me
No it doesn't; the CommandBuilder does.
|
|
|
|
|
There is a reason why you are getting limited help on this subject, very few experienced devs will use the table adapters. They are one of the worst things MS has inflicted on us, they allow a new developer to get data to the UI with almost no knowledge of how it was achieved.
Look into buiding or implementing a data access layer DAL. This will give you much greater knowledge and therefore control over your data.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: worst things MS has inflicted on us, they allow a new developer to get data to the UI with almost no knowledge of how it was achieved.
And this is a bad thing why?
Anyway, the problem is not that I don't know what I'm doing. I know exactly what it should look like, and know that if I can get the screen to look like this, it will work as expected. The internals of the Table Adapter are not the problem; the problem is the Designer changing parameters without my permission.
If you don't know how to convince the UI to not drop data, man up and confess your ignorance rather than using it as an excuse to make yourself sound like an elitist snob. That does nobody any good, and makes you look stupid.
Allow me to demonstrate:
Bad: HAHAHAHA!!!! LOSER!!!! ONLY N000BS USE M$ COMPONENTS!!!! ROLL YOUR OWN N0000B!!!!1111ONEONE
Good: Oooh, sorry, I never use TableAdapters, I've never had anything but problems with them, and in my experience, most people around here feel the same. Have you considered making your own or implementing one from somewhere else? It always seems to work out easier for me in the long run.
You see the difference? They both offer the same advice, but the second one actually comes across as advice rather than an elitist slam.
----
All ranting aside, I do appreciate your advice, and while it doesn't look like implementing some other solution is an immediately viable solution (I'm not the only person in this code, and the senior programmer who originally implemented this would seem to take offense at the idea that he has no idea what he's doing), I will keep it in mind should I come across database needs more complex than can be solved through whatever it takes to get this UI in line (which looks like my only option might be to let VS destroy my Parameters and then manually rewrite them all.... sigh....)
|
|
|
|
|
Trevortni wrote: man up and confess your ignorance rather than using it as an excuse to make yourself sound like an elitist snob.
I thought it was rather obvious that I didn't have the answer, the reason I waited to see if somone else had come up with something more constructive.
Trevortni wrote: senior programmer who originally implemented this
I'm astonished a senior dev would be using table adaptors, I would not allow that on any project I was involved in and would question the devs claim to seniority.
Also this probably would have got a better response in the C#/VB forum as it really is the UI code misbehaving.
Do take a look at some of the articles here as there are some excellent DALs available in them.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: it really is the UI code misbehaving
I doubt that very much. It's the DataAdapter and CommandBuilder (and probably Clippy) trying to be helpful.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
