Its to slow when I handle XML field in SQL 2005!

It's to slow when I handle XML field in SQL 2005!

I find that it's slow when I handle XML field (maybe only stored 200kb date) in SQL 2005! Especially when I insert a sub tree to XML field !
I have indexed the XML field.

Is it possible that the XML field is not ripe in SQL 2005 database?

Can you quantify "slow"?|||I made a online bookmark program with ASP.Net 2.0+ SQL 2005, I stored bookmarks in a XML Field, I find it's very slow when I insert, edit or delete bookmark, especially import bookmark to a folder selected.you can try it athttp://www.hellocw.com/onlinebookmark/Default.aspx|||

I'm not sure what you mean by very slow... I just tried your site, clicking on the recently added pages was instaneous... The page came up quickly, and I even created an account, added a page to a couple folders (root, sports), and I couldn't see it taking any time at all.

So..What is slow?

|||Hi,i too need the functionality of importing and exporting bookmarks to my website will u plshelp me in this i mean will u pls give me some code sample.

Issue: How to convert a database field from varchar to datetime..?

Can anyone help me on this!
I've got more than a 1000 records in a SQL server database.
The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)
How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it.
thanks in advance!

You should be able to just convert them to datetime select cast(varDateField as dateTime) from yourTable. The only problem you will have is if you dont have date fields in any of the data.

Nick

Issue with distinct count

I have a table which contains activities, and I am trying to extract a section that has less than a certain number of activities for a given date range. I am receiving an error of

Incorrect syntax near 'activityid' for my select clause. Any help would be greatly appreciated.

Code Snippet

Query I'm working with
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
declare @.sectionid int, @.maxcount int, @.stdate datetime, @.enddate datetime
set @.sectionid = 37
set @.maxcount = 5
set @.stdate = '01/01/06'
set @.enddate = '01/31/06'

select distinct (count activityid) <= @.maxcount, a.sectionid
from activity a
where
a.sectionid = @.sectionid
AND
(a.date between @.stdate and @.enddate)

Table Definition
>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE PROCEDURE [dbo].[p_Activity_Create]
(
@.ActivityID INT OUTPUT,
@.SectionID INT,
@.ActivityCategoryID INT,
@.STIAssessmentTestID INT,
@.Name VARCHAR(30),
@.Date DATETIME,
@.MaximumScore DECIMAL(6,2),
@.WeightAddition DECIMAL(9,6),
@.WeightMultiplier DECIMAL(9,6),
@.IsAssessment BIT,
@.IsHomework BIT,
@.IsScored BIT,
@.MayBeDropped BIT,
@.IsDropped BIT,
@.IsComplete BIT,
@.Unit VARCHAR(50)

)
AS

SET NOCOUNT ON

INSERT INTO [dbo].[Activity]
(SectionID,
ActivityCategoryID,
STIAssessmentTestID,
[Name],
[Date],
MaximumScore,
WeightAddition,
WeightMultiplier,
IsAssessment,
IsHomework,
IsScored,
MayBeDropped,
IsDropped,
IsComplete,
Unit)
VALUES
(@.SectionID,
@.ActivityCategoryID,
@.STIAssessmentTestID,
@.Name,
@.Date,
@.MaximumScore,
@.WeightAddition,
@.WeightMultiplier,
@.IsAssessment,
@.IsHomework,
@.IsScored,
@.MayBeDropped,
@.IsDropped,
@.IsComplete,
@.Unit)

SET @.ActivityID = SCOPE_IDENTITY()
GO

Table data
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

ActivityID SectionID Name Date ActivityCategoryID STIAssessmentTestID MaximumScore WeightAddition WeightMultiplier IsAssessment IsHomework IsScored MayBeDropped IsDropped IsComplete 11 37 Homework 9/20/2005 49 NULL 25 0 1 0 1 1 1 0 0 14 37 Midterm 10/16/2005 51 NULL 100 0 1 1 0 1 1 0 0 38 37 Homework 1/16 1/16/2006 49 NULL 25 0 1 0 1 1 1 0 0 39 37 Homework 1/17 1/17/2006 49 NULL 25 0 1 0 1 1 1 0 0 40 37 Extra 1/18 1/18/2006 52 NULL 50 0 1 0 0 1 1 0 0 41 37 Homework 1/19 1/19/2006 49 NULL 25 0 1 0 1 1 1 0 0 12 37 Quiz 1/20/2006 50 NULL 50 0 1 0 0 1 1 0 0 13 37 Test 4/20/2006 51 NULL 100 0 1 0 0 1 1 0 0 15 37 Final 5/20/2006 51 NULL 100 0 1 1 0 1 1 0 0 1 42 Homework 9/20/2005 5 NULL 25 0 1 0 1 1 1 0 0 4 42 Midterm 10/16/2005 7 NULL 100 0 1 1 0 1 1 0 0 2 42 Quiz 1/20/2006 6 NULL 50 0 1 0 0 1 1 0 0 3 42 Test 4/20/2006 7 NULL 100 0 1 0 0 1 1 0 0 5 42 Final 5/20/2006 7 NULL 100 0 1 1 0 1 1 0 0 26 43 Homework 9/20/2005 85 NULL 25 0 1 0 1 1 1 0 0 29 43 Midterm 10/16/2005 87 NULL 100 0 1 1 0 1 1 0 0 27 43 Quiz 1/20/2006 86 NULL 50 0 1 0 0 1 1 0 0 42 43 Homework 4/17 4/17/2006 85 NULL 25 0 1 0 1 1 1 0 0 43 43 Extra 4/18 4/18/2006 88 NULL 50 0 1 0 0 1 1 0 0 44 43 Homework 4/19 4/19/2006 85 NULL 25 0 1 0 1 1 1 0 0 28 43 Test 4/20/2006 87 NULL 100 0 1 0 0 1 1 0 0 45 43 Extra 4/21 4/21/2006 88 NULL 50 0 1 0 0 1 1 0 0 30 43 Final 5/20/2006 87 NULL 100 0 1 1 0 1 1 0 0 16 49 Homework 9/20/2005 61 NULL 25 0 1 0 1 1 1 0 0 19 49 Midterm 10/16/2005 63 NULL 100 0 1 1 0 1 1 0 0 17 49 Quiz 1/20/2006 62 NULL 50 0 1 0 0 1 1 0 0 18 49 Test 4/20/2006 63 NULL 100 0 1 0 0 1 1 0 0 20 49 Final 5/20/2006 63 NULL 100 0 1 1 0 1 1 0 0 21 50 Homework 9/20/2005 65 NULL 25 0 1 0 1 1 1 0 0 24 50 Midterm 10/16/2005 67 NULL 100 0 1 1 0 1 1 0 0 22 50 Quiz 1/20/2006 66 NULL 50 0 1 0 0 1 1 0 0 23 50 Test 4/20/2006 67 NULL 100 0 1 0 0 1 1 0 0 25 50 Final 5/20/2006 67 NULL 100 0 1 1 0 1 1 0 0 31 53 Homework 9/20/2005 121 NULL 25 0 1 0 1 1 1 0 0 34 53 Midterm 10/16/2005 123 NULL 100 0 1 1 0 1 1 0 0 32 53 Quiz 1/20/2006 122 NULL 50 0 1 0 0 1 1 0 0 33 53 Test 4/20/2006 123 NULL 100 0 1 0 0 1 1 0 0 35 53 Final 5/20/2006 123 NULL 100 0 1 1 0 1 1 0 0 6 67 Homework 9/20/2005 33 NULL 25 0 1 0 1 1 1 0 0 9 67 Midterm 10/16/2005 35 NULL 100 0 1 1 0 1 1 0 0 7 67 Quiz 1/20/2006 34 NULL 50 0 1 0 0 1 1 0 0 8 67 Test 4/20/2006 35 NULL 100 0 1 0 0 1 1 0 0 10 67 Final 5/20/2006 35 NULL 100 0 1 1 0 1 1 0 0

43 row(s) affected)

The problem is with the line:

select distinct (count activityid) <= @.maxcount, a.sectionid

To me this looks like pseudocode.

You might try something more like:

select sectionId,
count(distinct activityId)
from activity a
where a.sectionId = @.sectionId
and a.date between @.stDate and @.endDate
group by sectionId
having count(distinct activityId) <= @.maxCount