how to fast sql query

kavita

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?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jan Montano19y ago
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
0
kavita19y ago
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.
0
Rolando Puno19y ago
The best idea is use store procedure...
0
Jan Montano19y ago
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
0