SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Compiling two selects as join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 11/12/2012 :  08:29:51  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/12/2012 :  08:38:30  Show Profile  Reply with Quote
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?
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 11/12/2012 :  09:42:44  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/12/2012 :  10:39:03  Show Profile  Reply with Quote
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
)
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 11/13/2012 :  06:01:47  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  06:48:43  Show Profile  Reply with Quote
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.

Edited by - sunitabeck on 11/13/2012 06:49:20
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 11/13/2012 :  07:06:58  Show Profile  Reply with Quote
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?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/13/2012 :  07:20:52  Show Profile  Reply with Quote
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.
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 12/03/2012 :  18:10:52  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000