SQL With Statements

A SQL query can sometimes get a lot bigger than we anticipated. A couple of thousand records later your query takes more than 30 seconds to run and your web site gives a nice “Timeout expired” message to your users. Then it’s time to get serious and analyze thoroughly your query.

So break out the analyzing tools:

·         SQL Server Management Studio

·         A Query Window

·         And the Execution Plan ()

By using this we found that ours was querying a table with 60 000 records over 10 000 times, It was taking on average 47 seconds to run.  We learned that we can use WITH statements to query only once a table, put the results in Memory and use the data from memory in a later SELECT.

Here is how to do it

WITH InMemory AS

(

SELECT * FROM tb_MyTable

)

SELECT * FROM InMemory

---

With a simple example it might not look like much, but consider this more complex query that was built to fix our issue:

DECLARE @UserLogin AS varchar(50)

SET @UserLogin = 'domain\user';

WITH Naeds AS

(

      SELECT Material,SalesChannel FROM tb_NAED n

      WHERE

            (SELECT COUNT(*) FROM tb_BusinessUnit bu WHERE n.AUG = bu.AUG AND n.PSI = bu.PSI AND bu.UserLogin = @UserLogin) > 0

            OR (SELECT COUNT(*) FROM tb_SupplyChainManager scm WHERE scm.SupplyChain = LEFT(n.AUG,2) AND scm.UserLogin = @UserLogin) > 0

            OR (SELECT COUNT(*) FROM tb_AG ag WHERE ag.AG = n.AG AND ag.UserLogin = @UserLogin) > 0

)

SELECT *

FROM tb_ProjectNaed pn

      INNER JOIN tb_Project p ON p.ID = pn.ProjectID

      LEFT OUTER JOIN tb_Naed n ON n.Material = pn.Material AND n.SalesChannel = p.SalesChannel

WHERE p.Originator = @UserLogin

            OR p.OriginatorManager = @UserLogin

            OR (SELECT COUNT(*) FROM tb_Administrator adm WHERE adm.UserLogin = @UserLogin) > 0

            OR pn.Material IN (SELECT Material FROM Naeds n WHERE n.SalesChannel = p.SalesChannel)

            OR (SELECT COUNT(*) FROM tb_InventoryCheck ic WHERE ic.SalesOrg = p.InventoryCheck AND ic.UserLogin = @UserLogin) > 0

            OR (SELECT COUNT(*) FROM tb_DemandPlanningGroup dpg WHERE dpg.SalesOrg = p.SalesOrg AND dpg.UserLogin = @UserLogin) > 0

            OR (SELECT COUNT(*) FROM tb_ProjectNotification ntf WHERE ntf.ProjectID = p.ID AND ntf.UserLogin = @UserLogin) > 0

---

In this example the red highlighted Naeds is used instead of the “real” table tb_Naed, this makes it so tb_Naed does not have to be queried on each record in tb_ProjectNaed. Saving us ALOT of time.

Now our query runs in 4 seconds instead of 47.

Lesson Learned.