locked
Large Amount Of Data In Table - SQL Execution Problem RRS feed

  • Question

  • HI!!!!!!!!!

    i designed a database for my application every things are working fine but in my one table contains more than 50 lac records so when ever i use this table in my any query it is taking lot of time to execute. i already create an index non clustered but still taking lot of time to execute

    please guide me for this

    thanks in advance


    Maulik A. Dusara Tech Lead.
    Wednesday, October 13, 2010 12:36 PM

Answers

  • What if you add WHERE condition like

    UPDATE #tmpBlackday SET LastClose = (SELECT TOP 1 NAVHISTORY.NavValue FROM NAVHISTORY INNER JOIN #tmpLastDate ON NAVHISTORY.SchemeIndex = #tmpLastDate.SchemeIndex AND NAVHISTORY.NavDate=#tmpLastDate.LastNav WHERE NAVHISTORY.SchemeIndex = #tmpBlackday.SchemeIndex ORDER BY NavDate DESC)

    WHERE EXISTS (SELECT *FROM NAVHISTORY INNER JOIN #tmpLastDate ON NAVHISTORY.SchemeIndex = #tmpLastDate.SchemeIndex AND NAVHISTORY.NavDate=#tmpLastDate.LastNav WHERE NAVHISTORY.SchemeIndex = #tmpBlackday.SchemeIndex )

    >in my query i used this table with inner join statement with SchemeMaster >Table's SchemeIndex with my NavHistory

    I see you are joing #tmpLastDate  table, how many rows does it contain?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 19, 2010 7:24 AM

All replies

  • Well to speed up the query  you need properly created indexes on the table, and I hope your query has a WHERE condition:-) Please  provide an executuob plan of the query to provide you with more accurate solution


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, October 13, 2010 1:04 PM
  • HI!!!!!!!!!

    i designed a database for my application every things are working fine but in my one table contains more than 50 lac records so when ever i use this table in my any query it is taking lot of time to execute. i already create an index non clustered but still taking lot of time to execute

    please guide me for this

    thanks in advance


    Maulik A. Dusara Tech Lead.

    What type of data present in the table (dimension/Fact)??

    If the Table is Fact Table then you can go for Partitioned Table and Index Strategies for better performance.
    Help: http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx

    I believe you need to more analyze this issue in terms of Query used to get data, Index Strategies, Server configuration. You can also use profiler to trace the query plan and analyze it using index hints from SQL server.

    thanks,

     


    - M S (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Thursday, October 14, 2010 5:12 AM
  • i designed a database for my application every things are working fine but in my one table contains more than 50 lac records so when ever i use this table in my any query it is taking lot of time to execute. i already create an index non clustered but still taking lot of time to execute


    50 lac (5m) is not a lot of rows for a mid-sized server or larger.

    what do you mean "a lot of time"?

    does it run faster if you run it twice in a row?

    have to show us the query or there's not much to say.

    Josh

     

    Thursday, October 14, 2010 9:34 PM
  • Hi Maulik,

    First of all you told about the row count , how many column do you have in your table? (it will decrease/increase your query performance)

    For the indexes , you need to create your indexes for your integer values, such as EmployeeID, DateID etc.. Not for string data types,

    engine is going to parse the query from the FROM part, after your WHERE conditions than GROUP BY , SELECT and finally ORDER BY statement, 

    so you need first to reorganize if it exists a join or more than one , you need to use where condition, and rebuild and recreate your indexes for your int data types columns,

     

    Best,

    Onur 


    Onur Onder
    Tuesday, October 19, 2010 12:12 AM
  • Hi Onur,

    Please don't confuse Maulik with those advices. Of course, indexes on the integer (or any narrow data types) performs better than on the varchar data types due the smaller row size although there is nothing wrong with the indexes on varchar colimns. Especially in the case when query/system benefits from those indexes.

    Number of columns also rarely affect query performance directly. Obviously there are the questions about covering indexes, memory grants, hash and sort in tempdb due incorrect row size estimation and so on although I doubt it currently affects Maulik' system.

    We need to see the actual query and execution plan to be more specific.



    Thank you!

    My blog: http://aboutsqlserver.com

    Tuesday, October 19, 2010 12:27 AM
  • This is my Table Structure

     

    CREATE TABLE NAVHISTORY
    (
        NavHistoryIndex        INT,
        CompanyIndex        INT,   
        SchemeIndex            INT,
        NavHistoryDate        DateTime,
        NavValue            Decimal(18,2)
    )

    i created Three Index

    1) NavHistoryDate

    2) SchemeIndex

    3) NavHistoryDate,SchemeIndex (Combine Index)

    all are non-clustered index

    in my query i used this table with inner join statement with SchemeMaster Table's SchemeIndex with my NavHistory Table's SchemeIndex and it's taking much more time to execute the query

     

    this is one of my query which is frequently used

    UPDATE #tmpBlackday SET LastClose = (SELECT TOP 1 NAVHISTORY.NavValue FROM NAVHISTORY INNER JOIN #tmpLastDate ON NAVHISTORY.SchemeIndex = #tmpLastDate.SchemeIndex AND NAVHISTORY.NavDate=#tmpLastDate.LastNav WHERE NAVHISTORY.SchemeIndex = #tmpBlackday.SchemeIndex ORDER BY NavDate DESC)


    Maulik A. Dusara Tech Lead.
    Tuesday, October 19, 2010 6:02 AM
  • What if you add WHERE condition like

    UPDATE #tmpBlackday SET LastClose = (SELECT TOP 1 NAVHISTORY.NavValue FROM NAVHISTORY INNER JOIN #tmpLastDate ON NAVHISTORY.SchemeIndex = #tmpLastDate.SchemeIndex AND NAVHISTORY.NavDate=#tmpLastDate.LastNav WHERE NAVHISTORY.SchemeIndex = #tmpBlackday.SchemeIndex ORDER BY NavDate DESC)

    WHERE EXISTS (SELECT *FROM NAVHISTORY INNER JOIN #tmpLastDate ON NAVHISTORY.SchemeIndex = #tmpLastDate.SchemeIndex AND NAVHISTORY.NavDate=#tmpLastDate.LastNav WHERE NAVHISTORY.SchemeIndex = #tmpBlackday.SchemeIndex )

    >in my query i used this table with inner join statement with SchemeMaster >Table's SchemeIndex with my NavHistory

    I see you are joing #tmpLastDate  table, how many rows does it contain?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 19, 2010 7:24 AM
  • What if you add WHERE condition like

    UPDATE #tmpBlackday SET LastClose = (SELECT TOP 1 NAVHISTORY.NavValue FROM NAVHISTORY INNER JOIN #tmpLastDate ON NAVHISTORY.SchemeIndex = #tmpLastDate.SchemeIndex AND NAVHISTORY.NavDate=#tmpLastDate.LastNav WHERE NAVHISTORY.SchemeIndex = #tmpBlackday.SchemeIndex ORDER BY NavDate DESC)

    WHERE EXISTS (SELECT *FROM NAVHISTORY INNER JOIN #tmpLastDate ON NAVHISTORY.SchemeIndex = #tmpLastDate.SchemeIndex AND NAVHISTORY.NavDate=#tmpLastDate.LastNav WHERE NAVHISTORY.SchemeIndex = #tmpBlackday.SchemeIndex )

    That would change what the query does. Not a good idea!

     

    >in my query i used this table with inner join statement with SchemeMaster >Table's SchemeIndex with my NavHistory

    I see you are joing #tmpLastDate  table, how many rows does it contain?

    Apart from the size of this temporary table, I would also like to know the layout. How is it created?

    And another question - I see no PRIMARY KEY in the CREATE TABLE statement. Did you forget to post it, or does the table not have a PRIMARY KEY?


    -- Hugo Kornelis, SQL Server MVP
    Wednesday, October 20, 2010 9:14 PM
  • Hi Hugo

    >That would change what the query does. Not a good idea!

    I think we will know for sure after the OP will post sample data+ an expected result


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, October 21, 2010 6:12 AM