none
why not one query? RRS feed

  • Question

  • hello :)

    i have strange query

    var result = from p in providers
    			 select new {
    					 offset = offset,
    					 idProvider = p.ID_Provider,
    					 name = p.NAME,
    					 dateReg = p.DateReg,
    					 countPrices = (from price in da.Provider_Prices
    								where price.ID_Provider == p.ID_Provider
    								select price).Count(),
    					 marks = new XElement("root", (from m in da.Provider_SferaModels
    								  where m.ID_Provider == p.ID_Provider && (m.ID_SystemCategory == 14 || m.ID_SystemCategory == 17)
    								  select new XElement("item", new XElement("MarkName", m.Catalog_MARK.MarkName), new XElement("ID_MARK", m.ID_Mark))
    															).Distinct().Take(4)), 

    and I want to ask, why i've got not one query with sub-queries, but one main query and many-many subqueries...

    And can i get one huge query :) because all this queries take about 2 seconds

     

    Thursday, April 29, 2010 9:19 AM

All replies

  • hello :)

    i have strange query

     

    var
     result = from
     p in
     providers
    select new {
    offset = offset,
    idProvider = p.ID_Provider,
    name = p.NAME,
    dateReg = p.DateReg,
    countPrices = (from price in da.Provider_Prices
    where price.ID_Provider == p.ID_Provider
    select price).Count(),
    marks = new XElement("root" , (from m in da.Provider_SferaModels
    where m.ID_Provider == p.ID_Provider && (m.ID_SystemCategory == 14 || m.ID_SystemCategory == 17)
    select new XElement("item" , new XElement("MarkName" , m.Catalog_MARK.MarkName), new XElement("ID_MARK" , m.ID_Mark))
    ).Distinct().Take(4)),

     

    and I want to ask, why i've got not one query with sub-queries, but one main query and many-many subqueries...

    And can i get one huge query :) because all this queries take about 2 seconds

     

    The reason why you are getting a lot of queries fired against the db is because your projection is somehow wrong. You are basically creating an isolated sub query for each p.ID_Provider.

    I believe you should do something like this instead:

    var result = from p in providers

    select new {

    offset = offset

    idProvider = p.ID_Provider

    countPrices = p.Provider_Prices.Count()  // this is where I dont know anything anout your data model and could be wrong :) Anyway.. you should reuse your existing 'p', okay?

    }

    Regarding the marks, this could turn out to be a lot more difficult, but again.. use the same approach as the before mentioned, use 'p' :)

    As you probably can see, it's very essential that the data relations is in place and one know them :)

    • Proposed as answer by liurong luo Tuesday, May 4, 2010 10:45 AM
    Friday, April 30, 2010 9:55 PM
  • ok, i will try.

     

    in simple, my DB looks like this:

    4 tables, Providers, Provider_Prices, Catalog_MARK, Provider_SferaModels

    Providers and Provider_Prices are connected with ID_Provider key, Providers and Provider_SferaModels has the same, Catalog_MARK and Provider_SferaModels -

    with ID_MARK. So, Providers and Catalog_MARK has many-to-many relationship and I created Provider_SferaModels table with keys ID_Provider and ID_MARK.

    And I want to select providers and top 4 marks for each in one query.

    Saturday, May 1, 2010 10:58 AM
  • any idea?
    Thursday, May 6, 2010 6:48 AM
  • Ahh okay.. thought we were done :)

    marks = new
     XElement("root"
    , (from
     m in
     da.Provider_SferaModels
    where m.ID_Provider == p.ID_Provider && (m.ID_SystemCategory == 14 || m.ID_SystemCategory == 17)
    select new XElement("item" , new XElement("MarkName" , m.Catalog_MARK.MarkName), new XElement("ID_MARK" , m.ID_Mark))
    ).Distinct().Take(4)),

    Here you reference da.Provider_SferaModels which creates yet another nested sql statement for each in your outer query, this should be avoided if possible.

    You must reference to p.Provider_SferaModels where m.ID_SystemCategory == 14 (please notice that i've omitted the clause between ID_Provider as this is already given by the relation).

    See the pattern?

     

     

    Thursday, May 6, 2010 5:53 PM
  • ok, I made this:

    marks = new XElement("root", (from m in p.Provider_SferaModels
    		  where m.ID_SystemCategory == 14 || m.ID_SystemCategory == 17
    		  select new XElement("item", new XElement("MarkName", m.Catalog_MARK.MarkName), new XElement("ID_MARK", m.ID_Mark))).Distinct().Take(4)),

    but result is the same, I have one main query and many sub-queries

    main:

    exec sp_executesql N'SELECT [t2].[ID_Provider] AS [idProvider], [t2].[NAME] AS [name], [t2].[DateReg] AS [dateReg], (
      SELECT COUNT(*)
      FROM [dbo].[Provider_Price] AS [t3]
      WHERE [t3].[ID_Provider] = [t2].[ID_Provider]
      ) AS [countPrices], (
      SELECT [t4].[TownName]
      FROM [dbo].[Towns] AS [t4]
      WHERE ([t4].[ID_Town]) = [t2].[ID_Town]
      ) AS [townName]
    FROM (
      SELECT TOP (35) [t0].[ID_Provider], [t0].[NAME], [t0].[DateReg], [t0].[ID_Town]
      FROM [dbo].[Providers] AS [t0]
      INNER JOIN [dbo].[aspnet_Membership] AS [t1] ON [t0].[UserId] = ([t1].[UserId])
      WHERE ([t1].[IsApproved] = 1) AND (([t0].[Sfera_Rozn] = @p0) OR ([t0].[Sfera_Opt] = @p1) OR ([t0].[Sfera_Manufactures] = @p2))
      ) AS [t2]',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=1,@p2=1
    sub:
    exec sp_executesql N'SELECT TOP (4) [t2].[MarkName] AS [content], [t2].[ID_Mark] AS [content2]
    FROM (
      SELECT DISTINCT [t1].[MarkName], [t0].[ID_Mark]
      FROM [dbo].[Provider_SferaModel] AS [t0]
      INNER JOIN [dbo].[Catalog_MARK] AS [t1] ON [t1].[ID_MARK] = [t0].[ID_Mark]
      WHERE (([t0].[ID_SystemCategory] = @p0) OR ([t0].[ID_SystemCategory] = @p1)) AND ([t0].[ID_Provider] = @x1)
      ) AS [t2]',N'@p0 int,@p1 int,@x1 int',@p0=14,@p1=17,@x1=7

    Friday, May 7, 2010 2:47 AM
  • I can see clearly now.. http://www.youtube.com/watch?v=hGfo8iFQYGk

    Well okay..It's caused by the Top 4 Distinct, quite obviously :) This is of course a stupid behavior when talking about performance, but happily it is only done when time for each SystemCategory.

    You would probably do it the same way in T-sql, right?

     

     

    Wednesday, May 12, 2010 11:55 AM