Slow GROUP BY

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
I would suspect it's just a heavy operation. Perhaps, indexing whatever
you're grouping by will help.



Also, consider asking same question in microsoft.public.sqlserver.ce as it's
more of SQL CE question, not NETCF.



Best regards,



Ilya



This posting is provided "AS IS" with no warranties, and confers no rights.
 
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,

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
 
Hey Philip,

I'm working on a quick recreation of your db in SQL Server CE so I can
try a couple of things, but there are a some points that you may not
know that I can see from your schema, indexes, and query:

1. using the AVG function to determine punchlist item completion status
decreases the chances of your query being optimizable at all. I'd simply
select
the value of Complete in the Punchlist table (a bit) or filter these values
in your
WHERE clause (e.g. WHERE Complete = 0 )

2. The index on Description in the Activity table is not being used. To
accomplish the inner join between Activity and PunchItem, only the
FK will be chosen. Suggest making your PK on Activity both the ID
and Desc fields.

3. The big time sink in the query is spent on the Lot table. You have a PK
on Lot ID. You have an index on Lot Number. You use the Lot Project ID
in your WHERE clause. You are getting no optimization from any of that.
Make a single PK on Lot that contains all three columns of that table.

4. Suggest an index on the PunchItem table of (ID, ActivityID)

I assume the table with the 32k rows you mentioned is PunchList?

--
Darren Shaffer
Principal Architect
Connected Innovation


Philip said:
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 said:
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.
Click to expand...
Click to expand...
 
Darren,

Thanks for your efforts.
1. As far as the AVG goes, I need to get summary information on the Complete
column. I need to know whether all items are complete (1) or all or any are
still incomplete (0), If I filter by 0, then I will not know what is
complete, so I can't make a determination. I can change the column type to
smallint, so I can aggregate, without the convert, but I still need to
summarize all the punchlist data for that project and lot.

2. We can try that.

3. We will try that.

4. We will try that.

5. Yes that is the big table.

I also need to look at the overall size as the indexes are added, since the
data set I am using is likely to grow once the client begins to use it and
want more.


Thanks Philip

Darren Shaffer said:
Hey Philip,

I'm working on a quick recreation of your db in SQL Server CE so I can
try a couple of things, but there are a some points that you may not
know that I can see from your schema, indexes, and query:

1. using the AVG function to determine punchlist item completion status
decreases the chances of your query being optimizable at all. I'd simply
select
the value of Complete in the Punchlist table (a bit) or filter these values
in your
WHERE clause (e.g. WHERE Complete = 0 )

2. The index on Description in the Activity table is not being used. To
accomplish the inner join between Activity and PunchItem, only the
FK will be chosen. Suggest making your PK on Activity both the ID
and Desc fields.

3. The big time sink in the query is spent on the Lot table. You have a PK
on Lot ID. You have an index on Lot Number. You use the Lot Project ID
in your WHERE clause. You are getting no optimization from any of that.
Make a single PK on Lot that contains all three columns of that table.

4. Suggest an index on the PunchItem table of (ID, ActivityID)

I assume the table with the 32k rows you mentioned is PunchList?

--
Darren Shaffer
Principal Architect
Connected Innovation


Philip said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
Daren,

I tried adding the columns to the PK, but now doesn't the child table's FK
have to match, so I would have to add all of the fields in the PK to the
child table and make them the FK?

Thanks Philip

Philip said:
Darren,

Thanks for your efforts.
1. As far as the AVG goes, I need to get summary information on the Complete
column. I need to know whether all items are complete (1) or all or any are
still incomplete (0), If I filter by 0, then I will not know what is
complete, so I can't make a determination. I can change the column type to
smallint, so I can aggregate, without the convert, but I still need to
summarize all the punchlist data for that project and lot.

2. We can try that.

3. We will try that.

4. We will try that.

5. Yes that is the big table.

I also need to look at the overall size as the indexes are added, since the
data set I am using is likely to grow once the client begins to use it and
want more.


Thanks Philip

Darren Shaffer said:
Hey Philip,

I'm working on a quick recreation of your db in SQL Server CE so I can
try a couple of things, but there are a some points that you may not
know that I can see from your schema, indexes, and query:

1. using the AVG function to determine punchlist item completion status
decreases the chances of your query being optimizable at all. I'd simply
select
the value of Complete in the Punchlist table (a bit) or filter these values
in your
WHERE clause (e.g. WHERE Complete = 0 )

2. The index on Description in the Activity table is not being used. To
accomplish the inner join between Activity and PunchItem, only the
FK will be chosen. Suggest making your PK on Activity both the ID
and Desc fields.

3. The big time sink in the query is spent on the Lot table. You have a PK
on Lot ID. You have an index on Lot Number. You use the Lot Project ID
in your WHERE clause. You are getting no optimization from any of that.
Make a single PK on Lot that contains all three columns of that table.

4. Suggest an index on the PunchItem table of (ID, ActivityID)

I assume the table with the 32k rows you mentioned is PunchList?

--
Darren Shaffer
Principal Architect
Connected Innovation


Philip said:
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

:

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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top