Compiling two selects as join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Compiling two selects as join

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-11-12 : 08:29:51

I have two select statements, which of one takes one second and the other takes two seconds to execute. If I use inner join, it takes 137 seconds to execute. If I hard code the other table (using where) instead of inner join, that's again a second or two.

I would basically need a way to order the executing order (like in algebra there is brackets for). How do I do that?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-12 : 08:38:30

That is hard to say without seeing the queries - if you can post the query (or a simplified reprsentative example if the code is very long), that would enable someone to offer more concrete suggestions.

When you join two queries, it does not necessarily follow that the number of rows returned would the same. When you run the joined query, does it return the correct results, and the expected number of rows even after 137 seconds?

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-11-12 : 09:42:44

The idea of the query is that I need to receive a list of invoices that have false CostCodes. A CostCode is false if the budget of the project has not been set to use the given CostCode. For example, here's an invoices table:

Invoice CostCode
1 A
2 B
3 A
4 C

and here's the budget for the project:

Costcode Budget
A 100
B 150

So the answer I want is
Invoice CostCode
4 C

Now the queries are like this:

--Get all invoices of this project (Let's call this as QueryA)
SELECT *
FROM invoices
WHERE project = thisparticularproject

--Get project CostCodes (Let's call this as QueryB)
SELECT * FROM (
SELECT DISTINCT CostCode
FROM CostCodes
INNER JOIN Budget
ON CostCodes.Project = Budgets.Project
AND CostCodes.CostCode = Budgets.CostCode
WHERE project = thisparticularproject
) AS TheBudgetAccounts

--Get list of used CostCodes (Let's call this as QueryC)
SELECT DISTINCT CostCode
FROM Invoices
WHERE project = thisparticularproject

And all together it comes to

QueryA
inner join
QueryC
where not exists(QueryB)
on
QueryA.CostCode = QueryB.CostCode

While the syntax in what I write here is not totally correct, the key is that you get the idea.

Now executing this like above takes that 137 seconds. If I instead execute this like:

QueryA where costcode = (hardcoded result of B and C)
the result comes in two seconds.

So one way to solve this is that I would somehow tell that
1) Create the result of B and C
2) Execute query and use the result from above as where clause

I actually execute this from VBA code now with this
QueryA where costcode = (hardcoded result of B and C)
way, using two queries, but I'm pretty sure there's no need for that so that T-SQL can pull out the result for me using only one query (and it's subqueries). And that it does it way faster than in 137 seconds.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-12 : 10:39:03

Can you simply the query like this?

SELECT * FROM Invoices i
WHERE project = thisparticularproject
AND NOT EXISTS
(
	SELECT * FROM CostCodes c
	WHERE i.project = c.project
	AND i.costcode = c.costcode
)

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-11-13 : 06:01:47

I don't think I understood this query correctly, should I have something there between AND and NOT EXISTS? The CostCodes table doesn't have invoices.

But I think this is close to what I'm looking for.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 06:48:43

If you run the query, do you get an error message? Or does it give you incorrect data? There should not be anything between AND and NOT EXISTS.

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-11-13 : 07:06:58

It doesn't return anything now. How does the "NOT EXISTS" work? I mean, we get rows that has basically a CostCode and a project from the inner query and from the outer query we get the invoices of the project, but how does the AND NOT EXISTS affect to there? It's different from EXCEPT, right?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 07:20:52

NOT EXISTS clause evaluates the inner query by applying the where conditions which involves outer columns and inner columns. If no rows are returned, the not exists clause returns true, and if at least one row is found, it returns true.

I may have misunderstood your data or requirements. The code I posted is against a scenario such as the one shown below. You can copy and paste the code to an SSMS query window and run it to see what it does

CREATE TABLE #Invoices(Project VARCHAR(32), Invoice INT, CostCode CHAR(1));
INSERT INTO #Invoices VALUES 
('thisparticularproject',1,'A'),
('thisparticularproject',2,'B'),
('thisparticularproject',3,'A'),
('thisparticularproject',4,'C');

CREATE TABLE #Budget (Project VARCHAR(32), CostCode CHAR(1), Budget INT);
INSERT INTO #Budget VALUES 
	('thisparticularproject','A',100),
	('thisparticularproject','B',150);

SELECT * FROM #Invoices i
WHERE project = 'thisparticularproject'
AND NOT EXISTS
(
	SELECT * FROM #Budget c
	WHERE i.project = c.project
	AND i.costcode = c.costcode
)

DROP TABLE #Invoices;
DROP TABLE #Budget

If that is not what you are looking for, if you can post sample data and code like I have posted above, it would be easy for people to respond more accurately.

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-12-03 : 18:10:52

I went with the last method, got it done though some ADODB problems prenvented me (for now) from doing all at once. Anyway, it works in three parts and is really fast. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources