SQL Server - SQL slow performance in large transaction?

Asked By Atenza

16-Oct-07 12:11 AM

Hi all,

The following SQL run very fast (less than 1 sec):
UPDATE tableA SET colUsefulValue = x.colD
FROM tableB y
INNER JOIN (SELECT colA, colB, colC, sum(colD) colD
FROM tableC
WHERE chrType = '06'
GROUP BY colA, colB, colC) x
on x.colA= y.colA and x.colB = y.colB and x.colC = y.colC
where y.colRef = tableA.colRef

tableA 7000 records
tableB 7000 records
tableC 35000 records
Those tables are physical table and not temp table

However, after begin transaction and creating many records in other tables,
this SQL requires more than 7 mins to complete. Any idea for the slow
performance? Is it related to begin transaction and creating many records in
the transaction? so upset now.....

Thanks in advance.

Martin

16-Oct-07 03:03 AM

Hi Martin

Have you looked to see if there is any blocking? Check out sp_who2 and
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3bQ224453

John

16-Oct-07 04:22 AM

Hi John,

I am using testing environment and double check for any blocking, there is
no blocking....
Would the slow performance cause by the 35000 records created inside the
transaction so that the index have not updated and hence slow in
performance?

I have tried another SQL just retrieve the resultset which is return
instantly. TableB.colRef is char(16) and TableA.colRef is char(10), is it
the reason?

Thanks,
Martin

16-Oct-07 04:55 AM

Hi

If you started from the same position and added the same data, I would
expect the indexes to behave the same regardless of the insertions being in a
transaction or not. You can check the index fragmentation with DBCC
SHOWCONTIG after the rows have been inserted.

If you use SQL Profiler you can see the time taken for each statement if you
look at statement level, you can check for cache misses and compilations.
Make sure that you run are starting from the same position and not benifiting
by having things already cached for one run and not the other.

John

16-Oct-07 08:27 AM

Martin,

If you are joining on columns with mismatched definitions, this can cause
performance problems.  For example, I would be surprised that joining a
char(16) to a char(10) would be a problem, but I recently found some code
joining an int to a char(20) representation of an int value.  Making the
data types match reduced the query from 5 minutes to 1 second.

If mismatched column definitions are the problem, then the indexes are being
ignored and the data scanned for every row joined. This causes a whole lot
of repeated I/O, which is the root of this particular performance problem.

You can easily check the definitions for the columns in your joins to make
sure that they match.  If they match, then this is not the problem.  If they
do not match, try to make them match.

RLF

16-Oct-07 12:20 PM

John,

Thank you for your help. After checking by using DBCC SHOWCONTIG, I found
that the index have around 40% Extent Scan Fragmentation after inserting
35000 records to the table. According to the Books Online, 0-10% may be
acceptable. It seems that after inserting 35K records, the index must be
rebuild. So trying DBREINDEX, even in transaction, the SQL execute much
faster and the performance is acceptable.

However, i have tried again for the whole process without transaction, and
check against the index fragmentation, the index also have around 40% Extent
Scan Fragmentation after inserting 35K records, the SQL also execute very
fast without DBREINDEX!! I really don't understand the underlying behaviour
of the SQL server. With transaction and without transaction would result in
huge performance change. Hope someone could explain it or giving direction
of this issue. Would it related to SQL server setting or memory issue?

Finally, the problem seems solved except that DBREINDEX cannot be executed
in my application, since the application does not have enough privilege.
Application role will set for every user and after set application role, the
privilege cannot be changed as i known. But this is my application problem
only. I will find a way to solve it.

Thank you for your supporting.

Martin

16-Oct-07 12:21 PM

RLF,

For the mismatched column definition in the join, totally agreed that it
will absolutely impact on performance. I have synchronized them and tried
again, however, the result is negative.

Yeah, for the index information, i have replied on next message

Thanks,
Martin