Paging Performance

[dj4uk]

I'm aware that the PagedDataSource and Datagrid controls still receive all records matching a query before paging them. Obviously when there is a large result set this could impact seriously on performance.

I am currently developing a website that uses a PagedDataSource with a repeater that uses the following stored procedure to generate results:

CREATE  PROCEDURE SY_SearchProducts
(
	@BrandID	int,
	@CategoryID	int,
	@StyleID	int,
	@SizeID		int,
	@ColourID	int
)
AS

	DECLARE	@SQL		varchar(5000)

	SET @SQL = 		'SELECT		PD.ProductID, PD.ProductName, '
	SET @SQL = @SQL + 	'		PD.ProductDescription, PD.RRP, PD.Price, '
	SET @SQL = @SQL + 	'		PD.CreationDate, PD.LastModified, PD.Visible, '
	SET @SQL = @SQL + 	'		BD.BrandID, BD.BrandName, '
	SET @SQL = @SQL + 	'		BD.BrandAbbreviation, BD.Visible AS BrandVisible '
	SET @SQL = @SQL + 	'FROM		Product_Directory AS PD '
	SET @SQL = @SQL + 	'INNER JOIN	Brand_Directory AS BD '
	SET @SQL = @SQL + 	'ON 		PD.BrandID = BD.BrandID '
	SET @SQL = @SQL + 	'INNER JOIN	Product_Items AS PrI '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PrI.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Product_Stock AS PS '
	SET @SQL = @SQL + 	'ON		PrI.ItemID = PS.ItemID '
	SET @SQL = @SQL + 	'RIGHT JOIN	Product_Category AS PC '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PC.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Category_Directory AS CD '
	SET @SQL = @SQL + 	'ON		PC.CategoryID = CD.CategoryID '
	SET @SQL = @SQL + 	'RIGHT JOIN	Product_Style AS PSt '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PSt.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Style_Directory AS SD '
	SET @SQL = @SQL + 	'ON		PSt.StyleID = SD.StyleID '
	SET @SQL = @SQL + 	'WHERE		BD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PrI.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PS.StockAvailable > 0 '
	SET @SQL = @SQL + 	'AND		SD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		CD.Visible = 1 '
	
	IF @BrandID != 0
		SET @SQL = @SQL +	'AND		BD.BrandID = ' + CONVERT(varchar(10), @BrandID)

	IF @CategoryID != 0
		SET @SQL = @SQL +	'AND		CD.CategoryID = ' + CONVERT(varchar(10), @CategoryID)

	IF @StyleID != 0
		SET @SQL = @SQL +	'AND		SD.StyleID = ' + CONVERT(varchar(10), @StyleID)

	IF @SizeID != 0
		SET @SQL = @SQL +	'AND		PS.SizeID = ' + CONVERT(varchar(10), @SizeID)

	IF @ColourID != 0
		SET @SQL = @SQL +	'AND		PrI.ColourID = ' + CONVERT(varchar(10), @ColourID)

	SET @SQL = @SQL + 	'GROUP BY	PD.ProductID, PD.ProductName, '
	SET @SQL = @SQL + 	'		PD.ProductDescription, PD.RRP, PD.Price, '
	SET @SQL = @SQL + 	'		PD.CreationDate, PD.LastModified, PD.Visible, '
	SET @SQL = @SQL + 	'		BD.BrandID, BD.BrandName, '
	SET @SQL = @SQL + 	'		BD.BrandAbbreviation, BD.Visible '
	
	SET @SQL = @SQL + 	'ORDER BY	PD.Price ASC, BD.BrandName ASC, PD.ProductName ASC '
	EXEC(@SQL)
GO

I am estimating that this stored procedure could return up about 5000 records. Do I need to look at implementing paging within the stored procedure rather than in ASP.NET?

As the stored procedure uses dynamic SQL I'm not quite sure how to achieve this.

Any help on this or any other optimisation tips would be much appreciated.

DJ

[Magiaus]

Hellswraith showed me a trick. You dump the keys from the query into a #TempTable with an auto number then select again from the #tempTable with some math.

This is fairly simple. Just change the SELECT and make it fit your query.

ALTER PROCEDURE dbo.Contacts_GetContactsPageByType
(
@PageSize int,
@PageIndex int,
@Type varchar(200)
)
AS
 
CREATE TABLE #TempTable
(
ContactRowID int IDENTITY(1,1),
ContactKey char(36)
)
 
 
 
INSERT INTO #TempTable (ContactKey)
SELECT Guid
FROM Contacts
WHERE (Type = @Type)
AND EmailAddress != '[email protected]'
ORDER BY LastName, FirstName, MiddleName, EmailAddress
 
 
 
SELECT #TempTable.ContactRowID, Contacts.*
FROM #TempTable
INNER JOIN Contacts ON
Contacts.Guid = #TempTable.ContactKey
WHERE ContactRowID > @PageSize * @PageIndex
AND ContactRowID <= @PageSize * (@PageIndex+1)
ORDER BY #TempTable.ContactRowID
 
RETURN 
 

It took a day or so for me to get my head all the way around this, but it saves on page weight and bandwidth.

[Magiaus]

I also gotta say. That's a big a join a.

[dj4uk]

Magiaus thanks thats a possibility.

If I create a temporary table within dynamic SQL will be still be available to static SQL within the stored procedure?

Cheers

DJ

[Magiaus]

As far as I know the tabe is avaliable across the db based on the connection. As long as the connection is still open it should be there. What I don't know is how to get a ref to it. I'm not sure if you can do a #TempTable AS TableName or something.

In truth that method can be a little costly on the server side for SQL. I open a connection get a page and close a connection for each page. So #TempTable is made over and over again. I think it is still better than making a cache though, because the info sent to the user is less unless they view every page of data.

[jhermiz]

I'm aware that the PagedDataSource and Datagrid controls still receive all records matching a query before paging them. Obviously when there is a large result set this could impact seriously on performance.

I am currently developing a website that uses a PagedDataSource with a repeater that uses the following stored procedure to generate results:

CREATE  PROCEDURE SY_SearchProducts
(
	@BrandID	int,
	@CategoryID	int,
	@StyleID	int,
	@SizeID		int,
	@ColourID	int
)
AS

	DECLARE	@SQL		varchar(5000)

	SET @SQL = 		'SELECT		PD.ProductID, PD.ProductName, '
	SET @SQL = @SQL + 	'		PD.ProductDescription, PD.RRP, PD.Price, '
	SET @SQL = @SQL + 	'		PD.CreationDate, PD.LastModified, PD.Visible, '
	SET @SQL = @SQL + 	'		BD.BrandID, BD.BrandName, '
	SET @SQL = @SQL + 	'		BD.BrandAbbreviation, BD.Visible AS BrandVisible '
	SET @SQL = @SQL + 	'FROM		Product_Directory AS PD '
	SET @SQL = @SQL + 	'INNER JOIN	Brand_Directory AS BD '
	SET @SQL = @SQL + 	'ON 		PD.BrandID = BD.BrandID '
	SET @SQL = @SQL + 	'INNER JOIN	Product_Items AS PrI '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PrI.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Product_Stock AS PS '
	SET @SQL = @SQL + 	'ON		PrI.ItemID = PS.ItemID '
	SET @SQL = @SQL + 	'RIGHT JOIN	Product_Category AS PC '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PC.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Category_Directory AS CD '
	SET @SQL = @SQL + 	'ON		PC.CategoryID = CD.CategoryID '
	SET @SQL = @SQL + 	'RIGHT JOIN	Product_Style AS PSt '
	SET @SQL = @SQL + 	'ON		PD.ProductID = PSt.ProductID '
	SET @SQL = @SQL + 	'INNER JOIN	Style_Directory AS SD '
	SET @SQL = @SQL + 	'ON		PSt.StyleID = SD.StyleID '
	SET @SQL = @SQL + 	'WHERE		BD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PrI.Visible = 1 '
	SET @SQL = @SQL + 	'AND		PS.StockAvailable > 0 '
	SET @SQL = @SQL + 	'AND		SD.Visible = 1 '
	SET @SQL = @SQL + 	'AND		CD.Visible = 1 '
	
	IF @BrandID != 0
		SET @SQL = @SQL +	'AND		BD.BrandID = ' + CONVERT(varchar(10), @BrandID)

	IF @CategoryID != 0
		SET @SQL = @SQL +	'AND		CD.CategoryID = ' + CONVERT(varchar(10), @CategoryID)

	IF @StyleID != 0
		SET @SQL = @SQL +	'AND		SD.StyleID = ' + CONVERT(varchar(10), @StyleID)

	IF @SizeID != 0
		SET @SQL = @SQL +	'AND		PS.SizeID = ' + CONVERT(varchar(10), @SizeID)

	IF @ColourID != 0
		SET @SQL = @SQL +	'AND		PrI.ColourID = ' + CONVERT(varchar(10), @ColourID)

	SET @SQL = @SQL + 	'GROUP BY	PD.ProductID, PD.ProductName, '
	SET @SQL = @SQL + 	'		PD.ProductDescription, PD.RRP, PD.Price, '
	SET @SQL = @SQL + 	'		PD.CreationDate, PD.LastModified, PD.Visible, '
	SET @SQL = @SQL + 	'		BD.BrandID, BD.BrandName, '
	SET @SQL = @SQL + 	'		BD.BrandAbbreviation, BD.Visible '
	
	SET @SQL = @SQL + 	'ORDER BY	PD.Price ASC, BD.BrandName ASC, PD.ProductName ASC '
	EXEC(@SQL)
GO

I am estimating that this stored procedure could return up about 5000 records. Do I need to look at implementing paging within the stored procedure rather than in ASP.NET?

As the stored procedure uses dynamic SQL I'm not quite sure how to achieve this.

Any help on this or any other optimisation tips would be much appreciated.

DJ

No offense to you, however that is very very very very very...(times 100) bad code. You are writing a query that will:

1) Suffer from SQL Injection
2) Is slow
3) Uses dynamic SQL
4) Will need bandages
5) Wheres the boolean logic at ?????
...

I could go on...my advice to you, grab a SQL book and learn how to do it the proper way, or you're just asking for trouble.

[jhermiz]

BTW:

What's up with those right joins? You need to sit down and write this query correctly and logically. There is NO need for a right join.

Jon

[dj4uk]

Thanks for your replies.

I'm aware that dynamic SQL is a nightmare can you suggest a way I can remove the need for it? Basically the only reason for its use is to add to the WHERE when input parameters are available. i.e. when a parameter is 0 then it shouldn't be included in the WHERE if it is not 0 then it does. It's been suggested to me on another forum that the following might be better (avoiding the need for dynamic SQL). Would you agree?

SELECT * FROM TableName
WHERE FieldName = Case When @Param <> 0 Then @Param Else FieldName End

With regard to the RIGHT JOIN I'm pretty convinced it is needed with my table structure what do you think it should be replaced with?

No offence but I've posted on this forum to get help and suggestions. I'm not asking for someone to do it for me just gives some pointers. Suggesting grabbing a SQL book isn't much help

Cheers

DJ

[jhermiz]

Thanks for your replies.

I'm aware that dynamic SQL is a nightmare can you suggest a way I can remove the need for it? Basically the only reason for its use is to add to the WHERE when input parameters are available. i.e. when a parameter is 0 then it shouldn't be included in the WHERE if it is not 0 then it does. It's been suggested to me on another forum that the following might be better (avoiding the need for dynamic SQL). Would you agree?

SELECT * FROM TableName
WHERE FieldName = Case When @Param <> 0 Then @Param Else FieldName End

With regard to the RIGHT JOIN I'm pretty convinced it is needed with my table structure what do you think it should be replaced with?

No offence but I've posted on this forum to get help and suggestions. I'm not asking for someone to do it for me just gives some pointers. Suggesting grabbing a SQL book isn't much help

Cheers

DJ

Fair enough...

But did you know...

@CustomerID IS NULL OR CustomerID=@CustomerID

eliminates any need for any dynamic SQL. Think about it...if you call a stored procedure and you dont pass the CustomerID you get:

TRUE OR FALSE this evaluates to not using the CustomerID
but if you pass @CustomerID and it has a value you have:

False OR TRUE which filters the table. It's all a matter of boolean logic and your SQL will be much cleaner and easier to manage. Not to mention performance, and security.

Think about it...

PS: You have to set CustomerID as default to = NULL in case nothing is sent as the CustomerID (if something is sent dont worry it takes that value).

I know you dont have a CustomerID I am just using that as an example...

