none
Reducing database calls RRS feed

  • Question

  • Hey all, I have three queries I need to kick off every 5 or so minutes and rather than lazily kick off each one , I want them instead to kick off in a bacth , similar to passing a string to SQL Server embedded with 3 selects that are pulled into a DataSet - 3 tables pulled but just one database call.

    So if I had something like:


    query1 = db.MYTABLE1.Where(c => c.ID > maxID1);
    query2 = db.MYTABLE2.Where(c => c.ID > maxID2);
    query3 = db.MYTABLE2.Where(c => c.ID > maxID3);

    How do I get all my results at one time?

    Do I instead try to mimic my "3 queries in one string loaded into a dataset" scheme?

     

    Wednesday, October 28, 2009 2:50 PM

Answers

  • Hi Bill,

     

    Glad to see you again and I am really happy that you are interested in using LINQ to SQL!  J

     

    In such scenario, I recommend you to build a stored procedure which has three input parameters (maxID1, maxID2, and maxID3) and returns three SELECT results. 

     

    The stored procedure can be:

    ================================================================= 
    CREATE PROCEDURE [dbo].[GetMultipleResults]

          -- Add the parameters for the stored procedure here

          @MaxID1 int,

          @MaxID2 int,

          @MaxID3 int

    AS

    BEGIN

          -- SET NOCOUNT ON added to prevent extra result sets from

          -- interfering with SELECT statements.

          SET NOCOUNT ON;

     

        -- Insert statements for procedure here

          SELECT *

          FROM [MYTABLE1]

          WHERE ID > @MaxID1

         

          SELECT *

          FROM [MYTABLE2]

          WHERE ID > @MaxID2

         

          SELECT *

          FROM [MYTABLE3]

          WHERE ID > @MaxID3

    END
    ================================================================= 

    In LINQ to SQL dmbl designer, we can directly drag the stored procedure into the dbml file.   By default the stored procedure method is under the DataContext class.  We can modify the method definition as: (The MYTABLE1, MYTABLE2 and MYTABLE3 entities should have been dragged into the dbml file)

    ================================================================= 

    [Function(Name="dbo.GetMultipleResults")]

    [ResultType(typeof(MYTABLE1))]

    [ResultType(typeof(MYTABLE2))]

    [ResultType(typeof(MYTABLE3))]

    public IMultipleResults GetMultipleResults([Parameter(Name="MaxID1", DbType="Int")] System.Nullable<int> maxID1, [Parameter(Name="MaxID2", DbType="Int")] System.Nullable<int> maxID2, [Parameter(Name="MaxID3", DbType="Int")] System.Nullable<int> maxID3)

    {

        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), maxID1, maxID2, maxID3);

        return (IMultipleResults)result.ReturnValue;

    }

    ================================================================= 

     

    Then we can call the stored procedure method as:

    ================================================================= 

    var results = db.GetMultipleResults(maxID1, maxID2, maxID3);

    var table1 = results.GetResult<MYTABLE1>();

    var table2 = results.GetResult<MYTABLE2>();

    var table3 = results.GetResult<MYTABLE3>();

    ================================================================= 

     

    For additional information about how to use stored procedure to retrieve data via LINQ to SQL, please see http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx. 

     

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 29, 2009 2:29 AM
    Moderator
  • Hi Bill,

     

    I am sorry, I misunderstood your meaning since I thought you have resolved the problem by ADO.NET 2.0 methods.  By LINQ to SQL, except using stored procedures, here is one trick to embed the three SELECT queries into one LINQ query (one database call). 

    ===========================================================================
                    var MaxIDs = (from m in db.MYTABLEs

                                  select new

                                  {

                                      MaxID1 = db.MYTABLEs.Where(d => d.ID > 0).Max(d => d.ID),

                                      MaxID2 = db.MYTABLE2s.Where(d => d.ID > 0).Max(d => d.ID),

                                      MaxID3 = db.MYTABLE3s.Where(d => d.ID > 0).Max(d => d.ID),

                                  }).First();
    ===========================================================================

     

    However, the generated T-SQL will one more complicated than the original SELECTS since we need to embed the three MAX queries into a LINQ select query and then pick the first one.  Here is the T-SQL generated by LINQ to SQL provider:

    ===========================================================================
    SELECT TOP (1) [t4].[value] AS [MaxID1], [t4].[value2] AS [MaxID2], [t4].[value3] AS [MaxID3]

    FROM (

        SELECT (

            SELECT MAX([t1].[ID])

            FROM [dbo].[MYTABLE] AS [t1]

            WHERE [t1].[ID] > @p0

            ) AS [value], (

            SELECT MAX([t2].[ID])

            FROM [dbo].[MYTABLE2] AS [t2]

            WHERE [t2].[ID] > @p1

            ) AS [value2], (

            SELECT MAX([t3].[ID])

            FROM [dbo].[MYTABLE3] AS [t3]

            WHERE [t3].[ID] > @p2

            ) AS [value3]

        FROM [dbo].[MYTABLE] AS [t0]

        ) AS [t4]

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    ===========================================================================

    I prefer the stored procedure method for such scenario.  J


    Have a nice day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 3, 2009 7:35 AM
    Moderator

All replies

  • Hi Bill,

     

    Glad to see you again and I am really happy that you are interested in using LINQ to SQL!  J

     

    In such scenario, I recommend you to build a stored procedure which has three input parameters (maxID1, maxID2, and maxID3) and returns three SELECT results. 

     

    The stored procedure can be:

    ================================================================= 
    CREATE PROCEDURE [dbo].[GetMultipleResults]

          -- Add the parameters for the stored procedure here

          @MaxID1 int,

          @MaxID2 int,

          @MaxID3 int

    AS

    BEGIN

          -- SET NOCOUNT ON added to prevent extra result sets from

          -- interfering with SELECT statements.

          SET NOCOUNT ON;

     

        -- Insert statements for procedure here

          SELECT *

          FROM [MYTABLE1]

          WHERE ID > @MaxID1

         

          SELECT *

          FROM [MYTABLE2]

          WHERE ID > @MaxID2

         

          SELECT *

          FROM [MYTABLE3]

          WHERE ID > @MaxID3

    END
    ================================================================= 

    In LINQ to SQL dmbl designer, we can directly drag the stored procedure into the dbml file.   By default the stored procedure method is under the DataContext class.  We can modify the method definition as: (The MYTABLE1, MYTABLE2 and MYTABLE3 entities should have been dragged into the dbml file)

    ================================================================= 

    [Function(Name="dbo.GetMultipleResults")]

    [ResultType(typeof(MYTABLE1))]

    [ResultType(typeof(MYTABLE2))]

    [ResultType(typeof(MYTABLE3))]

    public IMultipleResults GetMultipleResults([Parameter(Name="MaxID1", DbType="Int")] System.Nullable<int> maxID1, [Parameter(Name="MaxID2", DbType="Int")] System.Nullable<int> maxID2, [Parameter(Name="MaxID3", DbType="Int")] System.Nullable<int> maxID3)

    {

        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), maxID1, maxID2, maxID3);

        return (IMultipleResults)result.ReturnValue;

    }

    ================================================================= 

     

    Then we can call the stored procedure method as:

    ================================================================= 

    var results = db.GetMultipleResults(maxID1, maxID2, maxID3);

    var table1 = results.GetResult<MYTABLE1>();

    var table2 = results.GetResult<MYTABLE2>();

    var table3 = results.GetResult<MYTABLE3>();

    ================================================================= 

     

    For additional information about how to use stored procedure to retrieve data via LINQ to SQL, please see http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx. 

     

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 29, 2009 2:29 AM
    Moderator
  • Hmmm... One thing I believe I've learned from some of the guru's @ the last VSLIVE convention is to not so much depend on stored procedures. To that effect, I can do something like this - and if I can't get LINQ to do this for me, I'll probably stick with it:
    System.Text.StringBuilder sb = new System.Text.StringBuilder();
    sb.Append("SELECT max(ID) FROM MYTABLE1 where ID > @maxID1;");
    sb.Append("SELECT max(ID) FROM MYTABLE2 where ID > @maxID2;");
    sb.Append("SELECT max(ID) FROM MYTABLE3 where ID > @maxID3;");
    using (SqlConnection conn = new SqlConnection(String.Format("data source={0};database={1};integrated security=SSPI;", dbServer, db)))
    {
        using (SqlCommand sqlCmd = new SqlCommand { Connection = conn, CommandType = CommandType.Text, CommandText = sb.ToString() })
        {
            sqlCmd.Parameters.Add("@maxID1", SqlDbType.Int).Value = maxID1;
            sqlCmd.Parameters.Add("@maxID2", SqlDbType.Int).Value = maxID2;
            sqlCmd.Parameters.Add("@maxID3", SqlDbType.Int).Value = maxID3;
    
            using (SqlDataAdapter da = new SqlDataAdapter { SelectCommand = sqlCmd })
            {
                System.Text.StringBuilder sbMessage;
                using (DataSet ds = new DataSet())
                {
                    da.Fill(ds);
                    // etc...
    
                }
            }
        }
    }
    

    I do however understand what you are trying to accomplish but again, wish to accomplish it similar to the "tried and true" method above.

    thanks also for responding once again ;)
    Friday, October 30, 2009 8:03 PM
  • Hi Bill,

     

    Thank you for sharing your solution.  It is really straight-forward to use ADO.NET 2.0 methods directly.  J 

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun
    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 2, 2009 7:59 AM
    Moderator
  • Lingzhi, that's sort of a depressing response... It's my intent to deliver LINQ to my team as "new Messiah" of interacting with their data but if such a simple operation can't be carried on the LINQ realm, I'm less inclined to report back that LINQ is really the way to go - especially considering that LINQ serves as the foundation of the Entity Framework, I'm all the more depressed.

    Sure, no solution fits every bill and as you pointed out, a Stroed Procedure can also be used here...
    Monday, November 2, 2009 5:43 PM
  • Hi Bill,

     

    I am sorry, I misunderstood your meaning since I thought you have resolved the problem by ADO.NET 2.0 methods.  By LINQ to SQL, except using stored procedures, here is one trick to embed the three SELECT queries into one LINQ query (one database call). 

    ===========================================================================
                    var MaxIDs = (from m in db.MYTABLEs

                                  select new

                                  {

                                      MaxID1 = db.MYTABLEs.Where(d => d.ID > 0).Max(d => d.ID),

                                      MaxID2 = db.MYTABLE2s.Where(d => d.ID > 0).Max(d => d.ID),

                                      MaxID3 = db.MYTABLE3s.Where(d => d.ID > 0).Max(d => d.ID),

                                  }).First();
    ===========================================================================

     

    However, the generated T-SQL will one more complicated than the original SELECTS since we need to embed the three MAX queries into a LINQ select query and then pick the first one.  Here is the T-SQL generated by LINQ to SQL provider:

    ===========================================================================
    SELECT TOP (1) [t4].[value] AS [MaxID1], [t4].[value2] AS [MaxID2], [t4].[value3] AS [MaxID3]

    FROM (

        SELECT (

            SELECT MAX([t1].[ID])

            FROM [dbo].[MYTABLE] AS [t1]

            WHERE [t1].[ID] > @p0

            ) AS [value], (

            SELECT MAX([t2].[ID])

            FROM [dbo].[MYTABLE2] AS [t2]

            WHERE [t2].[ID] > @p1

            ) AS [value2], (

            SELECT MAX([t3].[ID])

            FROM [dbo].[MYTABLE3] AS [t3]

            WHERE [t3].[ID] > @p2

            ) AS [value3]

        FROM [dbo].[MYTABLE] AS [t0]

        ) AS [t4]

    -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

    -- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
    ===========================================================================

    I prefer the stored procedure method for such scenario.  J


    Have a nice day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 3, 2009 7:35 AM
    Moderator
  • Hi Bill,

    How is the problem now?  If you need any further assistance, please feel free to let me know.

    Have a nice day!


    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 9, 2009 12:31 AM
    Moderator