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 |
|
mrsaif
Starting Member
43 Posts |
 Posted - 2006-02-17 : 09:30:50
|
| I have Following Query:"select shop.shop_id,shop.shop_title,cl.client_company_name,loc.city, loc.state,loc.zip_code,loc.location_id,shop.shop_end_date, shop.shop_start_date, shop.shoppers_pay,cl.client_id,shop.program_id,shop_bonusfrom @tbl_shops shop Inner Join @tbl_Programs prog on shop.program_id=prog.program_id Inner Join tbl_client_locations loc on shop.location_id=loc.location_id Inner Join tbl_client cl on loc.client_id=cl.client_id Inner Join @tbl_zip_codes TZC on TZC.Zip_Codes=loc.ZIP_Codewhere job_board_listing=1 and shop.shop_status_id=1 --and shop.shop_id not in (select shop_id from tbl_shop_requests where shopper_id=@intShopperId) and shop_end_date>=getdate()"Query takes 2 secods and return 12010 records.and if I uncomment "and shop.shop_id not in (select shop_id from tbl_shop_requests where shopper_id=@intShopperId) " then it takes 30 secons and return 12000 records. My Problem is that i do not want to get the record that exits in table tbl_shop_requests for a particular shopper. How can i reduce the execution time with the same results.Muhammad Saifullah |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-02-17 : 11:54:58
|
| what indexes do you have in place?without knowing anything about your data, I would guess that you should have a single-column index on shopper_id on the tbl_shop_requests table.You might also want to consider creating a covered index on this table if:1. you already have that single column index I mentioned2. Adding the single column index doesn't improve things enoughThe covered index would be a composite index on shopper_id and shop_id (in that order) on the tbl_shop_requests table.-ec |
 |
|
|
|
|
|
|
|
not in Query Problem