Data Access and ObjectDataSource Control

Re: Performing Lots of Counts on Rows in a Database

TATWORTH — Sat, 04 Jul 2009 17:25:25 GMT

>Brilliant, thank you very much. That's really useful.

You can find the source code at http://commondata.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=27454

Re: Performing Lots of Counts on Rows in a Database

TATWORTH — Sat, 04 Jul 2009 16:39:58 GMT

Here is the calling code:

    #region " Select "
    /// <summary>
    /// Select record on ChangeLog
    /// </summary>
    /// <param name="id">Id of record to fetch</param>
    /// <returns>ChangeLogSelect containing data for selected row</returns>
    public static ChangeLogSelect Select(int id)
    {
      var changeLogSelect = new ChangeLogSelect();
      var connect = CommonData.ConnectionString;
      var sqlConnection = new SqlConnection(connect);
      var sqlCommand = new SqlCommand("usp_ChangeLogSelect", sqlConnection);
      try
      {
        sqlCommand.CommandType = CommandType.StoredProcedure;
        sqlCommand.Parameters.Add("@Id", SqlDbType.Int);
        sqlCommand.Parameters["@Id"].Value = id;
        sqlCommand.Parameters.Add("@ChangeFilename", SqlDbType.VarChar, 128);
        sqlCommand.Parameters["@ChangeFilename"].Direction = ParameterDirection.Output;
        sqlCommand.Parameters.Add("@DateAdded", SqlDbType.DateTime);
        sqlCommand.Parameters["@DateAdded"].Direction = ParameterDirection.Output;
        sqlCommand.Parameters.Add("@Purpose", SqlDbType.VarChar, 128);
        sqlCommand.Parameters["@Purpose"].Direction = ParameterDirection.Output;
        sqlCommand.Parameters.Add("@Rerunnable", SqlDbType.Bit);
        sqlCommand.Parameters["@Rerunnable"].Direction = ParameterDirection.Output;
        sqlCommand.Connection.Open();
        sqlCommand.ExecuteNonQuery();
        changeLogSelect.Id = CommonData.NullToInteger(sqlCommand.Parameters["@Id"].Value);
        changeLogSelect.ChangeFilename = CommonData.NullToString(sqlCommand.Parameters["@ChangeFilename"].Value);
        changeLogSelect.DateAdded = CommonData.NullToDateString(sqlCommand.Parameters["@DateAdded"].Value);
        changeLogSelect.Purpose = CommonData.NullToString(sqlCommand.Parameters["@Purpose"].Value);
        changeLogSelect.Rerunnable = CommonData.NullToBoolean(sqlCommand.Parameters["@Rerunnable"].Value);
      }
      catch (Exception ex)
      {
        const string Message = "Failure calling usp_ChangeLogSelect";
        CommonData.WriteEventError(ex, Message);
        throw new DalGeneralException(Message, ex);
      }
      finally
      {
        if (sqlCommand.Connection != null)
        {
          sqlCommand.Connection.Close();
        }
        sqlCommand.Dispose();
        sqlConnection.Dispose();
      }
      return changeLogSelect;
    }
    #endregion

Re: Performing Lots of Counts on Rows in a Database

djs25uk — Sat, 04 Jul 2009 16:39:06 GMT

Brilliant, thank you very much. That's really useful.

Daniel

Re: Performing Lots of Counts on Rows in a Database

TATWORTH — Sat, 04 Jul 2009 16:38:50 GMT

Here is an example that returns mutiple values:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_ChangeLogSelect]
(
@Id               INT,
@ChangeFilename   VARCHAR (128) OUTPUT,
@DateAdded        DATETIME      OUTPUT,
@Purpose          VARCHAR (128) OUTPUT,
@Rerunnable       BIT           OUTPUT
) AS
-- Purpose:
--    Select record on ChangeLog table
-- Parameters:
--    Id                -
-- History:
-- 29Jun2009 Administrator Original coding
SET NOCOUNT ON
SELECT
@ChangeFilename     = ChangeLog.ChangeFilename,
@DateAdded          = ChangeLog.DateAdded,
@Purpose            = ChangeLog.Purpose,
@Rerunnable         = ChangeLog.Rerunnable
FROM ChangeLog WHERE ChangeLog.Id = @Id
RETURN
-------------- this is the end ----------------

Re: Performing Lots of Counts on Rows in a Database

TATWORTH — Sat, 04 Jul 2009 16:36:50 GMT

Here is the code that calls it.

#region " Count   "
    /// <summary>
    /// Count records on ChangeLog table
    /// </summary>
    /// <remarks>
    /// This code was autogenerated on 29Jun2009
    /// </remarks>
    /// <returns>Count of records</returns>
    public static int Count()
    {
      int count; // set to 0 by runtime
      var connect = CommonData.ConnectionString;
      var sqlConnection = new SqlConnection(connect);
      var sqlCommand = new SqlCommand("usp_ChangeLogCount", sqlConnection);
      try
      {
        sqlCommand.CommandType = CommandType.StoredProcedure;
        sqlCommand.Parameters.Add("@Count", SqlDbType.Int);
        sqlCommand.Parameters["@Count"].Direction = ParameterDirection.Output;
        sqlCommand.Connection.Open();
        sqlCommand.ExecuteNonQuery();
        sqlCommand.Connection.Close();
        count = CommonData.NullToInteger(sqlCommand.Parameters["@Count"].Value);
      }
      catch (Exception ex)
      {
        const string Message = "Failure calling usp_ChangeLogCount";
        CommonData.WriteEventError(ex, Message);
        throw new DalGeneralException(Message, ex);
      }
      finally
      {
        if (sqlCommand.Connection != null)
        {
          sqlCommand.Connection.Close();
        }
        sqlCommand.Dispose();
        sqlConnection.Dispose();
      }
      return count;
    }
    #endregion

