SQL Server Performance

URGENT --Pls Help

Discussion in 'Performance Tuning for DBAs' started by sql_dba, Apr 29, 2006.

  1. sql_dba New Member

    Hi,<br />TableX<br />[Field1] [bigint] NOT NULL PRIMARY KEY ,<br />[Field2] [varchar] (1<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NOT NULL ,<br />[Field3] [char] (3) NOT NULL ,<br />[Field4] [char] (3) NOT NULL <br /> <br /><br />I have a table with above structure and having 80 Million records. I need to perform 3 validations.<br />I have created Non clustered COVERING INDEX on(Field1 ,Field2,Field3,Field4)<br />We need to go live with this on wednesday!!<br /> <br />1. I need to find any duplicate values of (Field1).If find write to error table.<br /><br />SELECT Field1, COUNT(*)<br />FROM TableX <br />GROUP BY Field1<br />HAVING COUNT(*) &gt;1<br /><br />2. <br />SELECT distinct SUBSTRING(Field2,4,4) <br />FROM TableX<br /><br />3. I need to perform some length validations.<br /><br />SELECT Field1,Field2,Field3 ,Field4<br />FROM tableX<br />WHERE (<br /> LEN(REPLACE(LTRIM(RTRIM(Field3)),' ',''))&lt;3<br />OR LEN(replace(LTRIM(RTRIM(Field4)),' ',''))&lt;3<br />OR LEN(LTRIM(RTRIM(Field2))) &lt; 16<br /> )<br />Could you pls pass your comments /suggestion on these queries. <br />Please let me know if they can be optimized ...<br /><br />Thanks in advance.<br /><br /><br /><br />
  2. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">TableX<br />[Field1] [bigint] NOT NULL PRIMARY KEY ,<br />[Field2] [varchar] (1<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NOT NULL ,<br />[Field3] [char] (3) NOT NULL ,<br />[Field4] [char] (3) NOT NULL<br /><br /><br />I have a table with above structure and having 80 Million records. I need to perform 3 validations.<br />I have created Non clustered COVERING INDEX on(Field1 ,Field2,Field3,Field4)<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">If your primary key is clustered and you mentioned all tablex columns, there is not need for such index.<br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />1. I need to find any duplicate values of (Field1).If find write to error table.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">There is no need to do it, Field1 is primary key, so no duplicates are possible.<br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">2.<br />SELECT distinct SUBSTRING(Field2,4,4)<br />FROM TableX<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">What is the purpose of this test?<br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">3. I need to perform some length validations.<br /><br />SELECT Field1,Field2,Field3 ,Field4<br />FROM tableX<br />WHERE (<br />LEN(REPLACE(LTRIM(RTRIM(Field3)),' ',''))&lt;3<br />OR LEN(replace(LTRIM(RTRIM(Field4)),' ',''))&lt;3<br />OR LEN(LTRIM(RTRIM(Field2))) &lt; 16<br />)<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">field2 is varchar, so there is no need for rtrim.
  3. druer New Member

    quote:1. I need to find any duplicate values of (Field1).If find write to error table.

    SELECT Field1, COUNT(*)
    FROM TableX
    GROUP BY Field1
    HAVING COUNT(*) >1
    Having an index on all 4 of the fields will just slow things down because more pages will have to be read. Any index on just Field1 would be the best. Per Mirko, if you defined the tabe as you have indicated there couldn't be any duplicates so not sure of the purpose.


    quote:2. SELECT distinct SUBSTRING(Field2,4,4)
    FROM TableX
    That should work fine. However, for performance an index that contains just field2 would be the optimal case. As it will have to do a scan table or scan index based solely on what you identifed above.


    quote:3. I need to perform some length validations.

    SELECT Field1,Field2,Field3 ,Field4
    FROM tableX
    WHERE (
    LEN(REPLACE(LTRIM(RTRIM(Field3)),' ',''))<3
    OR LEN(replace(LTRIM(RTRIM(Field4)),' ',''))<3
    OR LEN(LTRIM(RTRIM(Field2))) < 16
    )
    The Len function already resolves right side spaces for CHAR fields, so that will be redundant and by doing the trimming you might cause the optimizer to do a table scan instead of an index scan using the index you described. The index you mentioned would not be ideal for the first query (if you really need to run it) because it will be longer than need be just for field1, and won't be ideal for this query since field1 is in the select clause, but not in the criteria. If you created an index on just field1, and then one on field2, field3, field4, field1 it would work better for this case.

    If any of these are just for giggles/grins before going live then you will probably want to remove the indexes and create indexes that are needed for the application and not slow its performance down by having unnecessary indexes.

    Hope it helps,
    Dalton

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.
  4. sql_dba New Member

    Thanks guys!
    I forgot mention few things. I am Loading data into temptable and creating index and performing these validations . I do not have any primary key on temp table. All these things are automated . IF any validations fail I need to put into some Error and send it customer sevices folks .. On fly if I create PK and if it failed for DUP key reason I will not be able to capture or what key it got dupicate. So.. I did in this way...

Share This Page