none
Tables having 100 crores records creating problem

    Question

  • I have one table name abc which has the records above 100 crores data. I am using this table in the procedure pqr where I am performing following operation in this table: 

    1) select count

    2) Delete

    3) Insert

    When I execute this procedure from my UI, ideally only one record should get inserted. But 4 records get inserted. I dont know why is this happening. As well as lot of time is taken to run the procedure. According to me time is taken because of select count, Delete operation, but because of these the insert is perform many times in stored procedure.


    • Moved by Bob BeaucheminMVP Sunday, September 01, 2013 5:44 PM Moving to a more relevent forum
    Sunday, September 01, 2013 3:55 PM

All replies

  • When I execute this procedure from my UI, ideally only one record should get inserted. But 4 records get inserted. I dont know why is this happening. As well as lot of time is taken to run the procedure. According to me time is taken because of select count, Delete operation, but because of these the insert is perform many times in stored procedure.

    We will need to see the stored procedure code in order to help you troubleshoot why 4 rows are inserted when only one is expected.  We'll also need the table DDL (CREATE TABLE statement with indexes and constraints) in order to help with the performance.  Performance is mostly a matter of query and index tuning but we don't have enough information here to make specific suggestions.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, September 01, 2013 4:24 PM
  • Is the value to be inserted directly passed to insert command as values or part of some select query.

    If the input data is part of any select query, check the query for first. Also, are those rows identical?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sunday, September 01, 2013 6:51 PM
  • >> I have one table name ABC which has the records [sic: rows are not records!] above 100 crores data. <<



    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    What is “above 100 crores”? 

    >> I am using this table in the procedure pqr where I am performing following operation in this table: <<

    How do you debug code without seeing it?? Magic? 

    >> When I execute this [magic invisible] procedure from my UI, ideally only one record [sic] should get inserted. <<

    And we can figure this out from invisible code!?  Try again. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, September 01, 2013 6:58 PM
  • >> I have one table name ABC which has the records [sic: rows are not records!] above 100 crores data. <<

    What is “above 100 crores”? 
    The OP is using a well known standard (in Asia), something I assume you'd appreciate. He means more than 1 billion rows (100 x 10,000,000), see Crore

    Gert-Jan

    Sunday, September 01, 2013 8:22 PM
  • Did not know that! Thanks!

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, September 01, 2013 9:30 PM