3 count queries vs UNION ALL

hi all,

i am not sure what would be more optimum for speed, i was wondering if one is better than the other.

i have 3 count queries that i can write as
SELECT COUNT(a) FROM ...
SELECT COUNT(b) FROM ...
SELECT COUNT(c) FROM ...

or i can have them in UNION ALL...

which would be faster three seperate queries or one UNION ALL query, any help?

thanks
Aleksdepends what your UNION ALL query does :)

if you mean this --select 'a' as tablename, count(*) as rows from tablea
union all select 'b', count(*) from tableb
union all select 'c', count(*) from tablecthen this would be faster|||can you explain why?|||let's pretend that you and i are sitting in new york, and i ask you to go and find out how many men, women, and children there are in los angeles

so you get on a plane, go to los angeles, then come back to new york and say "there are X men in los angeles"

then you get on a plane, go to los angeles, then come back to new york and say "there are Y women in los angeles"

finally, you get on a plane, go to los angeles, then come back to new york and say "there are Z children in los angeles"

compare the above to this --

you get on a plane, go to los angeles, then come back to new york and say "there are X men, Y women, and Z children in los angeles"

that's why ;)|||good enough...thanks|||What about other?|||Actually, aside from my poor atteempt at humor...If you did each count in an indeoendent thread and the launched them together so they went off asynchronously, then that might be faster

So, instead of just 1 agent (you flying back and forth in Rudy's example), there are 3 of you flying out at the same time, and instead of you having to doing the counting by yourself, you would have 2 other people doing the counting

but this sounds very much like a class assignement, and I'm sure the instructor wants the answer Rudy gave|||launch asynchronous threads in sql server?

please, do tell how this might be accomplished

i'm betting you lose more of your time (at $NNN/hour) setting this up than you will ever hope to save (at $0.000000000NNN/hour) in machine time over the life of the app|||launch asynchronous threads in sql server?

please, do tell how this might be accomplished

i'm betting you lose more of your time (at $NNN/hour) setting this up than you will ever hope to save (at $0.000000000NNN/hour) in machine time over the life of the app

Are you serious?|||yes, i am

are you?|||Are you saying that having 3 jobs launch at the same time is difficult?|||depends what your UNION ALL query does :)

if you mean this --select 'a' as tablename, count(*) as rows from tablea
union all select 'b', count(*) from tableb
union all select 'c', count(*) from tablecthen this would be fasterJust to muddy the waters further - faster than what?|||I think Ruday means faster than making 3 trips, even using the same spid, like OP suggested

SELECT COUNT(a) FROM ...
SELECT COUNT(b) FROM ...
SELECT COUNT(c) FROM ...

The better question is, what do they want to do with the results|||Are you saying that having 3 jobs launch at the same time is difficult?
it is if you don't know how to "launch" a "job" -- and then, presumably, collate the results, yes?

as i said before, i'm betting you lose more of your time (at $NNN/hour) setting this up than you will ever hope to save (at $0.000000000NNN/hour) in machine time over the life of the app|||With my eyes closed?

Again, it comes down to what they want to do with the results...|||i see things are interesting here...but here is the actual scenario
i am working on mobile device that has a db on an SD card....
currently, i am rewriting a form that someone has previously designed, prior to my work on the form, client complained that the form is very slow during load time. on the form, there is a drop down and 3 label fields. each field is a count. the drop down is a straight select all, however, the other three fields are coming from one SELECT query that does count. I wanted to know what would be faster for me, to do 3 different trips to the database or to use the existing SELECT UNION query...|||could we please see the existing SELECT UNION?|||SELECT 'AREA_COUNT' As ItemTitle, COUNT(AREA_ID) As ItemValue FROM AREA WHERE Area.LEADER_ID = {LEADER} GROUP BY Area.LEADER_ID

UNION

SELECT ju.STEP_CODE + '_COUNT' As ItemTitle, COUNT(distinct a.AREA_ID) As ItemValue FROM AREA a INNER JOIN JOB_UNIT ju ON a.AREA_ID = ju.AREA_ID AND ju.COMPLETE_DATE IS NULL INNER JOIN EMPLOYEE_WORK sw ON ju.JOB_UNIT_ID = sw.JOB_UNIT_ID AND sw.REASSIGN_REASON_CODE IS NULL WHERE Area.LEADER_ID = {LEADER} GROUP BY ju.STEP_CODE

UNION