Jon

[jhermiz]

Fair enough...

But did you know...

@CustomerID IS NULL OR CustomerID=@CustomerID

eliminates any need for any dynamic SQL. Think about it...if you call a stored procedure and you dont pass the CustomerID you get:

TRUE OR FALSE this evaluates to not using the CustomerID
but if you pass @CustomerID and it has a value you have:

False OR TRUE which filters the table. It's all a matter of boolean logic and your SQL will be much cleaner and easier to manage. Not to mention performance, and security.

Think about it...

PS: You have to set CustomerID as default to = NULL in case nothing is sent as the CustomerID (if something is sent dont worry it takes that value).

I know you dont have a CustomerID I am just using that as an example...

Jon

Also any right join can be corrected by changing your logic and using a left join.

[dj4uk]

@CustomerID IS NULL OR CustomerID=@CustomerID

Clever! I would have thought that would work better then using a CASE statement I'll give it a go.

With regards to a LEFT and RIGHT JOIN aren't they exactly the same thing but just have the tables in the opposite order? I'm guessing using a RIGHT JOIN has some kind of performance penalty.

Thanks for all your help.

DJ

[jhermiz]

Here's just one example I have done...

CREATE PROCEDURE rsp_my_actions  (
						@ClientID integer, 
						@Originator varchar(50)=NULL,
						@IssueStatus integer=0,
						@CustomerID bigint=NULL,
						@SiteID bigint=NULL,
						@CommissionID bigint=NULL,
						@IssueTypeID bigint=NULL,
						@StartDate datetime=NULL,
						@EndDate datetime=NULL,
						@IssueID bigint=NULL
					   )
AS
BEGIN
SET NOCOUNT ON
SELECT    dbo.ActionItem.IssueID, dbo.ActionItem.ActionItemNumber, dbo.ActionItem.ActionItem, dbo.ActionItem.PriorityID, 
                      dbo.ActionItem.TargetDate, dbo.ActionItem.OpenDate, dbo.ActionItem.ClosedDate, dbo.Issue.Issue
FROM         dbo.ActionItem INNER JOIN
                      dbo.Issue ON dbo.ActionItem.IssueID = dbo.Issue.IssueID LEFT OUTER JOIN
                      dbo.Commission ON dbo.Issue.CommissionID = dbo.Commission.CommissionID LEFT OUTER JOIN
                      dbo.Customer ON dbo.Issue.CustomerID = dbo.Customer.CustomerID LEFT OUTER JOIN
                      dbo.Site ON dbo.Issue.SiteID = dbo.Site.SiteID LEFT OUTER JOIN
	         dbo.IssueType ON dbo.Issue.IssueTypeID = dbo.IssueType.IssueTypeID
WHERE     	
(dbo.Issue.ClientID=@ClientID)  AND (dbo.ActionItem.Accepted=1)
--perform some boolean alegebra to get the expected results
AND (@CustomerID IS NULL OR dbo.Issue.CustomerID=@CustomerID)
AND (@SiteID IS NULL OR dbo.Issue.SiteID=@SiteID)
AND (@CommissionID IS NULL OR dbo.Issue.CommissionID=@CommissionID)
AND (@IssueTypeID IS NULL OR dbo.Issue.IssueTypeID=@IssueTypeID)
--make sure orig is upper case.
AND (@Originator IS NULL OR (LOWER(dbo.ActionItem.ResponsiblePerson)=LOWER(@Originator) OR LOWER(dbo.ActionItem.OptionalResponsiblePerson)=LOWER(@Originator)))
--issues
AND (@IssueStatus <> 0 OR  dbo.ActionItem.ClosedDate IS NULL)
AND (@IssueStatus <> 1 OR dbo.ActionItem.ClosedDate IS NOT NULL)
AND (@IssueStatus <> 2 OR dbo.ActionItem.ClosedDate IS NOT NULL AND dbo.ActionItem.TargetDate < GetDate())
AND (@IssueStatus <> 3 OR (dbo.ActionItem.ClosedDate IS NULL OR dbo.ActionItem.ClosedDate IS NOT NULL))
--dates
AND (@StartDate IS NULL OR dbo.ActionItem.OpenDate >= @StartDate)
AND (@EndDate IS NULL OR dbo.ActionItem.OpenDate <= @EndDate)
--do they want a single issue ?
AND (@IssueID IS NULL OR dbo.ActionItem.IssueID = @IssueID)
--do they want by wbs elementss
ORDER BY dbo.ActionItem.IssueID, dbo.Customer.Customer, dbo.Site.Site, dbo.Commission.Commission
Set NOCOUNT OFF
END
GO

