SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Which do you suggest?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jonasalbert20
Constraint Violating Yak Guru

Philippines
300 Posts

Posted - 11/08/2007 :  03:49:27  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
Good day to all, I have a question which do you suggest is much better a view or a function? I have a transaction data around 12M of rows already. Left joining it around 6 table to get all the necessary information. The other two tables have 5M-6M of datas. I put it on a view naming it as vwCompanyLedger.


view: vwCompanyLedger
select t1.col1
      ,t2.col2
      ,t3.col2
      ,t4.col2
      ,t5.col2
      ,t6.col2
      ,t.*
from tbltransactions as t
left join tbl1 as t1 on t1.id = t.id
left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id
left join tbl4 as t4 on t4.id = t.id
left join tbl5 as t5 on t5.id = t.id
left join tbl6 as t6 on t6.id = t.id



Regarding my function here's my DDL:


CREATE FUNCTION dbo.fnCompanyLedger(
 @From as datetime
,@To as datetime
,@CompanyID as numeric(18,0)
) RETURNS @List TABLE 
(
	Id int identity(1,1),
	col1 varchar(200),
	col2 varchar(200),
	col3 varchar(200),
	col4 varchar(200),
	col5 varchar(200),
	col6 varchar(200),
	trans7 varchar(200),
	trans8 varchar(200),
	trans9 varchar(200),
	trans10 varchar(200),
	trans11 varchar(200),
	trans12 varchar(200),
	trans13 varchar(200),
	trans14 varchar(200),
	transID as numeric(18),
	CompanyID as numeric(18)
)

BEGIN

insert into @List (col1, col2, col3, col4, col5, col16, 
		   trans7, trans8, trans9, trans10, 
		   trans11, trans12, trans13, trans14, transID, CompanyID)
select t1.col1
      ,t2.col2
      ,t3.col2
      ,t4.col2
      ,t5.col2
      ,t6.col2
      ,t.trans7, t.trans8, t.trans9, t.trans10, 
      ,t.trans11, t.trans12, t.trans13, t.trans14, transID, t.CompanyID 
from tbltransactions as t
left join tbl1 as t1 on t1.id = t.id
left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id
left join tbl4 as t4 on t4.id = t.id
left join tbl5 as t5 on t5.id = t.id
left join tbl6 as t6 on t6.id = t.id
WHERE dtFrom >= @From and dtTo <= @To
  and CompanyID = @CompanyID
RETURN

END



from that function and view, most of the report acquisition of my client involves in using that view or function. Say for instance joining it again in two(2) tables around 2-3M of rows each.

Example 2: Using the view


SELECT l.*, p.paymentDate, o.orderDate
FROM vwCompanyLedger as l
left join tblpayments as p on p.id =  l.col1
left join tblorders as o on p.id = l.col2
WHERE l.dtFrom >= @From and l.dtTo <= @To
  and l.CompanyID = @CompanyID



Example 2: Using the function


SELECT l.*, p.paymentDate, o.orderDate
FROM dbo.fnCompanyLedger(@From, @To, @CompanyID) as l
left join tblpayments as p on p.id =  l.col1
left join tblorders as o on p.id = l.col2



Which is better and much faster? example 1 or 2?
Thanks in advance.






Want Philippines to become 1st World COuntry? Go for World War 3...

Edited by - jonasalbert20 on 11/08/2007 04:30:08

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 11/08/2007 :  04:26:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why are you joining tbl1 four times on the same columns?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 11/08/2007 :  04:29:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is an equivalent (I think)
select		t1.col1,
		t2.col2,
		t3.col2,
		t1.col2,
		t1.col2,
		t1.col2
from		tbltransactions as t
left join	tbl1 as t1 on t1.id = t.id
left join	tbl2 as t2 on t2.id = t1.id
left join	tbl3 as t3 on t3.id = t1.id



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

Philippines
300 Posts

Posted - 11/08/2007 :  04:30:57  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
Sorry Peso. I edit my post. please look at it. Thanks

Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30241 Posts

