SQL Queries: A query for each situation, or is one enough?

[zachattack05]

So, the question is, if I have a table on a SQL server with 20 columns, and I need to retrieve just 4 columns for a form, and I am using stored procedures to do most of my queries, should I create a stored procedure to retrieve just the 4 columns, or is it okay to retrieve all 20 and just ignore the 16 I don't need?

I am curious about CPU overhead with this AND network traffic of course if queries end up being many rows.

I ask because I'm worried about maintaining a database with so many stored procedures, that when a situation arises where a table (or whatever) changes, I could potentially have to go back and fix a lot of them, I'm trying to avoid that.

Any thoughts?

[M.Waqas Aslam]

i think you have to use query which get only required columns ,

[gusano79]

Depending on your application, it might not matter too much, but there can be a significant performance penalty for getting all twenty fields instead of the four you need. Not just because of the amount of data sent across the wire, but also if you have multiple instances of your application running, it's that much more load on the database server.

It also keeps your application cleaner for greater maintainability. Next time someone looks at your code--and this could be you, six months from now, when you've forgotten everything =)--they won't have to wonder "why aren't we using these other sixteen fields?"

[zachattack05]

That's a good point gusano79, I'm just worried that I would need to create so many different stored procedures, some of which retrieve data from the same tables for several different reasons, and a procedure for each reason seems overkill.

I could also see it from the other standpoint of going back 6 months later, reviewing the stored procedures and saying, huh...why aren't we using x, y and z columns with this?

I dunno, maintaining an application is a huge job by itself, anything to make it easier, I am all for!

One other question if you don't mind.

Assuming the same scenario above, if I have a table of say contacts or whatever, and I have a listbox on the left of the form and the selected contact information on the right; technically one query, requesting ALL of the data could easily populate both the listbox and the information for each in just one trip to the server, basically a "Give it all to me!" kinda query. But is it best to do that if the contact table is 20 columns and say 6000 rows? Or, is it better to send one request for the listbox to just get names or whatever to display, and each time the user clicks a contact name, send a separate request to the server for just that contact's information?

I can see how the second option has a huge performance advantage, but, I see it being slightly harder to maintain given that there are two procedures here and if for some reason the server is dead for a second or so, with all of the data stored in the dataset, switching contacts is an "offline" operation since it's all there.

I dunno, there are advantages to both that I can see. What is your take on that scenario?

[skatamatic]

Could you not have parameters to these stored procs that specify how much data you actually care about? For example, a boolean flag indicating the whole table on true or just the 4 columns on false. Or for better scalability, a list of the indexes or some sort of identifier for all of the columns you want.

As for your concern with sending huge amounts of data, it would be a lot more efficient on network traffic and memory consumption if you just send a request for a contact after it is clicked on. There will be a bit more lag than if you had all the entries pre-loaded into memory though. But really it shouldn't be overly noticeable. But there will definately be a lot more lag if you have say 1000 clients loading 6000 rows each when all they care about is 1 or 2 contact entries. It's also possible to send larger blocks of entries (say +/- 20 entries surrounding the contact clicked) and retrieve these dynamically in the background as the listbox is scrolled. This is how Hotmail works - when you open Hotmail it automatically fetches a few of your newest emails and caches them because you are very likely to check those ones as opposed to older ones. This sort of hybrid approach would be your best bet at the best of both worlds.

[zachattack05]

skatamatic, honestly...I don't know.

My experience with stored procedures is EXTREMELY limited. Outside of the query designer I've only modified one stored procedure manually, and surprisingly it worked the first time.

When I ask this, I'm not asking for you to volunteer anything, but how difficult would it be to implement something like what you suggest? Given my limited skill-set with these types of queries of course. Is this something relatively simple or complex?

I like your idea, and from what I have read, stored procedures can be very powerful tools, which is why I am trying to use them when appropriate. It's just hard to wield a 15 kilo sword when your 5 years old. I need to read more about these things, do you have any suggestions on good reading material on stored procedures?

[Cameronsmith63]

Why cant you write a command in your program to get the data for these 4 columns? Or is it best practice to keep sql commands in sql itself? I mean, its only 4 columns.

[zachattack05]

Cameronsmith63, the 4 columns was just an example.

I wanted to keep the sql commands in sql mostly for security and because some of the methods the application uses requires inserting, updating, deleting and selecting records across multiple tables. With the stored procedures, sending one piece of data to a stored procedure can propagate it anywhere it needs to without sending multiple queries.

Besides, saving the procedures on the sql server allows upgrades to the application on one workstation and not another. It's easier, in my mind, for backwards compatibility, but I could be wrong.

[skatamatic]

skatamatic, honestly...I don't know.

My experience with stored procedures is EXTREMELY limited. Outside of the query designer I've only modified one stored procedure manually, and surprisingly it worked the first time.

When I ask this, I'm not asking for you to volunteer anything, but how difficult would it be to implement something like what you suggest? Given my limited skill-set with these types of queries of course. Is this something relatively simple or complex?

I like your idea, and from what I have read, stored procedures can be very powerful tools, which is why I am trying to use them when appropriate. It's just hard to wield a 15 kilo sword when your 5 years old. I need to read more about these things, do you have any suggestions on good reading material on stored procedures?

Well it's problems like these that you will likely learn quite a bit out of. Then down the road when a similar situation arises, you can say "Wait a minute, I know what I'm doing!" :P . Give it a go and post any specific problems you have.

[zachattack05]

skatamatic, any suggestions for good reading material on the subject?

[skatamatic]

It depends on what type of database you are using for the stored proc aspect.

This looks like a decent resource for MySQL (which is really all I use).

As for the optimizing, I would put that on hold and just fetch 1 record at a time as the user clicks them. Once that's working we can take it from there :)

[thines01]

The best thing you can do is think about re-use of your code
(and as mentioned consider stored procedures).

You are the one who has to live with your code (for now) and your successor will have many questions.

Write it in a way that serves you now and in the future.

[zachattack05]

It depends on what type of database you are using for the stored proc aspect.

This looks like a decent resource for MySQL (which is really all I use).

As for the optimizing, I would put that on hold and just fetch 1 record at a time as the user clicks them. Once that's working we can take it from there :)

I'll take a look at that.

Thanks for the help! I really appreciate it.