SELECT 'UNASSIGNED_COUNT' As ItemTitle, COUNT(a.AREA_ID) As ItemValue FROM AREA a INNER JOIN USER_CREDENTIAL uc ON RECORD_ID = 0 INNER JOIN EMPLOYEE s ON uc.FDCA_EMPLOYEE_ID = s.FDCA_EMPLOYEE_ID INNER JOIN JOB_UNIT ju ON a.AREA_ID = ju.AREA_ID AND s.FLD_OPCODE = ju.FLD_OPCODE AND ju.COMPLETE_DATE IS NULL LEFT OUTER JOIN EMPLOYEE_WORK sw ON ju.JOB_UNIT_ID = sw.JOB_UNIT_ID AND sw.REASSIGN_REASON_CODE IS NULL WHERE sw.JOB_UNIT_ID IS NULL AND Area.LEADER_ID = {LEADER} GROUP BY Area.LEADER_ID

UNION

SELECT 'COMPLETE_COUNT' As ItemTitle, COUNT(a.AREA_ID) As ItemValue FROM AREA a INNER JOIN USER_CREDENTIAL uc ON RECORD_ID = 0 INNER JOIN EMPLOYEE s ON uc.FDCA_EMPLOYEE_ID = s.FDCA_EMPLOYEE_ID INNER JOIN JOB_UNIT ju ON a.AREA_ID = ju.AREA_ID AND s.FLD_OPCODE = ju.FLD_OPCODE AND ju.COMPLETE_DATE IS NOT NULL WHERE Area.LEADER_ID = {LEADER} GROUP BY Area.LEADER_ID|||more efficient would be to use UNION ALL so sql doesn't have to sort. but it will be a negligible difference. you'd notice it more if each query were returning many rows.

in general it's better to use UNION ALL instead of UNION if you can get away with it though.|||FYI when you do this --

... WHERE xxx.LEADER_ID = {LEADER} GROUP BY xxx.LEADER_ID

then if xxx.LEADER_ID is not mentioned in the SELECT, you can go ahead and eliminate the GROUP BY clause|||What kinda join is this?

FROM AREA a
INNER JOIN USER_CREDENTIAL uc
ON RECORD_ID = 0|||Yes remove to group by and convert the union's

What does the show plan say? Do you have any index or table scans?

SELECT 'AREA_COUNT' As ItemTitle
, COUNT(AREA_ID) As ItemValue
FROM AREA
WHERE Area.LEADER_ID = {LEADER}
--GROUP BY Area.LEADER_ID
UNION ALL
SELECT ju.STEP_CODE + '_COUNT' As ItemTitle
, COUNT(distinct a.AREA_ID) As ItemValue
FROM AREA a
INNER JOIN JOB_UNIT ju
ON a.AREA_ID = ju.AREA_ID
AND ju.COMPLETE_DATE IS NULL
INNER JOIN EMPLOYEE_WORK sw
ON ju.JOB_UNIT_ID = sw.JOB_UNIT_ID
AND sw.REASSIGN_REASON_CODE IS NULL
WHERE Area.LEADER_ID = {LEADER}
--GROUP BY ju.STEP_CODE
UNION ALL
SELECT 'UNASSIGNED_COUNT' As ItemTitle
, COUNT(a.AREA_ID) As ItemValue
FROM AREA a
INNER JOIN USER_CREDENTIAL uc
ON RECORD_ID = 0
INNER JOIN EMPLOYEE s
ON uc.FDCA_EMPLOYEE_ID = s.FDCA_EMPLOYEE_ID
INNER JOIN JOB_UNIT ju
ON a.AREA_ID = ju.AREA_ID
AND s.FLD_OPCODE = ju.FLD_OPCODE
AND ju.COMPLETE_DATE IS NULL
LEFT JOIN EMPLOYEE_WORK sw
ON ju.JOB_UNIT_ID = sw.JOB_UNIT_ID
AND sw.REASSIGN_REASON_CODE IS NULL
WHERE sw.JOB_UNIT_ID IS NULL
AND Area.LEADER_ID = {LEADER}
--GROUP BY Area.LEADER_ID
UNION ALL
SELECT 'COMPLETE_COUNT' As ItemTitle
, COUNT(a.AREA_ID) As ItemValue
FROM AREA a
INNER JOIN USER_CREDENTIAL uc
ON RECORD_ID = 0
INNER JOIN EMPLOYEE s
ON uc.FDCA_EMPLOYEE_ID = s.FDCA_EMPLOYEE_ID
INNER JOIN JOB_UNIT ju
ON a.AREA_ID = ju.AREA_ID
AND s.FLD_OPCODE = ju.FLD_OPCODE
AND ju.COMPLETE_DATE IS NOT NULL
WHERE Area.LEADER_ID = {LEADER}
--GROUP BY Area.LEADER_ID|||indexes are in place for all the primary fields in the tables...i am going to go with UNION ALL, after all there is a lot of data...thank you all|||I'm still at a loss to see what those group by's would do|||well, it seems that i have to use GROUP BY, I am getting an error when I try to compile...when i do use the group by clause the error goes away...