Posted - 11/08/2007 :  04:38:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are these also typos?

left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

Philippines
300 Posts

Posted - 11/08/2007 :  06:56:16  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
quote:
Originally posted by Peso

Are these also typos?

left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id



E 12°55'05.25"
N 56°04'39.16"




Nope Peso. thanks for the response. Any suggestion from here???




Want Philippines to become 1st World COuntry? Go for World War 3...

Edited by - jonasalbert20 on 11/13/2007 02:26:49
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

Philippines
300 Posts

Posted - 11/15/2007 :  00:36:05  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
quote:
Originally posted by jonasalbert20

quote:
Originally posted by Peso

Are these also typos?

left join tbl2 as t2 on t2.id = t1.id
left join tbl3 as t3 on t3.id = t1.id



E 12°55'05.25"
N 56°04'39.16"




Nope Peso. thanks for the response. Any suggestion from here???




Want Philippines to become 1st World COuntry? Go for World War 3...



Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/15/2007 :  01:24:38  Show Profile  Visit Merkin's Homepage  Reply with Quote
quote:
Originally posted by jonasalbert20

Which is better and much faster? example 1 or 2?
Thanks in advance.





Why don't you run them and see which one finishes first. I'm 99% sure that will be the fastest method.


Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

Philippines
300 Posts

Posted - 11/16/2007 :  04:46:12  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
How about which is better in other aspects? advantage and advantage?
ex: performance and processing.

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/16/2007 :  07:29:09  Show Profile  Visit Merkin's Homepage  Reply with Quote
WTF

Why don't you test both methods. That will show which is the quickest, and which uses more CPU, which uses more IO etc.
It should be pretty black and white, what definition of "better" won't that cover ?


Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

Philippines
300 Posts

Posted - 11/16/2007 :  07:33:44  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
Any other comments and suggestion aside from this guy here?

For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/16/2007 :  07:45:01  Show Profile  Visit Merkin's Homepage  Reply with Quote
Is this your version of "Extreme Programming" ? Writing code and refusing to test it and hoping someone on the internet will tell you it's good ?

Because that is pretty extreme.


Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

Philippines
300 Posts

Posted - 11/16/2007 :  08:01:31  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
Be polite... Is that what you have learned from grade school?

Any way thanks for the comment.

Comments and suggestions in most polite manner is greatly appreciated.

Thanks in advance.






For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 11/16/2007 :  17:46:43  Show Profile  Visit Merkin's Homepage  Reply with Quote
OK.
That comment was a little snarky, I posted it late at night.
I'll rephrase.

Could you please explain why you are unable to test this code yourself, given that you are the only one with access to the exact set of data and hardware that it will run on.


Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson
Go to Top of Page

jonasalbert20
Constraint Violating Yak Guru

Philippines
300 Posts

Posted - 11/16/2007 :  18:30:15  Show Profile  Send jonasalbert20 a Yahoo! Message  Reply with Quote
Thanks.


This is the specs of my laptop: http://www.sharp.co.jp/mebius/products/pccs50l/index.html

I'm running my query on it. My client instructed me to hide all necessary calculations to any users including myself even in the server. White papers, including the source code of the stored procedure should be submitted to him also.


Server specs is this.
http://www-132.ibm.com/webapp/wcs/stores/servlet/ProductDisplay?productId=4611686018425486597&storeId=1&langId=-1&categoryId=4611686018425199905&dualCurrId=73&catalogId=-840


I'm wondering what would be the performance when I deploy it on the server?





For fast result follow this...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Want Philippines to become 1st World COuntry? Go for World War 3...

Edited by - jonasalbert20 on 11/17/2007 01:21:18
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17632 Posts

Posted - 11/17/2007 :  01:25:56  Show Profile  Reply with Quote
What Merkin means is, you have to test it yourself. We don't have the same laptop and server that you are having and we are not going to buy one just to test the performance of your query for you.

Well, if you are willing to pay for it, that's diff story

quote:
I'm wondering what would be the performance when I deploy it on the server?

There is only one way to find out. And you know the answer to that.


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000