SQL Server Performance Forum – Threads Archive

Better query way…

hi…i’m newbie to sql…forgive my stupidness.. My question is, is there anyway to write better query for the statement below? SELECT h.B_Name,h.B_Add_1,h.B_Add_2,h.B_City,h.B_Postal_Code,h.B_Country,h.B_Province,h.S_Name,h.S_Add_1,h.S_Add_2,h.S_City,h.S_Postal_Code,h.S_Country,h.S_Province, l.order_num,l.qty_ordered, i.sort_name, p.price_1 FROM tmp_oh h
INNER JOIN tmp_ol l ON (h.order_num = l.order_num)
INNER JOIN item i ON (l.item_num = i.item_num)
INNER JOIN price p ON (i.item_num = p.item_num)
WHERE h.order_num = <cfqueryPARAM value = "#Attributes.hdnRecID#" CFSQLType = "CF_SQL_INTEGER">
thanks for help… regard,
mfei

Im not familiar with the syntax starting with ‘<cfqueryPARAM value..’, im taking a guess the CF means cold fusion and this is how you pass parameters in cold fusion? If this is the case, I dont see anything wrong with the query. In what way are you hoping it can be improved? If its not performing well, first make sure you have suitable indices on these tables.

See the query execution plan and follow as Chappy refers about indexes. Better to capture the trace from PROFILER by executing the query. HTH _________
Satya SKJ

sorry for creating doubts..here are clearer explanation <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> 1. Yup…cfqueryPARAM is a syntax to pass parameter in coldfsusion. Forget about it.. 2. This question was asked just because I some doubts in joint statement versus where clause. When i try to replace the above query statement with the statement below, to my suprise it eventually looped faster and take shorter time than the joint statement as i used before. Therefore i would like to find out why and also curious to know iwhether is there any other better ways to write a query. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thanks SELECT h.B_Name,h.B_Add_1,h.B_Add_2,h.B_City,h.B_Postal_Code,h.B_Country,h.B_Province,h.S_Name,h.S_Add_1,h.S_Add_2,h.S_City,h.S_Postal_Code,h.S_Country,h.S_Province,line.order_num, line.qty_ordered,itm.sort_name, prc.price_1 FROM tmp_oh head,tmp_ol line, item itm, price prc WHERE head.order_num = line.order_num AND line.item_num = itm.item_num AND itm.item_num = prc.item_num AND head.order_num = <cfqueryPARAM value = "#Attributes.hdnRecID#" CFSQLType = "CF_SQL_INTEGER"> thanks, mfei

If you use INNER JOINs only, using JOINs or putting your statements in the WHERE clause will yield the same results. It is preferrable to use the INNER JOIN syntax though, as OUTER JOINs work better and have more functionality than their old style equivalent *=, and it’s good to use a consitent syntax. I doubt if using WHERE instead of INNER JOIN will make the query run faster. Identical queries may have different execution times depending on the server load and network traffic etc. What also might have happened is that the first time you ran the query the data was loaded from disk into memory, where the second time the data was still cached in memory. Accessing data in memory is of course a lot faster than getting it from disk. The way to check the actual perfomance of the queries is to run both the queries in Query Analyzer and look at the Execution Plan (you can get this by pressing Ctrl+K and then running the query). You can also use SET STATISTICS IO ON at the beginning of the batch that includes both the queries and check the value for ‘logical reads’. This value is independent of whether the data the query accesses has already been loaded into memory or not.

Can you please post the execution plan of both the queries? Gaurav

<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> okok…no problems…here’s the execution plan :- This is using joint statement : QryTmpOrder (Records=8, Time=40ms) SQL = SELECT h.B_Name,h.B_Add_1,h.B_Add_2,h.B_City,h.B_Postal_Code,h.B_Country,h.B_Province,h.S_Name,h.S_Add_1,h.S_Add_2,h.S_City,h.S_Postal_Code,h.S_Country,h.S_Province, l.order_num, l.qty_ordered, i.sort_name, p.price_1 FROM tmp_oh h INNER JOIN tmp_ol l ON (h.order_num = l.order_num) INNER JOIN item i ON (l.item_num = i.item_num) INNER JOIN price p ON (i.item_num = p.item_num) WHERE h.order_num = ? Query Parameter Value(s) - Parameter #1 = 5 This is using where clause QryTmpOrder (Records=8, Time=30ms) SQL = SELECT h.B_Name,h.B_Add_1,h.B_Add_2,h.B_City,h.B_Postal_Code,h.B_Country,h.B_Province,h.S_Name,h.S_Add_1,h.S_Add_2,h.S_City,h.S_Postal_Code,h.S_Country,h.S_Province,l.order_num,l.qty_ordered,i.sort_name, p.price_1 FROM tmp_oh h,tmp_ol l, item i, price p WHERE h.order_num = l.order_num AND l.item_num = i.item_num AND i.item_num = p.item_num AND h.order_num = ? Query Parameter Value(s) - Parameter #1 = 5 As shown above, i queried 8 records from the database and joint statement required 40ms to complete the query while where clause only required 30ms to fulllfill the query. I wonder if I query 8,000 records would it means that 40s for joint statement while only 30s for where clause statement? sorry for disturbing your all and really appreciate all helps! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> regards, mfei

<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> regards, mfei

Before executing the query type SET SHOWPLAN_TEXT ON Execute the query and post the resultset on this website. Gaurav

Just to clarify, youll need to do.. SET SHOWPLAN_TEXT ON
GO
<then your query goes here>

]]>