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)
 efficiently returning data best way -output param?

Author  Topic 

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-13 : 11:09:12
Hi,

I have a query I am attempting to write, and I'm trying to model it so that its brought back as efficiently as possible. I just need three main columns brought back, but I need another 8 computed values brought back as well. If I were to do this by just bringing back 1 resultset, it would be 11 columsn, with 8 columns of redundant data.

Is the best practice for this to use OUTPUT parameters? It seems to me it would make the most sense, I just want to get some opinions from those with expertise. I don't seem to see them used too often in most queries I see posted here, however that doesnt really mean anything I guess.

To help illustrate my data, I have this screenshot. (the data is beign passed via XML to a flash object for charting)



Does this look like a good structure to return the data ?
Anyone recommend any best practices on ways to populate the output params ? Should I run a SELECT for each variable ? (seems kinda expensive)


So far my query is looking like below :


Thanks very much for any feedback!!
mike123


CREATE PROCEDURE [dbo].[select_stats_myAccount_MonthlySummary]
(
@partnerID int,
@monthsAgo int,
--0 denotes current month so far, 1 = previous full month, 2 = 2 months previous full month etc
@inbound_total int OUTPUT,
@inbound_mean int OUTPUT,
@inbound_min int OUTPUT,
@inbound_max int OUTPUT,
@outbound_total int OUTPUT,
@outbound_mean int OUTPUT,
@outbound_min int OUTPUT,
@outbound_max int OUTPUT
)
AS SET NOCOUNT ON

--somehow populate all the OUTPUT params here

SELECT coalesce(Inbound.clickdate, Outbound.clickdate) as clickdate,
coalesce(Inbound.inbound_clicks_total, 0) as inbound_clicks_total,
coalesce(Outbound.outbound_clicks_total, 0) as outbound_clicks_total
from --Inbound
(SELECT dateadd(day, datediff(day, 0, clickDate), 0) as clickDate,
COUNT(clickID) AS inbound_clicks_total
FROM tblLinkSwap_ClickLog_InBound
WHERE clickDate >= dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo ,0)
AND clickDate < dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo +1,0)
GROUP BY dateadd(day, datediff(day, 0, clickDate), 0)) Inbound
full outer join --Outbound
(SELECT dateadd(day, datediff(day, 0, tblLinkSwap_ClickLog_OutBound.clickDate), 0) as clickDate,
COUNT(tblLinkSwap_ClickLog_OutBound.clickID) AS outbound_clicks_total
FROM tblLinkSwap_ClickLog_OutBound
JOIN [tblLinkSwap_OutBoundLinks] on tblLinkSwap_OutBoundLinks.linkID = tblLinkSwap_ClickLog_OutBound.linkID
WHERE tblLinkSwap_OutBoundLinks.partnerID = 1
AND tblLinkSwap_ClickLog_OutBound.clickDate >= dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo ,0)
AND tblLinkSwap_ClickLog_OutBound.clickDate < dateadd(month,datediff(month,@monthsAgo ,getdate())-@monthsAgo +1,0)
GROUP BY dateadd(day, datediff(day, 0, tblLinkSwap_ClickLog_OutBound.clickDate), 0)) Outbound
on Inbound.clickdate = Outbound.clickdate




GO

Kobojunkie
Starting Member

8 Posts
Posted - 2008-11-13 : 12:17:32
You can return two resultsets

One that returns the Non-Calculated fields
The Other that returns the calculated fields
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-13 : 12:23:55
quote:
Originally posted by Kobojunkie

You can return two resultsets

One that returns the Non-Calculated fields
The Other that returns the calculated fields



Are you saying I should write 2 seperate SPROCS , 1 for each purpose ?

Or are you saying I can bring back 2 result sets with 1 SPROC ?


Thanks!
mike123
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-13 : 13:27:41
Why don't you return the data as XML from the stored procedure directly?
See http://weblogs.sqlteam.com/peterl/archive/2008/11/06/Create-nested-hierachy-XML.aspx

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-11-13 : 13:31:29
Why are inbound and outbound two separate tables?


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-13 : 14:11:47
quote:
Originally posted by Peso

