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.
| Author |
Topic |
|
Sun Foster
Aged Yak Warrior
515 Posts |
 Posted - 2006-02-18 : 09:21:44
|
| I used code bellow but performance is not great.Any way to improve it? Both tables have more than 50000 records.select Customer_Name, Order_city from tblOrder where OrderID in (select OrderID from tblOrderHistory where OrderBy = '1123' and Qty > 15) |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-18 : 09:50:27
|
tryselect Customer_Name, Order_city from tblOrder owhere exixts (select OrderID from tblOrderHistory x where x.OrderID = o.OrderID and x.OrderBy = '1123' and x.Qty > 15) Also ensure that both tables are properly indexed----------------------------------'KH'Time is always against us |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-02-18 : 09:57:48
|
if this is a big table you'd be best of joining it.Go with the flow & have fun! Else fight the flow  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-18 : 12:02:17
|
| "best of joining it"Likely to then get duplicates which needs a DISTINCT ...Adding an index to tblOrderHistory with OrderBy, Qty, OrderID may help.I presume that OrderID is the Primary Key for the tblOrder table? Making sure it is a Clustered index would help this particular query (might not help others though!). If OrderID is allocated as an ascending number (rather than a "random number") then make sure the FILL FACTOR is 100% (standard SQL maintenance plan defaults will likely change this to 90% if you are not careful!)Prefix the table name with the owner name ("dbo" presumably) will help the optimiser cache the query plan, as will parameterising the query - either use a Stored Procedure or something like sp_ExecuteSQL.Kristen |
|
|
|
|
|
|
how to improve this code?