.NET C# Question about efficiently performing multiple SQL queries

[BigDH01]

really? after i spent a day stumbling through linq to sql

so would i use queries to populate a DataTable?

You were learning LINQ to Entities when you were learning LINQ to SQL. The syntax is basically identical and the visual designer is very similar. The query syntax you were using would be the same.

 

[Markbnj]

I disagree with a lot of posts in this thread that blindly pointed the OP to LINQ to SQL. I don't mean to offend anyone, but have any of you actually stepped through the debugger to look at the SQL that is generated behind the scenes for some of the complex LINQ queries. Have you looked at the metadata-related queries that ADO.NET sends to SQL Server at runtime using the SQL Server Profiler before each DB call is made?

Good luck querying tables with more than a million rows, populating data warehouses, or hosting database-heavy Websites. LINQ to SQL does make things a lot easier, but those things are extremely unoptimized. In general, any decent programmer must learn the basics of optimally querying a database and understand concepts like database indexes. You can put a "where" LINQ query which searches for 10 different things... if the query is not covered by an index, you'll be royally screwed in production.

To the OP: please take the time to learn the basics of ADO.NET: DataTable/DataView, DataSet, disposing connection objects, utilizing "using" blocks, etc. And stay away from SqlDataReader objects . In general, unless you're dealing with Web Services, DataTable is usually the fastest since all the data is "binary." DataSet is OK for multiple tables, but it is extremely verbose because it is XML-based.

Once you have your data in memory, you can do some wonderful things via LINQ... for now, LINQ to SQL has a lot to improve.

I'm not a fan of LINQ to SQL either, although I do like using LINQ for set processing on collections. It definitely makes debugging harder, and I don't really have any desire at all to use it for database queries.

 

[dalearyous]

It definitely makes debugging harder, and I don't really have any desire at all to use it for database queries.

gee thanks. that is pretty much all i am doing

 

[BigDH01]

I disagree with a lot of posts in this thread that blindly pointed the OP to LINQ to SQL. I don't mean to offend anyone, but have any of you actually stepped through the debugger to look at the SQL that is generated behind the scenes for some of the complex LINQ queries. Have you looked at the metadata-related queries that ADO.NET sends to SQL Server at runtime using the SQL Server Profiler before each DB call is made?

Yes, I have. I'm quite aware of the performance penalty (in reads anyway). Bottom line, though, is that while LINQ to SQL or LINQ to Entities (which is what I use) are not the fastest solution, they are fast enough in the majority of cases. Most of us work with websites that get moderate usage. In these cases, there's simply not a compelling reason not to use an ORM imo. The flexibility and maintainability they provide simply outweighs the ~15% hit you take on queries. Developer time is valuable too.

And if it means anything, I used to have a similar attitude. I used to wonder why I would use LINQ when I could just write the queries myself because, dammit, I know T-SQL. Now I'm pretty much the sole developer with a database that has 80 tables and growing. The ease with which I can create and alter queries with LINQ made it very compelling to me to do so. I've been using LINQ to Entities for awhile now and would never go back. Performance-wise, the users can't tell the difference. Frankly, I could only tell the difference if I really tried to find it.

Good luck querying tables with more than a million rows, populating data warehouses, or hosting database-heavy Websites. LINQ to SQL does make things a lot easier, but those things are extremely unoptimized. In general, any decent programmer must learn the basics of optimally querying a database and understand concepts like database indexes. You can put a "where" LINQ query which searches for 10 different things... if the query is not covered by an index, you'll be royally screwed in production.

My site might only get moderate traffic, but I would consider it to be "database-heavy." I haven't noticed any issue with LINQ to Entities. Indexing is something that you'd have to know using ADO.NET or LINQ. And I would imagine that most large organizations that get thousands of queries per second probably have database administrators that handle this sort of thing for the developers. I'm not saying you shouldn't be aware of the concept, but if your site really needs every last ounce of performance, database performance tweaks will probably be abstracted away from you and you'll probably have database experts writing the SQL code and handing you a set of stored procedures to use.

Next week I start a job writing Python using Google's app engine. They have a data store that's pretty different than what I've been using and from my end I will basically be doing functional filtering (.filter("filter"), etc). I know the datastore is fast and reliable. How exactly does it do what it does? I have some vague idea but that's it. And frankly, that's all I care to know. I trust the Google engineers are good at what they do and will make sure my data is safe and delivered to me quickly.

To the OP: please take the time to learn the basics of ADO.NET: DataTable/DataView, DataSet, disposing connection objects, utilizing "using" blocks, etc. And stay away from SqlDataReader objects . In general, unless you're dealing with Web Services, DataTable is usually the fastest since all the data is "binary." DataSet is OK for multiple tables, but it is extremely verbose because it is XML-based.

Once you have your data in memory, you can do some wonderful things via LINQ... for now, LINQ to SQL has a lot to improve.

ORMs have some room for performance improvement but they are here to stay. I think in three years the OP will have been much better served in both experience and professional opportunities if he learns EF as opposed to sticking with ADO.NET. Of course, I'd also encourage him to ditch webforms and move to MVC .

 

[BigDH01]

gee thanks. that is pretty much all i am doing

Do you find that your code is harder to debug now or are you noticing any hit on performance? If not, what's the problem? Would you rather work with the LINQ code you just wrote or the original ADO.NET code you posted?

 

[Oyster]

Telling someone to continue to use DataSet in this day and age is akin to suggesting someone develop using VI.

