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 CostCode1 A2 B3 A4 Cand here's the budget for the project:Costcode BudgetA 100B 150So the answer I want isInvoice CostCode4 CNow 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 CostCodeFROM CostCodesINNER JOIN BudgetON CostCodes.Project = Budgets.ProjectAND CostCodes.CostCode = Budgets.CostCodeWHERE project = thisparticularproject) AS TheBudgetAccounts--Get list of used CostCodes (Let's call this as QueryC)SELECT DISTINCT CostCodeFROM InvoicesWHERE project = thisparticularprojectAnd all together it comes toQueryAinner joinQueryCwhere not exists(QueryB)onQueryA.CostCode = QueryB.CostCodeWhile 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 that1) Create the result of B and C2) Execute query and use the result from above as where clauseI actually execute this from VBA code now with thisQueryA 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 iWHERE project = thisparticularprojectAND 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 doesCREATE 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 iWHERE 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! |
|
|
|