As for left and right joins, you can convert a right join to a left join with a bit of SQL magic, if I have some more time today I will post you a good article and examples.

Good luck,

Jon

[Magiaus]

What is diffrent about using a RIGHT JOIN, A LEFT JOIN, or an INNER JOIN.

I know there is a result set diffreance, but I'm not sure what it is... my dag ate my SQL Ref.....

I always use an Inner Join and Use very strict where clauses.....

This is a boolean driven query I have. At first I thought I would have to use dynamic sql but after thought I came up with this.

ALTER PROCEDURE dbo.Contacts_GetUsers
(
@Employees bit,
@DReps bit,
@Mreps bit,
@Gen bit
)
AS
CREATE TABLE #TempTable(
ContactRowID int IDENTITY(1,1),
ContactKey char(36)
)
 
 
IF @Employees = 1
 
BEGIN
 
INSERT INTO #TempTable (ContactKey) 
SELECT Contacts.Guid FROM Contacts
INNER JOIN Employees ON Employees.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 

 
IF @DReps = 1
 
BEGIN
 
INSERT INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN DistributorsRepresentatives ON DistributorsRepresentatives.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 

 
IF @MReps = 1
 
BEGIN
 
Insert INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN ManufacturersRepresentatives ON ManufacturersRepresentatives.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 
IF @Gen = 1
 
BEGIN
 
Insert INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
WHERE Contacts.Type = 'Contact'
AND Contacts.EmailAddress != '[email protected]'
END
 

 
SELECT DISTINCT #TempTable.ContactKey AS contact_key, Contacts.LastName, Contacts.FirstName, Contacts.MiddleName, Contacts.EmailAddress FROM #TempTable
INNER JOIN Contacts ON Contacts.Guid = #TempTable.ContactKey
WHERE Contacts.EmailAddress != '[email protected]'
ORDER BY Contacts.LastName ASC, Contacts.FirstName ASC, Contacts.MiddleName ASC, Contacts.EmailAddress ASC
RETURN 
 

this allows combo boxes to control what users are displayed. What is up with the editor it really messes with SQL.....

[jhermiz]

What is diffrent about using a RIGHT JOIN, A LEFT JOIN, or an INNER JOIN.

I know there is a result set diffreance, but I'm not sure what it is... my dag ate my SQL Ref.....

I always use an Inner Join and Use very strict where clauses.....

This is a boolean driven query I have. At first I thought I would have to use dynamic sql but after thought I came up with this.

ALTER PROCEDURE dbo.Contacts_GetUsers
(
@Employees bit,
@DReps bit,
@Mreps bit,
@Gen bit
)
AS
CREATE TABLE #TempTable(
ContactRowID int IDENTITY(1,1),
ContactKey char(36)
)
 
 
IF @Employees = 1
 
BEGIN
 
INSERT INTO #TempTable (ContactKey) 
SELECT Contacts.Guid FROM Contacts
INNER JOIN Employees ON Employees.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 

 
IF @DReps = 1
 
BEGIN
 
INSERT INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN DistributorsRepresentatives ON DistributorsRepresentatives.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 

 
IF @MReps = 1
 
BEGIN
 
Insert INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN ManufacturersRepresentatives ON ManufacturersRepresentatives.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 
IF @Gen = 1
 
BEGIN
 
Insert INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
WHERE Contacts.Type = 'Contact'
AND Contacts.EmailAddress != '[email protected]'
END
 

 
SELECT DISTINCT #TempTable.ContactKey AS contact_key, Contacts.LastName, Contacts.FirstName, Contacts.MiddleName, Contacts.EmailAddress FROM #TempTable
INNER JOIN Contacts ON Contacts.Guid = #TempTable.ContactKey
WHERE Contacts.EmailAddress != '[email protected]'
ORDER BY Contacts.LastName ASC, Contacts.FirstName ASC, Contacts.MiddleName ASC, Contacts.EmailAddress ASC
RETURN 
 

