Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les |||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les

Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les
Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les
|||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les

Insert taking a long time.

Hey guys,
I am inserting into a table, which is probabaly around 800meg in size
(the database is around 870meg) and inserts are taking between 6-8
seconds (as shown in profiler). Profiler states that it is doing ~216000
reads for this task.
I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
Xeon Processors (4 logical processors), and very fast ram.
Can anyone suggest why this may be taking so long, and also any ideas on
why its doing over 200,000 reads per insert.
Thanks in advance,
Les :)Can you post the full ddl for the table, plus any others that are related to
this one via any constraints? Any triggers on the table?
Likelihood is that there is a triigger causing one or more table scans or
refernces constraints somewhere that are not supported by appropriate
indexes.
Mike John
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size (the
> database is around 870meg) and inserts are taking between 6-8 seconds (as
> shown in profiler). Profiler states that it is doing ~216000 reads for
> this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz Xeon
> Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les :)|||Les
Try DROP INDEXes that defined o the table just before INSERTING and
re-create them after .
"Les Hughes" <lesHATESSPAM@.datarev.com.au> wrote in message
news:u6013fteFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Hey guys,
> I am inserting into a table, which is probabaly around 800meg in size
> (the database is around 870meg) and inserts are taking between 6-8
> seconds (as shown in profiler). Profiler states that it is doing ~216000
> reads for this task.
> I am running SQL Server 2000, Standard, on a machine with 2 x 2.4ghz
> Xeon Processors (4 logical processors), and very fast ram.
> Can anyone suggest why this may be taking so long, and also any ideas on
> why its doing over 200,000 reads per insert.
> Thanks in advance,
> Les :)

Insert record into temporary table from a select statement

Hi guys,

anyone can help me?
i using sp to select a select statement from a join table. due to the requirement, i need to group the data into monthly/weekly basic.

so i already collect the data for the month and use the case to make a new compute column in the selete statement call weekGroup. this is just a string showing "week 1", "week 2" ... "week 5".

so now i want to group the weekgroup and disply the average mark. so i need to insert all the record from the select statement into the temporary table and then use 2nd select statement to collect the new data in 5 record only. may i know how to make this posible?

regards
terence chuai believe you can do it in a simpler way. can you post some sample data and the output you are looking for?|||here is the sample data. i want to group them to be able to use in 3 record in this case.

weekgroup mark updatedate
Week 1 100.000000 2006-01-03 09:37:15.000
Week 1 100.000000 2006-01-06 12:18:09.000
Week 1 71.600000 2006-01-06 12:59:46.000
Week 1 100.000000 2006-01-06 13:03:52.000

Week 2 95.000000 2006-01-09 11:49:17.000
Week 2 100.000000 2006-01-09 12:19:19.000

Week 3 100.000000 2006-01-16 15:03:24.000
Week 3 71.600000 2006-01-16 15:05:31.000
Week 3 100.000000 2006-01-17 15:59:43.000
Week 3 100.000000 2006-01-17 16:57:38.000

--------
here is the code i did. i set the @.dtstart = 1st day of the month
@.dtWeekEnd = 1 week after the 1st day of the month
@.dtEnd = end of the month

i try to group by the weekgroup but it fail. and it only allow to group by the last update date(Answer.dtAnswer).

SELECT CASE
WHEN day(@.dtStart) <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) THEN 'Week 1'
WHEN day(@.dtStart)+7 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 7 THEN 'Week 2'
WHEN day(@.dtStart)+14 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 14 THEN 'Week 3'
WHEN day(@.dtStart)+21 <= day(Answer.dtAnswer) and day(Answer.dtAnswer) < day(@.dtWeekEnd) + 21 THEN 'Week 4'
else 'Week 5'
END as weekgroup, AVG(Answer.bScore) AS tScore, dtAnswer -- CONVERT(INT, AnswerKey.bRowId) AS bRowId-- day(Answer.dtAnswer) as days
FROM Answer INNER JOIN
AnswerKey ON Answer.lAnswerId = AnswerKey.lAnswerId INNER JOIN
QuestionDef ON AnswerKey.iQuestionDefId = QuestionDef.iQuestionDefId INNER JOIN
QuestionAnswerDef ON QuestionDef.iQuestionDefId = QuestionAnswerDef.iQuestionDefId AND AnswerKey.bKeyId = QuestionAnswerDef.bKeyId
WHERE (Answer.iMinutes BETWEEN 570 AND 1020) and (Answer.dtAnswer >= @.dtStart) AND (Answer.dtAnswer < @.dtEnd) and (Answer.iTemplateId = 1)
GROUP BY Answer.iTemplateId, AnswerKey.bRowId, QuestionDef.sQuestion, Answer.fDiscard,
QuestionAnswerDef.sAnswer, Answer.fIncomplete, AnswerKey.bScore, AnswerKey.bRowId, dtAnswer--, WeekGroup--, NoOfWeek
having (Answer.fDiscard = 0) AND (Answer.fIncomplete = 0)
order by dtAnswer--);|||what i understand, from the long sql, is you have a table with a numeric col and a date col and you want to have a AVG of the numeric col on at an interval of every 7 days, staring from the first day of the month...

an example to do the above for 1st half and 2nd half of the month could be

select avg(<Numeric Col>),
case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end
from table2
group by case when datediff(d,'20060101',dt) <= 15 then 'FirstHalf' else 'SecondHalf' end

here the first date of the month is '20060101'|||thank for your advice, i found a solution from your sample.

i dun know using the case also can put at group by so problem solve easily.

thanks a lot:D
regards
terence chua

INSERT performance

Hi guys,
Could someone please help me identify the botleneck in my case.
I have a table that includes one varchar(100) field and about 10 integer,
date, and boolean fields. The varchar column and two date columns are
indexed. The table includes about 1M of rows. No triggers, one foreign key
to a table with about 500 records.
When I insert about 50 records calling a stored procedure in a loop it
takes 30-60 second. During this time SQL takes 100% of CPU (AMD Athlone
2800) and makes lots of HDD reading requests. The database itself is quite
small and I guess is completely located in RAM, however I am not sure what
it reads from HDD in this case.
Is this performance normal? I guess I can improve it writing to those 50
records to a temporary table and then copying data to the main table. Am I
right? Are there any other ways to improve the insert performance?
Thanks,
Mike
Message posted via http://www.sqlmonster.com
Mike via SQLMonster.com wrote:
> Hi guys,
> Could someone please help me identify the botleneck in my case.
> I have a table that includes one varchar(100) field and about 10
> integer, date, and boolean fields. The varchar column and two date
> columns are indexed. The table includes about 1M of rows. No
> triggers, one foreign key to a table with about 500 records.
> When I insert about 50 records calling a stored procedure in a loop it
> takes 30-60 second. During this time SQL takes 100% of CPU (AMD
> Athlone 2800) and makes lots of HDD reading requests. The database
> itself is quite small and I guess is completely located in RAM,
> however I am not sure what it reads from HDD in this case.
> Is this performance normal? I guess I can improve it writing to those
> 50 records to a temporary table and then copying data to the main
> table. Am I right? Are there any other ways to improve the insert
> performance?
> Thanks,
> Mike
Take a look at the execution plan for the insert and see what's going
on. You can see a graphical plan in Query Analyzer, or use SET
STATISTICS PROFILE ON/OFF before and after the SP call.
David Gugick
Imceda Software
www.imceda.com
|||It seems like DBCC DBREINDEX solved the problem.
Message posted via http://www.sqlmonster.com

INSERT performance

Hi guys,
Could someone please help me identify the botleneck in my case.
I have a table that includes one varchar(100) field and about 10 integer,
date, and boolean fields. The varchar column and two date columns are
indexed. The table includes about 1M of rows. No triggers, one foreign key
to a table with about 500 records.
When I insert about 50 records calling a stored procedure in a loop it
takes 30-60 second. During this time SQL takes 100% of CPU (AMD Athlone
2800) and makes lots of HDD reading requests. The database itself is quite
small and I guess is completely located in RAM, however I am not sure what
it reads from HDD in this case.
Is this performance normal? I guess I can improve it writing to those 50
records to a temporary table and then copying data to the main table. Am I
right? Are there any other ways to improve the insert performance?
Thanks,
Mike
Message posted via http://www.droptable.comMike via droptable.com wrote:
> Hi guys,
> Could someone please help me identify the botleneck in my case.
> I have a table that includes one varchar(100) field and about 10
> integer, date, and boolean fields. The varchar column and two date
> columns are indexed. The table includes about 1M of rows. No
> triggers, one foreign key to a table with about 500 records.
> When I insert about 50 records calling a stored procedure in a loop it
> takes 30-60 second. During this time SQL takes 100% of CPU (AMD
> Athlone 2800) and makes lots of HDD reading requests. The database
> itself is quite small and I guess is completely located in RAM,
> however I am not sure what it reads from HDD in this case.
> Is this performance normal? I guess I can improve it writing to those
> 50 records to a temporary table and then copying data to the main
> table. Am I right? Are there any other ways to improve the insert
> performance?
> Thanks,
> Mike
Take a look at the execution plan for the insert and see what's going
on. You can see a graphical plan in Query Analyzer, or use SET
STATISTICS PROFILE ON/OFF before and after the SP call.
David Gugick
Imceda Software
www.imceda.com|||It seems like DBCC DBREINDEX solved the problem.
Message posted via http://www.droptable.com

INSERT performance

Hi guys,
Could someone please help me identify the botleneck in my case.
I have a table that includes one varchar(100) field and about 10 integer,
date, and boolean fields. The varchar column and two date columns are
indexed. The table includes about 1M of rows. No triggers, one foreign key
to a table with about 500 records.
When I insert about 50 records calling a stored procedure in a loop it
takes 30-60 second. During this time SQL takes 100% of CPU (AMD Athlone
2800) and makes lots of HDD reading requests. The database itself is quite
small and I guess is completely located in RAM, however I am not sure what
it reads from HDD in this case.
Is this performance normal? I guess I can improve it writing to those 50
records to a temporary table and then copying data to the main table. Am I
right? Are there any other ways to improve the insert performance?
Thanks,
Mike
--
Message posted via http://www.sqlmonster.comMike via SQLMonster.com wrote:
> Hi guys,
> Could someone please help me identify the botleneck in my case.
> I have a table that includes one varchar(100) field and about 10
> integer, date, and boolean fields. The varchar column and two date
> columns are indexed. The table includes about 1M of rows. No
> triggers, one foreign key to a table with about 500 records.
> When I insert about 50 records calling a stored procedure in a loop it
> takes 30-60 second. During this time SQL takes 100% of CPU (AMD
> Athlone 2800) and makes lots of HDD reading requests. The database
> itself is quite small and I guess is completely located in RAM,
> however I am not sure what it reads from HDD in this case.
> Is this performance normal? I guess I can improve it writing to those
> 50 records to a temporary table and then copying data to the main
> table. Am I right? Are there any other ways to improve the insert
> performance?
> Thanks,
> Mike
Take a look at the execution plan for the insert and see what's going
on. You can see a graphical plan in Query Analyzer, or use SET
STATISTICS PROFILE ON/OFF before and after the SP call.
--
David Gugick
Imceda Software
www.imceda.com|||It seems like DBCC DBREINDEX solved the problem.
--
Message posted via http://www.sqlmonster.com