July 27, 2006 at 7:51 pm
Hi,
I'm using SQL server 2000. Can you help me:
I write procedure in SQL7.0, it run about 20 minutes, but in SQL2000, it run 80 minutes. I don't know why it run very slow in SQL 2000.
in SQL 2000, CPU usage very very low(<5%) when running that proceduce
My procedure using many Loop (scanning #table)
thanks for reply!
July 28, 2006 at 2:44 am
Can you post the code, and also the table definition (including any indexes and an idea of how many rows the table holds)?
From what you've told us so far, I suspect that it is disk IO or memory that is your problem, not CPU. Are the different servers (SQL7 and SQL2000) on the same hardware, or very different hardware?
Have you tried looking at the execution plan for the SP? Unless it's a very simple query there will probably be differences between the two versions, but we need to know what the differences are.
July 28, 2006 at 5:03 am
Server SQL 7.0 : 2CPU 1,8G. 1GRam
Server SQL 2000 : 2CPU 2,4G. 2GRam (SQL2000 SP4)
All application on server SQL 2000 are faster than server SQL 7.0, only my proc is very slow and CPU usage very low(<5%).
In Server SQL 2000, if proc have simple query then it run faster than Server SQL 7.0 (and CPU usage very high, about 90%). If proc uses cursor or #table to scaning table (~500000 rows) then it very slow and CPU usage very low.
Can you help me!
July 28, 2006 at 8:12 am
I'm a bit confused.
When you say "proc", do you mean "process" or "stored procedure"?
You say "only my proc is very slow ..." , but then say that this proc can sometimes be very simple and sometimes very complex, so this is really too general for me to be able to give an answer to.
I wouldn't expect ANY process that used a cursor to scan a 500000 row table to run quickly. The same goes for a #temp table.
It would still be helpful if you post an example of code that performs slowly, together with the tables and indexes involved, and an indication of the number of rows in each table.
July 28, 2006 at 8:16 pm
Thanks to Philip!
"proc" is "stored procedure". Code is quite complex, I can post a part of code:
---------
CREATE procedure proc1
@fchitiet varchar(50),@fin varchar(50), @fout varchar(50) as
Declare @_calling_pre varchar(10),@_called_pre varchar(18),@_date_pre Datetime,@_start_time_pre varchar(6),@_end_time_pre varchar(6),@_duration_pre numeric(5),
@_calling varchar(10),@_called varchar(18),@_date Datetime,@_start_time varchar(6),@_end_time varchar(6),@_duration numeric(5),
@i numeric(10),@cnt numeric(10),@time int,@fkq varchar(50)
SET DATEFORMAT dmy
set nocount on
set @fkq=@fin+'kqi'
exec("drop table "+ @fkq)
exec("select * into "+@fkq+" from "+@fin+" where 1<>1")
drop table #a_ftemp_G
CREATE TABLE #a_ftemp_G (id numeric(10) identity ,calling varchar(10),called varchar(18),[date] Datetime,start_time varchar(6),end_time varchar(6),duration numeric(5))
INSERT #a_ftemp_G EXEC (' SELECT calling,called,date,start_time,end_time,duration from '+@fin)
Select @cnt=count(*) from #a_ftemp_G
if @cnt>1
begin
Set @i=1
Select @_calling_pre=calling,@_called_pre=called,@_date_pre=date,@_start_time_pre=start_time,@_end_time_pre=end_time from #a_ftemp_G where id=@i
Set @i=@i+1
while @i<=@cnt
begin
Select @_calling=calling,@_called=called,@_date=date,@_start_time=start_time,@_end_time=end_time from #a_ftemp_G where id=@i
-- code simple calculate
--insert result into @fkq
Set @i=@i+1
end--while
exec("select * into "+@fout+" from "+@fkq)
end--if
drop table #a_ftemp_G
exec("drop table "+ @fkq)
--------------------
CREATE procedure proc2
@fchitiet varchar(50),@fin varchar(50), @fout varchar(50) as
Declare @calling varchar(10),@called varchar(18),@fgroup_T varchar(50),@fgroup varchar(50),@fgoc_i varchar(50),
@cnt_group numeric(10),@i numeric(10)
Set nocount on
Set @fgroup_T=ltrim(rtrim(@fin))+'gT'
exec ("drop table "+ @fout)
exec("select * into "+@fout+" from "+@fin+" where 1<>1")
exec("drop table "+ @fgroup_T)
exec("select ltrim(rtrim(calling)) as calling,ltrim(rtrim(called)) as called,count(*) as cnt into "+ @fgroup_T+" from "+@fin+" group by ltrim(rtrim(calling)),ltrim(rtrim(called))")
Set @fgroup=ltrim(rtrim(@fin))+'g'
exec("drop table "+ @fgroup)
exec("select * into "+ @fgroup+" from "+ @fgroup_T+" where cnt>1")
exec("drop table "+ @fgroup_T)
exec GC_delete_temp #a_ftemp_group
CREATE TABLE #a_ftemp_group (id int identity, calling varchar(10), called varchar(18))
INSERT #a_ftemp_group EXEC ('SELECT calling,called from '+@fgroup)
Select @cnt_group=count(*) from #a_ftemp_group
Set @fgoc_i=ltrim(rtrim(@fin))+'i'
Set @i=1
While @i<=@cnt_group
begin
exec("drop table "+ @fgoc_i)
Select @calling=calling,@called=called from #a_ftemp_group where id=@i
Set @calling=ltrim(rtrim(@calling))
Set @called=ltrim(rtrim(@called))
exec("Select * into "+@fgoc_i+" from "+@fchitiet+" where ltrim(rtrim(calling))= '"+@calling+"' and ltrim(rtrim(called))='"+@called+"' order by calling,called,date,start_time asc")
exec proc1 @fchitiet,@Fgoc_i,@fout
Set @i=@i+1
End
exec("drop table "+ @fgoc_i)
exec("drop table "+ @fgroup)
drop table #a_ftemp_group
-----------
- When I run proc2, it always is slow, CPU usage about 2%~5% . Only these procedure are slow, other procedures, don't have loop table(don't using cursor or #temp table), are quickly.
please help me!
(I can speak a little bit English)
August 2, 2006 at 9:06 am
Try using "sp_executesql" instead of "exec"
e.g.
sp_executesql N'SELECT * INTO @fgoc_i FROM @fchitet WHERE ltrim(rtrim(calling)) = @calling AND ltrim(rtrim(called)) = @called order by calling, called, date, start_time asc',
N'@fgoc_i nvarchar(50), @fchitet nvarchar(50), @calling nvarchar(10), @called nvarchar(18)',
@fgoc_i, @fchitet, @calling, @called
Using sp_executesql is usually more efficient than using EXEC, because of the way it stores and re-uses query plans. With EXEC, the query plan gets re-compiled with every new execution. With SP_EXECUTESQL the first plan is stored for further re-use, and just the parameter values are changed (very like a stored procedure).
You should notice the difference, especially within the loop in your code.
In addition, if there are many rows in the tables #a_ftemp_G and #a_ftemp_group, it would be worth creating a CLUSTERED index on the identity columns in these tables once you've added the data.
August 4, 2006 at 3:24 am
Hi, Philip!
I has found the reason why my store procedure run slow, I used many query like "select into". I changed by using #temp table and scan #temp table, avoid using "select into" query, result is very fast!
thanks for your reply!
August 4, 2006 at 8:13 pm
Thanks for the feedback on this, Doanthi...
I don't know what it is but I've never had the speed problems with "Select Into" that so many have spoken about. It is true that I'll usually define all of my tables or temp tables at the front of the proc to keep DDL from mixing with DML (a nastly little source of recompiles), but I've just not had the speed problems folks are talking about when I use Select Into... I dunno...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply