Performance issue.. need ur help.

• bipin-1156860

  Newbie

  Points: 1

  More actions

  September 14, 2009 at 12:06 am

  #209866

  Hello,

  1st let me explain the process. ill make it as simple as possible. 🙂

  Consider records are being added into a table in order of their DATE.

  There is a COST field which gets updated. i.e COST = COST + previos record COST field.

  Now a situation arises when a record comes which is less than that of the last record in the table.

  for example consider the situation below.. records as per date

  COL1 COL2 DATE

  1 AS 15-03-09

  2 DF 16-03-09

  3 ER 17-03-09

  now a new record comes which has date 15-03-09

  now in this case i need to get records 2 & 3 and insert then again after record 3.

  I hope u have understood the process....

  Now let me explain to you my implementation.

  first of all i saved records matching to the date in a temp table... so now this temp table will contain record 1..

  then i fired a query with a NOT IN statement in the query... i.e NOT IN that temp table.. this will return me records 2 & 3....

  This is giving me a serious performance issue... for 48 lakh records it took 1hr 30 mins...

  All the reqd columns are indexed..

  pls suggest a soln that would improve the performance..

  thanks,

  Bipin

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  September 14, 2009 at 1:20 am

  #1052976

  Sorry , but i dont understand the problem. Are you creating a rolling balance column ?

  Can you post the code you presently have that may make things easier.

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Grant Fritchey

  SSC Guru

  Points: 399289

  More actions

  September 14, 2009 at 8:02 am

  #1053221

  There is no need to order the inserts of your data. This is because your data is not stored in a perfect order. Even a clustered index, which is logically ordered, will still, through page splits, etc., store the data in a less than perfect order. You need to supply structure and mechanism to retrieve the data in order.

  You just need to insert your data, and then retrieve it with an ORDER BY statement to get it back in the order needed, done.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning