|
Create a view so no other poor bugger has to look at this code again.
I would probably break it into a number of different procs servicing different parts of the profile. Supporting this query would be nasty.
What happens when the dietician want the name and diet details, does he get all of the face sheet.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
In the case of the dietician needs info we would generally do that on a different report with only the information needed by them.
I do think that breaking the SQL out to bring in only parts of the profile is a good idea then we could more easily reuse them.
However a concern of mine in doing that would be that if we have to tie all of these back together would that not be a pretty big performance hit?
I would think you would have to do that with multiple statements which is in turn more network traffic.
We try to do as much as we can with minimal hits to our DB because we have some users that are on very slow connections.
Thanks for the ideas and look forward to more.
Humble Programmer
|
|
|
|
|
I do have another question.
As you can see we try to do as much logic as possible in the SQL so that we do not have do manipulate the dataset once it is returned....
Because from my understanding the server will be able to optimize much more efficiently than you will most likely be able to do in your program.
For example rather than retrieve first and last name then concat as needed for the result just write it into the SQL so that your result set will already be correct.
Humble Programmer
|
|
|
|
|
programmervb.netc++ wrote: first and last name then concat as needed for the result just write it into the SQL
That is definitely reasonable, however you need to make a judgment call on how much formatting you do in the query. EG you may note the guy who wants to add CRLF to the data in the query, this is totally invalid whereas concat of the names is good.
It also depends on the volume of data you are delivering, if you are doing this profile for 1 patient then this query is almost valid (I would still split it out for support reasons) doing this for 1000s is not valid.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why would the number of clients really matter if your indexes are correct?
I understand that the record set has an affect on total time but I don't see how that really relates to a CONCAT for example.
If you are going to have to CONCAT last and first name on 1000 clients what benefit would you have from doing it locally instead of the server?
Also in either case the concatenation I think the concatenation would be happening in memory so in theory the server should have more...
and I would think that the CONCAT statement written in my case mySQL is probably more efficient than the equivalent code in .NET.
Thanks for your thoughts I do appreciate your input just want to have a full understanding of your reasoning.
Humble Programmer
|
|
|
|
|
programmervb.netc++ wrote: Ideas of how to improve this query
Shoot the person who wrote it!
Just say 'NO' to evaluated arguments for diadic functions! Ash
|
|
|
|
|
Very constructive thanks.
Humble Programmer
|
|
|
|
|
Hi All,
We are trying to run the following bdbackup command, on the command line from one of our virtual machines:
dbbackup -y -c "uid=xxx;pwd=xxx;dsn=DSN NAME" C:\xxx\Applications\xxx\Backup
only we are recieving the following error message:
"Parse error: DSN 'DSN NAME' does not exist"
The DSN is setup as a System DSN, which is what we are wanting the dbbackup to look at. The backup does work however when we set the DSN up as a User DSN.
Is there a way we can get the DBbackup to look at the System DSN instead of the User DSN?
Thank you,
Mel
|
|
|
|
|
i m trying to upload my sql database . but can't access the remote server.
naveen
modified on Friday, September 24, 2010 7:38 AM
|
|
|
|
|
Psssst....
Wrong forum!!
Quick, otherwise they'll set the dogs on you!!
"Benjamin is nobody's friend. If Benjamin were an ice cream flavor, he'd be pralines and dick." ~ Garth Algar
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." ~ Paul Neal "Red" Adair
|
|
|
|
|
Too late.
Here, JSOP... here, JSOP... good boy.
Sic 'em.
L u n a t i c F r i n g e
|
|
|
|
|
I haven't seen it. Ask Bob.
Panic, Chaos, Destruction.
My work here is done.
or "Drink. Get drunk. Fall over." - P O'H
|
|
|
|
|
naveensharma from Chandigarh wrote: can't access the remote server.
I hate it when that happens -- or worse, if the remote server hands you the DVD remote, when you wanted the one for the TV.
Peel me a grape!
|
|
|
|
|
Try harder or you'll lose your job, alternatively hand the job over to a trained professional.
Two heads are better than one.
|
|
|
|
|
Norm .net wrote: hand the job over to a trained professional
They're training vilage idiots now?
[edit]
Someoen gave you a onesee for that. Have a 5 instead.
Panic, Chaos, Destruction.
My work here is done.
or "Drink. Get drunk. Fall over." - P O'H
modified on Friday, September 24, 2010 7:24 AM
|
|
|
|
|
naveensharma from Chandigarh
You forgot to add your full address and phone number to your moniker. Optionally, you could add your bio. Makes the forums look really pretty.
|
|
|
|
|
I dont know.
The funniest thing about this particular signature is that by the time you realise it doesn't say anything it's too late to stop reading it.
My latest tip/trick
Visit the Hindi forum here.
|
|
|
|
|
Maybe there's a retard inhibitor in place...
EDIT -------
It's only fair to mention that my reply was added while this thread existed in the Lounge.
.45 ACP - because shooting twice is just silly ----- "Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997 ----- "The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
modified on Friday, September 24, 2010 7:56 AM
|
|
|
|
|
You need to talk to your IT department and straighten out your security.
Also what do you mean by "upload" -- restore, attach?
|
|
|
|
|
Ah welcome to the correct forum. Your question however is still very obscure.
What do you mean by upload? Are you trying to deploy the database to s server, restore a backup, connect to an existing database what!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a column called bookedhalls, the value will be stored as Balaji-A,Balaji-C, i written select query as follows
Select * from function where bookedhalls like '%Balaji-A,Balaji-C,%
by using this i will get the row.
Suppose if i give '%Balaji-C,Balaji-A,%' i cannot get the row. How to get that row by using
Balaji-C,Balaji-A. Anybody knows please reply.
modified on Friday, September 24, 2010 6:17 AM
|
|
|
|
|
Your data model is fundamentally wrong, you should almost never be storing multiple values in one column with some delimiter (such as a comma). This is especially true if you need to query based on one of those multiple values.
bookedhalls should be a separate table, referenced to the rest of that row using foreign key relationships, this would allow you to select based upon this join.
|
|
|
|
|
Actually i created table caolled function in that fun_start_date and fun_end_date and booked_halls collms is there. Suppose u conside one date it is booked for 2 floors (balaji-A, Balaji-B). User will select in forms.
so i can write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-A,BALAJI-B,%'
i will get the row. If there is no row means he can go for inserting. Here is Ok.
Suppose user if select like first Balaji_B then Balaji-A in coding i will get string as a BALAJI-B,BALAJI-A.
if i write like select * from function where fun_start_date = 'date' and fun_end_date = 'date' and booked_halls like '%BALAJI-B,BALAJI-A,%' i will not get the row . I should get that row. So nobody should not book for that floor. How to do this.
|
|
|
|
|
Did you actually read my response above? By fixing your data model, it fixes this issue.
|
|
|
|
|
As pointed out already, your table structure is not a good one, that is why you are finding it tricky to write this query. You should read up about database normalization.
If you are really stuck with this data structure and cannot change it then you will need to do something like:
select * from function where booked_halls like '%BALAJI_A%' and booked_halls like '%BALAJI_B%'
But really you should change the data model because what you have at the moment is wrong.
|
|
|
|