this allows combo boxes to control what users are displayed. What is up with the editor it really messes with SQL.....

Your procedure is still not efficient enough. There are a lot of improvements as using boolean logic such as IS NULL and passing default parameters.
Its good that your restrict using WHERE however, there are many cases you do need a left join rather than an inner join. i highly doubt that you have an application that does not use a left join ?????? Thats simply impossible, unless your relationships ALWAYS have a one side to many side and you are counting on showing both ???

What happens if I have a customer orders table and I just want to show customers who have placed orders?????? You do not and should not use an INNER join for this...im just looking for CUSTOMERS.

SELECT Customer.CustomerID, Customer.CustomerName, CustomerOrders.CustomerDate FROM CustomerOrders LEFT JOIN ON
CustomerOrders.CustomerID=Customer.CustomerID

[jhermiz]

What is diffrent about using a RIGHT JOIN, A LEFT JOIN, or an INNER JOIN.

I know there is a result set diffreance, but I'm not sure what it is... my dag ate my SQL Ref.....

I always use an Inner Join and Use very strict where clauses.....

This is a boolean driven query I have. At first I thought I would have to use dynamic sql but after thought I came up with this.

ALTER PROCEDURE dbo.Contacts_GetUsers
(
@Employees bit,
@DReps bit,
@Mreps bit,
@Gen bit
)
AS
CREATE TABLE #TempTable(
ContactRowID int IDENTITY(1,1),
ContactKey char(36)
)
 
 
IF @Employees = 1
 
BEGIN
 
INSERT INTO #TempTable (ContactKey) 
SELECT Contacts.Guid FROM Contacts
INNER JOIN Employees ON Employees.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 

 
IF @DReps = 1
 
BEGIN
 
INSERT INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN DistributorsRepresentatives ON DistributorsRepresentatives.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 

 
IF @MReps = 1
 
BEGIN
 
Insert INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
INNER JOIN ManufacturersRepresentatives ON ManufacturersRepresentatives.ContactGuid = Contacts.Guid
WHERE Contacts.EmailAddress != '[email protected]'
END
 
IF @Gen = 1
 
BEGIN
 
Insert INTO #TempTable (ContactKey)
SELECT Contacts.Guid FROM Contacts
WHERE Contacts.Type = 'Contact'
AND Contacts.EmailAddress != '[email protected]'
END
 

 
SELECT DISTINCT #TempTable.ContactKey AS contact_key, Contacts.LastName, Contacts.FirstName, Contacts.MiddleName, Contacts.EmailAddress FROM #TempTable
INNER JOIN Contacts ON Contacts.Guid = #TempTable.ContactKey
WHERE Contacts.EmailAddress != '[email protected]'
ORDER BY Contacts.LastName ASC, Contacts.FirstName ASC, Contacts.MiddleName ASC, Contacts.EmailAddress ASC
RETURN 
 

this allows combo boxes to control what users are displayed. What is up with the editor it really messes with SQL.....

This isn't normalized either. Think about this solution...I noticed you do a lot of selecting to find out if its a contractor, employee, etc.

You could have eliminated all of this and had one additional table and a foreign key:

EmployeeType
---------------
EmployeeTypeID
EmpoyeeType

Valid records could have included:
1 Contractor
2 Engineer

Than in your main table you reference the EmployeeTypeID number
in the employees table. That way you would have created a generic stored procedure and passed in the EmployeeTypeID and gotten the right results.

Jon

[Magiaus]

This isn't normalized either. Think about this solution...I noticed you do a lot of selecting to find out if its a contractor, employee, etc.

You could have eliminated all of this and had one additional table and a foreign key:

EmployeeType
---------------
EmployeeTypeID
EmpoyeeType

Valid records could have included:
1 Contractor
2 Engineer

Than in your main table you reference the EmployeeTypeID number
in the employees table. That way you would have created a generic stored procedure and passed in the EmployeeTypeID and gotten the right results.

Jon

I know. That was the original plan, but I changed it for time/pay reasons and put a type varchar on my contact table. Not the best, but if I am not getting paid I am not spending the time on it.

It would Be ContactType and I have a table with ex info for anything not a contact.


