Need help make this code run faster....

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

General SQL Server Forums

New to SQL Server Programming

Need help make this code run faster....

Author

Topic

feyang
Starting Member

1 Post

Posted - 2013-12-21 : 04:07:17

hi guys,

I am new to sql, and i am trying to make this sql code to run faster... the way i see it the code working fine but run a hell to slow...



SELECT SUPP.CODE, SUPP.NAME, TYPE.TYPE, TYPE.ID,
       SUM(STOCK.INITQTY + STOCK.INQTY - STOCK.OUTQTY) AS STOCKBALANCE,
       SUM((STOCK.INITQTY + STOCK.INQTY - STOCK.OUTQTY) * STOCK.BUYPRICE) AS STOCKCOST
                      
FROM   (TYPE INNER JOIN
             (STOCK INNER JOIN
             SUPP ON STOCK.CODE = SUPP.CODE) ON STOCK.TYPE = TYPE.TYPE)
                      
WHERE  (SUPP.CODE LIKE ?) AND (TYPE.TYPE LIKE ?) AND (STOCK.NAME LIKE ?)
                      GROUP BY SUPP.CODE, SUPP.NAME, TYPE.TYPE, TYPE.ID
                      ORDER BY SUPP.CODE

i believe it has to do with the inner join... but I have no idea how to replace the inner join with a cleaner and faster code..
any help will be appreciated... :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-21 : 04:14:20

try like below and see if its any better


SELECT su.CODE,su.NAME,t.TYPE,t.ID,STOCKBALANCE,STOCKCOST
FROM (SELECT TYPE,CODE,
      SUM(INITQTY + INQTY - OUTQTY) AS STOCKBALANCE,
      SUM((INITQTY + INQTY - OUTQTY) * BUYPRICE) AS STOCKCOST
      FROM STOCK 
      WHERE NAME LIKE ?
      GROUP BY TYPE,CODE
     )s
INNER JOIN TYPE t
ON t.TYPE = s.TYPE 
INNER JOIN SUPP su
ON su.CODE = s.CODE 
WHERE  su.CODE LIKE ?
AND t.TYPE LIKE ?
ORDER BY su.CODE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2013-12-23 : 09:02:36

quote:
Originally posted by feyang

WHERE (SUPP.CODE LIKE ?) AND (TYPE.TYPE LIKE ?) AND (STOCK.NAME LIKE ?)

The use of LIKE may be contributing to the slow speed. If you have "%" for any of the values, to select "everything", that will be much slower than just leaving that part of the query out.

Try the query using = instead (it won't be the flexible query you want, but it will tell you how fast SQL would be for a specific case). If that is very quick then you need to look at other ways of improving performance.

For example, you might use NULL to indicate "all" instead of "%" so you would then have:


WHERE	    (@SuppCode IS NULL OR SUPP.CODE LIKE @SuppCode) 
	AND (@TypeType IS NULL OR TYPE.TYPE LIKE @TypeType) 
	AND (@StockName IS NULL OR STOCK.NAME LIKE @StockName)

I don't know if that will be faster, but my gut feeling is that it will be if usually 2 out of the three are wildcards for "all"

Alternative is to use Dynamic SQL and to construct a WHERE clause that only includes those "Bits" of the logic test that are required - i.e. there are no "All" conditions, those are left out.

If you always have values for all three LIKE statements then make sure you have the best and most selective/covering index that you can, so that the query makes use of the index for speed.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-23 : 12:28:43

To follow up on what Kristen posted, here is a article that talks about Catch All Queries:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources