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
 General SQL Server Forums
 New to SQL Server Programming
 Need help make this code run faster....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

feyang
Starting Member

Indonesia
1 Posts

Posted - 12/21/2013 :  04:07:17  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/21/2013 :  04:14:20  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/23/2013 :  09:02:36  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/23/2013 :  12:28:43  Show Profile  Reply with Quote
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/
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