Rewriting left joins

Hello,

I am working on a query that has 11 left join statements, some are hitting against reference data that has a small amount of records, whereas others not so small. From a performance standpoint, should I look at rewriting this query, and how would I do so? What is an alternative to left joins; any examples anyone has?

Thanks.

The alternative to a join is a subquery. Google "Join or subquery" for information in which performs better. Loads of different opinions, but it would appear that only testingyourquery inyourenvironment will produce the right answer foryou.|||

bmains:

From a performance standpoint, should I look at rewriting this query

I would say that it depends on whether your query is performing poorly or not. I wouldn't touch it if it isn't broken

Rewriting Insert Statements

Hi Friends,
I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table.
I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)

INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)Maybe something along the lines of

SELECT 'OVERALL' AS [EName], trendmonth AS TrendMonth, SeverityLevel , Sum([Count])
FROM dbo.tbl_Ticket
group by EName, Trendmonth, SeverityLevel|||Works Great!! Thank you very much.

Re-use SqlCommand object

Is it ok to re-use a SqlCommand object? In a method, I am executing 2 separate parameterized sql statements. Before I run the second, I will clear the command objects parameters.(command.parameters.clear()) I'm just checking to see if it is good coding practice or not.

thanks,

SC

Half the reason command objects exist actualy. Go right ahead!|||

I think it's OK to re-use a SqlCommand object, if you can make sure to finish using all objects associated with the SqlCommand before reusing it. However I won't suggest to reuse a SqlCommand for multiple purpose, it's better to keep each SqlCommand for single task and Dispose the objects after using it.

Returning Values with 2 different SELECT statements?

Hi there,

just want to find out if it's possible to extract 2 different values with 1 select statement?

I need to extract values from a table, according to a specific condition...

eg: to extract values from tableA where "office_name" = "o_id" from tableB, where tableA is the one with the primary key... and then select the office from tableA where the "officeid"=0, and populate that in the same result set...

so that it can be populated in a <asp:DropDownList> control, and then be bound to the control based on the DataValueField, and DataTextField

is it possible, or not?

thx

SJB

Hi,

Do you want more than one row, or simply only 1 ?

If you are ok with multiple rows you can just do a UNION between the single queries.

SELECT SomeName
FROM SomeTable
INNER JOIN <YourJoin>
UNION
SELECT SomeName
FROM SomeOtherTable
WHERE <condition>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||thanks, I'll have look into it, I haven't tried in, but I will have a look, and see what happens... thx

returning values from sp_executesql statements

I'm trying to return a set the value of a variable with the output from a sp_executesql statement, but I'm not sure how to do it. Basically, what I want to do is:

set @.sql = 'select time from ' + @.tablename + ' where id = ' + @.int

How do I go about doing this?I don't understand why do you need sp_executesql for that
You can use a simple SQL statement like:

select @.return_value = time from your_table where id = @.int

or

select @.return_value = (select time from your_table where id = @.int)

Originally posted by dez182
I'm trying to return a set the value of a variable with the output from a sp_executesql statement, but I'm not sure how to do it. Basically, what I want to do is:

set @.sql = 'select time from ' + @.tablename + ' where id = ' + @.int

How do I go about doing this?|||because the table name that I'm selecting from need to change depending on user input.|||Hi try this.

DECLARE @.ssql varchar(255)
DECLARE @.tablename varchar(40)
DECLARE @.int int

SELECT @.tablename = 'sometable'
SELECT @.int = 5
SELECT @.ssql = 'select time from ' + @.tablename
SELECT @.ssql = @.ssql + ' where id = ' + CONVERT(VARCHAR(16),@.int)

EXEC(@.ssql)

Returning rows with NULL using WHERE statements

Hi All

I have only just started using SQL Server as part of a new job, and have come across something while using Select statements. When you do a query using the Where clause eg. Where city = 'London' it returns results for just London, if you then do the same but using Where city <> 'London' again the results are obvious, but records where the city field is NULL do not appear in either? I would have assumed them to appear as part of the second query...

As I said, this is probably a daft question but if anyone can shed some light on it, it'd be much appreciated.

take a look here http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html

you need to do Where city <> 'London' or city is null

because null compared to anything is unknown

even null compared to null is unknown take a look at this

declare @.i int,@.i2 int
select @.i ,@.i2
if @.i = @.i2
select 'yes'
else
select 'no'

assuming ansi nulls is on (which is the default and you should leave it like that)

if it is set to off you will get no as a result

SET ANSI_NULLS OFF
go
declare @.i int,@.i2 int
select @.i ,@.i2
if @.i = @.i2
select 'yes'
else
select 'no'
go

SET ANSI_NULLS ON

GO

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||That's great - thanks very much!

Returning recordset that includes a DateDiff field in hh:mm:ss format?

Hi, not too swift with anything other than simple SQL statements, so
I'm looking for some help.

Using SQL Server 2000 with this stored proc:

(@.varCust varchar(50))

AS
SET NOCOUNT ON

SELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,
d.WIdoc,
d.Start, d.StartUser, d.Finish, d.FinishUser
FROM tblWorkOrder w, tblDocs d
WHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder AND
d.Customer = @.varCust
GO

I'm trying to get a complete dataset so I can simply apply it as the
datasource to a datagrid in asp.net. I need to include a 'TimeSpan'
column that is the difference between d.Start and d.Finish. I also
need it to present in hh:mm:ss format in the datagrid column. (A) is
it possible to do this within the stored proc, and (B) how would "I"
do that?

Thanks!
Kathy[posted and mailed, please reply in news]

KathyB (KathyBurke40@.attbi.com) writes:
> SELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,
> d.WIdoc,
> d.Start, d.StartUser, d.Finish, d.FinishUser
> FROM tblWorkOrder w, tblDocs d
> WHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder AND
> d.Customer = @.varCust
> GO
> I'm trying to get a complete dataset so I can simply apply it as the
> datasource to a datagrid in asp.net. I need to include a 'TimeSpan'
> column that is the difference between d.Start and d.Finish. I also
> need it to present in hh:mm:ss format in the datagrid column. (A) is
> it possible to do this within the stored proc, and (B) how would "I"
> do that?

See this example:

declare @.a datetime, @.b datetime
select @.a = '2003-12-23 10:55:12',
@.b = '2003-12-23 21:45:09'
select convert(char(10),
dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.

Would you please tell me what this line does:

dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

Also, not to be totally gready, but how do I fit this into my return
dataset as a column?

thanks again for answering.

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Erland, this works GREAT!!!! Thanks so much and happy holidays!

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Kathy Burke (kathyburke40@.attbi.com) writes:
> Would you please tell me what this line does:
> dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

The complete expression was:

select convert(char(10),
dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

I encourage you look up the convert, dateadd and datediff cuntions
in Books Online. You find Convert under the top Cast and Convert.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, I did look it up but still didn't understand the 19000101 (other
than it is the date 01/01/1900). I found that the 108 is the format
code, etc.

Thanks again...it saves me a lot of time doing this is the query
results.

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Kathy Burke (kathyburke40@.attbi.com) writes:
> Erland, I did look it up but still didn't understand the 19000101 (other
> than it is the date 01/01/1900). I found that the 108 is the format
> code, etc.

You can replace 1900-01-01 with any date. I could also have left an
empty string - which would be the same as 1900-01-01 thanks to the
default rules for datetime literals. The important for the example is
that we use a datetime value of which the time portion is midnight.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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