how to fast sql query

Question

hi friend can anybody tell me how to fast my sql query.I m insert some field from another table that has around 5 lak data,I just insert 100 row according to date.I have use clustred index on date but speed is quite slow due to where clause in which I have use a description field and their are lots of and i.e " (detail like '%car%' and '%truck%' ) OR (detail like '%shirt%' and '%pant%' ) " Is their any solution?

Jan Montano · Answer

Hi kavita, Why did you make a clustered-index for your date? Is it somehow a primary key in your table? Declaring a non-clustered index for details will definitely help because you're where cause includes the detail. You could also consider using Dynamic SQL to use a stored procedure though there are pros and cons that comes with it -> Dynamic SQL Cheers, Jan

kavita · Answer

hi friends thank to write to me my query is ------ cmdfinal = "insert into tmpsearch1now(ssid,ref_no,sr_no,tender_category,sdate,estimated_cost,country,state,document_cost,detail,click_cost) select top 100 ssid='" & session_id & "',ref_no,sr_no,tender_category,bid_opening_date,estimated_cost,country,state,document_cost,detail,click_cost from archive_tender where " & strCmd & "" ---------- where strcmd depend upon user input if he input "car paper steel" then strcmd is: strcmd=(detail like '%car%' and detail like '%paper%') or (detail like '%car%' and detail like '%steel%' ) or (detail like '%paper%' and detail like '%steel%') that's why I cann't use stored procedure it gave an error. my idex is : CREATE CLUSTERED INDEX [BID_DATE_AR] ON [dbo].[archive_tender] ([bid_opening_date] desc ) WITH FILLFACTOR = 25 ,DROP_EXISTING ON [PRIMARY] the table contail around 5 lack data and update only one in a day.only I have set one clustred index on bid_opening_date.

Rolando Puno · Answer

The best idea is use store procedure...

Jan Montano · Answer

Hi Kavita, Please post the structure of your whole query. Is the detail column indexed? If not, making it indexed will make it faster. Could you also post the CREATE TABLE and CREATE INDEX statements for the table? Thanks. Regards, Jan