C# and SQL General Strategy Question
-
Greetings: I'm learning how to access SQL databases under C#. SQL is a little new to me but I'm really kind of enjoying it. I have a general sort of question about strategies and accepted approaches though: Let's say that I have to search a single table for records that match a certain criteria. What would be the best approach and why: 1. Implement an SqlCommand and use a SELECT statement to get SQL to do all the work and search for the records. 2. Load the table into either a DataReader or DataAdaptor and search the table myself(programmatically). What are the key considerations? Speed? Memory usage? Which approach is faster? Safer? Is there a universally prefered approach or is it case by case? I'm not seeing anything in the literature that I have that address these questions. Thanks in advance to anyone that responds, Mark
-
Greetings: I'm learning how to access SQL databases under C#. SQL is a little new to me but I'm really kind of enjoying it. I have a general sort of question about strategies and accepted approaches though: Let's say that I have to search a single table for records that match a certain criteria. What would be the best approach and why: 1. Implement an SqlCommand and use a SELECT statement to get SQL to do all the work and search for the records. 2. Load the table into either a DataReader or DataAdaptor and search the table myself(programmatically). What are the key considerations? Speed? Memory usage? Which approach is faster? Safer? Is there a universally prefered approach or is it case by case? I'm not seeing anything in the literature that I have that address these questions. Thanks in advance to anyone that responds, Mark
Using stored procedures is the preferred and recommended way access your data. If the tables have been properly constructed and have good indexes then in general it would be more advantageous to run the query in SQL Server and return the results. I say in general because there are other factors; is it a very complex search, size the dataset involved, etc.
only two letters away from being an asset
-
Using stored procedures is the preferred and recommended way access your data. If the tables have been properly constructed and have good indexes then in general it would be more advantageous to run the query in SQL Server and return the results. I say in general because there are other factors; is it a very complex search, size the dataset involved, etc.
only two letters away from being an asset
Hi Mark: I haven't got to stored procedures yet. I'm not sure what they are - just a script of consecutive SQL statements? By "properly constructing" my tables, are you refering to the various levels of normalization? I think I have a pretty good understanding of that. I'm a little sketchy on indexing though. Thanks for your input. Mark
-
Hi Mark: I haven't got to stored procedures yet. I'm not sure what they are - just a script of consecutive SQL statements? By "properly constructing" my tables, are you refering to the various levels of normalization? I think I have a pretty good understanding of that. I'm a little sketchy on indexing though. Thanks for your input. Mark
I would recommend some reading: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design[^]
only two letters away from being an asset
-
I would recommend some reading: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design[^]
only two letters away from being an asset
-
Hi Mark: I haven't got to stored procedures yet. I'm not sure what they are - just a script of consecutive SQL statements? By "properly constructing" my tables, are you refering to the various levels of normalization? I think I have a pretty good understanding of that. I'm a little sketchy on indexing though. Thanks for your input. Mark
If you're using SQL2005Express and you're completely new to SQL and SQL Server then I would recommend this book[^]for your learning pleasure.
"We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare. Now, thanks to the internet, we know this is not true." -- Professor Robert Silensky
-
Greetings: I'm learning how to access SQL databases under C#. SQL is a little new to me but I'm really kind of enjoying it. I have a general sort of question about strategies and accepted approaches though: Let's say that I have to search a single table for records that match a certain criteria. What would be the best approach and why: 1. Implement an SqlCommand and use a SELECT statement to get SQL to do all the work and search for the records. 2. Load the table into either a DataReader or DataAdaptor and search the table myself(programmatically). What are the key considerations? Speed? Memory usage? Which approach is faster? Safer? Is there a universally prefered approach or is it case by case? I'm not seeing anything in the literature that I have that address these questions. Thanks in advance to anyone that responds, Mark
You almost always want SQL Server to do the work. If you index the table appropriately and supply a suitable query, SQL Server can find a given row by touching only a small number of pages making up the index (loading them from the disk), and one page of the main table, if the columns you're requesting aren't part of the index. It only returns one row across the wire, which will reduce the time spent getting the network to transfer the row. If you make SQL Server give you all the rows, it will touch - and briefly lock - all the rows in the table, requiring all pages of the table to be read from the disk (slow), and the network time will be much higher. It'll take much longer to get your data. Then, you'll be using more memory on the client side to store the results while you search for the row you're after.
Stability. What an interesting concept. -- Chris Maunder
-
You almost always want SQL Server to do the work. If you index the table appropriately and supply a suitable query, SQL Server can find a given row by touching only a small number of pages making up the index (loading them from the disk), and one page of the main table, if the columns you're requesting aren't part of the index. It only returns one row across the wire, which will reduce the time spent getting the network to transfer the row. If you make SQL Server give you all the rows, it will touch - and briefly lock - all the rows in the table, requiring all pages of the table to be read from the disk (slow), and the network time will be much higher. It'll take much longer to get your data. Then, you'll be using more memory on the client side to store the results while you search for the row you're after.
Stability. What an interesting concept. -- Chris Maunder