query optimization
-
Hello, I have a sql (t-sql) question. I have two tables Orders and Agents. Each agent does a transaction (bying or selling) and transactions are recorded in Orders table. Columns in table "Orders": ItemId, bla, bla, bla, Seller, Buyer Columns buyer and seller are ID of responsible agent. Columns in table "Agents": AgentName, AgentSecondName, ID, bla, bla, bla NOW i want to display list of all agents alongside with numbers of sold and bought items, in a new table like this: AGentName, AgentSecondName, AgentId, Bought, Sold My query is SELECT AgentName, AgentSecondName, ID (SELECT COUNT(*) FROM Orders WHERE (Seller = Agents.ID)) AS SOLD, (SELECT COUNT(*) FROM Orders WHERE (Buyer = Agents.ID)) AS BOUGHT FROM Agents but itąs damn slow with a few millions of records that i have (although its still milliseconds its too slow), any idea how to speed it up?
-
Hello, I have a sql (t-sql) question. I have two tables Orders and Agents. Each agent does a transaction (bying or selling) and transactions are recorded in Orders table. Columns in table "Orders": ItemId, bla, bla, bla, Seller, Buyer Columns buyer and seller are ID of responsible agent. Columns in table "Agents": AgentName, AgentSecondName, ID, bla, bla, bla NOW i want to display list of all agents alongside with numbers of sold and bought items, in a new table like this: AGentName, AgentSecondName, AgentId, Bought, Sold My query is SELECT AgentName, AgentSecondName, ID (SELECT COUNT(*) FROM Orders WHERE (Seller = Agents.ID)) AS SOLD, (SELECT COUNT(*) FROM Orders WHERE (Buyer = Agents.ID)) AS BOUGHT FROM Agents but itąs damn slow with a few millions of records that i have (although its still milliseconds its too slow), any idea how to speed it up?
There are a number of ways to get this data, I would probably use two left joins and a sum on a case statement based on the id in the seller/buyer fields. You would need to create the alternative queries and run them through the profiler to evaluate the performance. Performance is more likely to be improved by adding an index to the buyer and seller id fields on the orders table.
Never underestimate the power of human stupidity RAH
-
Hello, I have a sql (t-sql) question. I have two tables Orders and Agents. Each agent does a transaction (bying or selling) and transactions are recorded in Orders table. Columns in table "Orders": ItemId, bla, bla, bla, Seller, Buyer Columns buyer and seller are ID of responsible agent. Columns in table "Agents": AgentName, AgentSecondName, ID, bla, bla, bla NOW i want to display list of all agents alongside with numbers of sold and bought items, in a new table like this: AGentName, AgentSecondName, AgentId, Bought, Sold My query is SELECT AgentName, AgentSecondName, ID (SELECT COUNT(*) FROM Orders WHERE (Seller = Agents.ID)) AS SOLD, (SELECT COUNT(*) FROM Orders WHERE (Buyer = Agents.ID)) AS BOUGHT FROM Agents but itąs damn slow with a few millions of records that i have (although its still milliseconds its too slow), any idea how to speed it up?
How's about this?
SELECT ID=MAX(Agent.ID)
,FirstName=MAX(Agent.FirstName)
,LastName=MAX(Agent.SecondName)
,Sold=SUM(CASE Seller WHEN Agent.ID THEN 1 ELSE 0 END)
,Bought=SUM(CASE Buyer WHEN Agent.ID THEN 1 ELSE 0 END)
FROM Agent
LEFT OUTER JOIN [Order]
ON Agent.ID=[Order].Seller
OR Agent.ID=[Order].Buyer
GROUP BY Agent.ID:-D (And, yeah, be sure to index Buyer and Seller)