No, there is no clustered index but 6 non-unique, non clustered index on the table.
We have identity column set in this table and a unique key is made of it and used in every select statement.That's not the best option. My recommendation is for a Constraint solution where the unique key will be automatically generated and no need at all for the update command:
Btw, there are also triggers on this table?
ALTER TABLE table1 DROP COLUMN refid; GO ALTER TABLE table1 ADD refid AS ('ABC-' + id)
Its default settings. 10 percent, unlimitedNever set autogrow in percentage. Tomorrow you'll have a transaction log file with 100GB and will grow 10GB. Best practice is to set a static value (100MB for example). So you know exactly how much it will grow every time.
No we do not have maintenance plan only we do auto backup every 8 hours via sql script. We shrink the data file and log file bi monthly. We do not have full time DBA nor developers with the complete DBA skills.Hope so, because a DBA that don't configure maintenance tasks and shrink data files is not a good DBA.
Currently i am going to share some points for why we shrink log files and data files.There is NO justification for shrinking in a periodically basis. In very rare occasions you shrink is an acceptable operation (during emergencies) but then a full database reindex need to be applied immediately after the shrink operation.
insert procedure takes time due to a query which preceedes to check duplicateIf your table has a Primary Key (PK) this check not necessary. A PK guarantee the uniqueness of a record and only thing you need to do is to handle with that error:
This way you'll save a SELECT and eventual locks associated.
BEGIN TRY insert into table1 (column1,column2,entrydate,updatedate,status) values ('','',getdate(),getdate(),'active') END TRY BEGIN CATCH IF @@Error = 2627 BEGIN -- Do here what you need to do for duplicate records END END CATCH
-- Check for column1 and column2 and see if tried within 30 minutes. --First select query select @tempid=id from tab_carts where column1='' and column2='' and datediff(minute,entrydate,getdate())<=30 -- Check whether it exists or not. if @tempid is null begin -- since it is not present generate one. insert into table1 (column1,column2,entrydate,updatedate,status) values ('','',getdate(),getdate(),'active') set @tempid =@@identity --Second select query select @groupcolumn=groupcolumnrefno from tab_carts where column1=@column1 and status='active' if @groupcolumn is null begin set @groupcolumn='BID-' + some uniqueval + convert(varchar(100),@tempid) end end else begin set @tempid =0 end update table1 set column11='BID-' + some uniqueval + convert(varchar(100),@tempid) groupcolumnrefno=@groupcolumn where id=@tempid
After creating the index try to run again the query to see if the timeouts gone.
CREATE INDEX idx_tab_carts ON tab_carts (column1, column2)
-- update table1 set column11='BID-' + some uniqueval + @tempid groupcolumnrefno=@groupcolumn where id=@tempid --
Open in new window