Why don't you return the data as XML from the stored procedure directly?
See http://weblogs.sqlteam.com/peterl/archive/2008/11/06/Create-nested-hierachy-XML.aspx

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


Hey Peso,

This seems like a really good idea and I think I can make use of this in certain parts of my application. I am wondering tho because of the way this flash application that I am workign with is designed, the data passed to it has to look like this. From what I can see formatting it like this might be a pain in the ass.

I'm not sure if formatting it this way would be practical ?

Here is an example:

<chart>
<axis_category font="arial" bold="1" size="10" color="000000" alpha="50" skip="1" />
<axis_ticks value_ticks="" category_ticks="1" major_thickness="2" minor_thickness="1" minor_count="3" major_color="000000" minor_color="888888" position="outside" />
<axis_value font="arial" bold="1" size="10" color="000000" alpha="50" steps="4" prefix="" suffix="" decimals="0" separator="" show_min="1" min="0" max="905.3" />
<chart_border color="000000" top_thickness="1" bottom_thickness="2" left_thickness="0" right_thickness="0" />
<chart_data>
<row>
<string></string>
<string>11-01</string>
<string>11-02</string>
<string>11-03</string>
<string>11-04</string>
<string>11-05</string>
<string>11-06</string>
<string>11-07</string>
<string>11-08</string>
<string>11-09</string>
<string>11-10</string>
<string>11-11</string>
<string>11-12</string>
</row>
<row>
<string>Incoming clicks you sent us (6,250)</string>
<number>381</number>
<number>380</number>
<number>388</number>
<number>429</number>
<number>435</number>
<number>502</number>
<number>534</number>
<number>624</number>
<number>513</number>
<number>777</number>
<number>693</number>
<number>594</number>
</row>
<row>
<string>Outgoing clicks we sent you (7,558)</string>
</row>
</chart_data>
<chart_grid_h alpha="5" color="0066FF" thickness="28" />
<chart_grid_v alpha="10" color="0066FF" thickness="1" />
<chart_rect x="63" y="50" width="740" height="220" positive_color="FFFFFF" positive_alpha="40" />
<chart_value prefix="Incoming: " position="cursor" size="12" color="000000" background_color="FFFFD0" alpha="100" />
<draw>
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="23" y="10" width="300" h_align="left">Nov 01, 2008 - Nov 12, 2008</text>
<image url="/flashcharts/charts.swf?library_path=/flashcharts/charts_library&php_source=/ajax/linkswap2/get_stats1?date=2008-11-01|2008-11-12" />
<rect x="65" y="305" width="12" height="12" fill_color="5a86fb" line_color="000000" />
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="80" y="302" h_align="left">Incoming clicks you sent us (6,250)</text>
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="320" h_align="left">Mean: 481</text>
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="335" h_align="left">Min: 380</text>
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="80" y="350" h_align="left">Max: 777</text>
<rect x="475" y="305" width="12" height="12" fill_color="f75b36" line_color="000000" />
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="14" x="490" y="302" h_align="left">Outgoing clicks we sent you (7,558)</text>
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="320" h_align="left">Mean: 581</text>
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="335" h_align="left">Min: 316</text>
<text color="000000" alpha="60" font="arial" rotation="0" bold="1" size="12" x="490" y="350" h_align="left">Max: 823</text>
</draw>
<legend_rect x="-100" y="-100" alpha="50" width="250" />
<legend_label layout="vertical" size="12" />
<series_color>
<value>5a86fb</value>
<value>000000</value>
</series_color>
<series_gap set_gap="30" />
Go to Top of Page

mike123
Master Smack Fu Yak Hacker

1462 Posts
Posted - 2008-11-13 : 14:13:54
quote:
Originally posted by Peso

Why are inbound and outbound two separate tables?


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


good question. The reason is because we track them slightly differently. "inbound" is tracked purely based on partnerID

"outbound" we actually track which "linkID" was clicked so we have 2 tables for outbound so we can relate the linkID to the partnerID, and just 1 for inbound.

Hope this makes sense.

Thanks! :)
mike123
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site