OSQL vs ISQLW peformance issue
-
Hello everyone,
I have some Transact-SQL executed on a table having about 4 million records.
If I use ISQLW (query analyzer) to excute this script, it takes about 25 minutes.
If I use OSQL to execute this script, it takes about 2 hours and a half.
I am on the same workstation and the data is on the same server.
Both tools have the same config when connected on the server:
-- network protocol: Named Pipes
set quoted_identifier off
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
Do you have any idea about what could explain this huge difference?
For the moment, I really don't understand what appenning.
Thank's
Carl
-
Can you post the script or a portion of it. I'd like to understand this as well, because I use OSQL a lot for batch processing.
Thanks..
-
Yes, no problem here is the script (for sure it can be done differently but it would not explain the huge difference between ISQLW and OSQL):
SET NOCOUNT ON
GO
DECLARE c_measure insensitive CURSOR
FOR SELECT sysrecno,route_id_1 FROM measure
WHERE route_id_1 IS NOT NULL
GO
DECLARE @cur_sysrecno BINARY(8)
DECLARE @cur_route_id_1 VARCHAR(5)
DECLARE @count int
DECLARE @length int
DECLARE @first_char VARCHAR(1)
DECLARE @last_char VARCHAR(1)
OPEN c_measure
SELECT @count = 0
SELECT @length = 0
FETCH c_measure INTO @cur_sysrecno,@cur_route_id_1
BEGIN transaction
WHILE (@@fetch_status <> -1)
BEGIN
SELECT @cur_route_id_1 = ltrim(rtrim(@cur_route_id_1))
SELECT @length = datalength(@cur_route_id_1)
IF (@length = 1)
BEGIN
IF (@cur_route_id_1 IN ('0','1','2','3','4','5','6','7','8','9'))
BEGIN
SELECT @cur_route_id_1 = ' ' + @cur_route_id_1
END
ELSE
BEGIN
SELECT @cur_route_id_1 = ' ' + @cur_route_id_1
END
END
IF (@length = 2)
BEGIN
SELECT @first_char = substring(@cur_route_id_1, 1, 1)
IF (@first_char IN ('0','1','2','3','4','5','6','7','8','9'))
BEGIN
SELECT @last_char = substring(@cur_route_id_1, @length, 1)
IF (@last_char IN ('0','1','2','3','4','5','6','7','8','9'))
BEGIN
SELECT @cur_route_id_1 = ' ' + @cur_route_id_1
END
ELSE
BEGIN
SELECT @cur_route_id_1 = ' ' + @cur_route_id_1
END
END
ELSE
BEGIN
SELECT @cur_route_id_1 = ' ' + @cur_route_id_1
END
END
IF (@length = 3)
BEGIN
SELECT @last_char = substring(@cur_route_id_1, @length, 1)
IF (@last_char IN ('0','1','2','3','4','5','6','7','8','9'))
BEGIN
SELECT @cur_route_id_1 = ' ' + @cur_route_id_1
END
ELSE
BEGIN
SELECT @cur_route_id_1 = ' ' + @cur_route_id_1
END
END
IF (@length = 4)
BEGIN
SELECT @cur_route_id_1 = ' ' + @cur_route_id_1
END
UPDATE measure
SET route_id_1 = @cur_route_id_1
WHERE sysrecno = @cur_sysrecno
SELECT @count = @count + 1
IF (@count%1000 = 0)
BEGIN
COMMIT transaction
BEGIN transaction
END
FETCH c_measure INTO @cur_sysrecno,@cur_route_id_1
END
COMMIT transaction
CLOSE c_measure
deallocate c_measure
-
More q: version of SQL Server and the OSQL parameters you use?
Not sure why there would be a big difference, but I suspect there is so much time spent in the commit or holding in memory a 2 million row cursor that OSQL cannot handle. Put some debug in your code like printing the date/time after major statements like commit and see where most of the time are spent. You can use OSQL -o
Some unsolicited suggestions on my part: -change the cursor type and use a FOR UPDATE cursor, increase the commit counters, totally rewriting the code to a single UPDATE statement with CASE clauses.
Regards
JA
-
Hello Ja,
SQL Server 2000 sp3a.
No specific parameter for OSQL.
I cannot see why the difference between OSQL and ISQLW would be because of the commiting work every 1000 records. It should be treated similarly by those two tools. No?
My goal here is not to have suggestion on how to rewrite this script but my goal is to learn why this huge difference between ISQLW and OSQL.
Its really unusual,
Best regards,
Carl
-
But you suggestion on putting debugging informations (datetime) in the script could lead me to an answer.
Thank's
Carl
-
Carl,
you can also use the -p parameter to give you statistics.
If you find anything, let us know.
Thanks,
JA
-
Hello Ja,
Changed the cursor type to use the FOR UPDATE close. It solved the peformance issue with OSQL
.But I still don't understand what makes osql behaves so badly with the original script while the same script behaves very good with ISQLW (Query Analyzer).
Still searching...
Best regards,
Carl
-
Carl,
Good to know you took one of my suggestion. Maybe it has to do with memory allocation at the command prompt. Good luck in your search. Maybe you can contact MS support for this.
Keep us posted.
JA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply