Totally different compile speed?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Totally different compile speed??

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-15 : 04:26:34

Set based approach

SELECT	m.st_code, m.st_desc, th.total_rm*th.forex_rate as total_rm, th.total_cn*th.forex_rate as total_cn, th.qtt_out - th.quantity as qty
FROM	st_mast m
		inner join
		(SELECT	st_code, t.trx_type, t.qtt_out, t.quantity, t.total_price, 
				CASE WHEN h.forex_rate = 0 then 1 else h.forex_rate END as forex_rate,
				CASE WHEN t.trx_type IN('CN','CNC','GRO') then 0 else t.total_price END as total_rm,
				CASE WHEN t.trx_type IN('CN','CNC','GRO') then t.total_price else 0 END as total_cn
		FROM	st_trx t 
				right outer join (
				SELECT	CASE WHEN trx_type in('DO','CDO','DOL') then do_no
						WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_no END as ref_no,
						CASE WHEN trx_type in('DO','CDO','DOL') then (CASE WHEN in_date IS NULL or in_date='' then do_date else in_date END)
						WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_date END as date,
						forex_rate,
						trx_type
				FROM	st_head)h on t.trnx_ref = h.ref_no and t.trx_type=h.trx_type
		WHERE	date>='2007-01-01 00:00:00.000' and date<='2007-12-31 00:00:00.000')th
		on m.st_code=th.st_code
WHERE	m.st_code>='' and m.st_code<='zzzzzzzzz'
ORDER BY m.st_code

Cursor based approach
[code][testkim]
AS
DECLARE @st_code VARCHAR(25)
DECLARE @st_codex VARCHAR(25)
DECLARE @st_desc VARCHAR(45)
DECLARE @trx_type VARCHAR(3)
DECLARE @qtt_out DECIMAL(21,8)
DECLARE @quantity DECIMAL(21,8)
DECLARE @total_price DECIMAL(21,8)
DECLARE @total_rm DECIMAL(21,8)
DECLARE @total_cn DECIMAL(21,8)

DECLARE @tov_total DECIMAL(21,8)
DECLARE @cn_total DECIMAL(21,8)
DECLARE @qty_total DECIMAL(21,8)

DECLARE @tov_ttotal DECIMAL(21,8)
DECLARE @cn_ttotal DECIMAL(21,8)
DECLARE @qty_ttotal DECIMAL(21,8)

DECLARE @tov_Gtotal DECIMAL(21,8)
DECLARE @cn_Gtotal DECIMAL(21,8)
DECLARE @qty_Gtotal DECIMAL(21,8)

DECLARE @x INT

DECLARE STK_TRX_CURSOR CURSOR FOR
SELECT m.st_code, m.st_desc, th.total_rm*th.forex_rate as total_rm, th.total_cn*th.forex_rate as total_cn, th.qtt_out - th.quantity as qty
FROM st_mast m
inner join
(SELECT st_code, t.trx_type, t.qtt_out, t.quantity, t.total_price,
CASE WHEN h.forex_rate = 0 then 1 else h.forex_rate END as forex_rate,
CASE WHEN t.trx_type IN('CN','CNC','GRO') then 0 else t.total_price END as total_rm,
CASE WHEN t.trx_type IN('CN','CNC','GRO') then t.total_price else 0 END as total_cn
FROM st_trx t
right outer join (
SELECT CASE WHEN trx_type in('DO','CDO','DOL') then do_no
WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_no END as ref_no,
CASE WHEN trx_type in('DO','CDO','DOL') then (CASE WHEN in_date IS NULL or in_date='' then do_date else in_date END)
WHEN trx_type in('INV','CS','CN','DN','POS','INC','CNC') then in_date END as date,
forex_rate,
trx_type
FROM st_head)h on t.trnx_ref = h.ref_no and t.trx_type=h.trx_type
WHERE date>='2007-01-01 00:00:00.000' and date<='2007-12-31 00:00:00.000')th
on m.st_code=th.st_code
WHERE m.st_code>='' and m.st_code<='zzzzzzzzz'
ORDER BY m.st_code

BEGIN

SET @x = 0
SET @tov_Gtotal = 0
SET @cn_Gtotal = 0
SET @qty_Gtotal = 0

SET @tov_ttotal = 0
SET @cn_ttotal = 0
SET @qty_ttotal = 0

OPEN STK_TRX_CURSOR

FETCH NEXT FROM STK_TRX_CURSOR
INTO @st_code, @st_desc, @tov_total, @cn_total, @qty_total

SET @st_codex = @st_code

WHILE @@FETCH_STATUS = 0
BEGIN

SET @tov_ttotal = @tov_ttotal + @tov_total
SET @cn_ttotal = @cn_ttotal + @cn_total
SET @qty_ttotal = @qty_ttotal + @qty_total

SET @tov_Gtotal = @tov_Gtotal + @tov_total
SET @cn_Gtotal = @cn_Gtotal + @cn_total
SET @qty_Gtotal = @qty_Gtotal + @qty_total
if @st_codex <> @st_code
BEGIN
SET @x = @x + 1

PRINT CAST(@x as char(5))+CAST(@st_desc as char(45))+'TOV'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@tov_ttotal) as char(14))
PRINT CAST('' as char(5))+CAST(@st_code as char(45))+'CNV'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@cn_ttotal) as char(14))
PRINT CAST('' as char(5))+CAST('' as char(45))+'QTY'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@qty_ttotal) as char(14))
PRINT ''

SET @st_codex = @st_code
SET @tov_ttotal = 0
SET @cn_ttotal = 0
SET @qty_ttotal = 0
END

FETCH NEXT FROM STK_TRX_CURSOR
INTO @st_code, @st_desc, @tov_total, @cn_total, @qty_total
END

CLOSE STK_TRX_CURSOR
DEALLOCATE STK_TRX_CURSOR

PRINT CAST('Grand total of report' as char(50))+'TOV'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@tov_Gtotal) as char(14))
PRINT CAST('' as char(5))+CAST('' as char(45))+'CNV'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@cn_Gtotal) as char(14))
PRINT CAST('' as char(5))+CAST('' as char(45))+'QTY'+CAST('' as char(100))+CAST(CONVERT(DECIMAL(14,2),@qty_Gtotal) as char(14))
END[code]

How come the cursor based still running until now without result T.T

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-15 : 05:32:26

Umh...because working on a row-by-row basis is *slooooooow* compared to set-based...?

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 05:56:11

If you want a full year, guaranteed, use this rewrite

~~WHERE date>='2007-01-01 00:00:00.000' and date<='2007-12-31 00:00:00.000')th~~

WHERE date >= '20070101' and date < '20080101') AS th

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-15 : 05:56:19

But it won slow until 100 times of set-based approach??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 06:09:12

No.
The setbased solution above will outperform the cursor-based approach every time.

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-15 : 21:40:02

ooo but if uses set based is more confusing for my level >"<

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 21:46:45

quote:
Originally posted by waterduck

ooo but if uses set based is more confusing for my level >"<

If you are working with any database, you have to think set based and write set based. Looping records in a database generally gives you slow performance.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-15 : 22:16:20

<--- still trying to learn about how to maximize query capablity, long long way to go

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 22:38:42

don't worry. . . take it step by step. First step, write in set based. You will have much better performance compare to cursor / looping method as you have already seen it for yourself.

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-15 : 23:18:10

howover in my comp i still need to loop out print 1 by 1 <- doesn't matter, im imporving my self xD
i have 1 question tat is if i add all my records to #temp den loop from #temp, will it be faster den i nested cursor??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 00:43:10

Why the need for PRINT?

If the SET-BASED code runs in a few seconds, there is no need for PRINT, right?
The only reason for PRINTing I can think of, is to have some kind of follow up for the slow CURSOR-code.

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 01:31:49

erm....the reason for print is...like...a senario when urs customer wan have a report in excel(heading, etc) how u going to make it?PRINT from cursor...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-16 : 01:56:04

excel ? read this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 02:34:27

So the prints are appearing in the status bar in Excel?
Why not just display "Importing..." in the status bar for a few seconds?

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 02:40:45

I think u misunderstood wat i wanna mean, my comp are displaying report whereby they must use(print syntax)
Example

ABC Company
Date From [2007/01/01] To [2007/12/31]
st_code                   st_desc                                       total_rm                                total_cn                                qty
------------------------- --------------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
                          SILVERSTONE KL-01 ATX CASING                  10953963.4900000000                     0.0000000000                            971.0000
>>                        >>                                            50.0000000000                           0.0000000000                            5.0000
?                         PRODUCT1525                                   24.0000000000                           0.0000000000                            2.0000
^-5%                                                                    -119.0440000000                         0.0000000000                            0.0000

After the example generated, they allow user to choose in which format(Excel, IE, adobe, notepad, etc) the report to be shown,
user then can modified the generated report(including the data) if they wan...

ps. the whole displayed date including those line(-) are also paste to the excel, IE, etc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 03:15:02

Let me see if I understand you correctly

1. Manually switch from Excel to SSSM or Query Analyzer
2. You run the stored procedure manually
3. Wait a very long time for stored procedure to finish the cursor code
4. You manually copy the printed output
5. Manually switch back to Excel
6. You manually paste the clipboard content to Excel

You did get the emphasis on manually, did you?
Do you actually believe the approach above is a better alternativa to the approach below?

1. Run a macro in Excel through a custom menu item. This is the only manual intervention in complete process.
2. The macro outputs the "headers" of report; ABC Company and the date range
3. The macro connects to the database and runs a stored procedure which has a set-based query which finish in a few seconds.
4. The result is returned to Excel via ADODB and the makro iterates the resultset and outputs to Excel.

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 03:21:42

The original work progress
~~1. Manually switch from Excel to SSSM or Query Analyzer~~
1. Run SSSM or Query Analyzer at developt software by us
2. You run the stored procedure manually at the developt software
3. Wait a very long time for stored procedure to finish the cursor code
~~4. You manually copy the printed output~~
4. The developt software copy the printed output to various of software(excel, ie, notepad, etc)
~~5. Manually switch back to Excel~~
~~6. You manually paste the clipboard content to Excel~~
The reason i kept on say various of format is because tats the oni bright side i could c =.=""

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 03:41:09

Can this approach be done by your developed software?

1. Click a menu item to start process
2. Build a string internally in app with two header lines (a few milliseconds).
3. Call the stored procedure
4. Append the result from stored procedure to the internal string with Recordset.GetString method (a few seconds)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 03:44:12

See GetString method here
http://www.w3schools.com/ado/met_rs_getstring.asp

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 04:13:19

Nop, i think can't...using clarion 6 as software develop....however not all the reporting can be done without cursor
example my fifo report lol

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 04:32:23

Why do you think a FIFO report canbot be done set-based?

Oh well, good luck.

E 12°55'05.63"
N 56°04'39.26"

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources