SQL Server Performance

Query performance

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by virmahi, Dec 29, 2008.

  1. virmahi New Member

    Hi, I am using temp tables to replace some inner queries to speed up the performance.
    The table in inner query has around 12 lakh records. Will it be better to create temp table and then insert all the huge records then ftech from temporary table. Even when I am selecting some records from that big table its taking 1 minute and 10 seconds.The inner query is as below :
    SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,Q.CLOFFCD,Q.COMPCODE,
    Q.CLGENDATE,Q.CLCUSTCUSTCD FROM CLEXEC Q
    INNER JOIN MROUTE R ON Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE
    The table 'CLEXEC' has 12 lakh records and table 'MROUTE' has 1300 records.
    Please advice.
  2. ndinakar Member

    Do you have indexes on these columns Q.RTCODE, R.RTCODE , Q.COMPCODE, R.COMPCODE , Q.CLOFFCD, R.OFFCODE ?
  3. virmahi New Member

    yes, there is a clustered index on these three columns
  4. satya Moderator

    Have you observed HIGH CPU during execution of this query?
    Do you see any blocking during the execution?
    Excessive resource consumption is not restricted to CPU resources, but can also include I/O storage bandwidth and memory bandwidth.
    So there might be a full table scan during this query execution, in this case you must avoid full table scans by restricting the result set with a reasonable WHERE clause, they might not perform as expected if there is not an appropriate index supporting that particular query.
    Queries can also run slowly because of wait states for logical locks and for system resources that are blocking the query. The cause of the blocking can be a poor application design, bad query plans, the lack of useful indexes, and an SQL Server instance that is improperly configured for the workload.

Share This Page