About the Left Join what is the difreance in joining inner and left. I know my table is on the left but why is it better to do a left join?

Why not

SELECT Order.ID, Clients.* FROM Orders INNER JOIN Clients On Clients.Key = Orders.ClientKey

Then again Why not

SELECT * FROM Orders, Clients WHERE Orders.ClientKey = Clients.Key

(old school)

Some of us still do use old school. Back before JOIN...... Seriously can you explain about JOIN's and what a Right is for and so on. My dog really did get my book. OR someone did rather.

[jhermiz]

In a parent child relationship an INNER join returns the result set such that
a primary key and foreign key must match. That is take x1 and x2, the resultant of rows is only true such that x1 is in x2. For example, you may need an INNER join to view CustomerOrders such that the CustomerID is available in BOTH the Customers table and the CustomerOrders table.
This is a true parent child relationship where the ID and Foreign Key must match and must be present in both tables so that the result is complete.

A very good example is an issues system where you have issues and action items. You want to display all issue action items ONLY if the action item has a correct foreign key back to an issue id.

Let us say for example, there is an action item whose parent was once deleted (but we did not enforce referential integrity). That is the Issue doesnt exist but the Action for that issue still exists. If I INNER JOINED to this action it would not display since the inner join states that there should be a join between both tables.

The left join states that everything on the left side (the parent table) should be displayed irregardless of the data on the right side. For instance, say we wanted to view all customers even if they did not have a single order. We would left join to view this information irregardless of the amount of orders.

The right join is almost rarely used, and any right join can be converted to logical left join or a union of some sort. A right join does completly opposite of the left join. It has the properties of returning the many side irregardless of the one side. For instance, in our example it would show all customer orders irregardless of the customer.

BTW: you should never SELECT *, that is poor practice, affects performance, and is just plain wrong. Even if you need every single field in a table, you should list them out individually. You never know what tomorrow brings, if you get my jist

Jon

[Magiaus]

Okay I think there are resons for SELECT * and I see where a LEFT/RIGHT would be useful on a very complex JOIN; joing 4 - 8 tables, but I always have a key match. That's probably why I wasn't seeing the point. I design my tables so that every key for related data is on the table. Unless like with that contact type field I cut a corner.

If I have Contacts and Employees and an employee is a contact I have a contact key in the employee table. If I have a Sales Rep who is an employee I have a contact and an employee key on the SalesRep table. Now this is easy if you are starting from scratch but I can see and remember it being a @#$%^&*( when working on a long standing data center with 300+ tables. My little app only has 40 though and maybe will get bigger.

As far a SELECT * what if I have a data object that loads info from a table. I have a stored proc that SELECT * FROM X WHERE key = @key. If I add fields I don't have to worry about updating that query. I have to update my UPDATE, INSERTand my object. It's one less thing to worry with. CRUD sucks any way you look at it, why not save myself a little time..?

I rarely want all the data returned unless there is a key match. I don't have any major reports on this app, but yes I know what you mean now. It came back to me. It's a must for proper reports to use LEFT. RIGHT does suck. I just remebered a join I had to write from Sales Reporting about 4 years ago. That thing was evil. it was DB2 also. Ahhhhh DOS

[jhermiz]

No Select * should NEVER be used. Even if you need all the fields, you should and could (but you're being lazy) list every single field out. Whenever you SELECT * the server performs additional work and is a load on the network traffic. There will come times where you add a field like a bit or a timestamp that you may never need to select from. Then what happens? You have to sit there and modify your SELECT * again and again...

SELECT * is very bad practice, I can send you many more reasons and articles if you'd like. But if you don't care, and the company you work for doesn't care, well that's on you guys. But no way in hell are all of our dba's gonna sit here and write bad code to bite us later on.

Just do it right...

[Magiaus]

Yep, lazy.... Unfortunatly I'm the DBA, the garaphic desighner, the programmer, the web monkey, and the computer tech. Damn lazy .......

I understand the speed loss though.

[jhermiz]

Yep, lazy.... Unfortunatly I'm the DBA, the garaphic desighner, the programmer, the web monkey, and the computer tech. Damn lazy .......

I understand the speed loss though.

Hey as long as you understand

[Wokawidget]

SELECT * shouldn't be used, it's sloppy.

But it's not the end of the world.

Woka