With so many types at your disposal in .NET, you can use whatever you want. My post was alluding to the notion of learning the basics of how DataSet and DataTable objects work. I do agree with switching to the Entity Framework... LINQ to SQL has been frozen and works only with SQL Server anyway.

really? after i spent a day stumbling through linq to sql

so would i use queries to populate a DataTable?

Those hours you spent on LINQ to SQL will help you immensely in other areas - LINQ to Objects, LINQ to XML, etc. Ultimately, I wasn't suggesting to avoid using LINQ to SQL... I was only pointing to pitfalls of blindly fetching data from your data sources via the techniques laid out in LINQ to SQL. You can still use LINQ on your objects like DataTable or anything that implements IQueryable<>. If you dig deep enough in the IDE-generated code for LINQ to SQL and the Entity Framework, you'll notice the frequent use of IQueryable<> objects.

Use a parameterized stored procedure... in fact, this is when you can use the "drag and drop" method to have .NET create a method stub for you. When you add a data source, Visual Studio will allow you to drag DB stored procedures and functions to your project. I don't like this method for tables and any other database objects because as soon as you change something (let's say a column name or data type) in the database, you'll be forced to recompile the entire project. Stored procedures and functions provide a *decent*, although not robust, decoupling between your programming code and your DB code. You can change your stored procedures and functions in the database on-the-fly, but you still have to stick to the same column selection. This is handy, for example, when you have to optimize your SELECT queries. This can be done using either LINQ to SQL or the Entity Framework.

 

[Oyster]

ORMs have some room for performance improvement but they are here to stay. I think in three years the OP will have been much better served in both experience and professional opportunities if he learns EF as opposed to sticking with ADO.NET. Of course, I'd also encourage him to ditch webforms and move to MVC .

Ummm, have you dug into the IDE-generated code for either LINQ to SQL or the Entity Framework? It *uses* ADO.NET.

Outside of that, I think I agree with the rest of your post. Again, my ultimate point is that if the OP spends the extra time to learn the underpinnings of the Framework, he/she will be a much better programmer.

 

[dalearyous]

ok i think i am going to stick with linq to sql. the page does not get an immense amount of traffic and if i could write this in php i would have most of it done in a few hours ... but it has to be .NET

the pages that are database heavy only show the results of a handful of queries, and the results are small. my biggest struggle is grabbing the data in a way to make it easy to format it on the front end. again, anyone of you could probably have this done in couple hours.

 

[BigDH01]

Ummm, have you dug into the IDE-generated code for either LINQ to SQL or the Entity Framework? It *uses* ADO.NET.

And EF's full name is ADO.NET Entity Framework . I think it's clear that my usage of ADO.NET in this context was referring to creating your transaction, connection, command, dataadapter, and dataset/datatable by hand.

 

[Bulldog13]

Don't listen to Oyster's discounting of Linq to SQL. His criticisms represent a small fraction of the type of querying a most line of business web programmers do. ORM tools are the way to go and Linq to SQL is a nice friendly introduction to that.

Once you get handy with your ORM tool of choice (entity framework here, though I started with Linq to SQL) you'll amaze other programmers who are still writing their SQL queries by hand and mashing them into DataSets. And then parsing them. And then squishing them into POCO objects. In an array. ::shudder::

Get handy with the IQueryable<T>, List<T>, and lambda expressions and your productivity will skyrocket.

 

[LokutusofBorg]

Get handy with the IQueryable<T>, List<T>, and lambda expressions and your productivity will skyrocket.

Yup yup yup.

 

[dalearyous]

these guides are awesome:

http://scottonwriting.net/sowblog/a...tt-guthrie-s-using-linq-to-sql-tutorials.aspx

fyi

 

[dalearyous]

few questions about this segment of code i wrote:

        DataClassesDataContext db = new DataClassesDataContext();
        var budgetQuery = from tb2 in db.ExpenseSummaries
                          where tb2.PROJECT_NUMBER == "134023" && tb2.PA_PERIOD_NAME == "12-01"
                          group tb2 by tb2.TASK_NUMBER into g
                          select new
                          {
                              budgetTotal = g.Sum(x => x.COST_BUDGET),
                          };

        GridView2.DataSource = budgetQuery;
        GridView2.DataBind();

so budgetTotal does not exist outside of the select statement right? is there a way to pass it so i can reuse that value? basically i need budgetTotal again, in another function, to do some basic math.

2nd question, is there anyway to bind this to something other than a GridView? i would prefer to do ExecuteScalar type thing because it only returns one value.

sorry, i am still a little lost and i have a book coming to read.

 

[LokutusofBorg]

If you hover your mouse over your var declaration there it will tell you what type is being returned by your LINQ query. You are either getting a single instance of your anonymous type, or an IEnumerable of them. Either way, you have access to your budgetTotal outside that query.

 

[Oyster]

Don't have an IDE, so haven't checked this for syntax, but regarding your ExecuteScalar() question, you can always select Single():

    var budgetQuery = 
        (
                from tb2 in db.ExpenseSummaries
                where tb2.PROJECT_NUMBER == "134023" && tb2.PA_PERIOD_NAME == "12-01"
                group tb2 by tb2.TASK_NUMBER into g
                select new
                {
                        budgetTotal = g.Sum(x => x.COST_BUDGET),
                }
        ).Single();

If your select returns more than one object, the CLR will throw an exception.

 

[LokutusofBorg]

If you're just looking to return the single int value that's the SUM then you can restructure your LINQ query to do just that. Look at a couple LINQ tutorials and it shouldn't take you more than a few minutes to figure it out.