SQL Select data flow
-
I have some questions regarding SQL select command, but first let me explain my condition first. I am a newbie in C# and SQL, and developing some window application with database connected from SQL Server. And the questions I have is when the select command only want to retreive some portions of data (one or two rows from hundreds of row from the same table). I know there are two type or methods of having the select command: stored procedure and non-stored procedure. For the stored procedure, the query is processed in the SQL Server, and return the result to the workstation. But for non-stored procedure select command, is it doing the same thing? or the query return all row of data to the workstation, and then the workstation process it after receive all the data? Since I have concern that my network will be overloaded if all the data are being transfered only to take one or some rows of data. Thank you very much in advance.
-
I have some questions regarding SQL select command, but first let me explain my condition first. I am a newbie in C# and SQL, and developing some window application with database connected from SQL Server. And the questions I have is when the select command only want to retreive some portions of data (one or two rows from hundreds of row from the same table). I know there are two type or methods of having the select command: stored procedure and non-stored procedure. For the stored procedure, the query is processed in the SQL Server, and return the result to the workstation. But for non-stored procedure select command, is it doing the same thing? or the query return all row of data to the workstation, and then the workstation process it after receive all the data? Since I have concern that my network will be overloaded if all the data are being transfered only to take one or some rows of data. Thank you very much in advance.
With an ad-hoc query (a query passed to the server, instead of using a stored proc) the SQL Server will still honor the where clause and only return the rows you want to the application. There are a couple of advantages to stored procs over ad-hoc queries: - ad-hoc queries have to be compiled every time you perform the query. Stored procedures are compiled, then the compiled version is re-used for some time, and re-evaluated/re-compiled only occasionally. This is a major benefit in high-volume systems. - depending on the app, sometimes it's possible to tweak performance and/or operation of an application with modifications to the stored procedure, without having to recompile the entire application to implement the change. - depending on the complexity of the query, sometimes it's simply easier to read the code when the SQL code is completely isolated from the C#/VB code, instead of the two being mixed together in the application. There are more benefits to procs, of course. And there are some benefits that ad-hoc queries have over procedures (although IMHO few and far between.) But I much prefer using procs. Mainly for the reasons listed above.
Grim
(aka Toby)
MCDBA, MCSD, MCP+SB
SELECT * FROM user WHERE clue IS NOT NULL GO
(0 row(s) affected)
-
I have some questions regarding SQL select command, but first let me explain my condition first. I am a newbie in C# and SQL, and developing some window application with database connected from SQL Server. And the questions I have is when the select command only want to retreive some portions of data (one or two rows from hundreds of row from the same table). I know there are two type or methods of having the select command: stored procedure and non-stored procedure. For the stored procedure, the query is processed in the SQL Server, and return the result to the workstation. But for non-stored procedure select command, is it doing the same thing? or the query return all row of data to the workstation, and then the workstation process it after receive all the data? Since I have concern that my network will be overloaded if all the data are being transfered only to take one or some rows of data. Thank you very much in advance.
ADO.NET (your
SqlDataAdapter, etc) is a disconnected system. Meaning, all the data is held and manipulate on the client. (Someone correct me if I am wrong here...) So, no matter how you structure your query, all the data goes to the client when you call theFillmethod on yourSqlDataAdapter. If you are concerned about swamping your connection, I suggest you write a more restrictive query, so that it only returns the rows you actually need. If you cannot tighten up the logic, you can try something like:select top 2 column1,column2, etc from YourTableHope this helps, Bill -
ADO.NET (your
SqlDataAdapter, etc) is a disconnected system. Meaning, all the data is held and manipulate on the client. (Someone correct me if I am wrong here...) So, no matter how you structure your query, all the data goes to the client when you call theFillmethod on yourSqlDataAdapter. If you are concerned about swamping your connection, I suggest you write a more restrictive query, so that it only returns the rows you actually need. If you cannot tighten up the logic, you can try something like:select top 2 column1,column2, etc from YourTableHope this helps, BillThank you very much for the reply. I guess from the reply, I understand it much more than before, and I think I am in the right path. In most of my query, I used ADO.NET, but restricted the select command to the columns and rows that I am interested in by using WHERE clause. My concern was, the network load of the select command. I was afraid that the ADO.NET select command retreive all the data and rows even I only selected one column or row. And then the client applied filter to the retreived data to get the data I wanted. But I guess I'm wrong. Again, thank you so much for the reply. It gives me very much information and knowledge about SQL.
-
Thank you very much for the reply. I guess from the reply, I understand it much more than before, and I think I am in the right path. In most of my query, I used ADO.NET, but restricted the select command to the columns and rows that I am interested in by using WHERE clause. My concern was, the network load of the select command. I was afraid that the ADO.NET select command retreive all the data and rows even I only selected one column or row. And then the client applied filter to the retreived data to get the data I wanted. But I guess I'm wrong. Again, thank you so much for the reply. It gives me very much information and knowledge about SQL.
-
SqlServer does the "selecting" so only the results of the query travel from the server to your client application. Bill
Yes, I understand it now. Thank you very much for the reply. :)
-
Thank you very much for the reply. I guess from the reply, I understand it much more than before, and I think I am in the right path. In most of my query, I used ADO.NET, but restricted the select command to the columns and rows that I am interested in by using WHERE clause. My concern was, the network load of the select command. I was afraid that the ADO.NET select command retreive all the data and rows even I only selected one column or row. And then the client applied filter to the retreived data to get the data I wanted. But I guess I'm wrong. Again, thank you so much for the reply. It gives me very much information and knowledge about SQL.
Antonius_r3 wrote: My concern was, the network load of the select command. I was afraid that the ADO.NET select command retreive all the data and rows even I only selected one column or row. And then the client applied filter to the retreived data to get the data I wanted. But I guess I'm wrong. Just as a clarification: It is SQL Server that is lowering your network traffic not ADO.Net. If you sent the same query to a remote MSAccess MDB database (via ADO.Net) you would experience a much heavier network hit since the filtering would happen on the client.