Running the same subquery multiple times

Hi,

I was wondering if this can be done...

I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?

E.g.
I have a table with 2 cols - amount, date.

SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)

Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.

As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?

This is only a basic example, what I'm trying to do will invovle a lot more calcultions.

Hope someone can help,
Thanks,
Stuartuse a VIEW

:)|||--Run this in a query analyzer or sp
Declare @.Total_Amount numeric (9)
Declare @.closing_amount numeric (9)

Set @.Total_Amount = (SELECT sum(total_amount) FROM table1 GROUP BY month(date))

--Make sure you only return 1 record here
Set @.closing_Amount = (SELECT closing_amount FROM table1 Where Column_Value = my_Value GROUP BY month(date))

print @.Total_Amount
print @.closing_Amount|||db0, i don't believe you can assign a column of values to a variable|||What I did eventually was use a whole load of user-defined functions. Some of them get called multiple times. but it seems quick enough.|||and the reason you chose not to use a VIEW was... ?

Running the same query on multiple servers

Hi, I periodically run the same query on multiple servers. Currently I
do this via query analyzer and would like to cut down the time it takes
to switch servers.
Is there a tool I can configure multiple connections on and run the same
query on selected servers.
Thanks
Dilan
create linked servers and run the queries as
select ... from servername.databasename.objectname
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT Community Leader]
SQL Server Technologist
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||Dandy Weyn [Dandyman] wrote:
> create linked servers and run the queries as
> select ... from servername.databasename.objectname
>
Thanks. I neglected to mention that the statements are always almost
updates to existing stored procedures and are supplied by our vendor as
patches. I received them as attachments in emails and have to save them
and then run them on about 8 servers.
Needless to say I am looking to click and go...
|||You can either use DMO or oSql to do this pretty easily. Both methods allow
you to easily connect to another server and issue the same commands. You do
have to write some code but it should only be a few lines and a google
search will most likely find some examples.
Andrew J. Kelly SQL MVP
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan
|||I tend to just use a batch file e.g. create 3 folders Output,Servers,Source
under a main folder (in my example it's DBADeploy). Create a batchfile as
below called deploy.bat in C:\DBADeploy
@.echo off
FOR /F %%f in (C:\DBADeploy\Servers\servers.txt) do
C:\DBADeploy\Source\Update.bat %%f
In the Servers folder create a file called servers.txt which is a list of
the target servers
In the Source folder create a file called update.sql with you code in it
(including USE database statements) and a batch file called Update.bat with
the contents below
@.echo off
set server=%1
set outputtemp=%1
set outputtemp=%outputtemp:\=_%
set outputfile="C:\DBADeploy\Output\%outputtemp%.txt"
osql -S %server% -d master -n -E -w 200 -h-1 -l 15 -i
"C:\DBADeploy\Source\update.sql" -o %outputfile%
echo Completed %1
Once that's setup then anything you want to deploy you can just stick in
update.sql and run deploy.bat and you're done. You can check the output for
each server in the Output folder.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dilan A" <dilan.a@.youtelus.net> wrote in message
news:up7hf.129920$S4.96172@.edtnps84...
> Hi, I periodically run the same query on multiple servers. Currently I do
> this via query analyzer and would like to cut down the time it takes to
> switch servers.
> Is there a tool I can configure multiple connections on and run the same
> query on selected servers.
> Thanks
> Dilan

Running stored procedure multiple times

I'm binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I'm using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this?

newSqlCommand = New SqlCommand("getDistinctValues", newConn)
newSqlCommand.CommandType = CommandType.StoredProcedure

Dim ownrParam As New SqlParameter("@.owner_id", SqlDbType.Int)
Dim colParam As New SqlParameter("@.column_name", SqlDbType.VarChar)
newSqlCommand.Parameters.Add(ownrParam)
newSqlCommand.Parameters.Add(colParam)

ownrParam.Value = OwnerID

colParam.Value = "Make"
newConn.Open()
ddlMake.DataSource = newSqlCommand.ExecuteReader()
ddlMake.DataTextField = "distinct_result"
ddlMake.DataBind()
newConn.Close()

colParam.Value = "Model"
newConn.Open()
ddlModel.DataSource = newSqlCommand.ExecuteReader()
ddlModel.DataTextField = "distinct_result"
ddlModel.DataBind()
newConn.Close()

and so on for 9 columns…

First of all, reusing the connection will increase your performance significantly.

|||

Hi

if you create a stored procedure returning multiple result sets and load these result sets into the DataSet, you will avoid multiple roundtrips to SQL server.

-yuriy

|||

Under ADO.NET, only the first Open opens the database, the rest pick up a connection from a pool as closing the database connection does not actually close the connection but returns it to a pool. You still open and close in your code, but what happens behind the scenes is slightly different from previous access methods such as ADO.

|||

Thanks guys.

Tatworth, I believe you are saying that I'm reusing the same connection by doing it this way. In that case, is it an acceptable and reasonably efficient way to do things?

I need the DISTINCT values for these columns, so I can't simply select all the values for all 9 columns at once, unless I'm prepared to eliminate repeated values in code. Is it possible for a single stored procedure to return 9 sets of distinct values?

|||

>Tatworth, I believe you are saying that I'm reusing the sameconnection by doing it this way. In that case, is it an acceptable andreasonably efficient way to do things? yes

> need the DISTINCT values for these columns, so I can't simplyselect all the values for all 9 columns at once, unless I'm prepared toeliminate repeated values in code. Is it possible for a single storedprocedure to return 9 sets of distinct values?
It is possible as a stored procedure can return multiple recordsets but not practical as ASP.NET can only read the first recordset without extra code - so keep doing it the way you are doing it now.

I will be unable to access this site for some days, so I suggest you mark this thread as closed and open a new thread if you have further queries.

|||

There are multiple approaches to handling the above depending on your situation, and the data involved.

Normally, I would use a sqldatasource (9 of them), and bind the dropdowns to the datasources, and evaluate from there if necessary. The SqlDatasource control is nice because you can quickly and easily add caching if necessary (If you think that the ownerid will be called multiple times in a short period of time and the results should always remain the same for each call, then it'd be very helpful).

However, since you've gone down the approach of handling it manually, the only thing that is going to really buy you anything (other than coding any caching yourself), is to return the results in a single trip from the database. One such method would be to do something like return a single resultset that has 2 values, one being the column name, the other the value (one of the distinct values) likes:
col1 val1

col1 val2

col1 val3

col2 val1

col2 val2

Then you can retrieve this resultset from the database into a dataset. With the dataset, you can then derive a view that has a filter clause (Like a WHERE clause), and bind to the dataview.

Another approach, although I have not personally tested this is to use the datareaders nextresult property. Like:

conn.open

dim dr as sqldatareader=cmd.executereader

{Your code to prep control 1 here}

control1.datasource=dr

control1.databind

{Ending stuff for control 1 here}

dr.nextresult

{Your code to prep control 2 here}

control2.datasource=dr

control2.databind

{Ending stuff for contorl 2 here}

dr.nextresult

...

This approach would be extremely easy to modify your current code into, as you would just make need to make a master stored procedure like:

CREATE PROCEDURE dbo.sp_TheMaster ( @.ownerid int) AS

EXEC sp_OriginalProcedure @.ownerid,'column1'

EXEC sp_OriginalProcedure @.ownerid,'column2'

...

That would return the 9 resultsets that you need, although I'm not sure of it's performance, but I suspect it would be better.

|||

Thanks for your detailed response Motley. I considered the sqldatasource – I think it would be possible to use a single datasource for all 9 queries – but I chose the RP option only because it seems tidier to me (less 'markup'). I'm assuming there would be no performance difference?

As time is an issue, and as it seems that what I'm doing is not disastrous in performance terms, I'll leave it and monitor it when the site is uploaded.

Thanks all for your input.