Hi, 

I don't know whether i can raise this question . Let me post for some possible suggestions.

I have a MS SQL SERVER STORED PROCECDURE which will receive a set of records from an external source. And then checks a particular table has records or not . If the table has no records in it  we will be making an entire insert into it the table. 

Say for example if the table has records contained in it then our SP will check if the table has records . Obviously it was true so our SP will match a particular record received with the whole records received  previously in table(based on date). In techincal terms, which means i will be making an multiple where with all the columns in the table with the record received (one by one) joining with AND operator.

If the result was true we will be updating and if not we will be making a new entry. So if the records contained in table and the records received were in number of lakhs lot of time were invloved over there to complete the operation for one table.

We have surfed in net about some possible cases of improving sql performance where one of the options was INDEXING . We didnt apply that technology to our table . If we need to apply means we should be applying Non clustered Index for all columns and also no use of that as per my knowledge if we do that, because if we enable indexing it will take some time While Insertion.

We have also checked with the order of where clause and also it doesnt have any effect.

Is there anything we can implement so that we can try or else no other possibilties.     Regards,

Selvam.M

• Edited by selva173 2 hours 3 minutes ago

One method I can think of is to add a computed column in the table based on hashing algorithm (HASH_BYTES function). Then you can use this to do the comparison against incoming data to determine new records vs existing ones

Here's a similar logic I implemented for doing Type 2 processing in one of dimensions. See Method 2 which discusses on using HASH_BYTES function in T-SQL

http://visakhm.blogspot.ae/2014/06/ssis-tips-implementing-scd.html

Yep, in order to speed up the query you need properly defined indexes.. In your case I would start looking into MERGE command introduced in SQL Server 2008