Re: Slow GROUP BY

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Philip (Philip_at_discussions.microsoft.com)
Date: 03/20/05 

Date: Sun, 20 Mar 2005 14:41:03 -0800

Darren,

Here is the SQLDMO that creates the tables in question:

  '---------------------------------------------
  'Activty Table
  '---------------------------------------------

  Set odmoSQLServerTable = New SQLDMO.Table
  
  With odmoSQLServerTable
    .Name = "Activity"

    With .Columns
      .Add CreateColumn("ID", "uniqueidentifier", , , , False, , , ,
"(NewID())") ', , , , False, True, 1, 1)
      .Add CreateColumn("Description", "NVarChar", 255, , , False)
    End With
  End With
     
  Call CreateCheckConstraint(odmoSQLServerTable,
CreateCheckConstraintForStrings("Description", False),
"CK_Activity_Description")
  
  '
  odmoSQLServerTable.Columns.Item("ID").IsRowGuidCol = True
  
' 'create primary key
  Call CreatePrimaryKeyA(odmoSQLServerTable, "PK_Activity", True, "ID")

' Call CreateUniqueConstraint(odmoSQLServerTable,
"IX_Activity_Description", "Description")
  
  rodmoSQLServerDb.Tables.Add odmoSQLServerTable
    
  '---------------------------------------------
  'PunchItem Table
  '---------------------------------------------

  Set odmoSQLServerTable = New SQLDMO.Table
  
  With odmoSQLServerTable
    .Name = "PunchItem"

    With .Columns
      .Add CreateColumn("ID", "uniqueidentifier", , , , False, , , ,
"(NewID())", , True) ', , , , False, True, 1, 1)
      .Add CreateColumn("ActivityID", "uniqueidentifier")
      .Add CreateColumn("Description", "NVarChar", 255, , , False)
    End With
  End With
     
  Call CreateCheckConstraint(odmoSQLServerTable,
CreateCheckConstraintForStrings("Description", False),
"CK_PunchItem_Description")
     
  'create primary key
  Call CreatePrimaryKeyA(odmoSQLServerTable, "PK_PunchItem", True, "ID")

  'Call CreateUniqueConstraint(odmoSQLServerTable,
"IX_PunchItem_ActivityIDDescription", "ActivityID", "Description")
  
  rodmoSQLServerDb.Tables.Add odmoSQLServerTable

  Call
rodmoSQLServerDb.ExecuteImmediate(CreateConstraintSQL(odmoSQLServerTable.Name, "ActivityID", "Activity", "ID", "FK_PunchItem_Activity"))
  
  '---------------------------------------------
  'Lot Table
  '---------------------------------------------
  Set odmoSQLServerTable = New SQLDMO.Table
  
  With odmoSQLServerTable
    .Name = "Lot"
    
    With .Columns
      .Add CreateColumn("ID", "uniqueidentifier", , , , False, , , ,
"(NewID())", , True) ', , , , False, True, 1, 1)
      .Add CreateColumn("ProjectID", "Int")
      .Add CreateColumn("Number", "nvarchar", 16)
    End With
  End With
  
  Call CreateCheckConstraint(odmoSQLServerTable,
CreateCheckConstraintForStrings("Number", False), "CK_Lot_Number")
  
  Call CreatePrimaryKeyA(odmoSQLServerTable, "PK_Lot", True, "ID")
  
' Call CreateUniqueConstraint(odmoSQLServerTable, "IX_Lot_ProjectIdNumber",
"ProjectId", "Number")
  
  rodmoSQLServerDb.Tables.Add odmoSQLServerTable
  
  Call
rodmoSQLServerDb.ExecuteImmediate(CreateConstraintSQL(odmoSQLServerTable.Name, "ProjectID", "[Project]", "ID", "FK_Lot_Project"))
  
  '---------------------------------------------
  'PunchList Table
  '---------------------------------------------

  Set odmoSQLServerTable = New SQLDMO.Table
  
  With odmoSQLServerTable
    .Name = "Punchlist"

    With .Columns
      .Add CreateColumn("ID", "uniqueidentifier", , , , False, , , ,
"(NewID())", , True) ', , , , False, True, 1, 1)
      .Add CreateColumn("LotID", "uniqueidentifier")
      .Add CreateColumn("UserID", "Int")
      .Add CreateColumn("PunchItemID", "uniqueidentifier")
      .Add CreateColumn("Complete", "Bit")
      .Add CreateColumn("Note", "nvarchar", 1024, , , True)
      .Add CreateColumn("LastUpdate", "datetime")
    End With
  End With
     
  Call CreateCheckConstraint(odmoSQLServerTable,
CreateCheckConstraintForStrings("Note", True), "CK_PunchList_Note")
     
  'create primary key
  Call CreatePrimaryKeyA(odmoSQLServerTable, "PK_PunchList", True, "ID")
  
  rodmoSQLServerDb.Tables.Add odmoSQLServerTable
  
  Call
rodmoSQLServerDb.ExecuteImmediate(CreateConstraintSQL(odmoSQLServerTable.Name, "PunchItemID", "PunchItem", "ID", "FK_PunchList_PunchItem"))
  Call
rodmoSQLServerDb.ExecuteImmediate(CreateConstraintSQL(odmoSQLServerTable.Name, "LotID", "Lot", "ID", "FK_PunchList_Lot"))

  Set oRes = New CResources
  
  Call CreateStoredProcedure(rodmoSQLServerDb, "NSMCleanUp",
oRes.GetStoredProcedure("NSMCLEANUP"))
  Call CreateStoredProcedure(rodmoSQLServerDb, "NSMAddPunchlistItem",
oRes.GetStoredProcedure("NSMADDPUNCHLISTITEM"))
  Call CreateStoredProcedure(rodmoSQLServerDb, "NSMGetPunchlist",
oRes.GetStoredProcedure("NSMGETPUNCHLIST"))
  
  ' Set database version
  Call
rodmoSQLServerDb.ExecuteImmediate(CreateVersionExtendedProperty(mtNSM_CURRENT_DATABASE_VERSION))
  ' Set database description
  Call
rodmoSQLServerDb.ExecuteImmediate(CreateDescriptionExtendedProperty(mtNSM_SQL_DATABASE_NAME))

Here is a Sql statement from the immediate window:

"SELECT Lot.Number AS LotNumber, Activity.Description AS
ActivityDescription, AVG(CONVERT(int, PunchList.Complete)) AS complete FROM
PunchList INNER JOIN Lot ON PunchList.LotId = Lot.Id INNER JOIN PunchItem ON
PunchList.PunchItemId = PunchItem.Id INNER JOIN Activity ON
PunchItem.ActivityId = Activity.Id WHERE(Lot.ProjectId = 97) GROUP BY
Lot.Number, Activity.Description"

Indexes have been added by manually for testing to the Lot.Number and
Activity.Description columns.

Thanks Philip

"Darren Shaffer" wrote:

> To really help you, please post the following:
>
> 1. the exact schema of the parent and child table
> 2. the exact indexes you have defined on both
> 3. your exact SQL statement that includes the GROUP BY
>
> SQL Server CE's query processor uses a single index in any
> execution plan and there are some statements that the QP
> cannot optimize. With the info above, we can help you.
> --
> Darren Shaffer
> Principal Architect
> Connected Innovation
>
> "Philip" <Philip@discussions.microsoft.com> wrote in message
> news:7C0F151B-7A9D-4439-8FE2-58FEBF7B12CF@microsoft.com...
> >I am trying to use a Group by clause with either a DataSet.Fill or a
> > DataReader. Both deliver the same performance which is too slow. The SQL
> > CE
> > 2.0 database has about 32,000 rows in its largest table. I want to get
> > summary status, so I group by the parent table's columns. e.g.: "group by
> > lot.number, activity.description"
> >
> > If I remove the group by the datareader is fast, until i loop all the
> > extra
> > rows, which is the same speed as the group by or the dataset.fill with or
> > without the group by, since filling it will the dup data or aggregating
> > seem
> > to be the same work. What can be done?
> >
> > Philip
>
>
>

Relevant Pages

  • Re: Joining two datatables with no primary key
    ... to write a SQL statement with inner join. ... These are existing datatables and ... As I said, though, their is no primary key column, only the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Why db.OpenRecordset("SQL STRING HERE", dbOpenDynaset) is not working ?
    ... Sub Tester() ... Dim r As Recordset ... FROM tblBatches INNER JOIN (tblClusters INNER JOIN ((tblAdmin INNER ... you have a where statement that looks the same as the where statement in your SQL statement. ...
    (comp.databases.ms-access)
  • Re: subform records
    ... open the Immediate Window. ... Copy the SQL statement there, and paste it into SQL View in a new query. ... tblSet, with SetID primary key, and OrderID foreign key. ... Dim strSql as String ...
    (microsoft.public.access.formscoding)
  • Re: DISTINCTROW
    ... the sql statement is querying 2 tables and 1 stored query. ... but these are not unique indexes. ... tblB does not have primary key and does not have index. ...
    (microsoft.public.access.queries)
  • Re: subform records
    ... open the Immediate Window. ... Copy the SQL statement there, and paste it into SQL View in a new query. ... Allen Browne - Microsoft MVP. ... tblSet, with SetID primary key, and OrderID foreign key. ...
    (microsoft.public.access.formscoding)