473,881 Members | 1,511 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Re-using a calculated sub-select field in a view?

RCS
I've been running into more and more complexity with an application, because
as time goes on - we need more and more high-level, rolled-up information.
And so I've created views, and views that use other views.. and the queries
are getting slower and slower.

This morning, I'm working on something like this:

select
<some columns>,
"calculatedcolu mn" = (select top 1 crap from stuff where
thingy='whatcha macallit')
from
someview

now, I realized that I need to really return "calculatedcolu mn" in a couple
other places in the select like this - well, this is what I WANT to do:
select
<some columns>,
calculatedcolum n = (select top 1 crap from stuff where
thingy='whatcha macallit'),
otherfield = case SomeBit
when 1 then calculatedcolum n
else count(somefield )
end,
otherfield1 = case SomeotherBit
when 1 then calculatedcolum n
else sum(somefield)
end,
otherfield2 = case SomeBit2
when 1 then calculatedcolum n
else avg(somefield)
end,
otherfield3 = case SomeBit3
when 1 then calculatedcolum n
else count(somefield )
end,
from
someview

Point is, I CAN'T do that, so I have to re-run that sub-select for EACH of
these cases, and that is KILLING this stored procedure. It seems to me, that
if the database when and already got that field, for that row - I should be
able to re-use, rather than going back out additional times.

Is there a way to so this? Put simpler:

select
x = (select top 1 user_id from users),
bestUser=x,
smartestUser=x
from
Users

can I re-use "x" in that example. Thanks!
Jul 23 '05 #1
6 2806
RCS (rs****@gmail.c om) writes:
Is there a way to so this? Put simpler:

select
x = (select top 1 user_id from users),
bestUser=x,
smartestUser=x
from
Users

can I re-use "x" in that example. Thanks!


Maybe. It depends a little what is in that subquery. If it's uncorrelated
to the main query, you could to:

SELECT x.x, bestUser = CASE WHEN ... THEN x ELSE NULL END, ...
FROM users
CROSS JOIN (SELECT TOP 1 user_id FROM users ORDER BY somecol) AS x

Here I'm using a derived table. A derived table is kind of a temp table
in the middle of the query. However, it is never materialized, and SQL
Server may not even compute the table as such, but can recast computation
order as result is the same.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
RCS
Thanks for the response!

In this particular case, the general gist is that this is for tracking
scores for tests.. the outer select is for general information (module name,
etc) and the subselects are tallying up total users, how many passed, how
many failed.. so in psuedo sql, it's something like this (this is very, very
over-simplied - but hopefully is clear):

select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = (select count(distinct userid) from modulescores where
moduleid=m.modu leid and didpass=1)
from module as m

but it gets more complicated, I have to add some logic in for several fields
that say "if the module is a training module, then return back 'totalusers'
becuase even just finishing a practice module, should be considered
'passing'". For example.. So what I am doing now (using the same example
above) is:

select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = case moduleistrainin g
when 1 then (select count(*) from moduleusers where moduleid =
m.moduleid) -- this is the same as "totalusers " above
else (select count(distinct userid) from modulescores where
moduleid=m.modu leid and didpass=1)
end
from module as m

But that "(select count(*) from moduleusers where moduleid = m.moduleid)"
(again, as a simple example) is in real life, a complex join, to a couple of
views and is slow - and I have to keep re-using it in other select fields.

I've never had a use for, or maybe never understood the "cross join"
statment - and I'm not clear on how you mean to use it below. Could you
elaborate?? Thanks again!!
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
RCS (rs****@gmail.c om) writes:
Is there a way to so this? Put simpler:

select
x = (select top 1 user_id from users),
bestUser=x,
smartestUser=x
from
Users

can I re-use "x" in that example. Thanks!


Maybe. It depends a little what is in that subquery. If it's uncorrelated
to the main query, you could to:

SELECT x.x, bestUser = CASE WHEN ... THEN x ELSE NULL END, ...
FROM users
CROSS JOIN (SELECT TOP 1 user_id FROM users ORDER BY somecol) AS x

Here I'm using a derived table. A derived table is kind of a temp table
in the middle of the query. However, it is never materialized, and SQL
Server may not even compute the table as such, but can recast computation
order as result is the same.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #3
RCS (rs****@gmail.c om) writes:
select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = case moduleistrainin g
when 1 then (select count(*) from moduleusers where moduleid =
m.moduleid) -- this is the same as "totalusers " above
else (select count(distinct userid) from modulescores where
moduleid=m.modu leid and didpass=1)
end
from module as m