the error i am getting is Invalid Expression|||when i do use the group by clause the error goes away...one of the best. reasons. ever.

say, what about that interesting join that brett asked you about?|||well, it seems that i have to use GROUP BY, I am getting an error when I try to compile...when i do use the group by clause the error goes away...

the error i am getting is Invalid Expression

Well that's not a good reason...

Post the DDL of your tables

And the indexes (or is that indicies?)|||well, it seems that i have to use GROUP BY, I am getting an error when I try to compile...when i do use the group by clause the error goes away...

another way to achieve error free execution is to comment out the whole query.

it would also run faster that way.|||another way to achieve error free execution is to comment out the whole query.

it would also run faster that way.

U FUNNY

But don't give up the day job just yet|||I actually had a boss one time that fancied himself an expert in t-sql.

He once claimed he fixed a bug in one of his procs by removing a comment.

I don't work there anymore thankfully. the company has since popped.|||I just wonder what kind of meaning that queries has...look at this sample

USE Northwind

SELECT 'Label1' AS Source, COUNT(*)
FROM orders
GROUP BY CustomerID
UNION ALL
SELECT 'Label2' AS Source, COUNT(*)
FROM [order details]
GROUP BY ProductID

Kind of the same thing he''s doig...no?

2nd INSERT INTO statement in a Button click event

Hi everyone.
I am trying to write 2 INSERT INTO statements into a Button click event. Both statements go to the same database but differnet tables. The first statement works fine but the 2nd causes an error with the Try, Catch, Finally statement. When I remove the ExecuteNonQuery from the 2nd statement, the 2nd INSERT INTO statement fails to work. Any help would be brilliant. Thanks!

Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command

'Insert Guide changes into ChangesReport table in sql server
Me.SqlCommandThemeTest.Connection = Me.SqlConnection1

Dim Name As String
Dim values As String
Dim sSQL As String
Name = "Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText ContentManager"
values = "lstTheme, lstGuideName, lstGuidePage, lstPageType, lstChangeCategory, lstChangeFrom, txtChangeFrom, lstChangeTo, txtChangeTo, Label1"
SqlCommandThemeTest.CommandText = "INSERT INTO dbo.ChangesReport (Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager) VALUES (@.themeValue, @.guideValue, @.guidepageValue, @.pagetypeValue, @.changecategoryValue, @.changefromValue, @.changefromtextValue, @.changetoValue, @.changetotextValue, @.contentmanagerValue)"

SqlCommandThemeTest.Parameters.Add("@.themeValue", lstTheme.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.guideValue", lstGuideName.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.guidepageValue", lstGuidePage.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.pagetypeValue", lstPageType.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changecategoryValue", lstChangeCategory.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changefromValue", lstChangeFrom.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changefromtextValue", txtChangeFrom.Text)
SqlCommandThemeTest.Parameters.Add("@.changetoValue", lstChangeTo.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changetotextValue", txtChangeTo.Text)
SqlCommandThemeTest.Parameters.Add("@.contentmanagerValue", Label1.Text)

Try
Me.SqlConnection1.Open()
Me.SqlCommandThemeTest.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try

'Insert textbox to ChangeLogFrom ddl
Me.CmdDDLFromUpdate.Connection = Me.SqlConnection1

Name = "ChangeFromText"
values = "txtChangeFrom"
sSQL = "INSERT INTO dbo.Change Log From (ChangeLogFrom) VALUES (@.changelogfromValue)"
Me.CmdDDLFromUpdate.Parameters.Add("@.changelogfromValue", txtChangeFrom.Text)

Try
Me.SqlConnection1.Open()
Me.CmdDDLFromUpdate.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try

I have tried various different things with these 2 statements but can still only get the first to insert.

Any advice would be great. Thanks

|||

It's hard to tell since you've only cut and pasted certain parts of the code. For example, I don't see where you've set the commandtext of the second sqlcommand. I see the sSQL string being set, but that isn't the same thing.

You might also want the keep the sqlconnection open for both updates, no use in closing it then reopening it.

|||Why dont you pass all the parameters to a stored proc and do both the inserts there. That will save you a round trip and your code will be at one place.

2005: using SMO

Hello,
I am learning SQL Server 2005. I would like to write a procedure
making database backup using SMO. According to my book I should write
such code:

using Microsoft.SqlServer.Management.SMO;
public static void MakeBackup()
{
Server server = new Server("localhost");
Backup backup = new Backup();
backup.Action = BackupActionType.Database;
backup.BackupSetName = "Backup copy";
backup.BackupSetDescription = "Backup copy";
backup.Database = "DemoSQLServer";
backup.Devices.AddDevice("C:\\DemoSQLServer.bak",
DeviceType.File);
backup.SqlBackup(server);
}

The code cannot be compiled because of error:
The type or namespace name 'Management' does not exist in the
namespace 'Microsoft.SqlServer'.
Please help to correct the problem - I cannot find proper name of SMO
namespace. Is the code OK?
Thank you very much
/RAM/I tried adding references to Microsoft.SqlServer.ConnectionInfo and
Microsoft.SqlServer.Smo - and I could build assembly. But then I
cannot create assembly in SQL Server:

"An exception occured while executing a Transact statement or batch
(Microsoft.SqlServer.Express.ConnectionInfo).
Assembly 'microsoft.sqlserver.smo, version=9.0.242.0, culture=neutral,
publictoken=89845dcd8080cc91.' was not found in the SQL catalog.
(Microsoft SQL Server, Error: 6503)"

Could you help me to solve it...|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

I tried adding references to Microsoft.SqlServer.ConnectionInfo and
Microsoft.SqlServer.Smo - and I could build assembly. But then I
cannot create assembly in SQL Server:
>
"An exception occured while executing a Transact statement or batch
(Microsoft.SqlServer.Express.ConnectionInfo).
Assembly 'microsoft.sqlserver.smo, version=9.0.242.0, culture=neutral,
publictoken=89845dcd8080cc91.' was not found in the SQL catalog.
(Microsoft SQL Server, Error: 6503)"
>
Could you help me to solve it...

There is a smaller set of .Net Fx assemblies that are loaded by default
in SQL Server. Apparently Microsoft.SqlServer.Smo is not one of them.
You could create an assembly in your database for the DLL with CREATE
ASSEMBLY, but I suspect that there is a reason why this DLL is not
loaded. I don't think the intention is that SMO is to be called from
within SQL Server. (But I have stayed away from SMO, so I don't really
know for sure.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

2005 report designer and 2000 report server

Is it possible/ok to write a report with sql 2005 report designer and deploy
the report to a SQL 2000 Report Server?
--
moondaddy@.noemail.noemailHello George,
I understand that you'd like to design report in report designer in VS 2005
and deploy it to RS 2000. If I'm off-base, please let me know.
As far as I know, this feature is not supported. You could open/upgrade RS
2000 report project via RS 2005 report designer but you could deploy report
project of RS 2005 to 2000 server. This is because implmentation/connection
of RS 2005 is different from RS 2000. Also, the RDL schema has changed, and
the old server will not understand the new one.
Also, VS 2003 report designer can directly publish to a RS 2005 report
server.
If you have any questions or concerns on this, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||The report viewer control that ships with VS 2005 only works against RS 2005
reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Roberto Nunes" <RobertoNunes@.discussions.microsoft.com> wrote in message
news:12425E77-1244-4868-B27D-05986A5BDDDE@.microsoft.com...
> Hello Peter,
> I have an ASPNET 2.0 Application and I would like show SQL 2000 reports.
> Could I do use Report View control?
> "Peter Yang [MSFT]" wrote:
>> Hello George,
>> I understand that you'd like to design report in report designer in VS
>> 2005
>> and deploy it to RS 2000. If I'm off-base, please let me know.
>> As far as I know, this feature is not supported. You could open/upgrade
>> RS
>> 2000 report project via RS 2005 report designer but you could deploy
>> report
>> project of RS 2005 to 2000 server. This is because
>> implmentation/connection
>> of RS 2005 is different from RS 2000. Also, the RDL schema has changed,
>> and
>> the old server will not understand the new one.
>> Also, VS 2003 report designer can directly publish to a RS 2005 report
>> server.
>> If you have any questions or concerns on this, please feel free to let's
>> know. Thank you.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Community Support
>> ==================================================>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> ications
>> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent
>> issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each
>> follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Hello Peter,
I have an ASPNET 2.0 Application and I would like show SQL 2000 reports.
Could I do use Report View control?
"Peter Yang [MSFT]" wrote:
> Hello George,
> I understand that you'd like to design report in report designer in VS 2005
> and deploy it to RS 2000. If I'm off-base, please let me know.
> As far as I know, this feature is not supported. You could open/upgrade RS
> 2000 report project via RS 2005 report designer but you could deploy report
> project of RS 2005 to 2000 server. This is because implmentation/connection
> of RS 2005 is different from RS 2000. Also, the RDL schema has changed, and
> the old server will not understand the new one.
> Also, VS 2003 report designer can directly publish to a RS 2005 report
> server.
> If you have any questions or concerns on this, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>