Re: Performing Lots of Counts on Rows in a Database

TATWORTH — Sat, 04 Jul 2009 16:35:28 GMT

Here is a stored procedure that returns a count of records in a table

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_ChangeLogCount]
(
@Count         INT OUTPUT
) AS
-- Purpose:
--     Count records on ChangeLog table
-- Parameters:
--    Count          - Count of records
-- History:
-- 29Jun2009 Administrator Original coding
SET NOCOUNT ON
SELECT @COUNT = COUNT(*) FROM ChangeLog
RETURN
-------------- this is the end ----------------

Re: Performing Lots of Counts on Rows in a Database

malcolms — Fri, 03 Jul 2009 21:51:23 GMT

You can return multiple counts by doing this:

select count(column1) as column1, count(column2) as column2 from your_table

Re: Performing Lots of Counts on Rows in a Database

djs25uk — Fri, 03 Jul 2009 18:48:58 GMT

Please could you help me with one final question on this? How do I return multiple values from a stored procedure? For example, I'd like to perform the 4 different counts (above) in one stored procedure and return all those values to my ASP .net page for use. I've done a lot of googling around this and there are loads of similar posts but no clear answers.

Any help would be really appreciated.

Thanks

Daniel

Re: Performing Lots of Counts on Rows in a Database

malcolms — Fri, 19 Jun 2009 12:37:33 GMT

Niikola I agree with you totally. Improving performance is easier, in my opinion, when you need to work with stored procedures because you have access to execution plans inside of SQL Server.

Re: Performing Lots of Counts on Rows in a Database

Niikola — Fri, 19 Jun 2009 08:58:37 GMT

malcolms:
The performance difference between LINQ to SQL and stored procedures these days is minimal.

Agree in 90% of cases when performance is not issue at all, but try to tune LINQ when performance is primary goal

Re: Performing Lots of Counts on Rows in a Database

malcolms — Fri, 19 Jun 2009 04:42:15 GMT

The performance difference between LINQ to SQL and stored procedures these days is minimal. But I'm an old stored procedure guy and I like to use them for two reasons:

For security. Using stored procedures means you have only given users access to execute stored procs and not select permissions on the tables
Execution plans are a great way to fine tune stored procs

But if you use stored procedures you can use the Count() method to return the number of rows from a table, like this:

select count(*) from your_table

Re: Performing Lots of Counts on Rows in a Database

djs25uk — Wed, 17 Jun 2009 17:52:40 GMT

I didn't realise SQL is more efficient than Linq - why is this so? I'll explore the stored procedure idea - sounds good.

Thanks for your help

Daniel

Re: Performing Lots of Counts on Rows in a Database

Niikola — Wed, 17 Jun 2009 15:02:01 GMT

You can use SQL query or stored procedure which would be much easier to make more efficient

Performing Lots of Counts on Rows in a Database

djs25uk — Tue, 16 Jun 2009 19:09:52 GMT

Dear All

I'm looking to create an ASP .net page which perform lots of counts on records in my database and display them as a table to the end-user. At the moment, I'm using four LINQ queries to perform the counts - these are triggered when each row in my GridView is bound (OnRowDataBound event). So you can probably imagine, I'm cycling through all the records in my database four times for each row in my table. It is likely that this ASP .net page will eventually contain 3-4 tables, all populated in this similar way.

What I'm doing seems totally inefficient and I imagine isn't doing my SQL database a lot of good. What is the 'best practice' approach when you need to perform a lot of counts on rows in a database based on different criteria? How is it best to do it? Although I'm using a GridView at the moment to display the data, it doesn't have to be -- I'm happy to use something else if you think it is better...

        Dim TotalObservationsThisYear = From Observation In db.Observations _
                                      Where Observation.observationDate >= ReportDateFrom _
                                      And Observation.observationDate <= ReportDateTo _
                                      And Observation.observationPublished IsNot Nothing _
                                      And Observation.observationSubjectLevel = observationSubjectLevel.Text _
                                      Select Observation.observationID
        Return TotalObservationsThisYear.Count

        Dim TotalObservationsEver = From Observation In db.Observations _
                                      Where Observation.observationPublished IsNot Nothing _
                                      And Observation.observationSubjectLevel = observationSubjectLevel.Text _
                                      Select Observation.observationID
        Return TotalObservationsEver.Count

        Dim CountObservationsThisYear = From Observation In db.Observations _
                                      Where Observation.observationDate >= ReportDateFrom _
                                      And Observation.observationDate <= ReportDateTo _
                                      And Observation.observationOverallGrade = observationOverallGrade _
                                      And Observation.observationPublished IsNot Nothing _
                                      And Observation.observationSubjectLevel = observationSubjectLevel.Text _
                                      Select Observation.observationID
        Return CountObservationsThisYear.Count

        Dim CountObservationsEver = From Observation In db.Observations _
                                      Where Observation.observationPublished IsNot Nothing _
                                      And Observation.observationOverallGrade = observationOverallGrade _
                                      And Observation.observationSubjectLevel = observationSubjectLevel.Text _
                                      Select Observation.observationID
        Return CountObservationsEver.Count

I'd really appreciate your help/advice on this.

Many thanks

Daniel