sql - Methods to Accelerate Read From a Large Table -

I try to log everything in SQL, so add a table called log Think and add to it There is every log table:

• ID UNIQUEIDENTIFIER - PK
• NETWORK
• Information: XML
• UniqueID BIGINT

  I log in as everything: login, check permissions, see page , Obs I used to think that some of the log restoration implementation is required, so some log records are re-instated, none of these, log < / Code> The table is about 8 lakh records, but the recordable record is about 200 thousand, so every time we need to restore, we need to choose between 8 lakhs, then I decide to add a new table and this new rythm To add a log of comfortably: log_restore :

  • ID UNIQUEIDENTIFIER
  • logad DATETIME
  • IP NVARCHAR
  • Action NVARCHAR
  • info > XML
  • UniqueID BIGINT - PK

    OK when I need to log in every thing is fine.

    But when I need to look at logs: get all the records from the log process and merge it with the log_restore table.

    So I need to speed up the process There is no effect when it is inserted (it does not slow down) , this is my opinion:

    1. When adding it to log_restore , add it to the logs table (hence there is no need of the union)

       Create a view with this selection command

    2. Add simple datatype columns instead of XML

    3. Normal Add cluster pk to datatype column B IGINT

       What are your thoughts? any suggestion?

       In general, a small space should be used as possibly possible; This helps greatly reduce the disk while executing the query and requires less time than the smaller data!

       The following tuning columns can be done:

       • Use non-empty columns (decreases storage space, decreases the number of
       • IP
       • / Code> Address should not be stored as NVHAR; if you are accumulating IPv4 addresses, then 4 bytes will be sufficient (Binaries (4)) 18 bytes (Verbenary (16) for IPv6 support ) Is required, meanwhile The DHARR will need 30 bytes for IPv4 and 78 bytes for IPv6 ... (Search the web for Inset_Neto, Inset_Atten, Inset_Nontop, Inset_Pton, to find out how to switch between binary and string addressing)
       • Instead of storing similar data in two separate tables, type restitutional type column BIT indicates that a log entry or re
       • The information column is correct: it would be better to use text or NTN data type
       • action Instead of using an NVRAR type for you, you have all possible actions in a verb {/ code> table (assuming they are in a finite number), and they are an integer foreign key (as small as , The better)

         Index optimization is also very important Is important. Use an index on multiple columns if your query tests multiple columns at the same time. For example, if you select all the rows of comfort according to a certain amount of time related to a particular IP, then it will greatly increase the speed of the query:

           Nonclassed index IX_IndexName Log on to ASC, IP ASC, LogiAtt ASC)   
          If you need to retrieve all the recordable rows from the IP address according to a specific action, In the method, such as index should be selected:  
           logs Anolstrd Index IX_IndexName make (IP ASC Action ASC, Logdiatt ASC)   
          etc.  
          To be honest, in order to properly optimize, I really need to see your full SQL query ...