SELECT m.modulname, mu.totalusres,
passed = CASE m.moduleistrain g
WHEN 1 THEN mu.totalusrers
ELSE ms.didpass
END
FROM modules m
JOIN (SELECT moduleid, totalusers = COUNT(*)
FROM moduleusers
GROUP BY moduleid) mu ON m.moduleid = mu.moduleid
JOIN (SELECT moduleid, didpass = COUNT(DISTINCT userid
FROM modulescores
WHERE didpass = 1
GROUP BY moduleid) AS ms ON m.moduleid = ms.moduleid

See my preivious post for the explanation of the derived tables.
I've never had a use for, or maybe never understood the "cross join"
statment - and I'm not clear on how you mean to use it below. Could you
elaborate?? Thanks again!!


A cross join gives all possible combinations; this is the famous
"carteisan product". I used a cross join this case because that was
how I could translate your very loose example.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
RCS
Ah-HA!!! Very nice - thanks very much!!!

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
RCS (rs****@gmail.c om) writes:
select
modulename,
totalusers = (select count(*) from moduleusers where moduleid =
m.moduleid),
passed = case moduleistrainin g
when 1 then (select count(*) from moduleusers where moduleid =
m.moduleid) -- this is the same as "totalusers " above
else (select count(distinct userid) from modulescores where
moduleid=m.modu leid and didpass=1)
end
from module as m

SELECT m.modulname, mu.totalusres,
passed = CASE m.moduleistrain g
WHEN 1 THEN mu.totalusrers
ELSE ms.didpass
END
FROM modules m
JOIN (SELECT moduleid, totalusers = COUNT(*)
FROM moduleusers
GROUP BY moduleid) mu ON m.moduleid = mu.moduleid
JOIN (SELECT moduleid, didpass = COUNT(DISTINCT userid
FROM modulescores
WHERE didpass = 1
GROUP BY moduleid) AS ms ON m.moduleid = ms.moduleid

See my preivious post for the explanation of the derived tables.
I've never had a use for, or maybe never understood the "cross join"
statment - and I'm not clear on how you mean to use it below. Could you
elaborate?? Thanks again!!


A cross join gives all possible combinations; this is the famous
"carteisan product". I used a cross join this case because that was
how I could translate your very loose example.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #5
It's difficult to understand what you are really trying to do, but if
calculatedcolum n is going to be the same all the way through, DECLARE
and SET it upfront, i.e.
DECLARE @CalculatedColu mn int <or whatever>
SET @CalculatedColu mn = (select top 1 crap from stuff where
thingy='whatcha macallit')

then every time you need it again, just put @CalculatedColu mn instead
of the whole statement that figures it out.

Jul 23 '05 #6
A variable could be used if you know the result is going to be static.

I've also run into a problem similar to this when updating a table
with calculations, but when the calculations are based off the current
row it's updating the use of a variable gets thrown out the window :(
I had to keep reselecting the result, a correlated sub-query really,
but with my update statement I used it a hell of a lot and a simple
update statement grew to over 300 lines :)

But hey, with a cursor it took 40 mins with 1.5 million rows, now it
takes 5 mins :)

I'm going to take a look into that derived table example, might be
able to speed my statements up! Cheers!

Ian
"Ellen K" <ek*******@yaho o.com> wrote in message news:<11******* *************** @f14g2000cwb.go oglegroups.com> ...
It's difficult to understand what you are really trying to do, but if
calculatedcolum n is going to be the same all the way through, DECLARE
and SET it upfront, i.e.
DECLARE @CalculatedColu mn int <or whatever>
SET @CalculatedColu mn = (select top 1 crap from stuff where
thingy='whatcha macallit')

then every time you need it again, just put @CalculatedColu mn instead
of the whole statement that figures it out.

Jul 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4326
by: Nel | last post by:
I have a question related to the "security" issues posed by Globals ON. It is good programming technique IMO to initialise variables, even if it's just $foo = 0; $bar = ""; Surely it would be better to promote better programming than rely on PHP to compensate for lazy programming?
4
6435
by: Craig Bailey | last post by:
Anyone recommend a good script editor for Mac OS X? Just finished a 4-day PHP class in front of a Windows machine, and liked the editor we used. Don't recall the name, but it gave line numbers as well as some color coding, etc. Having trouble finding the same in an editor that'll run on OS X. -- Floydian Slip(tm) - "Broadcasting from the dark side of the moon"
11
4022
by: James | last post by:
My form and results are on one page. If I use : if ($Company) { $query = "Select Company, Contact From tblworking Where ID = $Company Order By Company ASC"; }
4
18551
by: Alan Walkington | last post by:
Folks: How can I get an /exec'ed/ process to run in the background on an XP box? I have a monitor-like process which I am starting as 'exec("something.exe");' and, of course the exec function blocks until something.exe terminates. Just what I /don't/ want. (Wouldn't an & be nice here! Sigh) I need something.exe to disconnect and run in the background while I
1
3708
by: John Ryan | last post by:
What PHP code would I use to check if submitted sites to my directory actually exist?? I want to use something that can return the server code to me, ie HTTP 300 OK, or whatever. Can I do this with sockets??
8
4423
by: Beowulf | last post by:
Hi Guru's, I have a query regarding using PHP to maintain a user profiles list. I want to be able to have a form where users can fill in their profile info (Name, hobbies etc) and attach an image, which will upload the record to a mySql db so users can then either view all profiles or query.. I.e. show all males in UK, all femails over 35 etc. Now, I'm not asking for How to do this but more what would be the best way? I've looked at...
1
3835
by: Clarice Almeida Hughes | last post by:
tenho um index onde tenho o link pro arq css, como sao visualizados pelo include todas as paginas aderem ao css linkado no index. so q eu preciso de alguns links com outras cores no css, o q devo fazer?
2
5319
by: JW | last post by:
I wanted have this as part of a flood control script: <? echo ("Flood control in place - please wait " . $floodinterval . " seconds between postings."); sleep(5); // go back two pages echo "<script>window.history.go(-2);</script>"; exit; ?>
2
3348
by: Frans Schmidt | last post by:
I want to make a new database with several tables, so I did the following: <?php CREATE DATABASE bedrijf; CREATE TABLE werknemers (voornaam varchar(15), achternaam varchar(20), leeftijd tinyint(3), adres varchar(30), woonplaats varchar(20),
2
51403
by: sky2070 | last post by:
Parse error: parse error, unexpected T_OBJECT_OPERATOR, expecting ')' in c:\inetpub\wwwroot\session.php on line 19 can anyone tell me what is wrong with this code??? <? // Define the Session class class Session { // Define the properties:
0
9929
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
11102
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10818
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10403
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7112
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5978
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3225
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.